[gdal-dev] Trouble using TEMPORARY=ON with ogr2ogr
Even Rouault
even.rouault at spatialys.com
Sun Dec 11 13:58:14 PST 2022
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 list
> gdal-dev at lists.osgeo.org
> https://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/20221211/e81eadbd/attachment.htm>
More information about the gdal-dev
mailing list