Horton

Thread

  1. Trying to understand Tuple Header

    Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> — 2026-05-03T18:44:53Z

    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


Freundliche Grüße

i. A. Ramachandran Subramanian
Zentralbereich Informationstechnologie

Alte Leipziger Lebensversicherung a.G.


Hallesche Krankenversicherung a.G.





Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814





 
Hallesche Krankenversicherung a.G.,  Löffelstraße 34-38, 70597 Stuttgart
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
Wiltrud Pekarek, Udo Wilcsek
Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
 



 
Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum 





______________________

ALH Gruppe
Alte Leipziger-Platz 1, 61440 Oberursel
Tel.: +49 (6171) 66-4882
Fax: +49 (6171) 66-800-4882
E-Mail: ramachandran.subramanian@alte-leipziger.de
www.alte-leipziger.de
www.hallesche.de

  2. Re: Trying to understand Tuple Header

    David G. Johnston <david.g.johnston@gmail.com> — 2026-05-03T19:02:49Z

    On Sunday, May 3, 2026, Subramanian,Ramachandran <
ramachandran.subramanian@alte-leipziger.de> wrote:

> How does postgres know that this 4th tuple has been rolled back ?  Please
> help me learn that.
>
>
The pg_xact file holds information pertaining to whether a transaction
produced live or dead tuples upon ending.

David J.

  3. AW: Trying to understand Tuple Header

    Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> — 2026-05-03T19:25:14Z

    Thank you so much for your speedy reply.


I Googled and found Egor Rogov’s SQL.

The binary value of t_infomask for both the tuples are identical, but they produce different column values for xmin_commited, xmin_aborted ….  in the SQL  !!!.

Am I not seeing something that is obvious?


Regards

Ram







SELECT t_ctid, cast(t_infomask as bit(16))  FROM heap_page_items(get_raw_page('One_Page_Wonder',0));
t_ctid |    t_infomask
--------+------------------
(0,1)  | 0000100000000010
(0,2)  | 0000100000000010
(2 rows)

SELECT * FROM One_Page_Wonder ;
id |        text        |         time_stamp
----+--------------------+----------------------------
  1 | First row Inserted | 2026-05-03 21:18:24.631047
(1 row)

SELECT '(0,'||lp||')' AS ctid,
       CASE lp_flags
         WHEN 0 THEN 'unused'
         WHEN 1 THEN 'normal'
         WHEN 2 THEN 'redirect to '||lp_off
         WHEN 3 THEN 'dead'
       END AS state,
       t_xmin as xmin,
       t_xmax as xmax,
       (t_infomask & 256) > 0  AS xmin_commited,
       (t_infomask & 512) > 0  AS xmin_aborted,
       (t_infomask & 1024) > 0 AS xmax_commited,
       (t_infomask & 2048) > 0 AS xmax_aborted,
       t_ctid
FROM heap_page_items(get_raw_page('One_Page_wonder',0));
ctid  | state  | xmin | xmax | xmin_commited | xmin_aborted | xmax_commited | xmax_aborted | t_ctid
-------+--------+------+------+---------------+--------------+---------------+--------------+--------
(0,1) | normal | 3673 |    0 | t             | f            | f             | t            | (0,1)
(0,2) | normal | 3674 |    0 | f             | t            | f             | t            | (0,2)
(2 rows)


Freundliche Grüße

i. A. Ramachandran Subramanian
Zentralbereich Informationstechnologie

Alte Leipziger Lebensversicherung a.G.


Hallesche Krankenversicherung a.G.







Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814





 
Hallesche Krankenversicherung a.G.,  Löffelstraße 34-38, 70597 Stuttgart
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
Wiltrud Pekarek, Udo Wilcsek
Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
 



 
Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum 





______________________

ALH Gruppe
Alte Leipziger-Platz 1, 61440 Oberursel
Tel.: +49 (6171) 66-4882
Fax: +49 (6171) 66-800-4882
E-Mail: ramachandran.subramanian@alte-leipziger.de
www.alte-leipziger.de
www.hallesche.de




Von: David G. Johnston <david.g.johnston@gmail.com>
Gesendet: Sonntag, 3. Mai 2026 21:03
An: Subramanian,Ramachandran IT-md-db <ramachandran.subramanian@alte-leipziger.de>
Cc: pgsql-novice@lists.postgresql.org
Betreff: Re: Trying to understand Tuple Header

On Sunday, May 3, 2026, Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de<mailto:ramachandran.subramanian@alte-leipziger.de>> wrote:
How does postgres know that this 4th tuple has been rolled back ?  Please help me learn that.

The pg_xact file holds information pertaining to whether a transaction produced live or dead tuples upon ending.

David J.

  4. Re: Trying to understand Tuple Header

    David G. Johnston <david.g.johnston@gmail.com> — 2026-05-03T19:48:47Z

    On Sun, May 3, 2026 at 12:25 PM Subramanian,Ramachandran <
ramachandran.subramanian@alte-leipziger.de> wrote:

> The binary value of t_infomask for both the tuples are identical, but they
> produce different column values for xmin_commited, xmin_aborted ….  in the
> SQL  !!!.
>
>
>
> Am I not seeing something that is obvious?
>

