Horton

Re: BUG #15990: PROCEDURE throws "SQL Error [XX000]: ERROR: no known snapshots" with PostGIS geometries

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Andres Freund <andres@anarazel.de>, a.wicht@gmail.com, pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Date: 2021-05-12T03:25:57Z
Lists: pgsql-bugs

Attachments

I wrote:
> I think the bigger-picture question is, if we're trying to detoast
> as the first step in a new transaction of a procedure, where's the
> guarantee that the TOAST data still exists to be fetched?  For sure
> we aren't holding any locks that would stop VACUUM from reclaiming
> recently-dead TOAST rows.

Yeah.  So here's a patch I actually believe in to some extent, based
on Konstantin's idea.

> I'm still wondering why plpgsql-toast.spec is failing to show the
> problem, too.

The answer to that is that it's not testing commit-inside-a-cursor-loop,
and if it were, it'd still fail to show the problem because its test
table contains just one row.

Interestingly, if you try the test case added here without adding the
code patch, you get a "missing chunk number ... for toast value ..."
error, not "no known snapshots".  I think that's because the test
case has additional commands after the COMMIT, causing the transaction
snapshot to get re-established.

			regards, tom lane

Commits

  1. Remove plpgsql's special-case code paths for SET/RESET.

  2. Restore the portal-level snapshot after procedure COMMIT/ROLLBACK.

  3. Avoid detoasting failure after COMMIT inside a plpgsql FOR loop.