【PostgreSQL】INSERT/UPDATE/DELETE で xmin/xmax がどう変わるかを確認してみた

[ec2-user@ip-xx-xx-xx-xx ~]$ sudo -u postgres psql
psql (17.10)
Type "help" for help.

postgres=# select * from version();
                                                    version                                                    
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.10 on x86_64-amazon-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit
(1 row)

$ sudo dnf install postgresql17-contrib -y

# PostgreSQLに接続
$ sudo -u postgres psql

-- データベースを作成
CREATE DATABASE blog_sample_db;

-- データベースに接続
\c blog_sample_db

-- まずpageinspectが有効化されているか確認
SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pageinspect';

-- 結果：0 rows（まだ有効化されていない）
 extname | extversion
---------+------------
(0 rows)

-- pageinspect拡張機能を有効化
CREATE EXTENSION pageinspect;

-- 再度確認
SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pageinspect';

-- 結果：有効化された
   extname   | extversion
-------------+------------
 pageinspect | 1.12
(1 row)

-- トランザクション外でXIDを確認
blog_sample_db=# SELECT pg_current_xact_id();
 pg_current_xact_id 
--------------------
                779
(1 row)

-- トランザクションを開始
blog_sample_db=# BEGIN;
BEGIN

-- トランザクション内でXIDを確認
blog_sample_db=*# SELECT pg_current_xact_id();
 pg_current_xact_id 
--------------------
                780  -- トランザクション開始時にXIDが採番される
(1 row)

-- 同じトランザクション内で再度確認
blog_sample_db=*# SELECT pg_current_xact_id();
 pg_current_xact_id 
--------------------
                780  -- 同じトランザクション内ではXIDは変わらない
(1 row)

blog_sample_db=*# COMMIT;
COMMIT

-- トランザクション終了後に再度確認
blog_sample_db=# SELECT pg_current_xact_id();
 pg_current_xact_id 
--------------------
                781  -- 新しいトランザクションには新しいXIDが採番される
(1 row)

-- テーブル作成
CREATE TABLE xmin_test (
    id   SERIAL PRIMARY KEY,
    name TEXT
);

-- テーブルの中身を確認（この時点では空）
blog_sample_db=# SELECT xmin, xmax, id, name FROM xmin_test;
 xmin | xmax | id | name 
------+------+----+------
(0 rows)

-- トランザクションを開始
blog_sample_db=# BEGIN;
BEGIN

-- 現在のXIDを確認（このXIDがxminに入るはず）
blog_sample_db=*# SELECT pg_current_xact_id();
 pg_current_xact_id 
--------------------
                794  -- XID: 794 が採番された
(1 row)

-- 1行INSERT
blog_sample_db=*# INSERT INTO xmin_test (name) VALUES ('Alice');
INSERT 0 1

-- INSERT後（COMMIT前）のxmin/xmaxを確認
blog_sample_db=*# SELECT xmin, xmax, id, name FROM xmin_test;
 xmin | xmax | id | name  
------+------+----+-------
  794 |    0 |  1 | Alice  -- xmin=794（挿入したXID）、xmax=0（削除・更新なし）
(1 row)

-- コミット
blog_sample_db=*# COMMIT;
COMMIT

-- COMMIT後のxmin/xmaxを確認
blog_sample_db=# SELECT xmin, xmax, id, name FROM xmin_test;
 xmin | xmax | id | name  
------+------+----+-------
  794 |    0 |  1 | Alice  -- COMMIT後も変化なし
(1 row)

-- UPDATE前の状態を確認
blog_sample_db=# SELECT xmin, xmax, id, name FROM xmin_test;
 xmin | xmax | id | name  
------+------+----+-------
  794 |    0 |  1 | Alice  -- xmin=794（INSERTしたXID）、xmax=0（まだ更新・削除なし）
(1 row)

-- トランザクションを開始
blog_sample_db=# BEGIN;
BEGIN

-- 現在のXIDを確認（このXIDが新しい行のxminと古い行のxmaxに入るはず）
blog_sample_db=*# SELECT pg_current_xact_id();
 pg_current_xact_id 
--------------------
                795  -- XID: 795 が採番された
(1 row)

-- name を 'Alice' から 'Bob' に UPDATE
blog_sample_db=*# UPDATE xmin_test SET name = 'Bob' WHERE id = 1;
UPDATE 1

-- UPDATE後（COMMIT前）のxmin/xmaxを確認
blog_sample_db=*# SELECT xmin, xmax, id, name FROM xmin_test;
 xmin | xmax | id | name 
------+------+----+------
  795 |    0 |  1 | Bob  -- xmin=795（更新したXID）、xmax=0（削除なし）
(1 row)

-- コミット
blog_sample_db=*# COMMIT;
COMMIT

-- COMMIT後のxmin/xmaxを確認
blog_sample_db=# SELECT xmin, xmax, id, name FROM xmin_test;
 xmin | xmax | id | name 
------+------+----+------
  795 |    0 |  1 | Bob  -- COMMIT後も変化なし
(1 row)

-- 通常のSELECTではlive tupleしか見えない
blog_sample_db=# SELECT xmin, xmax, id, name FROM xmin_test;
 xmin | xmax | id | name 
------+------+----+------
  795 |    0 |  1 | Bob  -- Aliceの行（dead tuple）は見えない
(1 row)

-- pageinspectを使うことでdead tupleも含めて確認できる
blog_sample_db=# SELECT lp, t_xmin, t_xmax, t_data
FROM heap_page_items(get_raw_page('xmin_test', 0));
 lp | t_xmin | t_xmax |         t_data         
----+--------+--------+------------------------
  1 |    794 |    795 | \x010000000d416c696365  -- dead tuple（Aliceの行）t_xmax=795（更新したXID）
  2 |    795 |      0 | \x0100000009426f62      -- live tuple（Bobの行）t_xmax=0（削除なし）
(2 rows)

-- 上記の "t_data" カラムの表示は16進数なので、デコードするとname列の値が確認できる
blog_sample_db=# SELECT convert_from('\x416c696365', 'UTF8');
 convert_from 
--------------
 Alice
(1 row)

blog_sample_db=# SELECT convert_from('\x426f62', 'UTF8');
 convert_from 
--------------
 Bob
(1 row)

-- DELETE前の状態を確認
blog_sample_db=# SELECT xmin, xmax, id, name FROM xmin_test;
 xmin | xmax | id | name 
------+------+----+------
  795 |    0 |  1 | Bob  -- xmin=795（UPDATEしたXID）、xmax=0（まだ削除なし）
(1 row)

-- トランザクションを開始
blog_sample_db=# BEGIN;
BEGIN

-- 現在のXIDを確認（このXIDがxmaxに入るはず）
blog_sample_db=*# SELECT pg_current_xact_id();
 pg_current_xact_id 
--------------------
                796  -- XID: 796 が採番された
(1 row)

-- Bobの行をDELETE
blog_sample_db=*# DELETE FROM xmin_test WHERE id = 1;
DELETE 1

-- DELETE後（COMMIT前）のxmin/xmaxを確認
blog_sample_db=*# SELECT xmin, xmax, id, name FROM xmin_test;
 xmin | xmax | id | name 
------+------+----+------
(0 rows)  -- 通常のSELECTでは既に見えない

-- コミット
blog_sample_db=*# COMMIT;
COMMIT

-- COMMIT後のxmin/xmaxを確認
blog_sample_db=# SELECT xmin, xmax, id, name FROM xmin_test;
 xmin | xmax | id | name 
------+------+----+------
(0 rows)  -- COMMIT後も見えない

-- pageinspectでdead tupleも確認
blog_sample_db=# SELECT lp, t_xmin, t_xmax, t_data
FROM heap_page_items(get_raw_page('xmin_test', 0));
 lp | t_xmin | t_xmax |         t_data         
----+--------+--------+------------------------
  1 |    794 |    795 | \x010000000d416c696365  -- dead tuple（Aliceの行）UPDATE(前述)で dead tupleになった
  2 |    795 |    796 | \x0100000009426f62      -- dead tuple（Bobの行）t_xmax=796（DELETEしたXID）
(2 rows)