
Создаем базу данных, расширение и таблицу.

=> create database db3;
CREATE DATABASE

=> \c db3
You are now connected to database "db3" as user "postgres".

=> create extension pageinspect;
CREATE EXTENSION

=> create table t(s text);
CREATE TABLE

Начинаем транзакцию.

=> begin;
BEGIN

Вставляем первую строку.

=> insert into t values ('first');
INSERT 0 1

=> select txid_current();
 txid_current 
--------------
         4002
(1 row)


=> select *, xmin, xmax from t;
   s   | xmin | xmax 
-------+------+------
 first | 4002 |    0
(1 row)


=> select lp, t_xmin, t_xmax from heap_page_items(get_raw_page('t',0)) order by lp;
 lp | t_xmin | t_xmax 
----+--------+--------
  1 |   4002 |      0
(1 row)


Точка сохранения и вторая строка.

=> savepoint sp;
SAVEPOINT

=> insert into t values ('second');
INSERT 0 1

=> select txid_current();
 txid_current 
--------------
         4002
(1 row)


N.B. Функция txid_current() выдает номер основной, а не вложенной, транзакции.

=> select *, xmin, xmax from t;
   s    | xmin | xmax 
--------+------+------
 first  | 4002 |    0
 second | 4003 |    0
(2 rows)


=> select lp, t_xmin, t_xmax from heap_page_items(get_raw_page('t',0)) order by lp;
 lp | t_xmin | t_xmax 
----+--------+--------
  1 |   4002 |      0
  2 |   4003 |      0
(2 rows)


Откат и вставка третьей строки.

=> rollback to sp;
ROLLBACK

=> insert into t values ('third');
INSERT 0 1

=> select txid_current();
 txid_current 
--------------
         4002
(1 row)


=> select *, xmin, xmax from t;
   s   | xmin | xmax 
-------+------+------
 first | 4002 |    0
 third | 4004 |    0
(2 rows)


=> select lp, t_xmin, t_xmax from heap_page_items(get_raw_page('t',0)) order by lp;
 lp | t_xmin | t_xmax 
----+--------+--------
  1 |   4002 |      0
  2 |   4003 |      0
  3 |   4004 |      0
(3 rows)


N.B. В странице продолжаем видеть откаченную строку.
Фиксация изменений.

=> commit;
COMMIT

=> select *, xmin, xmax from t;
   s   | xmin | xmax 
-------+------+------
 first | 4002 |    0
 third | 4004 |    0
(2 rows)


=> select lp, t_xmin, t_xmax,
=> (t_infomask & 256) > 0 as xmin_committed, (t_infomask & 512) > 0 as xmin_aborted
=> from heap_page_items(get_raw_page('t',0))
=> order by lp;
 lp | t_xmin | t_xmax | xmin_committed | xmin_aborted 
----+--------+--------+----------------+--------------
  1 |   4002 |      0 | t              | f
  2 |   4003 |      0 | f              | t
  3 |   4004 |      0 | t              | f
(3 rows)


N.B. Каждая вложенная транзакция имеет собственный статус.

=> \q
