Date: Wed, 4 Sep 2019 03:56:18 -0700
From: Andres Freund <andres@anarazel.de>
To: a.wicht@gmail.com, pgsql-bugs@lists.postgresql.org,
	Peter Eisentraut <peter_e@gmx.net>
Subject: Re: BUG #15990: PROCEDURE throws "SQL Error [XX000]: ERROR:  no
 known snapshots" with PostGIS geometries
Message-ID: <20190904105618.j5l6fhyesmprmstf@alap3.anarazel.de>
References: <15990-eee2ac466b11293d@postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <15990-eee2ac466b11293d@postgresql.org>
Precedence: bulk

Hi,

On 2019-09-04 10:06:16 +0000, PG Bug reporting form wrote:
> Note that the procedure fails as soon as the geometry column is part of the
> SELECT statement defining the FOR loop.
> Researching this error did not yield any useful information to me (at least
> none which is evident to me).

The error is from:
static void
init_toast_snapshot(Snapshot toast_snapshot)
{
	Snapshot	snapshot = GetOldestSnapshot();

	if (snapshot == NULL)
		elog(ERROR, "no known snapshots");

	InitToastSnapshot(*toast_snapshot, snapshot->lsn, snapshot->whenTaken);
}


> CREATE OR REPLACE PROCEDURE temp.testprocedure(polygon_tbl regclass)
> AS $$
> DECLARE
>     _poly_tbl   ALIAS FOR $1;
>     _rcd        RECORD;
> BEGIN
>     FOR _rcd IN
>         EXECUTE format ('SELECT gid, geom FROM %s', _poly_tbl)
>     LOOP
>         INSERT INTO temp.mytable (gid, geom) VALUES (_rcd.gid, _rcd.geom);
>         COMMIT;
>     END LOOP;
> END;
> $$
> LANGUAGE plpgsql;

Hm. I don't immediately see anything here that could really be postgis
specific. I assume it's just because the geom datum is large and gets
toasted.  A bit of playing shows that it can be reproduced without:

CREATE TABLE toasted(id serial primary key, data text);
INSERT INTO toasted(data) VALUES((SELECT string_agg(random()::text, ':') FROM generate_series(1, 1000)));
INSERT INTO toasted(data) VALUES((SELECT string_agg(random()::text, ':') FROM generate_series(1, 1000)));
INSERT 0 1

DO $$ DECLARE v_r record; BEGIN FOR v_r in SELECT data FROM toasted LOOP INSERT INTO toasted(data) VALUES(v_r.data);COMMIT;END LOOP;END;$$;

ERROR:  XX000: no known snapshots
CONTEXT:  PL/pgSQL function inline_code_block line 1 at FOR over SELECT rows
LOCATION:  init_toast_snapshot, tuptoaster.c:2416

Note that there's no errors if there's only one already in the table,
not if all the data is inserted without being sourced from a table.

This looks like it might be a procedure related bug to me. Peter?

The backtrace in my lightly modified tree is:

#0  init_toast_snapshot (toast_snapshot=0x7ffd5dc53280)
    at /home/andres/src/postgresql/src/backend/access/heap/tuptoaster.c:2416
#1  0x000055ee5a7fc0ef in toast_fetch_datum (attr=0x55ee5d155a78)
    at /home/andres/src/postgresql/src/backend/access/heap/tuptoaster.c:1930
#2  0x000055ee5a7f8bb2 in heap_tuple_fetch_attr (attr=0x55ee5d155a78)
    at /home/andres/src/postgresql/src/backend/access/heap/tuptoaster.c:108
#3  0x000055ee5a7fad29 in toast_flatten_tuple (tup=0x55ee5d155a48,
    tupleDesc=0x55ee5d14f510)
    at /home/andres/src/postgresql/src/backend/access/heap/tuptoaster.c:1110
#4  0x000055ee5ac77d32 in expanded_record_set_tuple (erh=0x55ee5d14f3f8,
    tuple=0x55ee5d155a48, copy=true, expand_external=true)
    at /home/andres/src/postgresql/src/backend/utils/adt/expandedrecord.c:473
#5  0x00007f4450307cef in exec_for_query (estate=0x7ffd5dc57920, stmt=0x55ee5d154b00,
    portal=0x55ee5d09e040, prefetch_ok=true)
    at /home/andres/src/postgresql/src/pl/plpgsql/src/pl_exec.c:5970
#6  0x00007f4450301984 in exec_stmt_fors (estate=0x7ffd5dc57920, stmt=0x55ee5d154b00)
    at /home/andres/src/postgresql/src/pl/plpgsql/src/pl_exec.c:2791
#7  0x00007f44502ffedc in exec_stmt (estate=0x7ffd5dc57920, stmt=0x55ee5d154b00)
    at /home/andres/src/postgresql/src/pl/plpgsql/src/pl_exec.c:1997
#8  0x00007f44502ffc94 in exec_stmts (estate=0x7ffd5dc57920, stmts=0x55ee5d154ef0)
    at /home/andres/src/postgresql/src/pl/plpgsql/src/pl_exec.c:1924
#9  0x00007f44502ffb40 in exec_stmt_block (estate=0x7ffd5dc57920, block=0x55ee5d154f28)
    at /home/andres/src/postgresql/src/pl/plpgsql/src/pl_exec.c:1865
