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

Search Discussions

  • Michael Paquier at Aug 17, 2011 at 12:19 am
    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
  • Robert Haas at Aug 17, 2011 at 12:45 am

    On Tue, Aug 16, 2011 at 8:10 PM, Tatsuo Ishii 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
  • Tatsuo Ishii at Aug 17, 2011 at 12:51 am

    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
  • Robert Haas at Aug 17, 2011 at 1:10 am

    On Tue, Aug 16, 2011 at 8: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.
    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
  • Joe Abbate at Aug 17, 2011 at 2:01 am

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedAug 17, '11 at 12:09a
activeAug 17, '11 at 2:01a
posts6
users4
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase