Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

ogr2ogr doesn't work with PostgreSQL 12beta #1692

Closed
robe2 opened this issue Jul 2, 2019 · 3 comments
Closed

ogr2ogr doesn't work with PostgreSQL 12beta #1692

robe2 opened this issue Jul 2, 2019 · 3 comments

Comments

@robe2
Copy link
Contributor

robe2 commented Jul 2, 2019 •
edited
Loading

Expected behavior and actual behavior.

I was testing out a geopackage this query on PostgreSQL 12 beta2 (I know it's not released yet)
using the geopackage from https://gadm.org/download_world.html
(I used the world version, but I think any will d)

Steps to reproduce the problem.

ogr2ogr -f "PostgreSQL" PG:"host=localhost user=postgres port=5449 dbname=postgis_in_action password=mypassword" gadm36_levels.gpkg -lco GEOMETRY_NAME=geom -where "gid_0='USA'" -t_srs "EPSG:2163" -nln "us.admin_boundaries" level5

Instead of loading into the database, returns this error:

ERROR 1: ERROR: column s.consrc does not exist
LINE 1: ...nrelid = c.oid AND a.attnum = ANY (s.conkey) AND (s.consrc L...
^
HINT: Perhaps you meant to reference the column "s.conkey" or the column "s.conbin".

ERROR 1: ERROR: column s.consrc does not exist
LINE 1: ...nrelid = c.oid AND a.attnum = ANY (s.conkey) AND (s.consrc L...
^
HINT: Perhaps you meant to reference the column "s.conkey" or the column "s.conbin".

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 "us"."admin_boundaries" ( "fid" SERIAL, PRIMARY KEY ("fid"), "gid_0" VARCHAR, "name_0" VARCHAR, "gid_1" VARCHAR, "name_1" VARCHAR, "gid_2" VARCHAR, "name_2" VARCHAR, "gid_3" VARCHAR, "name_3" VARCHAR, "gid_4" VARCHAR, "name_4" VARCHAR, "gid_5" VARCHAR, "name_5" VARCHAR, "type_5" VARCHAR, "engtype_5" VARCHAR, "cc_5" VARCHAR, "geom" geometry(MULTIPOLYGON,1) )
ERROR: current transaction is aborted, commands ignored until end of transaction block

I think this is caused by changes in PostgreSQL 12, similar to the issue we faced in PostGIS -
detail of issue (and resolution) at https://trac.osgeo.org/postgis/ticket/4231

Operating system

Windows 10 64-bit

GDAL version and provenance

GDAL 3.1.0dev from - http://download.gisinternals.com/sdk/downloads/release-1911-x64-gdal-mapserver.zip (build dated 2019-06-30 23:49
@rouault rouault closed this as completed in 0ee2ee2 Jul 2, 2019
rouault added a commit that referenced this issue Jul 2, 2019
@rouault
PG: add support for PostgreSQL 12 (fixes #1692)
b868dbe
rouault added a commit that referenced this issue Jul 2, 2019
@rouault
PG: add support for PostgreSQL 12 (fixes #1692)
963618e
@pramsey
Copy link
Contributor

pramsey commented Jul 11, 2019

I see @robe2 beat me here. I just ran into this doing some testing work. Same symptoms.

@jimmyrocks
Copy link

I just compiled it with 963618e and it works! Thanks for the quick response @rouault!

@andy-harney andy-harney mentioned this issue Nov 7, 2019
Closed
@yosukefk yosukefk mentioned this issue Dec 21, 2019
Closed
@kristfal kristfal mentioned this issue Jan 13, 2020
Closed
7 tasks
@smnorris smnorris mentioned this issue Jan 31, 2020
Closed
@mzagaja mzagaja mentioned this issue May 29, 2020
Closed
@jacekkow jacekkow mentioned this issue Jun 2, 2020
Merged
archie83 referenced this issue in frodrigo/osmose-backend Mar 4, 2021
@frodrigo
Add GDAL to docker image osm-fr#1186
50188f9
@archie83 archie83 mentioned this issue Mar 4, 2021
Closed
ahmednoureldeen added a commit to cartologic/cartoview-project that referenced this issue Apr 21, 2021
@ahmednoureldeen
upgrade gdal to 3.2.1 to fix this issue OSGeo/gdal#1692
1055f44
ahmednoureldeen added a commit to cartologic/cartoview-project that referenced this issue Apr 21, 2021
@ahmednoureldeen
upgrade gdal to 3.2.1 to fix this issue OSGeo/gdal#1692
c3e6be4 
(cherry picked from commit 1055f44)
This was referenced Apr 21, 2021
Closed
Merged
afabiani pushed a commit to GeoNode/geonode that referenced this issue Apr 26, 2021
@ahmednoureldeen
[Fixes #7362] GDAL upgrade to issue wtih postgres 12 and higher (#7363)
5f45545 
* GDAL upgrade to issue wtih postgres 12 and higher OSGeo/gdal#1692

* add bullseye to source list to get GDAL 3.2.1 instead of compiling it.
afabiani pushed a commit to GeoNode/geonode that referenced this issue Apr 26, 2021
@ahmednoureldeen
[Fixes #7362] GDAL upgrade to issue wtih postgres 12 and higher (#7363)
b3917e4 
* GDAL upgrade to issue wtih postgres 12 and higher OSGeo/gdal#1692

* add bullseye to source list to get GDAL 3.2.1 instead of compiling it.

(cherry picked from commit 5f45545)
@afabiani afabiani mentioned this issue Apr 26, 2021
Merged
12 tasks
afabiani pushed a commit to GeoNode/geonode that referenced this issue Apr 26, 2021
@ahmednoureldeen
[Fixes #7362] GDAL upgrade to issue wtih postgres 12 and higher (#7363)…
a57e40d 
… (#7389)

* GDAL upgrade to issue wtih postgres 12 and higher OSGeo/gdal#1692

* add bullseye to source list to get GDAL 3.2.1 instead of compiling it.

(cherry picked from commit 5f45545)

Co-authored-by: Ahmed Nour Eldeen <[email protected]>
@danny-wang
Copy link

I meet the same error on postgres13. my gdal version is GDAL 2.4.2, released 2019/06/28

tpo pushed a commit to tpo/qwc-docker that referenced this issue Sep 9, 2021
this branch is a dead end
7ce98c9 
the problem is that I need to both be able to
mount an external directory into the postgis-base
container AND want postgres to have an initially
configured qwc_config DB. I can't think of a way
out of these two requirements excluding each other
mutually.

(Edit: the below is because gdal 3 (in buster) isn't
 compatible with PG >= 12
 See OSGeo/gdal#1692)

Also once the postgis-base image contains the qwc_config
DB, I want to inherit it and add demo data on top in the
qwc-demo-db image. However I am currently unable to
succeed in that: the docker-entry script makes a mess
somehow and won't let postgres start and I haven't
succeeded yet in starting postgres inside the container
correctly manually.

This is what happens when I use the (modified) docker-entry
script:

 ---> Running in e1a3c07df318
waiting for server to start....2021-09-08 15:39:45.738 UTC [18] LOG:  starting PostgreSQL 13.4 (Debian 13.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2021-09-08 15:39:45.740 UTC [18] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-09-08 15:39:45.747 UTC [19] LOG:  database system was shut down at 2021-09-08 15:12:28 UTC
2021-09-08 15:39:45.771 UTC [18] LOG:  database system is ready to accept connections
 done
server started

/tmp/docker-entrypoint-setup-demo-data.sh: running /tmp/setup-demo-data.sh
2021-09-08 15:39:46.036 UTC [29] ERROR:  column s.consrc does not exist at character 677
2021-09-08 15:39:46.036 UTC [29] HINT:  Perhaps you meant to reference the column "s.conkey" or the column "s.conbin".
2021-09-08 15:39:46.036 UTC [29] STATEMENT:  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(s.consrc)::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 (s.consrc LIKE '%geometrytype(% = %' OR s.consrc LIKE '%ndims(% = %' OR s.consrc 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
2021-09-08 15:39:46.036 UTC [29] ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-09-08 15:39:46.036 UTC [29] STATEMENT:  SELECT srid FROM spatial_ref_sys WHERE auth_name = 'EPSG' AND auth_srid = 3857
ERROR 1: ERROR:  column s.consrc does not exist

et cetera...
@tmszi tmszi mentioned this issue Jul 12, 2022
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@jimmyrocks @pramsey @robe2 @danny-wang