Re: Hidden Risk w/ UPDATE Cascade and Trigger-Based Validation - Mailing list pgsql-general

On 3 June 2011 01:26, David Johnston <polobo@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