Обсуждение: 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