Save as PG dump create obsolete dumps

[pcav]

When saving a shp as PG dump (I do it through QGIS, but this should not matter, as it is using GDAL code for this), I get an obsolete dump:

SET standard_conforming_strings = OFF;
DROP TABLE IF EXISTS "public"."test_export" CASCADE;
DELETE FROM geometry_columns WHERE f_table_name = 'test_export' AND
f_table_schema = 'public';
BEGIN;
CREATE TABLE "public"."test_export" ( "ogc_fid" SERIAL, CONSTRAINT
"test_export_pk" PRIMARY KEY ("ogc_fid") );
SELECT
AddGeometryColumn('public','test_export','wkb_geometry',4326,'LINESTRING',2);

...

which it is not using the modern syntax, with standard geom name

geom GEOMETRY(LineString, 4326));

Debian Sid
GDAL 3.1.3+dfsg-1

[pcav]

This is a serious bug, because it prevents the import of layers in PostGIS 3.x

[pcav]

ERROR:  function addgeometrycolumn(unknown, unknown, unknown, integer, unknown, integer) does not exist
RIGA 1: SELECT AddGeometryColumn('public','world_simpl',NULL,3857,'M...
               ^
NOTA: No function matches the given name and argument types. You might need to add explicit type casts.

[rouault]

This is a serious bug, because it prevents the import of layers in PostGIS 3.x

weird, the AddGeometryColumn() geometry is still in https://github.com/postgis/postgis/blob/master/postgis/postgis.sql.in#L2376
Are you sure your PostGIS install is OK ?

[jgrocha]

Hi @pcav
I still have the AddGeometryColumn in Postgis 3. Tested on Ubuntu, one with Postgis compiled from sources and one installed from packages.

Full log (on POSTGIS="3.1.0dev 3.1.0alpha2-36-g25825b61d" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="7.2.0" LIBXML="2.9.10" LIBJSON="0.13.1")

postgres=# create database test;
CREATE DATABASE
postgres=# \c test
psql (13.0 (Ubuntu 13.0-1.pgdg20.04+1), server 12.4 (Ubuntu 12.4-1.pgdg20.04+1))
You are now connected to database "test" as user "postgres".
test=# create extension postgis;
CREATE EXTENSION
test=# select postgis_full_version();
test=# SET standard_conforming_strings = OFF;
SET
test=# DROP TABLE IF EXISTS "public"."test_export" CASCADE;
NOTICE:  table "test_export" does not exist, skipping
DROP TABLE
test=# DELETE FROM geometry_columns WHERE f_table_name = 'test_export' AND
test-# f_table_schema = 'public';
DELETE 0
test=# BEGIN;
BEGIN
test=*# CREATE TABLE "public"."test_export" ( "ogc_fid" SERIAL, CONSTRAINT
test(*# "test_export_pk" PRIMARY KEY ("ogc_fid") );
CREATE TABLE
test=*# SELECT
test-*# AddGeometryColumn('public','test_export','wkb_geometry',4326,'LINESTRING',2);
                         addgeometrycolumn                         
-------------------------------------------------------------------
 public.test_export.wkb_geometry SRID:4326 TYPE:LINESTRING DIMS:2 
(1 row)

test=*# 

Have you installed postgresql-xx-postgis-3-scripts package?

Postgis related problem

I don't think this is a GDAL issue. GDAL still used this syntax because it can be used accross more versions of Postgis.

[pcav]

Yes, scripts installed. One more test: it fails also with Postgresql11/postgis2.5

[pcav]

so it might be a problem on the pg/packaging side after all.
in any case, why using the old syntax?

[jgrocha]

Yes, scripts installed. One more test: it fails also with Postgresql11/postgis2.5

The scripts should have created the function. Can you check the search path? Try to reset the search path. Was the Postgis installed in another schema than public?

[jgrocha]

GDAL still used this syntax because it can be used across more versions of Postgis.

PostgreSQL typmod syntax is supported only in more recent versions.

[rouault]

@pcav Just tried the following and it works fine

docker pull kartoza/postgis:latest   # PostgreSQL 13 with PostGIS 3
docker run --name "postgis" -p 25432:5432 -d -t kartoza/postgis
ogr2ogr -f pgdump poly.sql poly.shp
psql -h localhost -U docker  -p 25432 -d gis -f poly.sql
ogrinfo "pg:dbname=gis port=25432 host=localhost user=docker password=docker"

[pcav]

Yes, scripts installed. One more test: it fails also with Postgresql11/postgis2.5

The scripts should have created the function. Can you check the search path?

default search_path

Try to reset the search path. Was the Postgis installed in another schema than public?

not, a plain install

[rouault]

not, a plain install

There might be something particular with your conf. I've just done the following successfully:

docker pull debian:sid
docker run --rm -it -p 25432:5432 debian:sid
# inside Docker container
apt update
apt install postgresql postgis
su - postgres
echo "listen_addresses = '*'" >> /etc/postgresql/13/main/postgresql.conf
echo "host all postgres 0.0.0.0/0 trust" >> /etc/postgresql/13/main/pg_hba.conf
exit
service postgresql restart
# other console, outside Docker container
psql -h 127.0.0.1 -U postgres -d postgres  -p 25432 -c "CREATE EXTENSION postgis"
psql -h 127.0.0.1 -U postgres -d postgres  -p 25432 -f poly.sql

[pcav]

Indeed, on a fresh db it works. I need to investigate further, sorry for the noise.