Re: [GENERAL] Hidden Risk w/ UPDATE Cascade and Trigger-Based Validation
On 3 June 2011 01:26, David Johnston pol...@yahoo.com wrote: Hi, I am trying to get a better understanding of how the following Foreign Keys with Update Cascades and validation trigger interact. The basic setup is a permission table where the two permission parts share a common “group/parent” which is embedded into their id/PK and which change via the FK cascade mechanism. Rest of my thoughts and questions follow the setup. I have the following schema (parts omitted/simplified for brevity since everything works as expected) CREATE TABLE userstorepermission ( userid text NOT NULL FK UPDATE CASCADE, storeid text NOT NULL FK UPDATE CASCADE, PRIMARY KEY (userid, storeid) ); FUNCTION validate() RETURNS trigger AS SELECT groupid FROM store WHERE storeid = [NEW.storeid] INTO storegroup SELECT groupid FROM user WHERE userid = [NEW.userid] INTO usergroup RAISE NOTICE ‘Validating User Store Permission U:%;%, S:%;%’, NEW.userid, usergroup, NEW.storeid, storegroup; IF (usergroup storegroup) THEN RAISE NOTICE ‘Disallow’; RETURN null; ELSE RAISE NOTICE ‘Allow’; RETURN NEW; END; CREATE TRIGGER INSERT OR UPDATE EXECUTE validate(); Basically if I change the groupid both the userid and storeid values in userstorepermission will change as well. This is desired. When I do update the shared groupid the following NOTICES are raised from the validation function above: The change for groupid was TESTSGB - TESTSGD: NOTICE: Validating User Store Permission U:tester@TESTSGB;NULL S:[TESTSGD]STORE01;TESTSGD [at this point apparently both user and store have been updated and storeid in the permission table is being change] CONTEXT: SQL statement UPDATE ONLY domain.userstorepermission SET s_id = $1 WHERE $2 OPERATOR(pg_catalog.=) s_id NOTICE: Allow CONTEXT: SQL statement UPDATE ONLY domain.userstorepermission SET s_id = $1 WHERE $2 OPERATOR(pg_catalog.=) s_id NOTICE: Validating User Store Permission U:tester@TESTSGD;TESTSGD S:[TESTSGD]STORE01;TESTSGD [and now the userid in the permission table gets its turn] CONTEXT: SQL statement UPDATE ONLY domain.userstorepermission SET u_id = $1 WHERE $2 OPERATOR(pg_catalog.=) u_id NOTICE: Allow CONTEXT: SQL statement UPDATE ONLY domain.userstorepermission SET u_id = $1 WHERE $2 OPERATOR(pg_catalog.=) u_id The end result is that both values are changed as desired but the notices, while they indirectly make sense (only one of the values can be update cascaded at a time), are somewhat confusing and thus I am not sure if I am possibly missing something that could eventually blow up in my face. I expect other similar situations will present themselves in my model so I want to get more understanding on at least whether what I am doing is safe and ideally whether the CASCADE rules possibly relax intra-process enforcement of constraints in order to allow this kind of multi-column key update to succeed. I see BUG #5505 from January of last year where Tom confirms that the trigger will fire but never addresses the second point about the referential integrity check NOT FAILING since the example’s table_2 contains a value not present in table_1… Conceptually, as long as I consistently update ALL the relevant FKs the initial and resulting state should remain consistent but only with a different value. I’ll probably do some more playing with “missing” a FK Update Cascade and see whether the proper failures occurs but regardless some thoughts and/or pointers are welcomed. Hmm, perhaps it would be better if your validation trigger raised an exception in the disallow case, rather than risk silently breaking the FK (even if you get to a point where you think that can't happen). Regards, Dean -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hidden Risk w/ UPDATE Cascade and Trigger-Based Validation
David Johnston pol...@yahoo.com writes: I am trying to get a better understanding of how the following Foreign Keys with Update Cascades and validation trigger interact. The basic setup is a permission table where the two permission parts share a common group/parent which is embedded into their id/PK and which change via the FK cascade mechanism. Rest of my thoughts and questions follow the setup. Well, the short answer is that there's not very much behind the curtain here. The FK CASCADE mechanisms just run SQL queries (like the ones you showed in CONTEXT lines) to perform the necessary adjustments of the referencing table when something changes in the referenced table. If you have a trigger on the referencing table that prevents some of these updates, then the updates don't get done ... and the result will be that the FK condition no longer holds everywhere. It might be safer if your trigger actually threw errors, rather than silently disabling such updates. Then at least the original referenced-table update would get rolled back and the two tables would remain consistent. There have been occasional discussions of how to make this stuff a bit cleaner/safer, but it's hard to see what to do without basically breaking the ability to have user-defined triggers on the referenced table. There are lots of safe and useful things such a trigger can do; but editorializing on the effects of an FK update query isn't one of them. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Hidden Risk w/ UPDATE Cascade and Trigger-Based Validation
Hi, I am trying to get a better understanding of how the following Foreign Keys with Update Cascades and validation trigger interact. The basic setup is a permission table where the two permission parts share a common group/parent which is embedded into their id/PK and which change via the FK cascade mechanism. Rest of my thoughts and questions follow the setup. I have the following schema (parts omitted/simplified for brevity since everything works as expected) CREATE TABLE userstorepermission ( userid text NOT NULL FK UPDATE CASCADE, storeid text NOT NULL FK UPDATE CASCADE, PRIMARY KEY (userid, storeid) ); FUNCTION validate() RETURNS trigger AS SELECT groupid FROM store WHERE storeid = [NEW.storeid] INTO storegroup SELECT groupid FROM user WHERE userid = [NEW.userid] INTO usergroup RAISE NOTICE 'Validating User Store Permission U:%;%, S:%;%', NEW.userid, usergroup, NEW.storeid, storegroup; IF (usergroup storegroup) THEN RAISE NOTICE 'Disallow'; RETURN null; ELSE RAISE NOTICE 'Allow'; RETURN NEW; END; CREATE TRIGGER INSERT OR UPDATE EXECUTE validate(); Basically if I change the groupid both the userid and storeid values in userstorepermission will change as well. This is desired. When I do update the shared groupid the following NOTICES are raised from the validation function above: The change for groupid was TESTSGB - TESTSGD: NOTICE: Validating User Store Permission U:tester@TESTSGB;NULL S:[TESTSGD]STORE01;TESTSGD [at this point apparently both user and store have been updated and storeid in the permission table is being change] CONTEXT: SQL statement UPDATE ONLY domain.userstorepermission SET s_id = $1 WHERE $2 OPERATOR(pg_catalog.=) s_id NOTICE: Allow CONTEXT: SQL statement UPDATE ONLY domain.userstorepermission SET s_id = $1 WHERE $2 OPERATOR(pg_catalog.=) s_id NOTICE: Validating User Store Permission U:tester@TESTSGD;TESTSGD S:[TESTSGD]STORE01;TESTSGD [and now the userid in the permission table gets its turn] CONTEXT: SQL statement UPDATE ONLY domain.userstorepermission SET u_id = $1 WHERE $2 OPERATOR(pg_catalog.=) u_id NOTICE: Allow CONTEXT: SQL statement UPDATE ONLY domain.userstorepermission SET u_id = $1 WHERE $2 OPERATOR(pg_catalog.=) u_id The end result is that both values are changed as desired but the notices, while they indirectly make sense (only one of the values can be update cascaded at a time), are somewhat confusing and thus I am not sure if I am possibly missing something that could eventually blow up in my face. I expect other similar situations will present themselves in my model so I want to get more understanding on at least whether what I am doing is safe and ideally whether the CASCADE rules possibly relax intra-process enforcement of constraints in order to allow this kind of multi-column key update to succeed. I see BUG #5505 from January of last year where Tom confirms that the trigger will fire but never addresses the second point about the referential integrity check NOT FAILING since the example's table_2 contains a value not present in table_1. Conceptually, as long as I consistently update ALL the relevant FKs the initial and resulting state should remain consistent but only with a different value. I'll probably do some more playing with missing a FK Update Cascade and see whether the proper failures occurs but regardless some thoughts and/or pointers are welcomed. Thanks, David J.
