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]/messages/by-id/1032535.1620789957@sss.pgh.pa.us 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
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