ogr2ogr does not see Postgresql schemas on gdal 3.9.0

[Nicolasribot]

What is the bug?

Running the following ogr2ogr command to import a GPKG file to a PostGIS database test (PG16.2, postgis 3.4.2) that contains a grid and osm schemas:

ogr2ogr -f Postgresql PG:"dbname=test" tileindexes.gpkg

causes the error:

ERROR 1: Schema "grid" does not exist

The GPKG file contains several tables:

INFO: Open of `tileindexes.gpkg'
      using driver `GPKG' successful.
1: osm.index_gfbk (Multi Polygon)
2: grid.tileindex_pluvial_1 (Polygon)
3: grid.tileindex_fluvial_1 (Polygon)
4: grid.tileindex_coastal_1 (Polygon)
5: grid.tileindex_10 (Polygon)
6: grid.tileindex_srtm_1 (Polygon)
7: grid.tileindex_ignlidarhd_1km (Polygon)

On previous GDAL versions, the GPKG tables grid.* were imported into the PG schema grid

Steps to reproduce the issue

• Create a GPKG file with a layer name grid.mylayer
• Create a schema grid in a postgis-enabled database
• Use ogr2ogr to import this GPKG into postgis

Versions and provenance

MacOS sonoma 14.5 GDAL from brew: GDAL 3.9.0, released 2024/05/07 Postgresql and postgis compiled on MacOS

Additional context

No response

[jratike80]

On previous GDAL versions, the GPKG tables grid.* were imported into the PG schema grid

I believe that such behavior has not been intentional, and I also believe that it has been wrong. SQLite does have sort of schema support with the attach database system https://sqlite.org/lang_attach.html. If there are no attached database "grid" then grid.tileindex_pluvial_1 is just a normal table with abnormal name in the default database, or schema. The full name for the table is then "main"."grid.tileindex_pluvial_1". Ogr2ogr should, for my mind, export that table into the default schema as create table "grid.tileindex_pluvial_1". Trying to find a schema from SQLite and doing create table "grid"."tileindex_pluvial_1" would be wrong.

But obviously there is some bug somewhere because you get ERROR 1: Schema "grid" does not exist.

[rouault]

@Nicolasribot A likely cause for the change of behavior is commit e166977235514f60db7beb60c3149258df03003f to fix #9125, but I cannot reproduce a failure:

$ ogr2ogr input.gpkg autotest/ogr/data/poly.shp -nln grid.poly
$ ogrinfo  pg:dbname=autotest -sql "create schema grid"
$ ogr2ogr -f Postgresql PG:"dbname=autotest" input.gpkg
$ ogrinfo PG:"dbname=autotest" grid.poly
INFO: Open of `PG:dbname=autotest'
      using driver `PostgreSQL' successful.

Layer name: grid.poly
[...]

Can you try running the following request against your database: SELECT nspname FROM pg_catalog.pg_namespace WHERE nspname ILIKE 'grid' . This is what GDAL now uses to figure out if the schema exists

[jratike80]

This sequence of commands worked for me, but for some reason the first run of ogr2ogr failed. The second one was successful. The errors from the first command:

ERROR 1: AddGeometryColumn failed for layer grid.poly.
ERROR 1: ERROR:  current transaction is aborted, commands ignored until end of transaction block

ERROR 1: ALTER TABLE "grid"."poly" ADD COLUMN "nr" INT8
ERROR 1: Unable to write feature 1 from layer grid.poly.
ERROR 1: ERROR:  relation "grid.poly" does not exist
LINE 1: ...equence('"grid"."poly"', 'fid'), MAX("fid")) FROM "grid"."po...

I think converting tables like "name.table" from SQLite into "name"."table" in PostGIS is odd. But perhaps it would feel odd another way round for other people.

[rouault]

This sequence of commands worked for me, but for some reason the first run of ogr2ogr failed

weird... Any way you would have a reproducer for that ? I've just tried in a fresh new database:

createdb tmptest
psql -d tmptest -c "create extension postgis"
psql -d tmptest -c "create schema grid"
ogr2ogr input.gpkg autotest/ogr/data/poly.shp -nln grid.poly
ogr2ogr -f postgresql pg:dbname=tmptest input.gpkg

I think converting tables like "name.table" from SQLite into "name"."table" in PostGIS is odd

yeah, one can dispute the appropriateness of doing this. But the driver has supported that for a long time

[Nicolasribot]

Thanks for the prompt reply. @rouault the query against catalog table worked:

