public inbox for pgsql-novice@postgresql.org
help / color / mirror / Atom feedTrying to understand Tuple Header
3+ messages / 2 participants
[nested] [flat]
* Trying to understand Tuple Header
@ 2026-05-03 18:44 Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
0 siblings, 1 reply; 3+ messages in thread
From: Subramanian,Ramachandran @ 2026-05-03 18:44 UTC (permalink / raw)
To: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trying to understand Tuple Header
@ 2026-05-03 19:02 David G. Johnston <david.g.johnston@gmail.com>
parent: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2026-05-03 19:02 UTC (permalink / raw)
To: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>; +Cc: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* AW: Trying to understand Tuple Header
@ 2026-05-03 19:25 Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Subramanian,Ramachandran @ 2026-05-03 19:25 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-05-03 19:25 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-03 18:44 Trying to understand Tuple Header Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-05-03 19:02 ` David G. Johnston <david.g.johnston@gmail.com>
2026-05-03 19:25 ` AW: Trying to understand Tuple Header Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox