Collected from the PG bugs email list.
|Reported by||Andreas Wicht|
Body of first available message related to this bug follows.
The following bug has been logged on the website: Bug reference: 15990 Logged by: Andreas Wicht Email address: (redacted) 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
|2019-09-04 10:06:16+00||PG Bug reporting form||BUG #15990: PROCEDURE throws "SQL Error [XX000]: ERROR: no known snapshots" with PostGIS geometries|
|2019-09-04 10:56:18+00||Andres Freund||Re: BUG #15990: PROCEDURE throws "SQL Error [XX000]: ERROR: no known snapshots" with PostGIS geometries|