Re: ERROR: no known snapshots

On 12.05.2021 05:42, Tom Lane wrote:
> If you're in a position to apply the patch and see if it resolves
> your real non-simplified case, that would be very helpful.
>
> Also, this fix in principle will create a small performance
> penalty for FOR-loops in non-atomic contexts such as DO loops.
> It'd be interesting to know if the penalty is noticeable in
> your usage.

Provided I understood your comment in the code, the slight performance
impact should be due to the disabled prefetching. That should not be an
issue, but I have not yet tested this.
What I have tested, I applied the patch to master and tested the actual
code against the new build on a small testserver. This works for me.

However, I poked around a little bit, and this does not seem to solve
all potential use cases, when I modify the simplified test by deferring
the lookup to be done inside the loop as an extra lookup instead of
doing it directly in the loop (whether that makes sense is another
question) , then this still produces the error:

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

\echo test2
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;
$$;

test1
DO
test2
psql:snapshot_error.sql:38: ERROR:  no known snapshots
CONTEXT:  PL/pgSQL function inline_code_block line 6 at FOR over SELECT rows