Bug report #13947
postgis tables with inherits can lead to not unique QgsFeature.id
|Affected QGIS version:||master||Regression?:||No|
|Operating System:||Easy fix?:||No|
|Pull Request or Patch supplied:||No||Resolution:|
|Crashes QGIS or corrupts data:||No||Copied to github as #:||21962|
Here is a test that reproduce the issue:
#2 Updated by Sebastian Dietrich almost 4 years ago
Please also note that your test creates the tables in a way that makes duplicate values much more likely:
When creating the child tables you define the inherited columns again. For the gid column this creates a new sequence for each child table. Duplicate values are almost guaranteed.
You should rather define only new columns and skip the inherited ones. The gid column in the child table then uses the same sequence as the parent table and duplicate values can only happen when manually entering values for gid.
CREATE TABLE qgis_test.child_table ( gid serial NOT NULL, geom geometry(Point,4326), code character varying, CONSTRAINT child_pkey PRIMARY KEY (gid) ) INHERITS ( qgis_test.base_table)
CREATE TABLE qgis_test.child_table ( CONSTRAINT child_pkey PRIMARY KEY (gid) ) INHERITS ( qgis_test.base_table)
#3 Updated by Alessandro Pasotti almost 4 years ago
Yes, pretty bad design but that was exactly the purpose of the test. Unfortunately, it is a stripped down real case, from a large organization in the public sector that manages millions of records in that way with potentially catastrophic results when used in QGIS yielding duplicated QgsFeature ids.
With Juerghen's fix the layer will be invalid. Still unsure if the patch really solve that problem though.
I'll try to elaborate the test a bit more.
#6 Updated by Sebastian Dietrich almost 4 years ago
Nice fix :-)
The actual problem is the incomplete support for inheritance in PostgreSQL. Inheritance together with Partitioning is such a mighty feature when working with huge datasets, but you give up the valuable good of consistency. No foreign keys, no unique indexes, exclusion constraints and many other features we are used to.
Since QGIS relies on consistent datasets you should treat inherited tables like (non-materialized) views: You have total responsibility for consistency. Usually this means you need to set up database triggers to enforce uniqueness across child tables and so on.