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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

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

PG Doc comments form
The following bug has been logged on the website:

Bug reference:      15990
Logged by:          Andreas Wicht
Email address:      [hidden email]
PostgreSQL version: 11.5
Operating system:   Ubuntu 18.04
Description:        

Hi there,

I am not sure where to place this problem, here or at the PostGIS mailing
list. I'd like to start here though.
I have a function which needs a commit after each loop (inserting a result
into a target table). So far I worked around this requirement with dblink.
When the new procedures were implemented I tried to port the function to a
procedure, greatly reducing the complexity.
While testing I started to get the above mentioned error.
I could dumb the procedure down to the very basics to reproduce the error.

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).

Steps to reproduce:
CREATE EXTENSION postgis;
CREATE SCHEMA temp;

wget
https://www.statistik-berlin-brandenburg.de/opendata/RBS_OD_ORT_2016_12.zip
unzip RBS_OD_ORT_2016_12.zip
shp2pgsql -I -g geom -s 25833 RBS_OD_ORT_2016_12.shp temp.test | psql -h XXX
-p XXX -d XXX -U XXX

CREATE TABLE temp.mytable (gid integer, geom geometry);

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;

CALL temp.testprocedure('temp.test');

---------
PostGIS version:
POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="110" GEOS="3.6.2-CAPI-1.10.2
4d2925d6" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.3, released
2017/11/20" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" TOPOLOGY
RASTER

PostgeSQL version:
PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled
by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit

Greetings
Andreas

Reply | Threaded
Open this post in threaded view
|

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

Andres Freund
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