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>,
        Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Subject: Re: BUG #15990: PROCEDURE throws "SQL Error [XX000]: ERROR: no known
 snapshots" with PostGIS geometries
In-reply-to: <912290.1620774094@sss.pgh.pa.us>
References: <15990-eee2ac466b11293d@postgresql.org>
 <20190904105618.j5l6fhyesmprmstf@alap3.anarazel.de>
 <912290.1620774094@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Tue, 11 May 2021 19:01:34 -0400"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <957138.1620784815.0@sss.pgh.pa.us>
Date: Tue, 11 May 2021 22:01:11 -0400
Message-ID: <957186.1620784871@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/957186.1620784871%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <957138.1620784815.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable

[ 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/03644c0e6bb82132ac783982b6a=
bffdf%40postgrespro.ru


------- =_aaaaaaaaaa0
Content-Type: text/x-diff; name="probably-incorrect-toast-fix.patch";
	charset="us-ascii"
Content-ID: <957138.1620784815.2@sss.pgh.pa.us>
Content-Description: probably-incorrect-toast-fix.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/backend/access/common/toast_internals.c b/src/backend/acc=
ess/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 =3D 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 =3D=3D NULL)
-		elog(ERROR, "no known snapshots");
+		snapshot =3D GetTransactionSnapshot();
 =

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

------- =_aaaaaaaaaa0--