Horton

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

Andres Freund <andres@anarazel.de>

View thread View on postgr.es
From: Andres Freund <andres@anarazel.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, Robert Haas <robertmhaas@gmail.com>, a.wicht@gmail.com, pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Date: 2021-05-12T15:51:53Z
Lists: pgsql-bugs 
Hi,

On 2021-05-12 11:37:46 -0400, Tom Lane wrote:
> ISTM there are two ways we could look at this:
> 
> 1. COMMIT is dropping the ball by not forcing there to be any
> registered transaction-level snapshot afterward.  (Maybe it's
> not exactly COMMIT that must do this, but in any case the
> snapshot situation after COMMIT is clearly different from
> normal running, and that seems highly bug-prone.)
> 
> 2. GetOldestSnapshot ought to be willing to fall back to
> CurrentSnapshot if FirstSnapshotSet is true but there are
> no active or registered snapshots.  But it's not clear how
> its promises about returning the "oldest" snapshot would apply.

FirstSnapshotSet doesn't indicate the snapshot is usable, unless
IsolationUsesXactSnapshot() - in which case it also registers the
snapshot. We don't maintain MyProc->xmin outside of that, which I think
means we can't rely on the snapshot at all?  Or am I missing something?

Greetings,

Andres Freund

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.