Re: BUG #15990: PROCEDURE throws "SQL Error [XX000]: ERROR: noknown snapshots" with PostGIS geometries
От | Andres Freund |
---|---|
Тема | Re: BUG #15990: PROCEDURE throws "SQL Error [XX000]: ERROR: noknown snapshots" with PostGIS geometries |
Дата | |
Msg-id | 20190904105618.j5l6fhyesmprmstf@alap3.anarazel.de обсуждение исходный текст |
Ответ на | BUG #15990: PROCEDURE throws "SQL Error [XX000]: ERROR: no known snapshots" with PostGIS geometries (PG Bug reporting form <noreply@postgresql.org>) |
Ответы |
Re: BUG #15990: PROCEDURE throws "SQL Error [XX000]: ERROR: no known snapshots" with PostGIS geometries
|
Список | pgsql-bugs |
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;ENDLOOP;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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: PG Bug reporting formДата:
Сообщение: BUG #15990: PROCEDURE throws "SQL Error [XX000]: ERROR: no known snapshots" with PostGIS geometries
Следующее
От: Juan José Santamaría FlechaДата:
Сообщение: Re: BUG #15858: could not stat file - over 4GB