How did I not know DBPs fired by BEFORE rules can't INSERT?
From Roy Hann@21:1/5 to All on Thu Apr 8 15:17:49 2021
I just stubbed my toe on a restriction I am amazed I didn't know about.
I have a REFERENCES constraint referring to a table that is effectively
a homebrewed function index. I wanted to define a BEFORE rule on the
"base" table that would compute and insert the required row in the
"index" before the row is inserted in the base table. (It's not really
an index; I planned to use the PK value rather than a TID.)
I am dismayed to be told "E_US18B5 CREATE RULE: Data change statements
are not permitted in procedures triggered by BEFORE rules".
I can work around it (or paper over the constraint and just hope it
never gets violated).
But I am puzzled why it makes sense to preemptively limit BEFORE rules
in this way. I can see how one might define a procedure that tries to
do something to a non-existent row and how that would probably be a bug.
But it is so necessary to protect me from doing something so silly
--something which would probably show up in testing--that I must also be prevented from doing valid and useful things?