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

Wed Nov 30 07:12:13 PST 2022

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).
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20221130/e6c91da3/attachment.htm>