Re: BUG #15990: PROCEDURE throws "SQL Error [XX000]: ERROR: no known snapshots" with PostGIS geometries

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