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

Dirk.Pispers at STADT-KOELN.DE Dirk.Pispers at STADT-KOELN.DE
Mon Jan 18 07:44:23 PST 2021

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"
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



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


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20210118/26df860c/attachment.html>

More information about the Qgis-user mailing list