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(at)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(at)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.