Bug report #17869
database check constraints on several fields can't be evaluated correctly on update in transaction mode
Status: | Closed | ||
---|---|---|---|
Priority: | Normal | ||
Assignee: | |||
Category: | Data Provider/PostGIS | ||
Affected QGIS version: | 2.18.15 | Regression?: | No |
Operating System: | Easy fix?: | No | |
Pull Request or Patch supplied: | No | Resolution: | fixed/implemented |
Crashes QGIS or corrupts data: | No | Copied to github as #: | 25764 |
Description
Hi all, still swimming in the depth of transaction groups :)
When a database as check constraints on multiple fields, update operations in transaction groups are made separatly for each field, which in some cases will fail.
Let's take for instance that table definition which constraint a mutual exclusion on two fk (one must be filled when the other must be NULL)
-- Table: ouvrage.bache -- DROP TABLE ouvrage.bache; CREATE TABLE ouvrage.bache ( id uuid NOT NULL DEFAULT uuid_generate_v4(), -- Identifiant unique. Lien vers la table ouvrepere numero_postal character varying NOT NULL, -- N° postal de l'ouvrage bp_id uuid, -- Lien vers la table des BP br_id uuid, -- Lien vers la table des BR CONSTRAINT bache_pkey PRIMARY KEY (id), CONSTRAINT chk_ouvrage_lie_bache CHECK ( br_id IS NOT NULL AND bp_id IS NULL AND puits_id IS NULL AND be_id IS NULL OR br_id IS NULL AND bp_id IS NOT NULL AND puits_id IS NULL AND be_id IS NULL ) ) WITH ( OIDS=FALSE ); It
When trying to switch value in a QGIS form, two separate instructions are committed, and they will be rejected when doing only one update operation would work.
UPDATE "maj_plan"."bache" SET "br_id"=NULL WHERE "id"::text='423d899e-d698-4807-925c-11a37c90399e'
raises
[ERREUR: la nouvelle ligne viole la contrainte de vérification « bache » de la relation « chk_ouvrage_lie_bache » DÃTAIL : La ligne en échec contient (423d899e-d698-4807-925c-11a37c90399e, BACHE1, 68df933f-b987-4010-9d71-bd0d6611e8b2, 7224ea6d-333f-4e06-b3e8-8603b202dc9e, 10, 5, 10, 10, f, 2018-01-11 00:00:00+01, ATOLCD, 2018-01-11 00:00:00+01, 2018-01-11 00:00:00+01, 2017, BACHE1 - ok, null, null, null, null) CONTEXTE : instruction SQL « UPDATE ouvrage.bache SET numero_postal=NEW.numero_postal, bache_position_id=NEW.bache_position_id::uuid, bache_occupant_id=NEW.bache_occupant_id::uuid, largeur=NEW.largeur, profondeur=NEW.profondeur, hauteur=NEW.hauteur, hauteur_sous_bache=NEW.hauteur_sous_bache, avec_capot=NEW.avec_capot, date_constat=NEW.date_constat, responsable_constat=NEW.responsable_constat, date_derniere_visite=NEW.date_derniere_visite, date_rehabilitation=NEW.date_rehabilitation, annee_creation=NEW.annee_creation, commentaire=NEW.commentaire, bp_id=NEW.bp_id::uuid, br_id=NEW.br_id::uuid, puits_id=NEW.puits_id::uuid, be_id=NEW.be_id::uuid WHERE id = OLD.id::uuid » fonction PL/pgsql maj_plan.tf_bache(), ligne 24 à instruction SQL
when this works
UPDATE "maj_plan"."bache" SET "bp_id"= '195de05c-6e0c-471e-bacd-2e21eb683083'::uuid, "br_id"= NULL WHERE "id"::text='423d899e-d698-4807-925c-11a37c90399e';
Associated revisions
Update all attributes in a single transaction
Fixes #17869
Update all attributes in a single transaction
Fixes #17869
History
#1
Updated by Regis Haubourg over 6 years ago
We found another case where this behavior is not good. When database has a audit trigger log, each form update will generate as many transactions as changed fields. This adds weight a lot the audit log and makes it harder to query since it is not possible to associate a transaction with a user update action.
#2
Updated by Jürgen Fischer over 6 years ago
- Assignee deleted (
Jürgen Fischer)
#3
Updated by Denis Rouzaud over 6 years ago
- Assignee set to David Signer
#4
Updated by Regis Haubourg over 6 years ago
- Assignee changed from David Signer to Paul Blottiere
Hi Denis,
Paul already has a branch ready here https://github.com/pblottiere/QGIS/tree/bugfix_transaction_constraints
Any feedback welcome.
#5
Updated by Denis Rouzaud over 6 years ago
feel free to PR
#6
Updated by Regis Haubourg over 6 years ago
and PR is here :) [[https://github.com/qgis/QGIS/pull/6142]]
#7
Updated by Paul Blottiere over 6 years ago
- Status changed from Open to Closed
- % Done changed from 0 to 100
Applied in changeset qgis|c1fac42518440680f246250024107201446d8adc.
#8
Updated by Giovanni Manghi over 6 years ago
- Resolution set to fixed/implemented