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 🙂