From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
cc: Robert Haas <robertmhaas@gmail.com>, Andres Freund <andres@anarazel.de>,
        a.wicht@gmail.com, pgsql-bugs@lists.postgresql.org,
        Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Subject: Re: BUG #15990: PROCEDURE throws "SQL Error [XX000]: ERROR: no known
 snapshots" with PostGIS geometries
In-reply-to: <1032535.1620789957@sss.pgh.pa.us>
References: <15990-eee2ac466b11293d@postgresql.org>
 <20190904105618.j5l6fhyesmprmstf@alap3.anarazel.de>
 <912290.1620774094@sss.pgh.pa.us> <957186.1620784871@sss.pgh.pa.us>
 <1032535.1620789957@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Tue, 11 May 2021 23:25:57 -0400"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <1097478.1620833778.0@sss.pgh.pa.us>
Date: Wed, 12 May 2021 11:37:46 -0400
Message-ID: <1097541.1620833866@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1097541.1620833866%40sss.pgh.pa.us>
Precedence: bulk

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

... okay, now I'm roping Alvaro into this thread, because the attached
test case (extracted from [1]) shows that there's still a problem,
and this time it seems like we are dropping the ball on snapshot
management.

The sequence of events here is that after the first COMMIT inside the
loop, we call _SPI_execute_plan to execute the "select txt into t from
test2 where i=3Dr.i;".  It does what it's supposed to, i.e.

            PushActiveSnapshot(GetTransactionSnapshot());
            ... run query ...
            PopActiveSnapshot();

and then hands back a tuple that includes a toasted datum.  plpgsql
knows it must detoast that value before storing it into "t", but
when it calls the toaster, GetOldestSnapshot returns NULL because
we have neither any "active" nor any "registered" snapshots.

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.

Thoughts?

			regards, tom lane

[1] https://www.postgresql.org/message-id/65424747-42ed-43d5-4cca-6b034814=
09a4%40perfexpert.ch


------- =_aaaaaaaaaa0
Content-Type: text/plain; name="toastbug2.sql"; charset="us-ascii"
Content-ID: <1097478.1620833778.2@sss.pgh.pa.us>
Content-Description: toastbug2.sql

drop table if exists test2;
CREATE TABLE test2(i integer, txt text);
alter table test2 alter column txt set storage external;
insert into test2 values (1, lpad('x', 3000));
insert into test2 values (2, lpad('x', 3000));

DO $$
DECLARE
    r record;
    t text;
BEGIN
  FOR r in (SELECT i FROM test2)
     LOOP
       select txt into t from test2 where i=r.i;
       COMMIT;
    END LOOP;
END;
$$;

------- =_aaaaaaaaaa0--