[Qgis-user] PostgreSQL connection in Data Source Manager and geometry type GEOMETRY

[Dirk.Pispers at STADT-KOELN.DE]

Dear List,

in version 3.10 LTR when connecting to a PostgreSQL database with the Data Source Manager, QGIS performs a query to determine all occurring geometry types in tables and views that have the unrestricted geometry type GEOMETRY.
In one of our main databases (size about 65 GB) this query needs 130 GB of temporary disc space and causes an incident, when the storage on the server runs out.
Of course the user can prevent this by checking the "Don't resolve type of unrestricted columns (GEOMTRY)", but if this is not default, most users even don't understand the meaning of the option.

The statement QGIS sends to the database is:
SELECT 0, array_agg(DISTINCT 25832::text || ':' || UPPER(geometrytype("geometrie")))  FROM "xxxx"."yyyyy"
UNION
SELECT 1, ...
and so on for 148 tables.

Because there were obviosly changes in this part of the program between 3.4 LTR and 3.10 LTR, we examined this query in 3.4 too.
There is one single query generated for each table instead of one query for all tables together connected with UNION.
This was much more ressource-friendly than the current query is.

A test in version 3.16 showed the same query as in 3.10 LTR is used.

Does anyone know, why the query was changed between 3.4 an 3.10?
Maybe there is a way to modify the query once more to avoid database incidents.

Thank you and kind regards

Dirk




________________________________

Monatlich aktuelle Informationen Ihrer Stadtverwaltung in unserem Newsletter! Newsletter Anmeldung<https://www.stadt-koeln.de/service/onlinedienste/newsletter-anmeldung?para=allgemein>

________________________________

[https://styleguide.bundesregierung.de/resource/blob/72496/1760346/6d7f611945ca42908c50804510c5335b/breg-vorschaubild-01-unterstuetzt-842x595px-jpg-srgb-v01-data.png]<https://www.bundesregierung.de/breg-de/themen/corona-warn-app>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20210118/26df860c/attachment.html>