Bug report #13947

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

Added by Alessandro Pasotti over 1 year ago. Updated over 1 year ago.

Status:Closed Start Date:12/04/2015
Priority:Normal Due date:
Assigned to:Jürgen Fischer % Done:

0%

Category:Data Provider
Target version:-
Platform: Pull Request or Patch supplied:No
Platform version: Affected version:master
Status info: Causes crash or corruption:No
Resolution: Tag:

Description

Associated revisions

Revision fc7fea55ce1291d033a1b07455d374ba175413b3
Added by Jürgen Fischer over 1 year ago

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

Revision 269f70928571be08fe67d14eb43bda77945d8773
Added by Jürgen Fischer over 1 year ago

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

History

Updated by Jürgen Fischer over 1 year ago

  • Status changed from Open to Closed

Updated by Sebastian Dietrich over 1 year 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)

Updated by Alessandro Pasotti over 1 year 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.

Updated by Alessandro Pasotti over 1 year ago

  • Status changed from Closed to Reopened

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

Updated by Jürgen Fischer over 1 year ago

  • Status changed from Reopened to Closed

Updated by Sebastian Dietrich over 1 year 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