Bug report #17869

database check constraints on several fields can't be evaluated correctly on update in transaction mode

Added by Regis Haubourg over 2 years ago. Updated over 2 years ago.

Status:Closed
Priority:Normal
Assignee:Paul Blottiere
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

Revision c1fac425
Added by Paul Blottiere over 2 years ago

Update all attributes in a single transaction

Fixes #17869

Revision 140b40bc
Added by Paul Blottiere over 2 years ago

Update all attributes in a single transaction

Fixes #17869

History

#1 Updated by Regis Haubourg over 2 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 2 years ago

  • Assignee deleted (Jürgen Fischer)

#3 Updated by Denis Rouzaud over 2 years ago

  • Assignee set to David Signer

#4 Updated by Regis Haubourg over 2 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 2 years ago

feel free to PR

#7 Updated by Paul Blottiere over 2 years ago

  • Status changed from Open to Closed
  • % Done changed from 0 to 100

#8 Updated by Giovanni Manghi over 2 years ago

  • Resolution set to fixed/implemented

Also available in: Atom PDF