ogr2ogr does not see Postgresql schemas on gdal 3.9.0 about gdal HOT 10 CLOSED

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.

from gdal.

@Nicolasribot A likely cause for the change of behavior is commit e166977 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

from gdal.

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.

from gdal.

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

from gdal.

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)

from gdal.

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.

from gdal.

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

from gdal.

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.

from gdal.

Reproduced and fixed per #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

from gdal.

Thanks a lot for the quick fix and the tips !

from gdal.