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
