How exactly the row visibility is determined?

Question

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

According to the MVCC model PostgreSQL uses, a new physical row was written and the old one invalidated (this can be seen from the ctid). The new one is still visible to all subsequent transactions.

Now there is an interesting thing happening when we roll back the UPDATE:

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 HEAP_XMAX_INVALID.
The last one was never really born, as shown by HEAP_XMIN_INVALID.

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 clog.

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 clog is the place to look at to determine visibility in these cases?
• Which flags (or combination of flags) tell the system to visit the clog?
• Is there a way to examine what's inside the clog? There are mentions about clog corruption 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.

Answer 1

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 clog.

The 2nd one has HEAP_XMAX_INVALID. That means it doesn't have to consult the clog, because someone has already done so, seen that the xmax is aborted, and set a "hint bit" so that future processes do not need to visit the clog again for that row.

Which flags (or combination of flags) tell the system to visit the clog?

If there is no heap_xmin_committed or heap_xmin_invalid, then you have to visit the clog to see what the disposition of xmin was. If the transaction is still in progress, then the row is not visible to you and you can't set any flags. If the transaction committed or rolled-back, you set the heap_xmin_committed or heap_xmin_invalid accordingly (if it is convenient to do that--it is not mandatory) so future people don't need to look it up.

If xmin is valid and committed, and if xmax is not zero, and there is no heap_max_committed or heap_max_invalid, then you have to visit the clog to see what the disposition of that transaction was.

Is there a way to examine what's inside the clog? There are mentions about clog corruption 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.

I'm not aware of a user-friendly way of doing so. You can use "od" to dump the clog files in a suitable way to inspect them, and figure out where to inspect by using the macros defined in src/backend/access/transam/clog.c

I'm surprised there are no extensions on PGXN that does the work for you, but I couldn't find one. But I think it wouldn't be all that useful, because you really need to be able to do this while your server is not running.

Answer 2

Have a look at HeapTupleSatisfiesMVCC() implementation: the actual clog check happens in TransactionIdDidCommit(), but it is only called if transaction status cannot be inferred from the infomask bits (HeapTupleHeaderXminCommitted() macro and friends).

I've traced back access to pg_clog to functions TransactionDidCommit() and TransactionDidAbort(), then I've looked up where these are called and the only place in the code related to your question appears to be in HeapTupleSatisfiesMVCC(). From the code of this function you can see that the actual clog lookup can only happen if the tuple doesn't have the related infomask bits set: the code starts with checking the bits with HeapTupleHeaderXminCommitted() et al. And clog lookup only happens if the bit(s) are not set.