Managing object dependencies in PostgreSQL: Removing dependent objects (Part2)

In PostgreSQL, object binding (or dependencies) encompasses the connections existing among various database elements. These interdependencies hold significant importance when it comes to the management and modification of objects within the database. They ensure that adjustments made to one object don’t inadvertently disrupt other dependent objects.

This series is divided into two posts. In the Managing object dependencies in PostgreSQL – Overview and helpful inspection queries (Part 1) post, we introduced object dependencies and discussed various types of dependencies with examples. We also discussed the catalog tables and queries that help you find the dependencies based on which you can take appropriate actions. In this post, we dive deep into the techniques for managing object dependencies in PostgreSQL. We explore how to use DROP and ALTER statements to handle dependencies and discuss best practices for avoiding potential problems.

Prerequisites

To follow along with this post, complete the following prerequisites:

Create an Amazon Aurora PostgreSQL-Compatible Edition cluster or Amazon Relational Database Service (Amazon RDS) for PostgreSQL instance if you don’t already have one. For instructions, refer to Create an Amazon Aurora PostgreSQL-Compatible DB cluster or Create a PostgreSQL DB instance, respectively.
Create an Amazon Elastic Compute Cloud (Amazon EC2) instance to install the PostgreSQL client to access the Aurora PostgreSQL or RDS for PostgreSQL instance. For instructions, refer to Create your EC2 resources and launch your EC2 instance. Or you can set up connectivity between your RDS database and EC2 compute instance in one click.
On Amazon Linux 2023, you can use the following commands to download the psql command line tool to run the SQL statements in the blog:
```
sudo dnf install postgresql15.x86_64
```

The pg_depend table

To grasp PostgreSQL dependencies, we need to start with the pg_depend system catalog table. This table stores information about the relationships between various database objects, allowing you to understand which objects depend on others. The following table contains a breakdown of the columns in the pg_depend table.

Name	Type	References	Description
`classid`	OID	`pg_class.oid`	The object ID of the system catalog the dependent object is in.
`objid`	OID	any OID column	The OID of the specific dependent object.
`objsubid`	INT4		For a table column, this is the column number. For other object types, this is set to zero.
`refclassid`	OID	`pg_class.oid`	The OID of the system catalog the referenced object is in.
`refobjid`	OID	any OID column	The OID of the specific referenced object.
`refobjsubid`	INT4		Similar to objsubid; used for table columns.
`deptype`	CHAR		A code defining the specific semantics of this dependency relationship.

Let’s dive deep into these columns:

classid – The classid column represents the OID of the system catalog where the dependent object is listed. For instance, for a schema, classid will be set to pg_namespace, whereas for a table or a view, it will be pg_class. This column helps you identify the category to which the dependent object belongs.
objid – The objid column contains the OID of the specific dependent object. It’s stored in the relation mentioned in the classid column. For example, if you’re dealing with a table, objid will reference to that table’s OID.
objsubid – The objsubid column is used only when the dependency involves a table’s column. In such cases, it stores the column number. For other object types, it’s set to zero.
refclassid and refobjid – These columns, similar to their counterparts, classid and objid, indicate the system catalog and the specific referenced object, respectively. They help establish the dependencies between objects.
deptype – The deptype column holds a code defining the semantics of the dependency relationship. PostgreSQL uses various codes (as explained in “Dependency categories” section) to categorize dependencies, and understanding these codes is crucial. We provide examples in the next section.

Dependency categories

Here are some basic examples of the codes to categorize dependencies.

DEPENDENCY_NORMAL (n)

This indicates a normal relationship between separately created objects. The dependent object can be dropped without affecting the referenced object. The referenced object can only be dropped by specifying CASCADE, which will also drop the dependent object. An example of this is a table column’s normal dependency on its data type.

The following an example of a normal dependency:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE person (
    name text,
    current_mood mood
);

In this example, the current_mood column in the person table has a normal dependency on the mood type. If you try to drop the mood type, PostgreSQL will prevent you from doing so because it’s being used by the person table. However, if you use the CASCADE option, it will drop both the mood type and the person table.

To demonstrate this, let’s attempt to drop the mood type without using CASCADE:

