Understanding object dependencies

In most databases, there will be dependencies between objects in the database. Sometimes, we need to understand these dependencies to figure out how to perform certain actions, such as modifying or deleting existing objects. Let's look at this in detail.

Getting ready

We'll use the following simple database to understand and investigate them:

1. Create two tables as follows:

   CREATE TABLE orders (
    orderid integer PRIMARY KEY
   );
   CREATE TABLE orderlines (
    orderid integer
   ,lineid smallint
   ,PRIMARY KEY (orderid, lineid)
   );

2. Now, we add a link between them to enforce what is known as referential integrity, as follows:

   ALTER TABLE orderlines ADD FOREIGN KEY (orderid)
   REFERENCES orders (orderid);

3. If we try to drop the referenced table, we get the following message:

   DROP TABLE orders;
   ERROR: cannot drop table orders because other objects depend on it
   DETAIL: constraint orderlines_orderid_fkey on table orderlines depends on table orders
   HINT: Use DROP ... CASCADE to drop the dependent objects too.

Be very careful! If you follow the hint, you may accidentally remove all the objects that have any dependency on the orders table. You might think that this would be a great idea, but it is not the right thing to do. It might work, but we need to ensure that it will work.

Therefore, you need to know what dependencies are present on the orders table, and then review them. Then, you can decide whether it is okay to issue the CASCADE version of the command, or whether you should reconcile the situation manually.

How to do it…

You can use the following command from psql to display full information about a table, the constraints that are defined upon it, and the constraints that reference it:

\d+ orders

You can also get specific details of the constraints by using the following query:

SELECT * FROM pg_constraint
WHERE confrelid = 'orders'::regclass;

The aforementioned queries only covered constraints between tables. This is not the end of the story, so read the There's more... section.

How it works…

When we create a foreign key, we add a constraint to the catalog table, known as pg_constraint. Therefore, the query shows us how to find all the constraints that depend upon the orders table.

There's more…

With Postgres, there's always a little more when you look beneath the surface. In this case, there's a lot more, and it's important.

We didn't discuss dependencies with other kinds of objects. Two important types of objects that might have dependencies on tables are views and functions.

Consider the following command:

DROP TABLE orders;

If you issue this, the dependency on any of the views will prevent the table from being dropped. So, you need to remove those views and then drop the table.

The story with function dependencies is not as useful. Relationships between functions and tables are not recorded in the catalog, nor is the dependency information between functions. This is partly due to the fact that most PostgreSQL procedural languages allow dynamic query execution, so you wouldn't be able to tell which tables or functions a function would access until it executes. That's only partly the reason because most functions clearly reference other tables and functions, so it should be possible to identify and store those dependencies. However, right now, we don't do that. So, make a note that you need to record the dependency information for your functions manually so that you'll know if and when it's okay to remove or alter a table or other objects that the functions depend on.

Subdirectory	Purpose
base	This is the main table storage. Beneath this directory, each database has its own directory, within which the files for each database table or index are located.
global	Here are the tables that are shared across all databases, including the list of databases.
pg_commit_ts	Here we store transaction commit timestamp data (from 9.5 onward).
pg_dynshmem	This includes dynamic shared memory information (from 9.4 onward).
pg_logical	This includes logical decoding status data.
pg_multixact	This includes files used for shared row-level locks.
pg_notify	This includes the LISTEN/NOTIFY status files.
pg_replslot	This includes information about replication slots (from 9.4 onward).
pg_serial	This includes information on committed serializable transactions.
pg_snapshots	This includes exported snapshot files.
pg_stat	This includes permanent statistics data.
pg_stat_tmp	This includes transient statistics data.
pg_subtrans	This includes subtransaction status data.
pg_tblspc	This includes symbolic links to tablespace directories.
pg_twophase	This includes state files for prepared transactions.
pg_wal	This includes the transaction log or Write-Ahead Log (WAL) (formerly pg_xlog).
pg_xact	This includes the transaction status files (formerly pg_clog).