1
2
3
4
5

postgres=# select datname, oid from pg_database where datname = 'postgres';
postgres |   5

postgres=# select relname, oid from pg_class where relname='actor';
actor   | 16475

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

# tree -L 2 $PGDATA
.
├── PG_VERSION
├── base
│   ├── 1
│   └── pgsql_tmp
├── global
│   ├── 1213
│   ├── 1213_fsm
│   ├── 1213_vm
│   ├── pg_control
│   ├── pg_filenode.map
│   └── pg_internal.init
├── pg_commit_ts
├── pg_dynshmem
├── pg_hba.conf
├── pg_ident.conf
├── pg_logical
│   ├── mappings
│   ├── replorigin_checkpoint
│   └── snapshots
├── pg_multixact
│   ├── members
│   └── offsets
├── pg_notify
├── pg_replslot
├── pg_serial
├── pg_snapshots
├── pg_stat
├── pg_stat_tmp
├── pg_subtrans
│   └── 0000
├── pg_tblspc
├── pg_twophase
├── pg_wal
│   ├── 000000010000000000000004
│   ├── 000000010000000000000005
│   └── archive_status
├── pg_xact
│   └── 0000
├── postgresql.auto.conf
├── postgresql.conf
├── postmaster.opts
└── postmaster.pid

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

create database test;

