Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#4012 closed defect (fixed)

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

Reported by: hsenot Owned by: Even Rouault
Priority: normal Milestone: 1.8.1
Component: OGR_SF Version: 1.8.0
Severity: normal Keywords: ogr2ogr pg
Cc:

Description (last modified by warmerdam)

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.

Attachments (5)

test_PG.bat (601 bytes) - added by hsenot 7 years ago.
Windows script to reproduce behaviour
test_run.log (787 bytes) - added by hsenot 7 years ago.
Script log on 1.7.1
test_run.2.log (520 bytes) - added by hsenot 7 years ago.
Script log on 1.9dev
test_run.3.log (518 bytes) - added by hsenot 7 years ago.
Script log on 1.8
test.7z (452 bytes) - added by hsenot 7 years ago.
Zip of the non-spatial MapInfo? file used

Download all attachments as: .zip

Change History (12)

Changed 7 years ago by hsenot

Attachment: test_PG.bat added

Windows script to reproduce behaviour

Changed 7 years ago by hsenot

Attachment: test_run.log added

Script log on 1.7.1

Changed 7 years ago by hsenot

Attachment: test_run.2.log added

Script log on 1.9dev

Changed 7 years ago by hsenot

Attachment: test_run.3.log added

Script log on 1.8

Changed 7 years ago by hsenot

Attachment: test.7z added

Zip of the non-spatial MapInfo? file used

comment:1 Changed 7 years ago by hsenot

Version: unspecified1.8.0

comment:2 Changed 7 years ago by Even Rouault

Component: defaultOGR_SF
Keywords: pg added
Owner: changed from warmerdam to 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.

comment:3 Changed 7 years ago by warmerdam

Description: modified (diff)

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.

comment:4 Changed 7 years ago by 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 ?

comment:5 Changed 7 years ago by 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.

comment:6 Changed 7 years ago by Even Rouault

Milestone: 1.8.1
Resolution: fixed
Status: newclosed

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)

comment:7 in reply to:  5 Changed 7 years ago by 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

Note: See TracTickets for help on using tickets.