QGIS » QGIS Application

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';