I've been working with some views that UNION ALL two tables and are
also updatable. On field in the view ('committed') simply indicates
what table a row came from. I don't want people to try and update
that field and think it'll take effect, so I have an assert function:
CREATE OR REPLACE RULE test_v__update AS ON UPDATE TO test_v DO INSTEAD
UPDATE test_committed set i=NEW.i
WHERE ( s=OLD.s )
AND assert( NOT NEW.committed IS DISTINCT FROM
OLD.committed, 'Changing committed is not allowed' )
;
All fine and good, but the assert would fire on this case:
update test_v set committed = true,i=i+1 WHERE s=1;
Where s=1 is absolutely a row in the 'committed' table. I finally
added some debugging and found the problem:
NOTICE: OLD.committed = TRUE
NOTICE: NOT DISTINCT =TRUE
NOTICE: NEW.committed = TRUE
NOTICE: NOT DISTINCT with s =TRUE
NOTICE: OLD.committed = FALSE
NOTICE: NOT DISTINCT =FALSE
AHA! The debug functions (and therefor the assert) was being
evaluated for each row in either table, even if they're marked as
IMMUTABLE.
This poses a problem in 2 ways: first, it means that every assert has
to include s = OLD.s AND ..., complicating code. But perhaps even
worse, it looks like the functions will force evaluation to happen
for every row in each table. That's not going to cut it on a multi-
million row table...
Changing the rule so that the functions were actually executed as
part of the SET seems to have solved the issue, but it's *really* ugly:
CREATE OR REPLACE RULE test_v__update AS ON UPDATE TO test_v DO INSTEAD
UPDATE test_committed set i=NEW.i
, s = CASE WHEN assert( NOT NEW.committed IS DISTINCT
FROM OLD.committed, 'Changing committed is not allowed' ) THEN s ELSE
NULL END
WHERE s=OLD.s
;
I suspect I could do something like
CREATE OR REPLACE RULE ...
UPDATE test_committed SET i = NEW.i
WHERE s IN ( SELECT s FROM ( SELECT s, assert(...) FROM
test_committed WHERE s = OLD.s ) a )
;
instead, but I haven't found a way to do that without making matters
worse...
Does anyone have any ideas on a clean and reliable way to do this?
What I think would be ideal is if there was some way to force
evaluation order in the WHERE clause of the update, but I don't think
that's possible.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828