QGIS » QGIS Application

Two related issues are found with CONSTRAINT TRIGGERs on PostgreSQL tables. The triggers behave normally in UPDATE statements, but not with QGIS 2.8.1, using PostgreSQL 9.1 with PostGIS 1.5.

Here is the test data and trigger function. The trigger function raises an exception if there is more than one "pick" row selected:

CREATE TABLE qgistest (
  gid integer PRIMARY KEY,
  pick boolean
);

SELECT AddGeometryColumn ('qgistest', 'geom', 4326, 'POINT', 2);

INSERT INTO qgistest(gid, geom) VALUES
(1, 'SRID=4326;POINT(1 2)'),
(2, 'SRID=4326;POINT(3 4)');

CREATE OR REPLACE FUNCTION pick_no_more_than_one() RETURNS trigger AS
$BODY$BEGIN
  IF (SELECT count(*) > 1 FROM qgistest WHERE pick) THEN
    RAISE EXCEPTION 'too many picked';
  END IF;
  RETURN NEW;
END;$BODY$ LANGUAGE plpgsql VOLATILE COST 100;

This is the first behaviour, with the expected results from an SQL client:

-- DROP TRIGGER pick_no_more_than_one_tg ON qgistest;
CREATE CONSTRAINT TRIGGER pick_no_more_than_one_tg
  AFTER INSERT OR UPDATE
  ON qgistest FOR EACH ROW
  EXECUTE PROCEDURE pick_no_more_than_one();

-- Always fails, since there is more than one "pick" 
UPDATE qgistest SET pick = true;

-- But these work, since there is at most one "pick" 
UPDATE qgistest SET pick = false RETURNING *;
UPDATE qgistest SET pick = (gid = 1)::boolean RETURNING *;
UPDATE qgistest SET pick = (gid = 2)::boolean RETURNING *;

Add the table to QGIS, open the table, and observe that the "pick" column shows 'f', 't' (from the last test). Start editing, and swap conditions, such that they are 't', 'f', then save changes. The commit error shows:

Could not commit changes to layer qgistest
Errors: ERROR: 2 attribute value change(s) not applied.
Provider errors:
PostGIS error while changing attributes: ERROR: too many picked

My guess is that QGIS is attempting to modify the data similar to this transaction (which is related to the second behaviour):

UPDATE qgistest SET pick = (gid = 2)::boolean;
-- This transaction fails without a deferrable trigger
START TRANSACTION;
UPDATE qgistest SET pick = true WHERE gid = 1;
UPDATE qgistest SET pick = false WHERE gid = 2;
COMMIT;

If the above is similar to what is happening to QGIS, then it is expected behaviour, since trigger functions are not deferred by default.

The second behaviour is if deferrable triggers are used for transactions.

-- Replace the trigger with one that is INITIALLY DEFERRED
DROP TRIGGER pick_no_more_than_one_tg ON qgistest;
CREATE CONSTRAINT TRIGGER pick_no_more_than_one_tg
  AFTER INSERT OR UPDATE
  ON qgistest
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW
  EXECUTE PROCEDURE pick_no_more_than_one();

Now the UPDATE transaction that previously failed works, as expected, however QGIS behaves differently. Editing, and setting both "pick" to 't', then saving shows no errors, and both values silently return to their values before editing. Note that `UPDATE qgistest SET pick = true;` still raises an error from an SQL client, so it's not clear why QGIS does not report this commit error.