OGR2OGR fails importing data to postgis

[anguscarr]

When I run ogr2ogr, I get:

C:\Temp>"c:\program files\gdal\ogr2ogr" -f "PostgreSQL" PG:"host=127.0.0.1 user=apc dbname=FMP port=5433"
 -nln black_spruce_2011.aoc01 c:\temp\tmpsqqk3e PAL
ERROR 1: AddGeometryColumn failed for layer black_spruce_2011.aoc01,
 layer creation has failed.
ERROR 1: Terminating translation prematurely after failed 
translation of layer PAL (use -skipfailures to skip errors)

I had a look into the DB logs, and got this:

ERROR:  cannot delete from view "geometry_columns"
HINT:  You need an unconditional ON DELETE DO INSTEAD rule or an INSTEAD OF DELETE trigger.
STATEMENT:  DELETE FROM geometry_columns WHERE f_table_name = 
'aoc01' AND f_table_schema = 'black_spruce_2011'
ERROR:  current transaction is aborted, commands ignored until end of transaction block

For the short term, I added this rule:

CREATE OR REPLACE RULE ogr2ogr_delete_problem AS 
	ON DELETE TO public.geometry_columns
	DO INSTEAD NOTHING ;

Obviously, that's not a long-term solution, but I don't think I should fix it without looking to the larger community. I assume this is an OGR2OGR bug, but since my fix of the day is on my DB, I thought I would post here.

Is it safe to leave this rule in place for the long term? Right now, I am loading data into an empty db, and it's ok for now.

[anguscarr]

I am using OGR2OGR 1.8.1, and I observe that I have run into ogr2ogr bug ​http://trac.osgeo.org/gdal/ticket/4217 , which has been fixed in v1.9 beta.

My question remains is it safe to leave the rule in place? It maintains compatibility with my older clients (admittedly they are using QGIS v. 1.6 or so, so I can update them if needed).

[robe]

Angus,

I think that is safe enough. I was thinking we should probably do the same in PostGIS code or at the very least put it in our legacy_compatibility_layer.sql install file.

---

Paul,Strk,Mark, Chris — can you think of any reason why we shouldn't add that to the legacy_compatibility_layer.sql . I think the reason strk (or soeone else — can't remember) was against was because it was just silently ignoring a problem instead of fixing it and allowing the error to be raised would cause people to fix the issue in their underlying code. Unfortunately I don't think there is a way for us to throw up a notice in a rule.

For PostgreSQL 9.1 plus, we could use a view trigger instead and then we can raise a notice. For 9.0 and less, we can only using view rules which have to be pure slq.

[robe]

Angus, you had this marked as 1.5, but I assume you are running 2.0? Since there should be no issue with 1.5 since 1.5 uses a table. Just confirming

[anguscarr]

Replying to robe:

Angus, you had this marked as 1.5, but I assume you are running 2.0? Since there should be no issue with 1.5 since 1.5 uses a table. Just confirming

Yup. Oops.

For what it is worth, backwards compatibility here is more important than the correctness of code in individual projects.

If nothing else, perhaps this will give me a search term next time it comes up

[robe]

You know I just realized that the OGR case its just deleting a record from the view which I think is completely safe to ignore. Since the record would be gone anyway.

For inserts its a bit trickier and perhaps there is no need to take care of the insert case (or for that case we would want to put that in legacy).

[strk]

I see no reason not to add the RULE. We could even have it raise a WARNING so that clients are instructed about changing their code… We could have a rule for INSERT, UPDATE and DELETE.

[strk]

Rules added on INSERT/UPDATE/DELETE with r8749. No warning was implemented.