[2.0 regression] ogr2ogr into PostGIS fails when overwriting several existing layers

[saultdon]

I compiled GDAL 2.0.0 from the 2.0 branch in svn for Linux 64 bit.

Converted a filegdb[1] to spatialite using:

ogr2ogr --config OGR_SQLITE_SYNCHRONOUS OFF --config OGR_SQLITE_CACHE 1024 -a_srs "EPSG:3005" -gt 65536 -update -progress -overwrite -f SQLite ../dra.sqlite /vsizip/dgtl_road_atlas.gdb.zip/dgtl_road_atlas.gdb -dsco SPATIALITE=YES -lco FORMAT=SPATIALITE -dim 2

But when I try to convert dra.sqlite to PostGIS with:

ogr2ogr -f PostgreSQL -gt 65536 -progress -update -overwrite -a_srs "EPSG:3005" pg:"dbname=bcgov host=localhost active_schema=dra" dra.sqlite -lco GEOMETRY_NAME=geom -lco OVERWRITE=yes -nlt PROMOTE_TO_MULTI

It outputs the following error(s):

ERROR 1: ERROR:  current transaction is aborted, commands ignored until end of transaction block

ERROR 1: 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: 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: 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: 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: 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: CREATE TABLE "dra"."dgtl_road_atlas_dpar_sp" ( "objectid" SERIAL, PRIMARY KEY ("objectid"), "id" INTEGER, "name_full" VARCHAR, "rd_class" VARCHAR, "alias1_id" INTEGER, "alias2_id" INTEGER, "alias3_id" INTEGER, "alias4_id" INTEGER, "feat_type" VARCHAR, "hwy_exit" VARCHAR, "hwy_rte" VARCHAR, "int_from" INTEGER, "int_to" INTEGER, "length_2d" FLOAT8, "length_3d" FLOAT8, "name_id" INTEGER, "name_ali1" VARCHAR, "name_ali2" VARCHAR, "name_ali3" VARCHAR, "name_ali4" VARCHAR, "rd_ln_tot" SMALLINT, "rd_surface" VARCHAR, "sup_cmpny" VARCHAR, "sup_detail" VARCHAR, "sup_method" VARCHAR, "sup_src_dt" VARCHAR, "geometry" geometry(MULTILINESTRING,1) NOT NULL )
ERROR:  current transaction is aborted, commands ignored until end of transaction block

ERROR 1: Unable to write feature 1 from layer dgtl_road_atlas_dpar_sp.

ERROR 1: Terminating translation prematurely after failed
translation of layer dgtl_road_atlas_dpar_sp (use -skipfailures to skip errors)

[1] ​ftp://ftp.geobc.gov.bc.ca/sections/outgoing/bmgs/DRA_Public/dgtl_road_atlas.gdb.zip

[Even Rouault]

I initially didn't manage to reproduce the problem, but when running again the conversion to PostgreSQL, I did reproduce it. Hum, so you've found a regression in 2.0 when inserting into PostGIS with existing layers, several layers at a time, and with COPY mode which is the default now.

Possible workarounds :

• add -lyr_transaction (so as to use transactions with a layer scope, instead of transactions with a dataset scope). This flag is undocumented and was a provision in case of issues with dataset transactions...
• insert one layer at a time
• add --config PG_USE_COPY NO
• manually remove the PostGIS tables before running ogr2ogr

Fixed by trunk r29464, branches/2.0 r29465 "Fix 2.0 regression when overwriting several existing PostGIS layers with ogr2ogr (#6018)"