From: | reg_pg_stefanz(at)perfexpert(dot)ch |
---|---|
To: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | ERROR: no known snapshots |
Date: | 2021-05-11 21:01:07 |
Message-ID: | dae29212-ad31-8701-ef16-dd7420bfaa56@perfexpert.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Dhanisha | 2021-05-11 22:42:27 | Re: [RPM/CentOS7] Need to disable repo_gpgcheck on pgdg-common when using RPM version 42.0-17.1 |
Previous Message | Vijaykumar Jain | 2021-05-11 18:33:06 | Re: force partition pruning |