postgres=# DROP TYPE mood;
ERROR:  cannot drop type mood because other objects depend on it
DETAIL:  column current_mood of table person depends on type mood
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
postgres=#

PostgreSQL will return an error, indicating that the mood type is still being used by the person table.

Now, let’s try to drop the mood type with the CASCADE option:

postgres=# DROP TYPE mood CASCADE ;
NOTICE:  drop cascades to column current_mood of table person
DROP TYPE
postgres=#

This time, PostgreSQL will successfully drop the mood type and also drop the person table because of the normal dependency between the person table and the mood type.

DEPENDENCY_AUTO (a)

In this case, the dependent object can be dropped separately from the referenced object, but it should be automatically dropped if the referenced object is dropped, regardless of RESTRICT or CASCADE mode. For instance, a named constraint on a table is made auto-dependent on the table, ensuring it’s removed if the table is dropped.

The following is an example of an automatic dependency.

First, create a table with a named constraint:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    CONSTRAINT age_check CHECK (age >= 18)
);

Check the pg_constraint system catalog for the constraint:

postgres=# SELECT conname, connamespace::regnamespace, conrelid::regclass FROM pg_catalog.pg_constraint WHERE conname = 'age_check';
  conname  | connamespace | conrelid
-----------+--------------+----------
 age_check | public       | students
(1 row)

In this case, the age_check constraint is an automatic dependency on the students table. If we drop this table, the age_check constraint will be automatically dropped as well, regardless of whether we use RESTRICT or CASCADE mode:

postgres=# DROP TABLE students ;
DROP TABLE

Now the table and its constraint age_check are both dropped. If we try to query this constraint from the pg_constraint system catalog, we find that it no longer exists:

postgres=# SELECT conname, connamespace::regnamespace, conrelid::regclass FROM pg_catalog.pg_constraint WHERE conname = 'age_check';
 conname | connamespace | conrelid
---------+--------------+----------
(0 rows)

This query returns no rows, confirming that the age_check constraint was automatically dropped when the students table was dropped.

DEPENDENCY_INTERNAL (i)

This indicates that the dependent object was created as part of the referenced object’s creation and is an internal part of its implementation. Dropping the dependent object is disallowed, and dropping the referenced object will propagate to drop the dependent object, with or without CASCADE. An example is a trigger created to enforce a foreign key constraint.

The following code example demonstrates an internal dependency between a foreign key constraint and its trigger.

Create the teachers table:

CREATE TABLE teachers (
  teacher_id SERIAL PRIMARY KEY,
  name VARCHAR(50)
);

Create the students table with a foreign key constraint referencing teachers:

CREATE TABLE students (
  student_id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  teacher_id INTEGER,
  FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);

In this example, the teacher_id column in the students table has a foreign key constraint referencing the teacher_id column in the teachers table. PostgreSQL automatically creates a trigger to enforce this foreign key constraint. This trigger is an example of an internal dependency (DEPENDENCY_INTERNAL or ‘i‘) because it was created as part of the constraint’s creation and is an integral part of its implementation.

postgres=# select tgrelid::regclass, tgname, tgfoid::regproc from pg_catalog.pg_trigger where tgrelid ='students'::regclass;
 tgrelid  |            tgname            |       tgfoid
----------+------------------------------+---------------------
 students | RI_ConstraintTrigger_c_16435 | "RI_FKey_check_ins"
 students | RI_ConstraintTrigger_c_16436 | "RI_FKey_check_upd"
(2 rows)

You can’t directly drop these internal triggers:

postgres=# DROP TRIGGER RI_ConstraintTrigger_c_16435 ON students;
ERROR:  trigger "ri_constrainttrigger_c_16435" for table "students" does not exist
postgres=# DROP TRIGGER RI_ConstraintTrigger_c_16436 ON students;
ERROR:  trigger "ri_constrainttrigger_c_16436" for table "students" does not exist

However, if you drop the foreign key constraint, these triggers will be dropped automatically:

postgres=# ALTER TABLE students DROP CONSTRAINT students_teacher_id_fkey ;
ALTER TABLE
Time: 1.012 ms
postgres=# select tgrelid::regclass, tgname, tgfoid::regproc from pg_catalog.pg_trigger where tgrelid ='students'::regclass;
 tgrelid | tgname | tgfoid
---------+--------+--------
(0 rows)

DEPENDENCY_EXTENSION (e)

In this scenario, the dependent object is a member of the extension that is the referenced object. The dependent object can only be dropped via DROP EXTENSION on the referenced object. Functionally, this type of dependency acts like an internal dependency, but it’s kept separate for clarity.

The following code is an example of creating an extension and checking its dependency type:

CREATE EXTENSION hstore;

postgres=# SELECT
     classid::regclass,
     objid::regproc,
     refclassid::regclass,
     deptype
 FROM
     pg_catalog.pg_depend
 WHERE
     deptype = 'e'
     AND refobjid IN (
         SELECT
             oid
         FROM
             pg_catalog.pg_extension
         WHERE
             extname = 'hstore')
 ORDER BY
     1
 LIMIT 10;
 classid |        objid        |  refclassid  | deptype
---------+---------------------+--------------+---------
 pg_type | 16438               | pg_extension | e
 pg_type | 16522               | pg_extension | e
 pg_proc | hstore_send         | pg_extension | e
 pg_proc | hstore_out          | pg_extension | e
 pg_proc | hstore_version_diag | pg_extension | e
 pg_proc | fetchval            | pg_extension | e
 pg_proc | slice_array         | pg_extension | e
 pg_proc | hstore_recv         | pg_extension | e
 pg_proc | hstore_in           | pg_extension | e
 pg_proc | slice               | pg_extension | e
(10 rows)

In this example, we first create an extension named hstore. The second command is a query that retrieves the dependency type (deptype) for the hstore extension from the pg_depend table. This will return 'e', indicating an extension dependency.

DEPENDENCY_PARTITION_PRI (P) and DEPENDENCY_PARTITION_SEC (S)

These represent primary and secondary partition dependencies in PostgreSQL. The dependent object is created as part of the referenced object and must not be dropped unless one of the referenced objects is dropped. Primary and secondary dependencies behave identically, but the primary is preferred for error messages. Partition dependencies are added to normal dependencies, simplifying ATTACH/DETACH PARTITION operations.

The following is an example to create a partitioned table with child tables and check the dependencies:

CREATE TABLE orders (
  id SERIAL,
  product_id INTEGER,
  quantity INTEGER,
  price NUMERIC,
  order_date DATE
) PARTITION BY RANGE (order_date);


CREATE TABLE orders_y2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE INDEX idx_orders ON orders(id);

In this example, the orders_y2023 table is a partition of the orders table and an index created on the orders table created an index on orders_y2023 automatically.

postgres=# select * from pg_catalog.pg_indexes where tablename in ('orders', 'orders_y2020');
 schemaname |  tablename   |         indexname         | tablespace |                                       indexdef
------------+--------------+---------------------------+------------+--------------------------------------------------------------------------------------
 public     | orders       | idx_orders                |            | CREATE INDEX idx_orders ON ONLY public.orders USING btree (id)
 public     | orders_y2023| orders_y2020_id_idx |            | CREATE INDEX orders_y2020_id_idx ON public.orders_y2023 USING btree (id)
(2 rows)

Let’s check the dependency type:

postgres=# select classid::regclass, objid::regclass, refclassid::regclass, refobjid::regclass, deptype from pg_catalog.pg_depend where deptype ='P';
 classid  |           objid           | refclassid |  refobjid  | deptype
----------+---------------------------+------------+------------+---------
 pg_class | orders_y2023_id_idx | pg_class   | idx_orders | P
(1 row)
postgres=# select classid::regclass, objid::regclass, refclassid::regclass, refobjid::regclass, deptype from pg_catalog.pg_depend where deptype ='S';
 classid  |           objid           | refclassid |   refobjid   | deptype
----------+---------------------------+------------+--------------+---------
 pg_class | orders_y2023_id_idx | pg_class   | orders_y2023 | S
(1 row)

The primary and secondary dependencies are the parent table (orders) and the partition child (orders_y2023).

