QGIS 3.10 - Unable to load layers from PostgreSQL 12 / PostGIS

[andy-harney]

Describe the bug

Connecting to a PostgreSQL 12 DB works fine, but trying to load layers results in the following error.

2019-11-07T11:42:08     WARNING    Erroneous query: SELECT l.f_table_name,l.f_table_schema,l.f_geometry_column,upper(l.type),l.srid,l.coord_dimension,c.relkind,obj_description(c.oid),array_agg(a.attname), count(CASE WHEN t.typname IN ('geometry','geography','topogeometry','raster') THEN 1 ELSE NULL END) , 1 FROM geometry_columns l,pg_class c,pg_namespace n,pg_attribute a,pg_type t WHERE c.relname=l.f_table_name AND l.f_table_schema=n.nspname AND NOT a.attisdropped AND a.attrelid=c.oid AND a.atttypid=t.oid AND a.attnum>0 AND n.oid=c.relnamespace AND has_schema_privilege(n.nspname,'usage') AND has_table_privilege('"'||n.nspname||'"."'||c.relname||'"','select') GROUP BY 1,2,3,4,5,6,7,c.oid,11 UNION SELECT l.f_table_name,l.f_table_schema,l.f_geography_column,upper(l.type),l.srid,2,c.relkind,obj_description(c.oid),array_agg(a.attname), count(CASE WHEN t.typname IN ('geometry','geography','topogeometry','raster') THEN 1 ELSE NULL END) , 1 FROM geography_columns l,pg_class c,pg_namespace n,pg_attribute a,pg_type t WHERE c.relname=l.f_table_name AND l.f_table_schema=n.nspname AND NOT a.attisdropped AND a.attrelid=c.oid AND a.atttypid=t.oid AND a.attnum>0 AND n.oid=c.relnamespace AND has_schema_privilege(n.nspname,'usage') AND has_table_privilege('"'||n.nspname||'"."'||c.relname||'"','select') GROUP BY 1,2,3,4,5,6,7,c.oid,11 UNION SELECT l.table_name,l.schema_name,l.feature_column,CASE WHEN l.feature_type = 1 THEN 'MULTIPOINT' WHEN l.feature_type = 2 THEN 'MULTILINESTRING' WHEN l.feature_type = 3 THEN 'MULTIPOLYGON' WHEN l.feature_type = 4 THEN 'GEOMETRYCOLLECTION' END AS type,(SELECT srid FROM topology.topology t WHERE l.topology_id=t.id),2,c.relkind,obj_description(c.oid),array_agg(a.attname), count(CASE WHEN t.typname IN ('geometry','geography','topogeometry','raster') THEN 1 ELSE NULL END) , 1 FROM topology.layer l,pg_class c,pg_namespace n,pg_attribute a,pg_type t WHERE c.relname=l.table_name AND l.schema_name=n.nspname AND NOT a.attisdropped AND a.attrelid=c.oid AND a.atttypid=t.oid AND a.attnum>0 AND n.oid=c.relnamespace AND has_schema_privilege(n.nspname,'usage') AND has_table_privilege('"'||n.nspname||'"."'||c.relname||'"','select') GROUP BY 1,2,3,4,5,6,7,c.oid,11 UNION SELECT l."r_table_name",l."r_table_schema",l."r_raster_column",'RASTER',l.srid,2,c.relkind,obj_description(c.oid),array_agg(a.attname), count(CASE WHEN t.typname IN ('geometry','geography','topogeometry','raster') THEN 1 ELSE NULL END) , 1 FROM raster_columns l,pg_class c,pg_namespace n,pg_attribute a,pg_type t WHERE c.relname=l."r_table_name" AND l."r_table_schema"=n.nspname AND NOT a.attisdropped AND a.attrelid=c.oid AND a.atttypid=t.oid AND a.attnum>0 AND n.oid=c.relnamespace AND has_schema_privilege(n.nspname,'usage') AND has_table_privilege('"'||n.nspname||'"."'||c.relname||'"','select') GROUP BY 1,2,3,4,5,6,7,c.oid,11 ORDER BY 2,1,3 returned 7 [ERROR: column s.consrc does not exist
             LINE 4: split_part(s.consrc, ' = ', 2),
              ^
             HINT: Perhaps you meant to reference the column "s.conkey" or the column "s.conbin".
             QUERY: 
              SELECT
              regexp_replace(
              split_part(s.consrc, ' = ', 2),
              '[\(\)]', '', 'g'
              )::integer
              FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
              WHERE n.nspname = $1
              AND c.relname = $2
              AND a.attname = $3
              AND a.attrelid = c.oid
              AND s.connamespace = n.oid
              AND s.conrelid = c.oid
              AND a.attnum = ANY (s.conkey)
              AND s.consrc LIKE '%st_srid(% = %';
              
             CONTEXT: SQL function "_raster_constraint_info_srid" during inlining
             ]
