3manuek · June 22, 2018 19:02 · Jun 22, 2018 · Jun 21, 2018 · Jun 21, 2018 · Jun 21, 2018
diff --git a/alter_full_table_relfilenode_n_locks.md b/alter_full_table_relfilenode_n_locks.md
@@ -116,8 +116,9 @@ postgres=# select relname, oid, relfilenode from pg_class where oid = 'test'::re
 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.
+> 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.
 
 
 
diff --git a/alter_full_table_relfilenode_n_locks.md b/alter_full_table_relfilenode_n_locks.md
@@ -1,4 +1,4 @@
-1) Open 2 `psql` terminals
+- Open 2 `psql` terminals
 
 ```
 postgres=# create table test AS SELECT clock_timestamp(), i::bigint as num from generate_series(1,10000000) i(i);
@@ -115,8 +115,8 @@ postgres=# select relname, oid, relfilenode from pg_class where oid = 'test'::re
 
 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.
+> 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.
 
 
 

diff --git a/gistfile1.txt → alter_full_table_relfilenode_n_locks.md b/gistfile1.txt → alter_full_table_relfilenode_n_locks.md
diff --git a/gistfile1.txt b/gistfile1.txt
@@ -0,0 +1,123 @@
+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.
+
+
+
+
No results found