Re: Database Horizon

On Thu, 2022-09-22 at 19:30 +0530, Goti wrote:
> I was reading through snapshot chapter in Egor Rogov's postgres internals and there I
> came across the below.. I am not sure how this is possible and how can I reproduce?
> Can someone explain the below 2 points if possible?
> 
> A real transaction at the Read Committed isolation level holds the database horizon
> in the same way, even if it is not executing any operators (being in the “idle in trasaction” state).
> 
> A virtual transaction at the Read Committed isolation level holds the horizon only while
> executing operators.

A transaction that changed something (this is what is meant by a "real transaction")
has a transaction ID.  VACUUM will not clean up tuples that have been invalidated after the
start of such a transaction, if the transaction is still active.  The transaction ID sets the
"xmin horizon" in such a case.

For a reading transaction, it is the xmin horizon of the current snapshot that holds back
VACUUM.  For a READ COMMITTED transaction, there is only a snapshot for running statements
and open cursors.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com