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

[ Roping Robert into this, as committer of 3e2f3c2e4 ]

I wrote:
> After an admittedly cursory look-around, it seems like the problem
> can be stated as "init_toast_snapshot expects that there already
> is a transaction snapshot, which there is not because we just
> committed and nothing has re-established a transaction snapshot".
> So the question is, where shall we force a new transaction snapshot
> to be created after a COMMIT/ROLLBACK inside a procedure?

> The most localized fix would be to let init_toast_snapshot itself
> do that, but that seems like a bit of a layering violation; plus
> I'm not quite convinced that's the only place with the issue.

I tried this, which leads to a nicely small patch and seems to resolve
the existing reports, but now I'm not sure that it's actually safe.
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.

In a recent discussion at [1], Konstantin Knizhnik reasoned that the
problem is that plpgsql is holding rows that it's prefetched but not
yet detoasted, and proposed disabling prefetch to solve this.  I think
he's probably right, although his patch strikes me as both overcomplicated
and wrong.  I suspect we must disable prefetch in any non-atomic
execution context, because we can't know whether a COMMIT will be executed
by some called procedure.

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

            regards, tom lane

[1] https://www.postgresql.org/message-id/flat/03644c0e6bb82132ac783982b6abffdf%40postgrespro.ru

diff --git a/src/backend/access/common/toast_internals.c b/src/backend/access/common/toast_internals.c
index 730cd04a2d..386c5bda2b 100644
--- a/src/backend/access/common/toast_internals.c
+++ b/src/backend/access/common/toast_internals.c
@@ -638,8 +638,12 @@ init_toast_snapshot(Snapshot toast_snapshot)
 {
     Snapshot    snapshot = GetOldestSnapshot();

+    /*
+     * It is possible to get here when no snapshot has yet been established in
+     * the current transaction.  If so, just create a transaction snapshot.
+     */
     if (snapshot == NULL)
-        elog(ERROR, "no known snapshots");
+        snapshot = GetTransactionSnapshot();

     InitToastSnapshot(*toast_snapshot, snapshot->lsn, snapshot->whenTaken);
 }