Managing nested dependencies

Nested dependencies in PostgreSQL occur when one database object depends on another, which in turn depends on another, creating a chain of dependencies. This is commonly seen with views and foreign keys. When working with nested dependencies, it’s essential to understand the full chain of dependencies to avoid disrupting database functionality. In the following sections, we discuss nested views, nested foreign keys, and options to deal with foreign key dependencies.

Nested views

Let’s take an example of nested views. Nested views are views that are based on other views.

Create the view v_orders based on the orders table:

CREATE VIEW v_orders AS
SELECT id, product_id, quantity, price, order_date
FROM orders;

Create the second view v_orders_summary based on the first view:

CREATE VIEW v_orders_summary AS
SELECT product_id, SUM(quantity) AS total_quantity, SUM(quantity * price) AS total_revenue
FROM v_orders
GROUP BY product_id;

Create the second view v_orders_summary_history based on the first view:

CREATE VIEW v_orders_summary_history AS
SELECT product_id, total_quantity, total_revenue
FROM v_orders_summary;

Now, if you try to drop or modify the v_orders view, it will throw an error that there are other dependent objects:

postgres=# DROP TABLE orders;
ERROR:  cannot drop table orders because other objects depend on it
DETAIL:  view v_orders depends on table orders
view v_orders_summary depends on view v_orders
view v_orders_summary_history depends on view v_orders_summary
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
postgres=#

It clearly shows all the dependents related to the table, and it will be same with the view as well:

postgres=# DROP VIEW v_orders;
ERROR:  cannot drop view v_orders because other objects depend on it
DETAIL:  view v_orders_summary depends on view v_orders
view v_orders_summary_history depends on view v_orders_summary
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
postgres=#

You can use the following query to find the order of dependency for these views to analyze:

postgres=# WITH RECURSIVE vlist AS (
    SELECT
        c.oid::regclass AS view_name
    FROM
        pg_catalog.pg_class c
    WHERE
        c.relname = 'orders'
    UNION ALL SELECT DISTINCT
        r.ev_class::regclass AS view_name
    FROM
        pg_catalog.pg_depend d
        JOIN pg_catalog.pg_rewrite r ON (r.oid = d.objid)
        JOIN vlist ON (vlist.view_name = d.refobjid)
    WHERE
        d.refobjsubid != 0
)
SELECT
    *
FROM
    vlist;

Following is the output of the above query:

        view_name
--------------------------
 orders
 v_orders
 v_orders_summary
 v_orders_summary_history
(4 rows)

Nested foreign keys

Foreign keys represent a link between two tables. When a table has a foreign key to another table, it creates a dependency. If the referenced table has further dependencies, then those are indirectly dependencies of the first table. This can affect operations like deleting records or altering tables, because changes need to be cascaded through the chain of dependencies to maintain data integrity.

Let’s create an example to show the nested foreign key dependencies.

Create the primary key table pk_1:

CREATE TABLE pk_1 (
    id int,
    eid int PRIMARY KEY,
    ename varchar,
    dname varchar
);

Create table pk_fk_1 references to the primary key of pk_1:

CREATE TABLE pk_fk_1 (
    id int,
    pid int PRIMARY KEY,
    eid int
);

--references pk_1(eid));
ALTER TABLE pk_fk_1
    ADD CONSTRAINT fk_for_pk_fk_1 FOREIGN KEY (eid) REFERENCES pk_1 (eid);

Create table pk_fk_2 references to the primary key of the pk_fk_1 table:

CREATE TABLE pk_fk_2 (
    id int,
    fid int PRIMARY KEY,
    pid int
);

--references pk_fk_1(pid));
ALTER TABLE pk_fk_2
    ADD CONSTRAINT fk_for_pk_fk_2 FOREIGN KEY (pid) REFERENCES pk_fk_1 (pid);

Before altering the reference columns or deleting data from these columns or dropping keys you need to analyze the dependencies because you can’t directly perform the operations on these columns:

INSERT INTO pk_1
    VALUES (generate_series(1, 50), generate_series(1, 50), 'ename' || generate_series(1, 50), 'dname' || generate_series(1, 50));

