Why is PostgreSQL blocking creation of a new table on update on foreign table?

Question

I understand why an UPDATE query on tableA can block an ALTER TABLE DDL statement on the same tableA.

ex:

UPDATE tableA SET column1 = 'test' WHERE id = 1;`

But I am trying to create a new table, say tableB, that only references the id of tableA with ON DELETE CASCADE.

CREATE TABLE tableB id REFERENCES tableA(id) ON DELETE CASCADE NOT NULL`;

This query waits until the update is complete.

I was able to figure that out by getting the blocking pids through this query

SELECT pg_blocking_pids(<pid of the create table query>);

I got the pid of the update query as the blocking pid.

Why is creating a new table blocked by the UPDATE query of the referenced table?

Answer 1

If you are executing both statements in a single transaction, they won't lock each other.

If you execute the statements in different transactions, one will have to wait until the other is done. The reason is that foreign key relationships are implemented by system triggers in PostgreSQL and creating a trigger on tablea requires a SHARE ROW EXCLUSIVE lock on the table.

If you look at the lock compatibility table, you will see that that lock conflicts with the ROW EXCLUSIVE lock required by INSERT, UPDATE and DELETE.

Make sure that your UPDATE are running in short transactions, then that won't be a problem.