QGIS 3.10.1 not possible to display the list of postgis tables

[frgis]

Hello,
since the version QGIS 3.10.1 when I try to add a postgis layer (or a table) there is an error message telling me that it is not possible to display the list of layers

For information :
I work with postgresql 9.6
It works with the ltr version
My os is Windows 10

Thank you.

[gioman]

since the version QGIS 3.10.1 when I try to add a postgis layer (or a table) there is an error message telling me that it is not possible to display the list of layers

@frgis cannot confirm here, same qgis version and platform.

[frgis]

Indeed you are right.
On an installation of another computer I do not observe the problem. I'm going to find out what's wrong.
Thank you. Sorry for the inconvenience

[frgis]

Hello,

I open this ticket again because I can't find the problem on a production installation with PostgreSQL 9.6 and Postgis 2. There is always the same problem as above with QGIS 3.10.1. I uninstalled and reinstalled without success. I tried with another computer and I have the same problem. Both computers have Windows 10 Professional and it is only when I log in with a superuser. With version 3.4.14 there is no problem. I will try to recover the error message today.

[frgis]

much resemblance to issue #32716

[frgis]

the attached error

2020-01-14T08:33:26 WARNING Requête erronée : 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','pcpatch','pcpoint','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(c.oid,'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','pcpatch','pcpoint','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(c.oid,'select') GROUP BY 1,2,3,4,5,6,7,c.oid,11 UNION SELECT l."table",l."schema",l."column",'POLYGON',l.srid,2,c.relkind,obj_description(c.oid),array_agg(a.attname), count(CASE WHEN t.typname IN ('geometry','geography','pcpatch','pcpoint','raster') THEN 1 ELSE NULL END) , 1 FROM pointcloud_columns l,pg_class c,pg_namespace n,pg_attribute a,pg_type t WHERE c.relname=l."table" AND l."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(c.oid,'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','pcpatch','pcpoint','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(c.oid,'select') GROUP BY 1,2,3,4,5,6,7,c.oid,11 ORDER BY 2,1,3 a retourné 7 [ERREUR: la relation « pointcloud_columns » n'existe pas
LINE 1: ...cpoint','raster') THEN 1 ELSE NULL END) , 1 FROM pointcloud...
^
]
2020-01-14T08:33:26 WARNING ATTENTION: ATTENTION: aucune transaction en cours
2020-01-14T08:33:26 WARNING Impossible d'obtenir une liste des tables spatialisées depuis la base de données

Thanks

[gioman]

[ERREUR: la relation « pointcloud_columns » n'existe pas

@strk wasn't his already fixed? if not, sounds any bell?

[frgis]

The result of the error is the same (no table displayed in the datasource manager).

However the nature of the error is in my opinion different.

Indeed, it searches here for a 'pointcloud_columns' table or view, But I don't have it.

I did come research and this view or table is linked to the installation of a specific postgresql extension that I don't have (point cloud > https://oslandia.com/wp-content /uploads/2018/05/Lemoine-Oslandia-Pointcloud.pdf)

you think that #32716 solves this problem ?

Thanks

[strk]

Should have been fixed with #32972 - are you sure you're using 3.10.1 ?

[gioman]

are you sure you're using 3.10.1

@strk the screenshot posted is clear.

[strk]

"are you sure" is about carefully checking if the information of the screenshot is correct :)
Can it be he has old postgres provider plugin on the system ? You cannot reproduce @gioman and he also cannot reproduce on another machine...

@frgis how did you install ? Can it be you have multiple qgis files (from older versions) around ?

[gioman]

@frgis how did you install ? Can it be you have multiple qgis files (from older versions) around ?

@frgis also how did you install PostgreSQL/PostGIS? Is a Windows server? if yes what installer you used?

[frgis]

Hello, I'm reproducing it right now.
Screen capture of QGIS version attached.
The day before yesterday, I completely uninstalled QGIS 3.4.14 and QGIS 3.10.1 from my computer (with the% appdata% preferences too).
I reinstalled QGIS 3.10.1 downloadable from the page (https://www.qgis.org/fr/site/forusers/download.html).
After I installed QGIS 3.4.14.
I observe the same problem on my computer.

For PostgreSQL and PostGIS, the current versions:
PostgreSQL 9.6.9 on x86_64-pc-linux-gnu (Debian 9.6.9-2.pgdg90 + 1), compiled by gcc (Debian 6.3.0-18 + deb9u1) 6.3.0 20170516, 64-bit
PostGIS 2.3 USE_GEOS = 1 USE_PROJ = 1 USE_STATS = 1

PostgreSQL is installed on DEBIAN 9

Note: no problem on QGIS 3.4.14

Nothing to do but in the Processing toolbox the SAGA algorithms are not also displayed. perhaps a link between the two problems ?

[strk]

@frgis can you also include output of:

SELECT
 has_table_privilege(c.oid, 'select')
 AND has_table_privilege(f.oid, 'select')
FROM pg_class c, pg_class f
WHERE c.relname = 'pointcloud_columns'
  AND f.relname = 'pointcloud_formats'
   ;

Run from the DBManager SQL window against the appropriate database connection

[strk]

And also: select oid::regclass::text from pg_class where relname = 'pointcloud_columns';

[frgis]

I see your message right now.
Tomorrow i test and I send the results of the sql queries.

Thanks

[frgis]

Hi,
for the first request

[frgis]

request2

it's a foreign table.
you found the origin of the problem: indeed when I connect on another database of the same instance postgresql it works !
So I have to remove this foreign table?

Thanks !

[strk]

The query verifying permissions of pointcloud_columns (first query) does not bother about the schema in which pointcloud_columns is, nor its type. Just checks if you have permissions to select from it.
Later, the PostgreSQL provider simply issues a SELECT xxx from pointcloud_columns which in your case results in the odd error saying that the table does not exist. Can you try, from DBManager, to issue a SELECT * from pointcloud_columns ?

My guess is that the schema in which your pointcloud_columns is installed is NOT found in your SEARCH_PATH. This is something we could tweak the permission verification query to account for, probably by consider pointcloud unavailable when the table is not in your search path. Would that do for you ? Are you up to send a patch in that direction ?

[frgis]

The result of the query

[frgis]

There is no need to change anything.
The problem is on my side

[strk]

Try also show search_path and compare with the schema pointcloud_columns is in.
I'm not sure there's nothing to change, maybe we could make a better query and use the pointcloud_columns we found. The PointCloud extension is relocatable so a user can install it in any schema, we could query such schema from the pg_extension table...

[strk]

A query to inspect schema in which pointcloud extension is installed would be:
select extnamespace::regnamespace::text, extname from pg_extension where extname = 'pointcloud';

[strk]

@frgis if you can build yourself, please try #33846

[frgis]

The pointcloud extension must be installed on the foreign database ( I use extension Foreign data wrapper ) and I have recovered the entire contents of the schema in which there are these tables.
I don't need it and by the way they are empty in the foreign database
I just removed the foreign tables pointcloud_xxxx and it works correctly. The problem is solved for me.

Thanks !