INSERT INTO pk_fk_1
    VALUES (generate_series(10, 60), generate_series(30, 80), generate_series(1, 50));

INSERT INTO pk_fk_2
    VALUES (generate_series(1, 50), generate_series(1, 50), generate_series(31, 80));

postgres=# DELETE FROM pk_1;
ERROR:  update or delete on table "pk_1" violates foreign key constraint "fk_for_pk_fk_1" on table "pk_fk_1"
DETAIL:  Key (eid)=(1) is still referenced from table "pk_fk_1".

postgres=# DELETE FROM pk_fk_1;
ERROR:  update or delete on table "pk_fk_1" violates foreign key constraint "fk_for_pk_fk_2" on table "pk_fk_2"
DETAIL:  Key (pid)=(31) is still referenced from table "pk_fk_2".

postgres=# ALTER TABLE pk_1 DROP CONSTRAINT pk_1_pkey;
ERROR:  cannot drop constraint pk_1_pkey on table pk_1 because other objects depend on it
DETAIL:  constraint fk_for_pk_fk_1 on table pk_fk_1 depends on index pk_1_pkey
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

postgres=# ALTER TABLE pk_fk_1 DROP CONSTRAINT pk_fk_1_pkey ;
ERROR:  cannot drop constraint pk_fk_1_pkey on table pk_fk_1 because other objects depend on it
DETAIL:  constraint fk_for_pk_fk_2 on table pk_fk_2 depends on index pk_fk_1_pkey
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

1. You can generate a dependent matrix for each table and list the tables in the order of dependency. Then, prepare the DELETE operations in the same order.

Generate a dependent matrix

You can generate a dependent matrix using the following query (we use the pk_1 table as an example):

WITH RECURSIVE getall AS (
    SELECT DISTINCT
        ac.*,
        ac.table_name || '; ' AS hierarchy,
        ARRAY[ac.table_name] AS route,
        1 AS _level,
        FALSE AS cycle
    FROM
        allconst ac
    WHERE
        dep_table_name IS NULL
        AND table_schema = 'public' --optional if you need to narrow select
        AND table_name = 'pk_1'
        AND dep_table_name IS NULL
    UNION ALL
    SELECT
        c.*,
        g.hierarchy || c.table_name || '; ' AS hierarchy,
        g.route || c.table_name AS route,
        g._level + 1 AS _level,
        c.table_name = ANY (g.route) AS cycle
    FROM
        allconst c
        INNER JOIN getall g ON c.dep_table_schema = g.table_schema
            AND c.dep_table_name = g.table_name
    WHERE
        c.table_schema = 'public'
        AND c.table_name != g.table_name --limit 10
        AND CYCLE = 'false'
        --and c.table_name='pk_1'
),
allconst AS (
    SELECT
        ns.nspname AS table_schema,
        c.relname AS table_name,
        c.relkind AS rel_kind,
        con.conname AS constraint_name,
        con.contype,
        refns.nspname AS dep_table_schema,
        refc.relname AS dep_table_name,
        refc.relkind AS dep_rel_kind
    FROM
        pg_catalog.pg_class c
        JOIN pg_catalog.pg_namespace ns ON c.relnamespace = ns.oid
        LEFT JOIN pg_catalog.pg_constraint con ON c.oid = con.conrelid
        LEFT JOIN pg_catalog.pg_class refc ON con.confrelid = refc.oid
        LEFT JOIN pg_catalog.pg_namespace refns ON refc.relnamespace = refns.oid
    WHERE
        c.relkind = 'r' --only ordinary tables
        AND ns.nspname = 'public'
)
SELECT DISTINCT
    *
FROM
    getall
WHERE
    hierarchy LIKE 'pk_1;%'
    AND CYCLE = 'false'
ORDER BY
    _level,
    hierarchy DESC;

table_schema | table_name | rel_kind | constraint_name | contype | dep_table_schema | dep_table_name | dep_rel_kind |        hierarchy         |         route          | _level | cycle
--------------+------------+----------+-----------------+---------+------------------+----------------+--------------+--------------------------+------------------------+--------+-------
 public       | pk_1       | r        | pk_1_pkey       | p       |                  |                |              | pk_1;                    | {pk_1}                 |      1 | f
 public       | pk_fk_1    | r        | fk_for_pk_fk_1  | f       | public           | pk_1           | r            | pk_1; pk_fk_1;           | {pk_1,pk_fk_1}         |      2 | f
 public       | pk_fk_2    | r        | fk_for_pk_fk_2  | f       | public           | pk_fk_1        | r            | pk_1; pk_fk_1; pk_fk_2;  | {pk_1,pk_fk_1,pk_fk_2} |      3 | f
(3 rows)

It generates three levels of dependency matrix for the pk_1 table. The following is an example of the dependent order for the table:

pk_1;                  
pk_1; pk_fk_1;         
pk_1; pk_fk_1; pk_fk_2;

2. You can implement ON DELETE CASCADE foreign keys. In this case, the database takes care of the delete from all the dependent tables.

Generate CREATE foreign keys

The following query generates SQLs for creating the foreign keys with ON DELETE CASCADE and the NOT VALID option. We use the NOT VALID option to skip validation because it’s already done for the current data and for new data, so validation will be enforced. It saves a lot of time for validation.

SELECT
    'alter table ' || nspname || '.' || relname || ' add constraint ' || conname || ' ' || CASE WHEN (constraint_def LIKE '%ON DELETE CASCADE%'
        AND constraint_def NOT LIKE '%DEFERRABLE INITIALLY DEFERRED%'
        AND constraint_def NOT LIKE 'NOT VALID') THEN
        constraint_def || ' NOT VALID;'
    WHEN (constraint_def LIKE '%DEFERRABLE INITIALLY DEFERRED%'
        AND constraint_def NOT ILIKE '%ON DELETE CASCADE%') THEN
        split_part(constraint_def, 'DEFERRABLE INITIALLY DEFERRED', 1) || ' ON DELETE CASCADE NOT VALID DEFERRABLE INITIALLY DEFERRED;'
    WHEN constraint_def LIKE '%ON DELETE CASCADE NOT VALID%' THEN
        constraint_def || ';'
    ELSE
        constraint_def || ' ON DELETE CASCADE NOT VALID;'
    END
FROM (
    SELECT
        nr.nspname,
        r.relname,
        conname,
        pg_catalog.pg_get_constraintdef(c.oid) AS constraint_def
    FROM
        pg_catalog.pg_constraint c,
        pg_catalog.pg_namespace nc,
        pg_catalog.pg_class r,
        pg_catalog.pg_namespace nr
    WHERE
        c.connamespace = nc.oid
        AND r.relnamespace = nr.oid
        AND (c.contype = 'f'::"char"
            AND c.conrelid = r.oid)
        AND r.relkind = 'r'::"char"
    GROUP BY
        r.relname,
        nr.nspname,
        c.oid,
        c.conname) a;


?column?
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 alter table public.pk_fk_1 add constraint fk_for_pk_fk_1 FOREIGN KEY (eid) REFERENCES pk_1(eid) ON DELETE CASCADE NOT VALID;
 alter table public.pk_fk_2 add constraint fk_for_pk_fk_2 FOREIGN KEY (pid) REFERENCES pk_fk_1(pid) ON DELETE CASCADE NOT VALID;
(2 rows)

Generate DROP foreign keys

The following query generates SQLs to drop the current foreign keys:

SELECT
    'alter table ' || nr.nspname || '.' || r.relname || ' drop constraint ' || string_agg(c.conname, ', drop constraint ') || ';'
FROM
    pg_catalog.pg_constraint c,
    pg_catalog.pg_namespace nc,
    pg_catalog.pg_class r,
    pg_catalog.pg_namespace nr
WHERE
    c.connamespace = nc.oid
    AND r.relnamespace = nr.oid
    AND (c.contype = 'f'::"char"
        AND c.conrelid = r.oid)
    AND r.relkind = 'r'::"char"
GROUP BY
    r.relname,
    nr.nspname;

                                  ?column?
----------------------------------------------------------------------------
 alter table public.pk_fk_1 drop constraint fk_for_pk_fk_1;
 alter table public.pk_fk_2 drop constraint fk_for_pk_fk_2;
