Thread: ERROR: no known snapshots

Hi

it run into an issue with: ERROR:  no known snapshots

It seems to me whenever I use a toasted value in a loop in plpgsql code  
I get this error. Originally it happened in a procedure with a loop, 
without a setting of and explicit storage on the column, eg. extended.
I can reproduce the error with the simplified code below, when I force 
it it with external setting.
Is this a known issue, is there something wrong with the code or is 
there a workaround?

What I found so far:
- using set storage main and hoping 8K is enough seems to work so far
- without the commit it does not happen (originally this was on purpose 
as there was  more code in between, this is just a stripped down version)

Stefan


drop table if exists test1;
CREATE TABLE test1(i integer, txt text);
insert into test1 values (1, lpad('x', 3000));
insert into test1 values (2, lpad('x', 3000));

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

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

\echo test2
DO $$
DECLARE
    r record;
    t text;
BEGIN
  FOR r in (SELECT txt FROM test2)
     LOOP
       t:=r.txt;
       COMMIT;
    END LOOP;
END;
$$;

\q

DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
DROP TABLE
CREATE TABLE
ALTER TABLE
INSERT 0 1
INSERT 0 1
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

reg_pg_stefanz@perfexpert.ch writes:
> it run into an issue with: ERROR:  no known snapshots

Hi!  This has been reported before, but I'm embarrassed to say that
the previous report seems to have fallen between the cracks.
I've created a patch [1] that resolves your example as presented.
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.

            regards, tom lane

[1] https://www.postgresql.org/message-id/1032535.1620789957%40sss.pgh.pa.us

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

reg_pg_stefanz@perfexpert.ch writes:
> 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:

Ugh.  Thanks for the test case!

            regards, tom lane