[gdal-dev] Trouble using TEMPORARY=ON with ogr2ogr

Tue Dec 13 18:46:36 PST 2022

Thank you (and Jeremy Palmer) so much!
I eagerly await the release with the fix. I'd be happy to contribute a
usage example to the documentation if that's helpful.
Daniel Mannarino

On Sun, Dec 11, 2022 at 4:58 PM Even Rouault <even.rouault at spatialys.com>
wrote:

> FYI Jeremy Palmer has just submitted a fix for that:
> https://github.com/OSGeo/gdal/pull/6896
> Le 30/11/2022 à 16:12, Daniel Mannarino a écrit :
>
> Hello!
>
>
> I'm having trouble using the temporary table option of ogr2ogr:
> https://gdal.org/drivers/vector/pg.html#layer-creation-options
>
> Ultimately I am trying to load data from a Shapefile into a temporary
> table, enrich the rows in the temporary table, and then copy from the
> temporary table into a final table. However I cannot get Postgres and
> ogr2ogr to work together. It sounds to me from the logs that they disagree
> on the table name. In my example code below I have removed everything that
> is to happen after loading the data (enriching, copying) for clarity.
>
>
> Very little is said about the temporary table option on the web, but from
> the source (and Postgres logs) it looks like ogr2ogr assumes a schema of
> "pg_temp_1":
> https://github.com/OSGeo/gdal/blob/dc39f7d68095621842a2547ab06c60a03fd34a19/ogr/ogrsf_frmts/pg/ogrpgdatasource.cpp#L1865-L1872
>
>
> It sounds to me from the Postgres docs this isn't safe to assume, but
> let's say it is for the moment.
>
>
> Here is my simplified script:
>
> #!/bin/bash
>
> set -e
>
>
> PGHOST="<host>"
>
> PGDATABASE="<db>"
>
> PGUSER="<user>"
>
> PGPASSWORD="<pass>"
>
> PGPORT=<port>
>
>
> LOCAL_FILE="/vsizip/test.shp.zip"
>
> SRC_LAYER="test"
>
> FID_NAME="gfw_fid"
>
> GEOMETRY_NAME="geom"
>
> TEMP_SCHEMA="pg_temp_1"
>
> TEMP_TABLE="bazinga"
>
>
>
> ogr2ogr -f "PostgreSQL" PG:"password=$PGPASSWORD host=$PGHOST port=$PGPORT
> dbname=$PGDATABASE user=$PGUSER" \
>
>     "$LOCAL_FILE" "$SRC_LAYER" \
>
>     -lco GEOMETRY_NAME="$GEOMETRY_NAME" \
>
>     -lco SPATIAL_INDEX=NONE \
>
>     -lco FID="$FID_NAME" \
>
>     -lco TEMPORARY=ON \
>
>     -nlt PROMOTE_TO_MULTI \
>
>     -nln "$TEMP_SCHEMA.$TEMP_TABLE" \
>
>    -t_srs EPSG:4326 \
>
>     --config PG_USE_COPY YES \
>
>     -makevalid -update
>
>
> # End of script
>
>
>
> Here is the Postgres log from attempting to run this script with the
> Shapefile in the PS:
>
>
> # START LOG
>
> 2022-11-28 21:16:51.206 UTC [504] LOG:  statement: set client_encoding to
> 'UTF8'
>
> 2022-11-28 21:16:51.209 UTC [504] LOG:  execute <unnamed>: SHOW
> client_encoding
>
> 2022-11-28 21:16:51.239 UTC [504] LOG:  execute <unnamed>: SELECT
> n.nspname FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
> WHERE proname = 'postgis_version'
>
> 2022-11-28 21:16:51.245 UTC [504] LOG:  execute <unnamed>: SELECT version()
>
> 2022-11-28 21:16:51.246 UTC [504] LOG:  execute <unnamed>: SHOW
> standard_conforming_strings
>
> 2022-11-28 21:16:51.250 UTC [504] LOG:  execute <unnamed>: SELECT oid,
> typname FROM pg_type WHERE typname IN ('geometry', 'geography') AND
> typtype='b'
>
> 2022-11-28 21:16:51.323 UTC [504] LOG:  execute <unnamed>: SELECT
> postgis_version()
>
> 2022-11-28 21:16:51.326 UTC [504] LOG:  execute <unnamed>: SET
> ENABLE_SEQSCAN = ON
>
> 2022-11-28 21:16:51.342 UTC [504] LOG:  execute <unnamed>: SELECT 1 FROM
> information_schema.tables WHERE table_name = 'geometry_columns' LIMIT 1
>
> 2022-11-28 21:16:51.345 UTC [504] LOG:  execute <unnamed>: SELECT 1 FROM
> information_schema.tables WHERE table_name = 'spatial_ref_sys' LIMIT 1
>
> 2022-11-28 21:16:51.354 UTC [504] LOG:  execute <unnamed>: SELECT
> ST_Srid('POINT EMPTY'::GEOMETRY)
>
> 2022-11-28 21:16:51.356 UTC [504] LOG:  execute <unnamed>: SELECT
> current_schema()
>
> 2022-11-28 21:16:51.394 UTC [504] LOG:  execute <unnamed>: BEGIN
>
> 2022-11-28 21:16:51.415 UTC [504] LOG:  execute <unnamed>: SELECT c.oid
> FROM pg_class c JOIN pg_namespace n ON c.relnamespace=n.oid WHERE c.relname
> = 'bazinga' AND n.nspname = 'pg_temp_1'
>
> 2022-11-28 21:16:51.418 UTC [504] LOG:  execute <unnamed>: SELECT c.oid
> FROM pg_class c JOIN pg_namespace n ON c.relnamespace=n.oid WHERE c.relname
> = 'bazinga' AND n.nspname = 'pg_temp_1'
>
> 2022-11-28 21:16:51.439 UTC [504] LOG:  execute <unnamed>: SELECT
> c.relname, n.nspname, c.relkind, a.attname, t.typname,
> postgis_typmod_dims(a.atttypmod) dim, postgis_typmod_srid(a.atttypmod)
> srid, postgis_typmod_type(a.atttypmod)::text geomtyp,
> array_agg(pg_get_constraintdef(s.oid))::text att_constraints, a.attnotnull,
> d.description FROM pg_class c JOIN pg_attribute a ON a.attrelid=c.oid JOIN
> pg_namespace n ON c.relnamespace = n.oid AND c.relkind in ('r','v','m','f')
> AND NOT ( n.nspname = 'public' AND c.relname = 'raster_columns' ) JOIN
> pg_type t ON a.atttypid = t.oid AND (t.typname = 'geometry'::name OR
> t.typname = 'geography'::name) LEFT JOIN pg_constraint s ON s.connamespace
> = n.oid AND s.conrelid = c.oid AND a.attnum = ANY (s.conkey) AND
> (pg_get_constraintdef(s.oid) LIKE '%geometrytype(% = %' OR
> pg_get_constraintdef(s.oid) LIKE '%ndims(% = %' OR
> pg_get_constraintdef(s.oid) LIKE '%srid(% = %') LEFT JOIN pg_description d
> ON d.objoid = c.oid AND d.classoid = 'pg_class'::regclass::oid AND
> d.objsubid = 0 GROUP BY c.relname, n.nspname, c.relkind, a.attname,
> t.typname, dim, srid, geomtyp, a.attnotnull, c.oid, a.attnum, d.description
> ORDER BY c.oid, a.attnum
>
> 2022-11-28 21:16:51.452 UTC [504] LOG:  execute <unnamed>: SELECT srid
> FROM spatial_ref_sys WHERE auth_name = 'EPSG' AND auth_srid = 4326
>
> 2022-11-28 21:16:51.545 UTC [504] LOG:  execute <unnamed>: CREATE
> TEMPORARY TABLE "bazinga" ( "gfw_fid" SERIAL, PRIMARY KEY ("gfw_fid"),
> "fid" NUMERIC(11,0), "geom" geometry(MULTIPOLYGON,4326) )
>
> 2022-11-28 21:16:51.575 UTC [504] LOG:  execute <unnamed>: COMMENT ON
> TABLE "pg_temp_1"."bazinga" IS NULL
>
> 2022-11-28 21:16:51.575 UTC [504] ERROR:  relation "pg_temp_1.bazinga"
> does not exist
>
> 2022-11-28 21:16:51.575 UTC [504] STATEMENT:  COMMENT ON TABLE
> "pg_temp_1"."bazinga" IS NULL
>
> 2022-11-28 21:16:51.585 UTC [504] ERROR:  current transaction is aborted,
> commands ignored until end of transaction block
>
> 2022-11-28 21:16:51.585 UTC [504] STATEMENT:  COPY "pg_temp_1"."bazinga"
> ("geom", "fid") FROM STDIN;
>
> 2022-11-28 21:16:51.587 UTC [504] LOG:  execute <unnamed>: ROLLBACK
>
>
> # END LOG
>
>
>
> I have tried many variations of quoting the table and schema name to no
> avail (including
>
>     -nln "\"$TEMP_SCHEMA\".\"$TEMP_TABLE\"" \
>
> ).
>
>
> I also tried wrapping the whole thing in a transaction by specifying
>
>     -doo PRELUDE_STATEMENTS="BEGIN;" \
>
>     -doo CLOSING_STATEMENTS="COMMIT;" \
>
>
> Can anyone explain how to get this to work? I actually first tried to
> create a temporary table by myself with -doo, but ran into other problems
> and will leave that for another thread.
>
>
> Thanks!
>
> Daniel Mannarino
>
>
> PS: Here's the Shapefile I'm using:
> https://github.com/wri/gfw-data-api/blob/master/tests/fixtures/test.shp.zip
>
> PPS: I am running PostgreSQL 12 (specifically the
> postgis/postgis:12-2.5-alpine Docker image) and running ogr2ogr from
> Homebrew's GDAL 3.5.3 package on MacOS 12.6 (amd64).
>
> _______________________________________________
> gdal-dev mailing listgdal-dev at lists.osgeo.orghttps://lists.osgeo.org/mailman/listinfo/gdal-dev
>
> -- http://www.spatialys.com
> My software is free, but my time generally not.
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20221213/349b976d/attachment-0001.htm>