(2 rows)

Run the DROP and ALTER operations to modify the foreign keys:

postgres=# alter table public.pk_fk_1 drop constraint fk_for_pk_fk_1;
ALTER TABLE
postgres=# alter table public.pk_fk_2 drop constraint fk_for_pk_fk_2;
ALTER TABLE
postgres=#  alter table public.pk_fk_1 add constraint fk_for_pk_fk_1 FOREIGN KEY (eid) REFERENCES pk_1(eid) ON DELETE CASCADE NOT VALID;
ALTER TABLE
postgres=# alter table public.pk_fk_2 add constraint fk_for_pk_fk_2 FOREIGN KEY (pid) REFERENCES pk_fk_1(pid) ON DELETE CASCADE NOT VALID;
ALTER TABLE

Let’s delete some rows from the pk_1 table and check if it’s cascaded to the other tables, pk_fk_1 and pk_fk_2:

postgres=# BEGIN;
BEGIN
postgres=*# select count(1) from pk_1;
 count
-------
    50
(1 row)

postgres=*# select count(1) from pk_fk_1;
 count
-------
    51
(1 row)

postgres=*# select count(1) from pk_fk_2;
 count
-------
    50
(1 row)

postgres=*# DELETE FROM pk_1 WHERE eid < 10;
DELETE 9
postgres=*# select count(1) from pk_1;
 count
-------
    41
(1 row)

postgres=*# select count(1) from pk_fk_1;
 count
-------
    42
(1 row)

postgres=*# select count(1) from pk_fk_2;
 count
-------
    42
(1 row)

The DELETE operation from pk_1 cascaded to the pk_fk_1 and pk_fk_2 tables, and nine rows are deleted from all three tables.

Considerations

This option has the following advantages:

You don’t need to generate a dependent matrix
You don’t need to take care of dependent deletion because the database will make sure of the order of deletion
You don’t need to manually delete table by table

However, it has the following disadvantages:

You need to recreate foreign keys that aren’t ON DELETE CASCADE.
A single DELETE operation on the parent table would delete from all the dependent tables in a single transaction. Therefore, you need to test and monitor replicas for any delay during the cleanups.
There is also an overhead due to the internal triggers created for foreign keys.

User dependencies

All the objects in this post are created through the test_dependent user. The following code shows what happens if you try to drop the user:

postgres=#drop user test_dependent ;
ERROR:  role "test_dependent" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema public
owner of type mood
owner of table person
owner of sequence students_id_seq
owner of table students
owner of sequence teachers_teacher_id_seq
owner of table teachers
owner of sequence orders_id_seq
owner of table orders
owner of table orders_y2023
owner of view v_orders
owner of view v_orders_summary
owner of view v_orders_summary_history
owner of table pk_1
owner of table pk_fk_1
owner of table pk_fk_2

You can’t drop the user unless you reassign the ownership of the objects to a different user or revoke if there are any other privileges.

You can use the following command to reassign the ownership of test_dependent to the postgres user:

postgres=#reassign OWNED BY test_dependent to postgres;
REASSIGN OWNED

postgres=#SELECT
     n.nspname AS schema_name,
     c.relname AS rel_name,
     CASE c.relkind
     WHEN 'r' THEN 'table'
     WHEN 'v' THEN 'view'
     WHEN 'm' THEN 'materialized view'
     WHEN 'i' THEN 'index'
     WHEN 'S' THEN 'sequence'
     WHEN 't' THEN 'TOAST table'
     WHEN 'f' THEN 'foreign table'
     WHEN 'p' THEN 'partitioned table'
     WHEN 'I' THEN 'partitioned index' END as "Type",
     pg_catalog.pg_get_userbyid(c.relowner) AS owner_name
   FROM pg_catalog.pg_class c
   JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind IN ('r','p','v','m','S','f','')
       AND n.nspname <> 'pg_catalog'
       AND n.nspname !~ '^pg_toast'
       AND n.nspname <> 'information_schema'
 UNION ALL

 -- functions (or procedures)
 SELECT
     n.nspname AS schema_name,
     p.proname,
     'p',
     pg_catalog.pg_get_userbyid(p.proowner)
   FROM pg_catalog.pg_proc p
   JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
   WHERE n.nspname <> 'pg_catalog'
       AND n.nspname !~ '^pg_toast'
       AND n.nspname <> 'information_schema';
 schema_name |         rel_name         |       Type        | owner_name
