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

... 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=r.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-6b03481409a4%40perfexpert.ch

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;
$$;