Bug report #18397

Primary key problem when using the interactive "Merge selected features" tool in an editing session

Added by Andreas Neumann about 6 years ago. Updated almost 6 years ago.

Status:Closed
Priority:High
Assignee:Nyall Dawson
Category:Editing
Affected QGIS version:3.1(master) Regression?:Yes
Operating System: Easy fix?:No
Pull Request or Patch supplied:Yes Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:26287

Description

The issue arises with PostgreSQL layers (e.g. of type int or bigint) where the pkey is generated from a sequence. But potentially, the same issue also applies to other data sources (Geopackage, SQLite)

If one interactively merges two or more adjacent features with the interactive "Merge selected features" tool, you first get a dialogue where the user chooses what to do with the attributes - this depends on the data type, and one can select to either use all the attributes of an existing feature or use aggregates.

Now an issue arises with the primary key column: if the user doesn't actively set a value, QGIS sends "NULL", which violates the primary key constraint and leads to the state where the user can't save the data (or looses data). This arises from the fact that the primary key column is automatically set to "skipped". Perhaps, in an older version of QGIS this meant that a new primary key is requested. But this doesn't work in current QGIS master.

In such a case, I think QGIS shouldn't send "NULL", but request a new pkey from the sequence. In addition, I think that if the user consciously selects a feature by using the "Take attributes from selected feature", the Primary key column shouldn't be skipped automatically - unless there is a technical reason to skip it.

qgis_transaction-mode-settings.png - Screenshot of transaction mode settings (33.2 KB) Andreas Neumann, 2018-03-15 09:05 AM

Associated revisions

Revision 615cb6ca
Added by Nyall Dawson almost 6 years ago

[needs-docs] In merge features dialog, if a layer has default
values/default value clauses present, then use these as the
initial value for the merged feature

Otherwise the dialog defaulted to skipping these attributes
or taking a value from an existing feature, which meant
that it could violate constraints on the backend.

Users can still easily overwrite these values if desired.

Fixes #18397

Revision 368fa908
Added by Nyall Dawson almost 6 years ago

[needs-docs] In merge features dialog, if a layer has default
values/default value clauses present, then use these as the
initial value for the merged feature

Otherwise the dialog defaulted to skipping these attributes
or taking a value from an existing feature, which meant
that it could violate constraints on the backend.

Users can still easily overwrite these values if desired.

Fixes #18397

(cherry-picked from 615cb6c)

History

#1 Updated by Andreas Neumann about 6 years ago

  • Regression? changed from No to Yes

The issue did not exist in QGIS 2.x - somehow the handling of PostgreSQL layers changed between 2.x and 3.x

#2 Updated by Nyall Dawson about 6 years ago

  • Status changed from Open to In Progress
  • Pull Request or Patch supplied changed from No to Yes

#3 Updated by Alessandro Pasotti about 6 years ago

  • Assignee set to Nyall Dawson

#4 Updated by Andreas Neumann about 6 years ago

I tested the PR. While it works fine in for normal PostgreSQL layers (not in transaction mode), it still fails when transaction mode is turned on.

The problem arises only if the two following project properties are activated:

  • Automatically create transaction groups where possible
  • Evaluate default values on provider side

Thanks for having a look at this specific "transaction mode" situation.

#5 Updated by Andreas Neumann about 6 years ago

I am adding the create table statement of a concerned table that shows the issue:

CREATE TABLE ews.ews_geologie
(
    t_id bigint NOT NULL DEFAULT nextval('ews.t_ili2db_seq'::regclass),
    t_basket bigint NOT NULL DEFAULT 4,
    t_ili_tid uuid DEFAULT uuid_generate_v4(),
    zulaessigkeit bigint NOT NULL,
    gebietsnummer character varying(10) COLLATE pg_catalog."default",
    mutationsdatum date,
    geologie_intern text COLLATE pg_catalog."default",
    geologie_extern text COLLATE pg_catalog."default",
    bemerkungen_intern text COLLATE pg_catalog."default",
    bemerkungen_extern text COLLATE pg_catalog."default",
    geometrie geometry(Polygon,2056),
    CONSTRAINT ews_geologie_pkey PRIMARY KEY (t_id),
    CONSTRAINT ews_geologie_t_basket_fkey FOREIGN KEY (t_basket)
        REFERENCES ews.t_ili2db_basket (t_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT ews_geologie_zulaessigkeit_fkey FOREIGN KEY (zulaessigkeit)
        REFERENCES ews.zulaessigkeit_catalogue (t_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

Note the bigint data type for the primary key column.

#7 Updated by Nyall Dawson almost 6 years ago

  • % Done changed from 0 to 100
  • Status changed from In Progress to Closed

Also available in: Atom PDF