In the simplest case, when we insert a new row into a table (and the transaction commits), it will be visible to all subsequent transactions. See
xmax being 0 in this example:
CREATE TABLE vis ( id serial, is_active boolean ); INSERT INTO vis (is_active) VALUES (FALSE); SELECT ctid, xmin, xmax, * FROM vis; ctid │xmin │ xmax │ id │ is_active ───────┼─────┼──────┼────┼─────────── (0,1) │2699 │ 0 │ 1 │ f
When we update it (because the flag was set to
FALSE by accident), it changes a bit:
UPDATE vis SET is_active = TRUE; SELECT ctid, xmin, xmax, * FROM vis; ctid │ xmin │ xmax │ id │ is_active ──────┼──────┼──────┼────┼─────────── (0,2) │ 2700 │ 0 │ 1 │ t
Now there is an interesting thing happening when we roll back the
BEGIN; UPDATE vis SET is_active = TRUE; ROLLBACK; SELECT ctid, xmin, xmax, * FROM vis; ctid │ xmin │ xmax │ id │ is_active ───────┼──────┼──────┼────┼─────────── (0,2) │ 2700 │ 2702 │ 1 │ t
The row version stays the same, but now
xmax is set to something. Despite this, subsequent transactions can see this (otherwise unchanged) row.
After reading a bit about this, you may figure out a few things about row visibility. There is the visibility map, but it only tells if a whole page is visible - it definitely does not work on the row (tuple) level. Then there is the commit log (aka
clog) - but how does Postgres figure out if it has to visit it?
I decided to have a look at the infomask bits to figure out how visibility actually works. To see them, the easiest way is to use the pageinspect extension. In order to find out which bits are set, I created a table to store them:
CREATE TABLE infomask ( i_flag text, i_bits bit(16) ); INSERT INTO infomask VALUES ('HEAP_HASNULL', x'0001'::bit(16)), ('HEAP_HASVARWIDTH', x'0002'::bit(16)), ('HEAP_HASEXTERNAL', x'0004'::bit(16)), ('HEAP_HASOID', x'0008'::bit(16)), ('HEAP_XMAX_KEYSHR_LOCK', x'0010'::bit(16)), ('HEAP_COMBOCID', x'0020'::bit(16)), ('HEAP_XMAX_EXCL_LOCK', x'0040'::bit(16)), ('HEAP_XMAX_LOCK_ONLY', x'0080'::bit(16)), ('HEAP_XMIN_COMMITTED', x'0100'::bit(16)), ('HEAP_XMIN_INVALID', x'0200'::bit(16)), ('HEAP_XMAX_COMMITTED', x'0400'::bit(16)), ('HEAP_XMAX_INVALID', x'0800'::bit(16)), ('HEAP_XMAX_IS_MULTI', x'1000'::bit(16)), ('HEAP_UPDATED', x'2000'::bit(16)), ('HEAP_MOVED_OFF', x'4000'::bit(16)), ('HEAP_MOVED_IN', x'8000'::bit(16)), ('HEAP_XACT_MASK', x'FFF0'::bit(16));
Then checked what's inside my
vis table - note that
pageinspect shows the physical contents of the heap, so not only the visible rows are returned:
SELECT t_xmin, t_xmax, string_agg(i_flag, ', ') FILTER (WHERE (t_infomask::bit(16) & i_bits)::integer::boolean) FROM heap_page_items(get_raw_page('vis', 0)), infomask GROUP BY t_xmin, t_xmax; t_xmin │ t_xmax │ string_agg ────────┼────────┼────────────────────────────────────────────────────── 2699 │ 2700 │ HEAP_XMIN_COMMITTED, HEAP_XMAX_COMMITTED 2700 │ 2702 │ HEAP_XMIN_COMMITTED, HEAP_XMAX_INVALID, HEAP_UPDATED 2702 │ 0 │ HEAP_XMIN_INVALID, HEAP_XMAX_INVALID, HEAP_UPDATED
What I understand from the above is that the first version came to life with transaction 2699, then successfully replaced by the new version at 2700.
Then the next one, which was alive since 2700, had a rolled back attempt of
UPDATE in 2702, seen from
The last one was never really born, as shown by
So, guessing from the above, the first and last case are obvious - they are not visible anymore to transaction 2703 or higher.
The second one has to be looked up somewhere - I suppose it is the commit log, aka
To further complicate the issues, a subsequent
UPDATE results in the following:
t_xmin │ t_xmax │ string_agg ────────┼────────┼──────────────────────────────────────────────────── 2699 │ 2700 │ HEAP_XMIN_COMMITTED, HEAP_XMAX_COMMITTED 2702 │ 0 │ HEAP_XMIN_INVALID, HEAP_XMAX_INVALID, HEAP_UPDATED 2703 │ 0 │ HEAP_XMAX_INVALID, HEAP_UPDATED 2700 │ 2703 │ HEAP_XMIN_COMMITTED, HEAP_UPDATED
Here I see already two candidates that could be visible. So, finally, here are my questions:
- Is my assumption that the
clogis the place to look at to determine visibility in these cases?
- Which flags (or combination of flags) tell the system to visit the
- Is there a way to examine what's inside the
clog? There are mentions about
clogcorruption in earlier versions of Postgres and a hint that one can build a fake file manually. This piece of information would help a lot with it.