createdb -p 16432 testgdal
psql -p 16432 -d testgdal -c "create extension postgis; create schema osm; create schema grid"
psql -p 16432 -d testgdal -c "SELECT nspname FROM pg_catalog.pg_namespace WHERE nspname ILIKE 'grid'"
#+---------+
#| nspname |
#+---------+
#| grid    |
#+---------+

The testcase I ran this morning is the following:

which gdalinfo
#/opt/homebrew/bin/gdalinfo
gdalinfo --version
#GDAL 3.9.0, released 2024/05/07
createdb -p 16432 testgdal
psql -p 16432 -d testgdal -c "create extension postgis; create schema osm; create schema grid"
ogr2ogr -f Postgresql PG:"dbname=testgdal port=16432" tileindexes.gpkg
#ERROR 1: ERROR:  current transaction is aborted, commands ignored until end of transaction block
#no COPY in progress
#
#ERROR 1: ERROR:  current transaction is aborted, commands ignored until end of transaction block
#
#ERROR 1: ERROR:  current transaction is aborted, commands ignored until end of transaction block
#
#ERROR 1: Schema "grid" does not exist.
#ERROR 1: Terminating translation prematurely after failed
#translation of layer grid.tileindex_pluvial_1 (use -skipfailures to skip errors)

/Applications/QGIS.app/Contents/MacOS/bin/ogr2ogr --version
#GDAL 3.3.2, released 2021/09/01
/Applications/QGIS.app/Contents/MacOS/bin/ogr2ogr -f Postgresql PG:"dbname=testgdal port=16432" tileindexes.gpkg
psql -p 16432 -d testgdal -c "select count(*) from grid.tileindex_pluvial_1"
#+-------+
#| count |
#+-------+
#| 20624 |
#+-------+
#(1 row)

I can provide the GPKG file, though its about 178MB (I also can try to reduce the dataset to keep only few geometries). This file was created from a postgis database, where tables were stored in grid and osm schemas.

I found the way ogr2ogr work quite handy in my case: keeping pg schema names in GPKG table names, and back to pg schema when importing to postgis. I aslo understand this mapping is debatable as schema feature in sqlite involves a separate file (it may not be convenient in all cases)

[rouault]

I can provide the GPKG file, though its about 178MB (I also can try to reduce the dataset to keep only few geometries).

yes that would be handy if you can provide a minimum reproducer.

[Nicolasribot]

Here it is. The commands I ran on this file:

env|sort|grep GDAL
#GDAL_DISABLE_READDIR_ON_OPEN=EMPTY_DIR
#GDAL_NUM_THREADS=ALL_CPUS
ogrinfo -so tileindexes_sample.gpkg
#INFO: Open of `tileindexes_sample.gpkg'
#      using driver `GPKG' successful.
#1: grid.tileindex_pluvial_1 (Polygon)
#2: grid.tileindex_10 (Polygon)
psql -p 16432 -d nicolas -c "drop database testgdal"
createdb -p 16432 testgdal
psql -p 16432 -d testgdal -c "create extension postgis; create schema osm; create schema grid"
ogr2ogr -f Postgresql PG:"dbname=testgdal port=16432" tileindexes_sample.gpkg
#ERROR 1: Schema "grid" does not exist.
/Applications/QGIS.app/Contents/MacOS/bin/ogr2ogr -f Postgresql PG:"dbname=testgdal port=16432" tileindexes_sample.gpkg
psql -p 16432 -d testgdal -c "select count(*) from grid.tileindex_pluvial_1"
#+-------+
#| count |
#+-------+
#|     4 |
#+-------+
#(1 row)

tileindexes_sample.gpkg.zip

[jratike80]

This sequence of commands worked for me, but for some reason the first run of ogr2ogr failed

weird... Any way you would have a reproducer for that ? I've just tried in a fresh new database:

I tried this and that but I could not reproduce. However, I notice that @Nicolasribot had similar error messages. Like COMMIT had been missing/staying in some queue from the "create schema grid" and therefore "#ERROR 1: Schema "grid" does not exist" in the next step.

[rouault]

Reproduced and fixed per https://github.com/OSGeo/gdal/pull/10327

The key to reproduce was to have an input dataset with several dotted layer names, whereas I tried with just one initially

Potential workarounds:

• use PG_USE_COPY=NO since the bug was specific to copy mode
• import one layer at a time

[Nicolasribot]

Thanks a lot for the quick fix and the tips !