Re: Finding tables dropped by DROP TABLE CASCADE

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