Waiting for 9.5 – Reduce lock levels of some trigger DDL and add FKs

On 5th of April, Simon Riggs committed patch:

Reduce lock levels of some trigger DDL and add FKs
 
Reduce lock levels to ShareRowExclusive for the following SQL
 CREATE TRIGGER (but not DROP or ALTER)
 ALTER TABLE ENABLE TRIGGER
 ALTER TABLE DISABLE TRIGGER
 ALTER TABLE … ADD CONSTRAINT FOREIGN KEY
 
Original work by Simon Riggs, extracted and refreshed by Andreas Karlsson
New test cases added by Andreas Karlsson
Reviewed by Noah Misch, Andres Freund, Michael Paquier and Simon Riggs

To be honest, the create trigger or alter table enable/disable trigger don't really excite me that much, as these operations are fast and can be trivially worked-around to not to lock other queries.

But – add foreign key is an exception. Because it has to actually check values in the table.

Let's consider this simple example:

$ CREATE TABLE t1 (id serial PRIMARY KEY);
$ INSERT INTO t1 (id) SELECT generate_series(1,100000) i;
$ CREATE TABLE t2 (id serial PRIMARY KEY, t1_id int4);
$ INSERT INTO t2 (t1_id) SELECT 1 + i % 100000 FROM generate_series(1,10000000) i;

This will create two tables with some rows. Now, let's add missing foreign key:

$ BEGIN;
$ ALTER TABLE t2 ADD FOREIGN KEY (t1_id) REFERENCES t1 (id);
$ SELECT relation::regclass, mode FROM pg_locks WHERE pid = pg_backend_pid() AND locktype = 'relation';

On older Pg, it will show:

 relation |        mode         
----------+---------------------
 pg_locks | AccessShareLock
 t1_pkey  | AccessShareLock
 t2_pkey  | AccessShareLock
 t2       | AccessShareLock
 t2       | AccessExclusiveLock
 t1       | AccessShareLock
 t1       | RowShareLock
 t1       | AccessExclusiveLock
(8 rows)

on new one:

 relation |         mode          
----------+-----------------------
 pg_locks | AccessShareLock
 t1_pkey  | AccessShareLock
 t2_pkey  | AccessShareLock
 t2       | AccessShareLock
 t2       | ShareRowExclusiveLock
 t1       | AccessShareLock
 t1       | RowShareLock
 t1       | ShareRowExclusiveLock
(8 rows)

As you can see, in old pg, both tables – t1 and t2 were locked with AccessExclusiveLock. Which means that nothing else, not even select, can use these tables.

But in our brand new 9.5 – the lock is only ShareRowExclusiveLock. And this means that selects will actually work. Writes will still be locked out, but at the very least, we'll get the ability to read from table while foreign key is being added.

Nice. Looks really helpful. Would be better to get write access too, but I guess we have to wait for this a bit longer 🙂