Обсуждение: Transaction wraparound and read committed isolation level
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/routine-vacuuming.html Description: hey guys thanks for the work you do we really appreciate it. In the transaction wraparound section this statement is misleading and got me really confused. “a row version with an insertion XID greater than the current transaction's XID is “in the future” and should not be visible to the current transaction“ If the current transaction isolation level is read committed it absolutely can see rows committed by future transactions with higher XIDs. Would be cool to add a note. this also bear the question that the wraparound isn’t really a problem with default isolation level but more for higher levels such as repeatable read and such. please correct me if my understanding is incorrect.
On Sun, 2023-02-19 at 03:10 +0000, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/routine-vacuuming.html > Description: > > hey guys > > thanks for the work you do we really appreciate it. > > In the transaction wraparound section this statement is misleading and got > me really confused. > > “a row version with an insertion XID greater than the current transaction's > XID is “in the future” and should not be visible to the current > transaction“ > > If the current transaction isolation level is read committed it absolutely > can see rows committed by future transactions with higher XIDs. You are right, in combination with PlanQualEval you can. > Would be cool to add a note. > > this also bear the question that the wraparound isn’t really a problem with > default isolation level but more for higher levels such as repeatable read > and such. > > please correct me if my understanding is incorrect. Wraparound can be a problem on all isolation levels. It has to do with transaction IDs and visibility. Yours, Laurenz Albe
On Tue, Feb 21, 2023 at 1:51 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > If the current transaction isolation level is read committed it absolutely > > can see rows committed by future transactions with higher XIDs. > > You are right, in combination with PlanQualEval you can. You don't need to bring EvalPlanQual (RC update conflict handling) into it. All that you need is two statements within the same READ COMMITTED transaction, combined with a second concurrently executing transaction. The second transaction need only start after the first one (giving it a later XID), and then commit before the first transaction's second statement begins. Each RC statement gets its own snapshot, so the second statement in the first transaction can see any effects from the first transaction. > > this also bear the question that the wraparound isn’t really a problem with > > default isolation level but more for higher levels such as repeatable read > > and such. > > > > please correct me if my understanding is incorrect. > > Wraparound can be a problem on all isolation levels. It has to do with > transaction IDs and visibility. The way that the docs explain wraparound is seriously misleading. -- Peter Geoghegan
On Tue, Feb 21, 2023 at 9:46 AM Peter Geoghegan <pg@bowt.ie> wrote: > All that you need is two statements within the same READ COMMITTED > transaction, combined with a second concurrently executing > transaction. The second transaction need only start after the first > one (giving it a later XID), and then commit before the first > transaction's second statement begins. Each RC statement gets its own > snapshot, so the second statement in the first transaction can see any > effects from the first transaction. Correction: Each RC statement gets its own snapshot, so the second statement in the first transaction can see any effects from the *second* (now committed) transaction. -- Peter Geoghegan