CREATE TABLE sal_emp (
    id int primary key,
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

INSERT INTO sal_emp
    VALUES (
    1,
    'Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}'
);

create index sal_emp_btree ON sal_emp (pay_by_quarter);
create index sal_emp_gin ON sal_emp USING gin(pay_by_quarter);

1
2
3
4
5

test=# show data_directory;
      data_directory
---------------------------
 /home/vagrant/pgdata/data
(1 row)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

test=# select oid,datname from pg_database;
  oid  |  datname
-------+-----------
     5 | postgres
 16966 | test
     1 | template1
     4 | template0

$ ls -ld base/16966/
drwx------ 2 vagrant vagrant 12288 Sep 16 01:59 base/16966/

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

test=# SELECT relname, oid, relfilenode FROM pg_class WHERE relname = 'sal_emp';
 relname |  oid  | relfilenode
---------+-------+-------------
 sal_emp | 16967 |       16967

test=# select * from pg_relation_filepath('sal_emp');
 pg_relation_filepath
----------------------
 base/16966/16967

$ ls -alh base/16966/16967
-rw------- 1 vagrant vagrant 8.0K Sep 16 02:03 base/16966/16967

1
2
3

ls -alh base/16966/16967*
-rw------- 1 vagrant vagrant 1.0G Sep 16 02:03 base/16966/16967
-rw------- 1 vagrant vagrant   1M Sep 16 02:03 base/16966/16967.1

1
2
3
4
5

test=# SELECT pg_filenode_relation(0, 16967);
pg_filenode_relation
----------------------
 sal_emp
(1 row)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

export PID=503065
ps f -p $(pstree -p ${PID} | sed 's/(/\n(/g' | grep '(' | sed 's/(\(.*\)).*/\1/' | tr "\n" " ")

    PID TTY      STAT   TIME COMMAND
 503065 pts/1    S+     0:00 ./src/backend/postgres
 503068 ?        Ss     0:00  \_ postgres: checkpointer
 503069 ?        Ss     0:00  \_ postgres: background writer
 503071 ?        Ss     0:00  \_ postgres: walwriter
 503072 ?        Ss     0:00  \_ postgres: autovacuum launcher
 503073 ?        Ss     0:00  \_ postgres: logical replication launcher
 503265 ?        Ss     0:00  \_ postgres: vagrant postgres [local] idle

1
2
3
4
5
6
7
8
9

DROP TABLE tbl;
CREATE TABLE tbl (data text);
BEGIN;INSERT INTO tbl VALUES('A');INSERT INTO tbl VALUES('B');INSERT INTO tbl VALUES('C');COMMIT;
SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_infomask2, t_infomask, t_hoff, t_bits, t_data FROM heap_page_items(get_raw_page('tbl', 0));
 tuple | t_xmin | t_xmax | t_cid | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_data
-------+--------+--------+-------+--------+-------------+------------+--------+--------+--------
     1 |   1130 |      0 |     0 | (0,1)  |           1 |       2050 |     24 |        | \x0541
     2 |   1130 |      0 |     1 | (0,2)  |           1 |       2050 |     24 |        | \x0542
     3 |   1130 |      0 |     2 | (0,3)  |           1 |       2050 |     24 |        | \x0543

1
2
3
4
5
6
7
8
9

DROP TABLE tbl;
CREATE TABLE tbl (data text);
INSERT INTO tbl VALUES('A');UPDATE tbl SET data='B';
SELECT t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_bits, t_data FROM heap_page_items(get_raw_page('tbl', 0));

 t_xmin | t_xmax | t_cid | t_ctid | t_bits | t_data
--------+--------+-------+--------+--------+--------
   99   |   100  |     0 | (0,2)  |        | \x0541
   100  |      0 |     0 | (0,2)  |        | \x0542

1
2
3
4
5
6
7
8

DROP TABLE tbl;
CREATE TABLE tbl (data text);
INSERT INTO tbl VALUES('A');
SELECT t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_data FROM heap_page_items(get_raw_page('tbl', 0));

 t_xmin | t_xmax | t_cid | t_ctid | t_data
--------+--------+-------+--------+--------
   99   |      0 |     0 | (0,1)  | \x0541

1
2
3
4
5
6
7
8
9

DROP TABLE tbl;
CREATE TABLE tbl (data text);
INSERT INTO tbl VALUES('A'); -- txid=99
Delete from tbl where data='A'; -- txid=100
SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_data FROM heap_page_items(get_raw_page('tbl', 0));

 tuple | t_xmin | t_xmax | t_cid | t_ctid | t_data
-------+--------+--------+-------+--------+--------
     1 |   99   |   100  |     0 | (0,1)  | \x0541

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

DROP TABLE tbl;
CREATE TABLE tbl (data text);
INSERT INTO tbl VALUES('A'); -- txid=99
SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_data FROM heap_page_items(get_raw_page('tbl', 0));
BEGIN;
UPDATE tbl set data='B';
SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_data FROM heap_page_items(get_raw_page('tbl', 0));
UPDATE tbl set data='C';
COMMIT;
SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_data FROM heap_page_items(get_raw_page('tbl', 0));

-- 执行 insert
 tuple | t_xmin | t_xmax | t_cid | t_ctid | t_data
-------+--------+--------+-------+--------+--------
     1 |   1168 |      0 |     0 | (0,1)  | \x0541

-- 执行第一次 update
 tuple | t_xmin | t_xmax | t_cid | t_ctid | t_data
-------+--------+--------+-------+--------+--------
     1 |   1168 |   1169 |     0 | (0,2)  | \x0541
     2 |   1169 |      0 |     0 | (0,2)  | \x0542

-- 执行第二次 update
 tuple | t_xmin | t_xmax | t_cid | t_ctid | t_data
-------+--------+--------+-------+--------+--------
     1 |   1168 |   1169 |     0 | (0,2)  | \x0541
     2 |   1169 |   1169 |     0 | (0,3)  | \x0542
     3 |   1169 |      0 |     1 | (0,3)  | \x0543

1
2
3
4
5
6
7
8

Tuple1:
    t_xmax=txid
    t_ctid '(0,1)' 修改为 '(0,2)'
Tuple2:
    t_xmin=txid
    t_xmax=0
    t_cid=0
    t_ctid='(0,2)'

1
2
3
4
5
6
7
8

Tuple2:
    t_xmax=txid
    t_ctid '(0,2)' 修改为 '(0,3)'
Tuple3:
    t_xmin=txid
    t_xmax=0
    t_cid=1
    t_ctid='(0,3)'

1
2
3
4
5

CREATE EXTENSION pg_freespacemap;
SELECT *, round(100 * avail/8192 ,2) as "freespace ratio" FROM pg_freespace('tbl');
 blkno | avail | freespace ratio 
-------+-------+-----------------
     0 |     0 |            0.00

1
2
3
4

SELECT txid_current_snapshot();
 txid_current_snapshot 
-----------------------
 100:104:100,102

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

T1: 
    TxA 开始执行第一个 SELECT 命令。当执行第一个命令时候，TxA请求获得 txid 和 快照。transaction manager 分配 txid=200, transation snapshot '200:200:'
T2:
    TxB 开始执行第一个 SELECT 命令，transaction manager 分配 tx=201，transaction snapshot '200:200:', 因此 TxB 中无法看到 TxA
T3:
    TxC 开始执行第一个 SELECT 命令，分配 tx=202，tx snapshot '200:200:'. TxC 看不到 TxA 和 TxB
T4:
    TxA 提交。transaction manager 移除他的事务信息
T5:
    TxB 和 TxC 再次执行 SELECT命令。
    TxB 请求获得 tx snapshoot，因为他的事务隔离级别是 `READ COMMITTED`，TxB 获得的 tx snapshot 是 '201:201:'。 TxA 已经 commited 了，故 TxB 可以看到 TxA
    TxC 因为其事务隔离级别是 `REPEATABLE READ`，应该继续使用之前获得的 snapshot '200:200:'. TxC 看不到 TxA 和 TxB

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

/* t_xmin status = ABORTED */
Rule 1: IF t_xmin status is 'ABORTED' THEN
            RETURN 'Invisible'
        END IF
        
        /* t_xmin status = IN_PROGRESS */
        IF t_xmin status is 'IN_PROGRESS' THEN
    	       IF t_xmin = current_txid THEN
Rule 2:          IF t_xmax = INVALID THEN
			            RETURN 'Visible'
Rule 3:          ELSE  /* this tuple has been deleted or updated by the current transaction itself. */
			             RETURN 'Invisible'
                 END IF
Rule 4:        ELSE   /* t_xmin ≠ current_txid */
		              RETURN 'Invisible'
               END IF
        END IF
             
        /* t_xmin status = COMMITTED */
        IF t_xmin status is 'COMMITTED' THEN
Rule 5:     IF t_xmin is active in the obtained transaction snapshot THEN
                RETURN 'Invisible'
Rule 6:     ELSE IF t_xmax = INVALID OR status of t_xmax is 'ABORTED' THEN
                RETURN 'Visible'
            ELSE IF t_xmax status is 'IN_PROGRESS' THEN
Rule 7:         IF t_xmax =  current_txid THEN
                    RETURN 'Invisible'
Rule 8:         ELSE  /* t_xmax ≠ current_txid */
                    RETURN 'Visible'
                END IF
            ELSE IF t_xmax status is 'COMMITTED' THEN
Rule 9:         IF t_xmax is active in the obtained transaction snapshot THEN
                    RETURN 'Visible'
Rule 10:        ELSE
                    RETURN 'Invisible'
                END IF
            END IF
        END IF

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

Rule 1: If Status(t_xmin) = ABORTED => Invisible

Rule 2: If Status(t_xmin) = IN_PROGRESS ∧ t_xmin = current_txid ∧ t_xmax = INVAILD => Visible
Rule 3: If Status(t_xmin) = IN_PROGRESS ∧ t_xmin = current_txid ∧ t_xmax != INVAILD => Invisible
Rule 4: If Status(t_xmin) = IN_PROGRESS ∧ t_xmin != current_txid => Invisible

Rule 5: If Status(t_xmin) = COMMITTED ∧ Snapshot(t_xmin) = active => Invisible
Rule 6: If Status(t_xmin) = COMMITTED ∧ (t_xmax = INVALID ∨ Status(t_xmax) = ABORTED) => Visible
Rule 7: If Status(t_xmin) = COMMITTED ∧ Status(t_xmax) = IN_PROGRESS ∧ t_xmax = current_txid => Invisible
Rule 8: If Status(t_xmin) = COMMITTED ∧ Status(t_xmax) = IN_PROGRESS ∧ t_xmax != current_txid => Visible
Rule 9: If Status(t_xmin) = COMMITTED ∧ Status(t_xmax) = COMMITTED ∧ Snapshot(t_xmax) = active => Visible
Rule 10: If Status(t_xmin) = COMMITTED ∧ Status(t_xmax) = COMMITTED ∧ Snapshot(t_xmax) != active => Invisible