2019-11-07T11:42:08     WARNING    NOTICE: WARNING: there is no transaction in progress
2019-11-07T11:42:08     WARNING    Unable to get list of spatially enabled tables from the database

I believe this is linked to the same issue reported against GDAL here OSGeo/gdal#1692
The GDAL version shipped with QGIS 3.10 is 2.4.1, which does not play nice with PostgreSQL 12.

QGIS and OS versions
QGIS 3.10 code revision 6c816b4
Windows 10
PostgreSQL 12
PostGIS 3

Additional context
I think this issue #32558 is the same.

[gioman]

ERROR: column s.consrc does not exist

@andy-harney this has been recently fixed in master, please give it a try.

[andy-harney]

Hi @gioman I can see that it looks fixed in 6c9f01e.
I've just downloaded qgis-dev from OSGeo4W and can see that is based off of 1c1ceb1112 which is from 9 days ago.

Unfortunately I don't have an environment set up to build QGIS.

Am I correct in saying that this fix will be included in the next point release on the 22nd November?

Thanks.

[saberraz]

Best to check it in weekly builds:
https://qgis.org/downloads/weekly/?C=M;O=D

So that you can report back before the release.

[elpaso]

@andy-harney 6c9f01e it's a Python only patch, you can quickly change the plugin python code on your machine without the need to rebuild anything.

[andy-harney]

Hi @elpaso
That does indeed fix adding layers via Database > DB Manager > ... > Add to Canvas.
Unfortunately adding layers via Data Source Manager > PostgreSQL still doesn't work.

[elpaso]

@andy-harney thank you for the feedback, I'll have a look.

[andy-harney]

@elpaso Thank you. 👍

[jef-n]

Best to check it in weekly builds:
https://qgis.org/downloads/weekly/?C=M;O=D

Not currently - master in x86_64 has been too large for a NSIS package for a while - that'll will change once GRASS doesn't depend on python2. But the weeklies (as all other of our standalone installer) are snapshots of OSGeo4W - so what's not there, won't be in the weeklies either.

[jef-n]

I've just downloaded qgis-dev from OSGeo4W and can see that is based off of 1c1ceb1 which is from 9 days ago.

That's misleading. 1c1ceb1 was in #32463 and that was merged into master 2 days ago.

Am I correct in saying that this fix will be included in the next point release on the 22nd November?

yes.

[elpaso]

@andy-harney I cannot reproduce it on master: I can add layers from PG 12 through the data source select dialog without problems, can you check the message logs and see what exactly the error is?

Or send me a DB dump of the table that does not open?

[andy-harney]

Hi @elpaso ,

Here is the paste from logs. Below is the connection window showing the test connection succeeds then a gif of the process I'm following. This is from qgis-dev from OSGeo4W, QGIS code revision 1c1ceb1