You didn't re-check the infomask data after running the select query,
instead assuming the bits didn't change.  They did.  SELECT is not a
read-only operation, it participates in optimizations.  Called "writing
hint bits".  Manually evaluating the various tests against the data you did
show would have proven that the pre-select-data had zeros where the second
query claims there are ones.

David J.

  5. Re: Trying to understand Tuple Header

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-05-03T22:50:23Z

    "David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sun, May 3, 2026 at 12:25 PM Subramanian,Ramachandran <
> ramachandran.subramanian@alte-leipziger.de> wrote:
>> The binary value of t_infomask for both the tuples are identical, but they
>> produce different column values for xmin_commited, xmin_aborted ….  in the
>> SQL  !!!.

> You didn't re-check the infomask data after running the select query,
> instead assuming the bits didn't change.  They did.  SELECT is not a
> read-only operation, it participates in optimizations.  Called "writing
> hint bits".

Well, it *might* update the hint bits, but it can only do that after
the tuple is visible to all live transactions.  So it matters what
else is happening within the database.  In any case, pg_xact is the
ultimate source of truth about whether a tuple is committed or not.

			regards, tom lane

  6. AW: Trying to understand Tuple Header

    Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> — 2026-05-04T04:04:30Z

    Wow!!!  Thank you.  You are correct. T_infomask did change.

I began with postgres in Nov 2025 and I am having so much fun learning it. I am deeply grateful for this community.


So the next select to the table cross references the pg_xact file to check if hint bits need to be updated or not ?  How deep does the next select look into the pg_xact file ? For example if I roll back and never touch this table for 30  Minutes and I come back and do a second select…will it look back into 30 mins of history in pg_xact ?


It is a wonderful morning to have learned a little bit of PG Magic 😊 .  Thank you all.





SELECT t_ctid,t_infomask, cast(t_infomask as bit(16))  FROM heap_page_items(get_raw_page('One_Page_Wonder',0));
t_ctid | t_infomask |    t_infomask
--------+------------+------------------
(0,1)  |       2050 | 0000100000000010
(0,2)  |       2050 | 0000100000000010
(2 rows)

SELECT * FROM One_Page_Wonder ;
id |        text        |         time_stamp
----+--------------------+----------------------------
  1 | First row Inserted | 2026-05-04 05:50:32.540905
(1 row)

SELECT t_ctid,t_infomask, cast(t_infomask as bit(16))  FROM heap_page_items(get_raw_page('One_Page_Wonder',0));
t_ctid | t_infomask |    t_infomask
--------+------------+------------------
(0,1)  |       2306 | 0000100100000010
(0,2)  |       2562 | 0000101000000010
(2 rows)



Freundliche Grüße

i. A. Ramachandran Subramanian
Zentralbereich Informationstechnologie

Alte Leipziger Lebensversicherung a.G.


Hallesche Krankenversicherung a.G.







Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814





 
Hallesche Krankenversicherung a.G.,  Löffelstraße 34-38, 70597 Stuttgart
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
Wiltrud Pekarek, Udo Wilcsek
Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
 



 
Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum 





______________________

ALH Gruppe
Alte Leipziger-Platz 1, 61440 Oberursel
Tel.: +49 (6171) 66-4882
Fax: +49 (6171) 66-800-4882
E-Mail: ramachandran.subramanian@alte-leipziger.de
www.alte-leipziger.de
www.hallesche.de




Von: David G. Johnston <david.g.johnston@gmail.com>
Gesendet: Sonntag, 3. Mai 2026 21:49
An: Subramanian,Ramachandran IT-md-db <ramachandran.subramanian@alte-leipziger.de>
Cc: pgsql-novice@lists.postgresql.org
Betreff: Re: Trying to understand Tuple Header

On Sun, May 3, 2026 at 12:25 PM Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de<mailto:ramachandran.subramanian@alte-leipziger.de>> wrote:
The binary value of t_infomask for both the tuples are identical, but they produce different column values for xmin_commited, xmin_aborted ….  in the SQL  !!!.

Am I not seeing something that is obvious?

You didn't re-check the infomask data after running the select query, instead assuming the bits didn't change.  They did.  SELECT is not a read-only operation, it participates in optimizations.  Called "writing hint bits".  Manually evaluating the various tests against the data you did show would have proven that the pre-select-data had zeros where the second query claims there are ones.

David J.

  7. Re: AW: Trying to understand Tuple Header

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-05-04T07:32:52Z

    On Mon, 2026-05-04 at 04:04 +0000, Subramanian,Ramachandran wrote:
> So the next select to the table cross references the pg_xact file to check if hint bits
> need to be updated or not ?  How deep does the next select look into the pg_xact file ?
> For example if I roll back and never touch this table for 30  Minutes and I come back
> and do a second select…will it look back into 30 mins of history in pg_xact ?

Yes.  It has to, to maintain correctness.

If nobody ever looks at the row, eventually autovacuum will freeze it.  Any pg_xact
segment that only contains transactions older than the oldest unfrozen row will
get reclaimed.

Yours,
Laurenz Albe