On 3 June 2011 01:26, David Johnston <polobo@xxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
