Imagine the following use case: You have a table t1, and based on it’s contents you create another table t2. What you want to achieve is: If table t1 gets dropped, you want to have table t2 to be dropped automatically. Can you do something like this in PostgreSQL? Short answer is yes, you have several options here. Depending on what exactly you want to achieve, one or the other option might be better for your use case.

Lets start with a little demo setup. One really simple table and a second one which uses the content of the first one to be created:

postgres=# create table t1 ( a int, b text );
CREATE TABLE
postgres=# insert into t1 select i, i::text from generate_series(1,100) i;
INSERT 0 100
postgres=# create table t2 as select count(*) from t1;
SELECT 1

Is there anything we can do, to drop the “t2” table automatically if table “t1” is dropped? The only option you have with this setup, is to use an event trigger. Using that you can create a trigger which fires, as soon as an object in the database gets dropped.

The trigger function to achieve this is quite simple:

postgres=# CREATE OR REPLACE FUNCTION f_drop_table_t2()
           RETURNS event_trigger LANGUAGE plpgsql 
AS 
$$
DECLARE
    tables_dropped record;
BEGIN
    FOR tables_dropped IN SELECT * FROM pg_event_trigger_dropped_objects()
    LOOP
      IF ( tables_dropped.object_name = 't1'
           AND
           tables_dropped.original )
      THEN
         EXECUTE 'drop table public.t2';
      END IF;
    END LOOP;
END;
$$;

The function pg_event_trigger_dropped_objects() returns a record which contains a list of all objects which have been dropped. The function above does not only check the name of the table but also the “original” property. This is required to capture only the base table and not any underlying system objects.

Now that we have the trigger function we can create the event trigger:

postgres=# CREATE EVENT TRIGGER etrg_drop_table_t2
           ON sql_drop
           EXECUTE FUNCTION f_drop_table_t2();    
CREATE EVENT TRIGGER

From now on, as soon as we drop the “t1” table, the “t2” table will also be dropped:

postgres=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
 public | t2   | table | postgres
(2 rows)

postgres=# drop table t1;
DROP TABLE
postgres=# \d
Did not find any relations.
postgres=# drop function f_drop_table_t2() cascade;
NOTICE:  drop cascades to event trigger etrg_drop_table_t2
DROP FUNCTION

As stated earlier above, there are other solutions for this requirement. By using a materialized view instead of a table for “t2”, we create a dependency from “t1” to “t2”:

postgres=# create table t1 ( a int, b text );
CREATE TABLE
postgres=# insert into t1 select i, i::text from generate_series(1,100) i;
INSERT 0 100
postgres=# create materialized view t2 as select count(*) from t1 with data;
SELECT 1
postgres=#

As PostgreSQL knows about this dependency, it will not let us drop “t1” by default:

postgres=# drop table t1;
ERROR:  cannot drop table t1 because other objects depend on it
DETAIL:  materialized view t2 depends on table t1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

By using “CASCADE” we can also get rid of “t2” automatically when we drop t1:

postgres=# drop table t1 cascade;
NOTICE:  drop cascades to materialized view t2
DROP TABLE
postgres=# \d
Did not find any relations.

The same concept can be used with a standard view:

postgres=# create table t1 ( a int, b text );
CREATE TABLE
postgres=# create view t2 as select count(*) from t1;
CREATE VIEW
postgres=# drop table t1;
ERROR:  cannot drop table t1 because other objects depend on it
DETAIL:  view t2 depends on table t1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
postgres=# drop table t1 cascade;
NOTICE:  drop cascades to view t2
DROP TABLE
postgres=# \d
Did not find any relations.
postgres=#

You might think, that a foreign key will be a solution for this use case as well, but it is not:

postgres=# create table t1 ( a int primary key, b text );
CREATE TABLE
postgres=# create table t2 ( a int references t1(a), b int );
CREATE TABLE
postgres=# drop table t1;
ERROR:  cannot drop table t1 because other objects depend on it
DETAIL:  constraint t2_a_fkey on table t2 depends on table t1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
postgres=# drop table t1 cascade;
NOTICE:  drop cascades to constraint t2_a_fkey on table t2
DROP TABLE
postgres=# \d t2
                 Table "public.t2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          |

This will only drop the constraint, but not the table.

Post Views: 2,107
Thumbnail [60x60]
by
Daniel Westermann