While working on a doc patch for this, I realized that the situation is worse than I originally thought. This means that anyone relying on pg_stat_activity.xact_start is not really seeing the time of the snapshot. They are seeing the time that BEGIN was issued. Further, there is no way to tell (AFAICT) when the snapshot was granted (i.e. when the transaction actually started for purposes of MVCC comparisons). All we can guarantee via pg_stat_activity is that if xact_start and query_start *are* identical, no snapshot has been granted yet, and if they are not identical, then the snapshot *might* have been granted, might not (depending on SHOW vs SELECT for example). I suppose checking "query" could show that, but all you have then is a general window saying that the snapshot was created sometime after xact_start but no later than query_start (and could be a lot earlier if this ain't query number one).
Maybe we doc patch pg_stat_activity too? Actually, let me just post my quick work-in-progress patch here in the meantime for discussion.
Cheers,
Greg
Attachment:
0001-Clarify-READ-REPEATABLE-behavior-a-bit-more.patch
Description: Binary data
