Обсуждение: 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...<br />Why not using an external wrapper function on reportDependentObjects in dependency.c to findthe list of Oids for a cascade deletion based on a list of objects?<br />Isn't it possible?<br clear="all" /><br />Regards,<br/>-- <br />Michael Paquier<br /><a href="http://michael.otacoo.com" target="_blank">http://michael.otacoo.com</a><br/>
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