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:

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;

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.

Let’s check the dependency type:

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

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

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

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.

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

Prerequisites

The pg_depend table

Dependency categories

DEPENDENCY_NORMAL (n)

DEPENDENCY_AUTO (a)

DEPENDENCY_INTERNAL (i)

DEPENDENCY_EXTENSION (e)

DEPENDENCY_PARTITION_PRI (P) and DEPENDENCY_PARTITION_SEC (S)

Managing nested dependencies

Nested views

Nested foreign keys

Generate a dependent matrix

Generate CREATE foreign keys

Generate DROP foreign keys

Considerations

User dependencies

Cleanup

Conclusion

About the authors

Binary logging optimizations in Amazon Aurora MySQL version 3

Discover and visualize graph schemas in Amazon Neptune

Enforce row-level security with the RDS Data API

LEAVE A REPLY Cancel reply

Most Popular

The overwhelmed person’s guide to Google Cloud: week of May 9

Binary logging optimizations in Amazon Aurora MySQL version 3

Collaborative ML research projects within a single cloud environment

How to strengthen supply chain security with GKE Security Posture

Recent Comments

EDITOR PICKS

Exploring the Click Element Variable in Google Tag Manager

How to track events with Google Tag Manager and Google Analytics

Data Layer Variable in GTM: What, Why, and Where?

POPULAR POSTS

Minimize your ecommerce data TCO with Local SSD and Isima’s bi(OS)

Run automatic model tuning with Amazon SageMaker JumpStart

Monitor deadlocks in Amazon RDS for SQL Server and set notifications using Amazon CloudWatch

POPULAR CATEGORY