[pg 10] relfilenode change on full table write and the AccessExclusiveLock

alter_full_table_relfilenode_n_locks.md

• Open 2 psql terminals

postgres=# create table test AS SELECT clock_timestamp(), i::bigint as num from generate_series(1,10000000) i(i);
SELECT 10000000
Time: 9305.262 ms (00:09.305)

postgres=# select relname, oid, relfilenode from pg_class where oid = 'test'::regclass::oid;
 relname |  oid  | relfilenode
---------+-------+-------------
 test    | 17129 |       17129
(1 row)

(Watch output in other terminal):

postgres=# \watch 2 select * from pg_locks where relation = 'test'::regclass::oid;
-[ RECORD 1 ]------+-------------------------
locktype           | relation
database           | 12328
relation           | 17129
page               |
tuple              |
virtualxid         |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 5/15
pid                | 3804
mode               | ShareUpdateExclusiveLock
granted            | t
fastpath           | f

Alter the table in the first terminal:

ALTER TABLE test ALTER num TYPE numeric;

Locks:

-[ RECORD 1 ]------+--------------------
locktype           | relation
database           | 12328
relation           | 17129
page               |
tuple              |
virtualxid         |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 4/16
pid                | 3783
mode               | AccessExclusiveLock
granted            | t
fastpath           | f

Thu 21 Jun 2018 03:35:21 PM UTC (every 2s)

-[ RECORD 1 ]------+--------------------
locktype           | relation
database           | 12328
relation           | 17129
page               |
tuple              |
virtualxid         |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 4/16
pid                | 3783
mode               | AccessExclusiveLock
granted            | t
fastpath           | f

Thu 21 Jun 2018 03:35:23 PM UTC (every 2s)

-[ RECORD 1 ]------+--------------------
locktype           | relation
database           | 12328
relation           | 17129
page               |
tuple              |
virtualxid         |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 4/16
pid                | 3783
mode               | AccessExclusiveLock
granted            | t
fastpath           | f

Finally, check the relfilenode:

postgres=# select relname, oid, relfilenode from pg_class where oid = 'test'::regclass::oid;
 relname |  oid  | relfilenode
---------+-------+-------------
 test    | 17129 |       17135
(1 row)

NOTE:

Right now, writing the full table causes the most strict lock level (AccessExclusiveLock) which is the most conflicting level (all transaction will wait until this is done). ALTER TABLE in these conditions might be dangerous in production, self logical-replication techniques can be an option for applying changes over large relations^1.

1. Relations as a set of relfilenodes of 1GB chunks. So the entire lock will apply on N*2 files over the FS. Costs for locks per allocated buffer may be costly and as the relation cannot be accessed, so there is a penalty over the number of cached pages of the relation. We need to run pg_buffercache during this operation, probably over an asserted PostgreSQL compilation.