Avoid detoasting failure after COMMIT inside a plpgsql FOR loop.
exec_for_query() normally tries to prefetch a few rows at a time
from the query being iterated over, so as to reduce executor
entry/exit overhead. Unfortunately this is unsafe if we have
COMMIT or ROLLBACK within the loop, because there might be
TOAST references in the data that we prefetched but haven't
yet examined. Immediately after the COMMIT/ROLLBACK, we have
no snapshots in the session, meaning that VACUUM is at liberty
to remove recently-deleted TOAST rows.
This was originally reported as a case triggering the "no known
snapshots" error in init_toast_snapshot(), but even if you miss
hitting that, you can get "missing toast chunk", as illustrated
by the added isolation test case.
To fix, just disable prefetching in non-atomic contexts. Maybe
there will be performance complaints prompting us to work harder
later, but it's not clear at the moment that this really costs
much, and I doubt we'd want to back-patch any complicated fix.
In passing, adjust that error message in init_toast_snapshot()
to be a little clearer about the likely cause of the problem.
Patch by me, based on earlier investigation by Konstantin Knizhnik.
Per bug #15990 from Andreas Wicht. Back-patch to v11 where
intra-procedure COMMIT was added.
Discussion: https://postgr.es/m/15990-eee2ac466b11293d@postgresql.org
Branch
------
REL_11_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/0c1b2cb17c2558e4fad897ded1818e465f370973
Modified Files
--------------
src/backend/access/heap/tuptoaster.c | 15 +++++++++-
src/pl/plpgsql/src/pl_exec.c | 11 +++++++
src/test/isolation/expected/plpgsql-toast.out | 43 +++++++++++++++++++++++++++
src/test/isolation/specs/plpgsql-toast.spec | 20 +++++++++++++
4 files changed, 88 insertions(+), 1 deletion(-)