Trying to understand Tuple Header - Mailing list pgsql-novice

Hello,

I created a table called One_Page_Wonder and inserted 3 rows in it.  I updated row 2 and row 3 . I looked at the tuples.

Row 1 is pristine, Row 2 and 3 have t_xmin and t_xmax values to show the updates and older version of the tuples. So far no confusion.

Then I truncated the table and inserted 1st row and comitted it and inserted 4th row  and rolled it back.

The tuple for 4th row that I rolled back looks identical to the 1st row that I commited !!!!

If I did not do a SELECT on One_Page_Wonder and just looked at the raw page, I would have concluded that both the tuples are valid and active and comitted.

How does postgres know that this 4th tuple has been rolled back ?  Please help me learn that.

Regards

Ram

SELECT * FROM heap_page_items(get_raw_page('One_Page_Wonder',0));

lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |                               t_data

----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------------------------------------------------------

  1 |   8136 |        1 |     56 |   3561 |      0 |        0 | (0,1)  |           3 |       2050 |     24 |        |       | \x0100000027466972737420726f7720496e736572746564008abd6087eef30200

  2 |   8080 |        1 |     56 |   3562 |   3565 |        0 | (0,5)  |       16387 |        258 |     24 |        |       | \x02000000275365636f6e6420726f77496e736572746564000fc66087eef30200

  3 |   8024 |        1 |     56 |   3563 |   3564 |        0 | (0,4)  |       16387 |        258 |     24 |        |       | \x030000002754686972642020726f77496e73657274656400cecb6087eef30200

  4 |   7968 |        1 |     56 |   3564 |      0 |        0 | (0,4)  |       32771 |      10242 |     24 |        |       | \x0300000021557064617465645f3372645f726f7700000000cecb6087eef30200

  5 |   7912 |        1 |     56 |   3565 |      0 |        0 | (0,5)  |       32771 |      10242 |     24 |        |       | \x0200000021557064617465645f3272645f726f77000000000fc66087eef30200

(5 rows)

SELECT * FROM One_Page_Wonder ;

id |        text        |         time_stamp

----+--------------------+----------------------------

  1 | First row Inserted | 2026-05-03 20:35:52.451466

  3 | Updated_3rd_row    | 2026-05-03 20:35:52.455118

  2 | Updated_2rd_row    | 2026-05-03 20:35:52.453647

(3 rows)

TRUNCATE TABLE One_Page_Wonder;

TRUNCATE TABLE

INSERT INTO One_Page_Wonder

(Id,Text)

VALUES

(1, 'First row Inserted');

INSERT 0 1

BEGIN;

BEGIN

SELECT pg_current_xact_id();

pg_current_xact_id

--------------------

               3568

(1 row)

INSERT INTO One_Page_Wonder

(Id,Text)

VALUES

(4, 'Fourth  rowInserted');

INSERT 0 1

ROLLBACK;

ROLLBACK

SELECT * FROM heap_page_items(get_raw_page('One_Page_Wonder',0));

lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |                               t_data

----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------------------------------------------------------

  1 |   8136 |        1 |     56 |   3567 |      0 |        0 | (0,1)  |           3 |       2050 |     24 |        |       | \x0100000027466972737420726f7720496e7365727465640091026187eef30200

  2 |   8080 |        1 |     56 |   3568 |      0 |        0 | (0,2)  |           3 |       2050 |     24 |        |       | \x0400000029466f757274682020726f77496e736572746564f4076187eef30200

(2 rows)

SELECT * FROM One_Page_Wonder ;

id |        text        |         time_stamp

----+--------------------+----------------------------

  1 | First row Inserted | 2026-05-03 20:35:52.469137

(1 row)

DROP TABLE One_Page_Wonder;

DROP TABLE

DROP EXTENSION pageinspect;

DROP EXTENSION