#10 0x00007f44502ffdce in exec_stmt (estate=0x7ffd5dc57920, stmt=0x55ee5d154f28)
    at /home/andres/src/postgresql/src/pl/plpgsql/src/pl_exec.c:1957
#11 0x00007f44502fd542 in plpgsql_exec_function (func=0x55ee5d149a98,
    fcinfo=0x7ffd5dc57b60, simple_eval_estate=0x55ee5d125448, atomic=false)
    at /home/andres/src/postgresql/src/pl/plpgsql/src/pl_exec.c:589
#12 0x00007f44502f7d58 in plpgsql_inline_handler (fcinfo=0x7ffd5dc57c40)
    at /home/andres/src/postgresql/src/pl/plpgsql/src/pl_handler.c:339
#13 0x000055ee5adab248 in FunctionCall1Coll (flinfo=0x7ffd5dc57ca0, collation=0, arg1=94482251264896) at /home/andres/src/postgresql/src/backend/utils/fmgr/fmgr.c:1140
#14 0x000055ee5adabde6 in OidFunctionCall1Coll (functionId=13404, collation=0, arg1=94482251264896) at /home/andres/src/postgresql/src/backend/utils/fmgr/fmgr.c:1418
#15 0x000055ee5a981ab1 in ExecuteDoStmt (stmt=0x55ee5d037070, atomic=false) at /home/andres/src/postgresql/src/backend/commands/functioncmds.c:2266
#16 0x000055ee5ac265e8 in standard_ProcessUtility (pstmt=0x55ee5d037370,
    queryString=0x55ee5d0363a8 "DO $$ DECLARE v_r record; BEGIN FOR v_r in SELECT data FROM toasted LOOP INSERT INTO toasted(data) VALUES(v_r.data);COMMIT;END LOOP;END;$$;",
    context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x55ee5d037440, completionTag=0x7ffd5dc58140 "") at /home/andres/src/postgresql/src/backend/tcop/utility.c:523
#17 0x000055ee5ac26123 in ProcessUtility (pstmt=0x55ee5d037370,
    queryString=0x55ee5d0363a8 "DO $$ DECLARE v_r record; BEGIN FOR v_r in SELECT data FROM toasted LOOP INSERT INTO toasted(data) VALUES(v_r.data);COMMIT;END LOOP;END;$$;",
    context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x55ee5d037440, completionTag=0x7ffd5dc58140 "") at /home/andres/src/postgresql/src/backend/tcop/utility.c:360
#18 0x000055ee5ac24f8a in PortalRunUtility (portal=0x55ee5d09df28, pstmt=0x55ee5d037370, isTopLevel=true, setHoldSnapshot=false, dest=0x55ee5d037440,
    completionTag=0x7ffd5dc58140 "") at /home/andres/src/postgresql/src/backend/tcop/pquery.c:1175
#19 0x000055ee5ac251ae in PortalRunMulti (portal=0x55ee5d09df28, isTopLevel=true, setHoldSnapshot=false, dest=0x55ee5d037440, altdest=0x55ee5d037440,
    completionTag=0x7ffd5dc58140 "") at /home/andres/src/postgresql/src/backend/tcop/pquery.c:1321
#20 0x000055ee5ac246ba in PortalRun (portal=0x55ee5d09df28, count=9223372036854775807, isTopLevel=true, run_once=true, dest=0x55ee5d037440, altdest=0x55ee5d037440,
    completionTag=0x7ffd5dc58140 "") at /home/andres/src/postgresql/src/backend/tcop/pquery.c:796
#21 0x000055ee5ac1e0b8 in exec_simple_query (
    query_string=0x55ee5d0363a8 "DO $$ DECLARE v_r record; BEGIN FOR v_r in SELECT data FROM toasted LOOP INSERT INTO toasted(data) VALUES(v_r.data);COMMIT;END LOOP;END;$$;")
    at /home/andres/src/postgresql/src/backend/tcop/postgres.c:1231
#22 0x000055ee5ac2276a in PostgresMain (argc=1, argv=0x55ee5d05c758, dbname=0x55ee5d05c6a0 "postgres", username=0x55ee5d032918 "andres")
    at /home/andres/src/postgresql/src/backend/tcop/postgres.c:4256
#23 0x000055ee5ab72e74 in BackendRun (port=0x55ee5d057b00) at /home/andres/src/postgresql/src/backend/postmaster/postmaster.c:4446
#24 0x000055ee5ab725ce in BackendStartup (port=0x55ee5d057b00) at /home/andres/src/postgresql/src/backend/postmaster/postmaster.c:4137
#25 0x000055ee5ab6e702 in ServerLoop () at /home/andres/src/postgresql/src/backend/postmaster/postmaster.c:1704
#26 0x000055ee5ab6df34 in PostmasterMain (argc=37, argv=0x55ee5d030290) at /home/andres/src/postgresql/src/backend/postmaster/postmaster.c:1377
#27 0x000055ee5aa7bb76 in main (argc=37, argv=0x55ee5d030290) at /home/andres/src/postgresql/src/backend/main/main.c:210


Which seems to suggest that the snapshot management for procedures
(possibly not even just plpgsql), isn't quite right.

Greetings,

Andres Freund