[GENERAL] Is there any way to reliably influence WHERE predicate evaluation ordering?

[Decibel!]

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 )

;

All fine and good, but the assert would fire on this case:

update test_v set committed = true,i=i+1 WHERE s=1;



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







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
;

I suspect I could do something like

CREATE OR REPLACE RULE ...
    UPDATE test_committed SET i = NEW.i

;




--
Decibel!, aka Jim C. Nasby, Database Architect  [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828

AND assert( NOT NEW.committed IS DISTINCT FROM OLD.committed, 'Changing committed is not allowed' ) Where s=1 is absolutely a row in the 'committed' table. I finally added some debugging and found the problem: 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: , s = CASE WHEN assert( NOT NEW.committed IS DISTINCT FROM OLD.committed, 'Changing committed is not allowed' ) THEN s ELSE NULL END 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.

smime.p7s
Description: S/MIME cryptographic signature