OGR2OGR - Postgre import with -nlt flag set to NONE doesn't create a record in geometry_columns anymore

[hsenot]

The following command has different results in different versions of the driver:

ogr2ogr -f "PostgreSQL" %CS% "test.tab" -nlt NONE -nln test_table

where:

• test.tab is a MapInfo file containing a non-spatial layer (just a regular table)
• CS="PG:host='localhost' port='54321' dbname='test_ogr' user='test_ogr' password='test_ogr'"

In GDAL 1.7.1, released 2010/02/08, a record is inserted in geometry_columns. But in both in:

• GDAL 1.8.0, released 2011/01/12
• GDAL 1.9dev, released 2011/01/18

no record is inserted.

This makes it difficult to use OGR2OGR to update/append non-spatial objects. The driver looks for a record in geometry_columns to choose between a create statement (if no record) or an update statement (if record exists) - with the behaviour of driver >= 1.8, it attempts a create on an existing table and thus fails the ogr2ogr statement.

[hsenot]

Windows script to reproduce behaviour

[hsenot]

Script log on 1.7.1

[hsenot]

Script log on 1.9dev

[hsenot]

Script log on 1.8

[hsenot]

Zip of the non-spatial MapInfo file used

[Even Rouault]

Hum, I think it might be due to r20471. You can try workarounding your issues by defining the environment variable PG_LIST_ALL_TABLES to YES. See ​http://gdal.org/ogr/drv_pg_advanced.html

I might consider reverting the controversial part of this changeset to restore previous behaviour. I somehow remembered it fixed an issue, perhaps the creation of tables with NONE geometry type that no longer worked at that point of the 1.8.0 development, but I need to do more testing both in the Postgis-enabled database and non postgis-enable database cases.

[warmerdam]

I think that tables without geometry (ie. -nlt NONE) should *not* be in the geometry_columns table but perhaps we need some logic to avoid the failure you are seeing. I'm not planning on taking any action myself - I just wanted to put a stake in the ground with regard to non-spatial tables and geometry_columns.

[Even Rouault]

Frank,

The issue is that in a Postgis-enabled database (i.e. that has a geometry_columns table), only tables that have a geometry column are listed when you ask for the list of layers. I think this has always been the behaviour.

I can imagine different ways of fixing the issue :

• a minor change in ogr2ogr. Currently it iterates through the layer list to determine if the target layer exists or not. Which explains why it fails in that use case, since non-spatial table are not listed. ogr2ogr could instead use GetLayerByName() to check if the layer exists or not. In the PG driver, GetLayerByName() is specialized to be able to return a 'hidden' layer for a non-spatial table even if it was not listed initially. That would fix the ogr2ogr -update/-append cases (but ogrinfo would still not show the table)
• change the PG driver to return all tables, and not only Postgis ones (that's to say to behave list PG_LIST_ALL_TABLES=YES, but that's not that simple since in that case, the optimizations to fasten the opening of the database would be lost). I'm not sure if there are potential implications for some applications using OGR ??
• or, what you dislike, revert to < 1.8.0 behaviour where non-spatial layers where created with a geometry type = NONE and recorded in geometry_columns table, and thus appeared in the layer list.

Any thought ?

[warmerdam]

I don't like the last two options, so I suppose that leaves the first. I could also live with users who want to load non-spatial tables having to set PG_LIST_ALL_TABLES themselves to get access to non-spatial tables.

[Even Rouault]

r22018 /trunk/ (6 files in 5 dirs): ogr2ogr: make -overwrite/-append work with non-spatial tables created by GDAL 1.8.0; Add a NONE_AS_UNKNOWN layer creation option that can be set to TRUE to force layers with geom type = wkbNone to be created as if it was wkbUnknown (PostGIS GEOMETRY type) to be able to revert to behaviour prior to GDAL 1.8.0 (#4012)

r22019 /branches/1.8/ (6 files in 5 dirs): ogr2ogr: make -overwrite/-append work with non-spatial tables created by GDAL 1.8.0; Add a NONE_AS_UNKNOWN layer creation option that can be set to TRUE to force layers with geom type = wkbNone to be created as if it was wkbUnknown (PostGIS GEOMETRY type) to be able to revert to behaviour prior to GDAL 1.8.0 (#4012)

[hsenot]

Replying to warmerdam:

I don't like the last two options, so I suppose that leaves the first. I could also live with users who want to load non-spatial tables having to set PG_LIST_ALL_TABLES themselves to get access to non-spatial tables.

Thanks, the PG_LIST_ALL_TABLES config option works perfectly for us (sorry I did not realize this option could be used before posting). Regards, Herve