-------------+--------------------------+-------------------+------------
 public      | person                   | table             | postgres
 public      | students_id_seq          | sequence          | postgres
 public      | students                 | table             | postgres
 public      | teachers_teacher_id_seq  | sequence          | postgres
 public      | teachers                 | table             | postgres
 public      | orders_id_seq            | sequence          | postgres
 public      | pk_fk_1                  | table             | postgres
 public      | orders                   | partitioned table | postgres
 public      | orders_y2023             | table             | postgres
 public      | v_orders                 | view              | postgres
 public      | v_orders_summary         | view              | postgres
 public      | v_orders_summary_history | view              | postgres
 public      | pk_1                     | table             | postgres
 public      | pk_fk_2                  | table             | postgres
(14 rows)

Now try to drop the user:

postgres=#drop user test_dependent ;
ERROR:  role "test_dependent" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema public

postgres=#\dn+ public
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                  +|
        |                   | test_dependent=UC/pg_database_owner    |
(1 row)

As you see, there are still public schema privileges to test_dependent. You need to revoke these privileges to drop the user:

postgres=#revoke all on schema public from test_dependent ;
REVOKE
postgres=#\dn+ public
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
(1 row)

You can now successfully drop the user:

postgres=#drop user test_dependent ;
DROP ROLE

Cleanup

Create snapshots and Terminate EC2 and delete Amazon RDS instances after completing all testing to prevent unnecessary expenses.

Conclusion

In the initial segment of our series on object dependencies in PostgreSQL, we introduced the concept and explored various types of dependencies. In this second part of the blog, we delved into the PostgreSQL catalog pg_depend table, which manages dependency relationships among database objects. This information is crucial for DROP commands to identify objects that need to be dropped using DROP CASCADE or to prevent dropping in the DROP RESTRICT case.

Understanding PostgreSQL dependencies is essential for managing your database effectively. It helps you navigate complex relationships between objects and make informed decisions about object modification or deletion. Although tracking dependencies may seem intricate at first, it provides PostgreSQL with great flexibility and control.

If you have any questions or suggestions about this post, feel free to leave a comment. We hope the information we shared helps!

About the authors

Baji Shaik is a Sr. Database Consultant with AWS Professional Services, Global Competency Centre. He is a Database Migration Expert and has developed many successful database solutions addressing challenging business requirements for moving databases from on premises Oracle, SQL Server to Amazon RDS and Amazon Aurora PostgreSQL/MySQL. He is an eminent author, having written several books on PostgreSQL. A few of his recent works include “ Procedural Programming with PostgreSQL PL/pgSQL” “ PostgreSQL Configuration,” “ Beginning PostgreSQL on the Cloud,” and “ PostgreSQL Development Essentials.” Furthermore, he has delivered several conference and workshop sessions.

Wajid Ali Mir is a Database Consultant at AWS and works as a database migration specialist, helping and enabling customers to build highly available, cost-effective database solutions and migrate their database workloads to the AWS Cloud. Wajid works with AWS customers and partners to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using AWS.

Viswanatha Shastry Medipalli is a Consultant with the AWS ProServe team. His background spans a wide depth and breadth of expertise and experience in database migrations. He has architected and designed many successful database solutions addressing challenging business requirements. He has provided solutions using Oracle, SQL Server and PostgreSQL for reporting, BI, applications, and development support. He also has a good knowledge of automation, and orchestration. His focus area is homogeneous and heterogeneous migrations of on-premise databases to Amazon.

Yaser Raja is a Principal Strategic Tech Advisor with Professional Services team at Amazon Web Services. He works with customers to build scalable, highly available and secure solutions in AWS cloud. His focus area includes database migrations and modernization, and leveraging Generative AI capabilities to build innovative solutions for the customers.

AWS Database Blog