PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15990
PG Version11.5
OSUbuntu 18.04
Opened2019-09-04 10:06:16+00
Reported byAndreas Wicht
StatusNew

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

Messages

DateAuthorSubject
2019-09-04 10:06:16+00PG Bug reporting formBUG #15990: PROCEDURE throws "SQL Error [XX000]: ERROR: no known snapshots" with PostGIS geometries
2019-09-04 10:56:18+00Andres FreundRe: BUG #15990: PROCEDURE throws "SQL Error [XX000]: ERROR: no known snapshots" with PostGIS geometries