'Add PostGIS layer' ignores multiple geometrytype constraint

[qgib]

Author Name: anne blankert (@anneb)
Original Redmine Issue: 5904
Affected QGIS version: master

---

When opening a PostGIS database with large tables, the QGis UI displays 'detecting...' for a while and scans the tables to see what geometrytypes it contains. If more than one geometrytype has been detected in the table, the UI displays separate layers for each of the detected geometrytypes.

The detection speed is improved dramatically if a database constraint is applied to the table. This works well if the constraint checks for only one geometry type, but if you define multiple geometry types in the constraint, like:

  ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) in ('POINT'::text, 'LINESTRING'::text, 'POLYGON'::text) OR geom IS NULL);

Only the first geometry constraint is recognized by QGIS (POINT in the above example).

On inspection of the source code for the postgres dataprovider, it seems like the software is enumerating all geometrytypes from the constraint. Maybe the other geometrytypes are dropped or ignored somewhere else?

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

The constraint aren't inspected at all. The geometry type of the geometries in the table are. If you are using "use estimated meta data" only a few geometries are inspected, if those don't happen to be of all geometry types, that are in the table, the list will be incomplete. So I suspect that you're using "use estimated meta data".

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

• status_id was changed from Open to Feedback

[qgib]

Author Name: anne blankert (@anneb)

---

I did not check checkbox 'estimated table statistics'

I turned on logging on the postgres database.

If I remove the constraint, the following is logged:
SELECT DISTINCT CASE WHEN upper(geometrytype("geom")) IN ('POINT','MULTIPOINT','POINTM','MULTIPOINTM') THEN 'POINT' WHEN upper(geometrytype("geom")) IN ('LINESTRING','MULTILINESTRING','LINESTRINGM','MULTILINESTRINGM') THEN 'LINESTRING' WHEN upper(geometrytype("geom")) IN ('POLYGON','MULTIPOLYGON','POLYGONM','MULTIPOLYGONM') THEN 'POLYGON' END, st_srid("geom") FROM "myschema"."mytable"

If I restore the constraint, the above query is not logged (verified twice) and there is no 'Detecting...' message for the table. It seems QGis thinks there is a shortcut, probably by inspecting the database meta tables?

I was looking at the source code of the postgres provider and there is a query using column consr from table pg_constraint in the pg_catalog, but according to the database log, the pg_constraint query is not used.

[qgib]

Author Name: anne blankert (@anneb)

---

Aha, the problem seems to be caused by Postgis 2.0 view 'geometry_columns'.
The view somehow takes into account the geometry constraints and sets the 'type' to the first type mentioned in the constraint.

The problem is not a QGis bug. Issue can be closed.

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

• resolution was changed from to worksforme
• status_id was changed from Feedback to Closed