ERROR: no known snapshots

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

Responses

Browse pgsql-general by date

  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