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
