Bug report #13947

postgis tables with inherits can lead to not unique QgsFeature.id

Added by Alessandro Pasotti almost 9 years ago. Updated almost 9 years ago.

Status:Closed
Priority:Normal
Assignee:Jürgen Fischer
Category:Data Provider
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

Description

Associated revisions

Revision fc7fea55
Added by Jürgen Fischer almost 9 years ago

postgres provider: verify uniqueness of parent table primary keys (fixes #13947)

Revision 269f7092
Added by Jürgen Fischer almost 9 years ago

[FEATURE] postgres provider: refine warning about estimated metadata (fixes #13947)

History

#1 Updated by Jürgen Fischer almost 9 years ago

  • Status changed from Open to Closed

#2 Updated by Sebastian Dietrich almost 9 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.

Bad:

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)

Good:

CREATE TABLE qgis_test.child_table
(
  CONSTRAINT child_pkey PRIMARY KEY (gid)
)
INHERITS ( qgis_test.base_table)

#3 Updated by Alessandro Pasotti almost 9 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.

#4 Updated by Alessandro Pasotti almost 9 years ago

  • Status changed from Closed to Reopened

The patch does not cover the case in which use estimated metadata is true.

#5 Updated by Jürgen Fischer almost 9 years ago

  • Status changed from Reopened to Closed

#6 Updated by Sebastian Dietrich almost 9 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.

Also available in: Atom PDF