2019-11-08T16:22:59     WARNING    Erroneous query: SELECT l.f_table_name,l.f_table_schema,l.f_geometry_column,upper(l.type),l.srid,l.coord_dimension,c.relkind,obj_description(c.oid),array_agg(a.attname), count(CASE WHEN t.typname IN ('geometry','geography','topogeometry','raster') THEN 1 ELSE NULL END) , 1 FROM geometry_columns l,pg_class c,pg_namespace n,pg_attribute a,pg_type t WHERE c.relname=l.f_table_name AND l.f_table_schema=n.nspname AND NOT a.attisdropped AND a.attrelid=c.oid AND a.atttypid=t.oid AND a.attnum>0 AND n.oid=c.relnamespace AND has_schema_privilege(n.nspname,'usage') AND has_table_privilege('"'||n.nspname||'"."'||c.relname||'"','select') GROUP BY 1,2,3,4,5,6,7,c.oid,11 UNION SELECT l.f_table_name,l.f_table_schema,l.f_geography_column,upper(l.type),l.srid,2,c.relkind,obj_description(c.oid),array_agg(a.attname), count(CASE WHEN t.typname IN ('geometry','geography','topogeometry','raster') THEN 1 ELSE NULL END) , 1 FROM geography_columns l,pg_class c,pg_namespace n,pg_attribute a,pg_type t WHERE c.relname=l.f_table_name AND l.f_table_schema=n.nspname AND NOT a.attisdropped AND a.attrelid=c.oid AND a.atttypid=t.oid AND a.attnum>0 AND n.oid=c.relnamespace AND has_schema_privilege(n.nspname,'usage') AND has_table_privilege('"'||n.nspname||'"."'||c.relname||'"','select') GROUP BY 1,2,3,4,5,6,7,c.oid,11 UNION SELECT l.table_name,l.schema_name,l.feature_column,CASE WHEN l.feature_type = 1 THEN 'MULTIPOINT' WHEN l.feature_type = 2 THEN 'MULTILINESTRING' WHEN l.feature_type = 3 THEN 'MULTIPOLYGON' WHEN l.feature_type = 4 THEN 'GEOMETRYCOLLECTION' END AS type,(SELECT srid FROM topology.topology t WHERE l.topology_id=t.id),2,c.relkind,obj_description(c.oid),array_agg(a.attname), count(CASE WHEN t.typname IN ('geometry','geography','topogeometry','raster') THEN 1 ELSE NULL END) , 1 FROM topology.layer l,pg_class c,pg_namespace n,pg_attribute a,pg_type t WHERE c.relname=l.table_name AND l.schema_name=n.nspname AND NOT a.attisdropped AND a.attrelid=c.oid AND a.atttypid=t.oid AND a.attnum>0 AND n.oid=c.relnamespace AND has_schema_privilege(n.nspname,'usage') AND has_table_privilege('"'||n.nspname||'"."'||c.relname||'"','select') GROUP BY 1,2,3,4,5,6,7,c.oid,11 UNION SELECT l."r_table_name",l."r_table_schema",l."r_raster_column",'RASTER',l.srid,2,c.relkind,obj_description(c.oid),array_agg(a.attname), count(CASE WHEN t.typname IN ('geometry','geography','topogeometry','raster') THEN 1 ELSE NULL END) , 1 FROM raster_columns l,pg_class c,pg_namespace n,pg_attribute a,pg_type t WHERE c.relname=l."r_table_name" AND l."r_table_schema"=n.nspname AND NOT a.attisdropped AND a.attrelid=c.oid AND a.atttypid=t.oid AND a.attnum>0 AND n.oid=c.relnamespace AND has_schema_privilege(n.nspname,'usage') AND has_table_privilege('"'||n.nspname||'"."'||c.relname||'"','select') GROUP BY 1,2,3,4,5,6,7,c.oid,11 ORDER BY 2,1,3 returned 7 [ERROR: column s.consrc does not exist
             LINE 4: split_part(s.consrc, ' = ', 2),
              ^
             HINT: Perhaps you meant to reference the column "s.conkey" or the column "s.conbin".
             QUERY: 
              SELECT
              regexp_replace(
              split_part(s.consrc, ' = ', 2),
              '[\(\)]', '', 'g'
              )::integer
              FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
              WHERE n.nspname = $1
              AND c.relname = $2
              AND a.attname = $3
              AND a.attrelid = c.oid
              AND s.connamespace = n.oid
              AND s.conrelid = c.oid
              AND a.attnum = ANY (s.conkey)
              AND s.consrc LIKE '%st_srid(% = %';
              
             CONTEXT: SQL function "_raster_constraint_info_srid" during inlining
             ]
2019-11-08T16:22:59     WARNING    NOTICE: WARNING: there is no transaction in progress
2019-11-08T16:22:59     WARNING    Unable to get list of spatially enabled tables from the database

Thanks again.

[elpaso]

Unfortunately there are still a lot of bugs in DB manager related to changes in the way definitions can be retrieved, but this patch fixes the constraint issue in the provider side.

My test case (on both version 10 and 12):

CREATE DOMAIN my_domain AS TEXT CHECK( VALUE in ('a', 'b', 'c', 'd') );
create table bug_gh_32716 ( id SERIAL PRIMARY KEY, postal my_domain NOT NULL );

try that on PG 10, and PG 12 in DB manager and see what I mean
CC @pcav

[andy-harney]

Yeah, I'm pretty certain that its just the retrieval of layers/tables from the data source manager that's causing the most immediate issues.

Existing QGZ files with layers in pulled from PostgreSQL 12 open and work fine.
However, I've not checked the functionality of attribute tables and such, but should they cause issues I'll create a new issue.

Thanks for looking into this.

[rful011]

I can confirm that this is fixed in nightly builds of 3.11