Bug report #12462
Inconsistent behaviour with PostgreSQL CONSTRAINT TRIGGER
|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|
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.
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.
#1 Updated by Mike Taves about 6 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