Finding tables dropped by DROP TABLE CASCADE
Hi,
I'm working on implemeting query cache for pgpool-II. The query cache
must be deleted if related tables are dropped. Finding tables oids
from DROP TABLE t1, t2, t3... is easy. Problem is DROP TABLE
CASCADE. It seems there's no easy way to find table oids which will be
deleted by DROP TABLE CASCADE. Any idea?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
Just a suggestion, but...
Why not using an external wrapper function on reportDependentObjects in
dependency.c to find the list of Oids for a cascade deletion based on a list
of objects?
Isn't it possible?
Regards,
--
Michael Paquier
http://michael.otacoo.com
On Tue, Aug 16, 2011 at 8:10 PM, Tatsuo Ishii <ishii@postgresql.org> wrote:
I'm working on implemeting query cache for pgpool-II. The query cache
must be deleted if related tables are dropped. Finding tables oids
from DROP TABLE t1, t2, t3... is easy. Problem is DROP TABLE
CASCADE. It seems there's no easy way to find table oids which will be
deleted by DROP TABLE CASCADE. Any idea?
Presumably it would also need to invalidated if someone did ALTER
TABLE (which might recurse into unspecified children).
It sort of seems like what you want to do is snoop the sinval traffic...
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Presumably it would also need to invalidated if someone did ALTER
TABLE (which might recurse into unspecified children).
Good point. For DROP TABLE/ALTER TABLE, I need to take care of its chidren.
It sort of seems like what you want to do is snoop the sinval traffic...
It's hard for pgpool-II since there's no API in PostgreSQL for
that. Maybe I will look into the system catalog to find out
children. I'm not sure if I can deal with CASCADE by the same method
though.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
On Tue, Aug 16, 2011 at 8:52 PM, Tatsuo Ishii <ishii@postgresql.org> wrote:
Presumably it would also need to invalidated if someone did ALTER
TABLE (which might recurse into unspecified children).Good point. For DROP TABLE/ALTER TABLE, I need to take care of its chidren.
It sort of seems like what you want to do is snoop the sinval traffic...
It's hard for pgpool-II since there's no API in PostgreSQL for
that. Maybe I will look into the system catalog to find out
children. I'm not sure if I can deal with CASCADE by the same method
though.
It's possible, but not too easy.
Maybe we should have a special LISTEN channel that plays back (some
subset of? some decoded version of?) the sinval messaging. I bet the
pgAdmin guys would like an automated way of knowing when tables had
been created/dropped, too...
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 08/16/2011 08:52 PM, Tatsuo Ishii wrote:
Presumably it would also need to invalidated if someone did ALTER
TABLE (which might recurse into unspecified children).Good point. For DROP TABLE/ALTER TABLE, I need to take care of its chidren.
It sort of seems like what you want to do is snoop the sinval traffic...
It's hard for pgpool-II since there's no API in PostgreSQL for
that. Maybe I will look into the system catalog to find out
children. I'm not sure if I can deal with CASCADE by the same method
though.
Not sure how much it will help, but I have implemented the logic to drop
dependent tables and other objects from catalog info, in Pyrseas. The
relevant code is in
https://github.com/jmafc/Pyrseas/blob/master/pyrseas/dbobject/table.py
In particular, _from_catalog() at line 375 fetches the information using
the query and inhquery SELECTs just above it. Then in diff_map()
starting at line 641 it issues the SQL to drop the various dependent
objects and tables.
If it's mostly inherited tables that you're concerned, the inhquery and
the code dealing with inhstack should be helpful.
Joe