Bug report #12462

Inconsistent behaviour with PostgreSQL CONSTRAINT TRIGGER

Added by Mike Taves almost 9 years ago. Updated over 8 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/PostGIS
Affected QGIS version:2.8.1 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:20617

Description

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.

Associated revisions

Revision 68c015e8
Added by Jürgen Fischer over 8 years ago

postgres provider: catch if commit fails (fixes #12462)

History

#1 Updated by Mike Taves almost 9 years ago

With the deferrable trigger (second part), while no error is shown in QGIS, the log messages (for PostGIS) silently show:

2015-03-27T12:51:41    1    Query: COMMIT returned 7 [ERROR:  too many picked
            ]
2015-03-27T12:51:41    1    NOTICE: NOTICE:  there is no transaction in progress

#2 Updated by Giovanni Manghi almost 9 years ago

  • Category changed from Data Provider to Data Provider/PostGIS

#3 Updated by Giovanni Manghi almost 9 years ago

  • Target version changed from Version 2.8.2 to Version 2.10

#4 Updated by Giovanni Manghi over 8 years ago

  • Target version deleted (Version 2.10)

#5 Updated by Jürgen Fischer over 8 years ago

  • Status changed from Open to Closed

Also available in: Atom PDF