Last active
June 22, 2018 19:02
-
-
Save 3manuek/a68c520e1c546f6c985e01905004012e to your computer and use it in GitHub Desktop.
[pg 10] relfilenode change on full table write and the AccessExclusiveLock
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 1) 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. | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment