From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: a.wicht@gmail.com
Subject: BUG #15990: PROCEDURE throws "SQL Error [XX000]: ERROR: no known snapshots" with PostGIS geometries
Date: Wed, 04 Sep 2019 10:06:16 +0000
Message-ID: <15990-eee2ac466b11293d@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 15990
Logged by: Andreas Wicht
Email address: a.wicht@gmail.com
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 instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, a.wicht@gmail.com
Subject: Re: BUG #15990: PROCEDURE throws "SQL Error [XX000]: ERROR: no known snapshots" with PostGIS geometries
In-Reply-To: <15990-eee2ac466b11293d@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox