Is there any way to reliably influence WHERE predicate evaluation ordering?
От | Decibel! |
---|---|
Тема | Is there any way to reliably influence WHERE predicate evaluation ordering? |
Дата | |
Msg-id | 0EFB0AFE-60DA-4344-8BFF-19BE85766F75@decibel.org обсуждение исходный текст |
Ответы |
Re: Is there any way to reliably influence WHERE predicate evaluation ordering?
|
Список | pgsql-general |
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
Вложения
В списке pgsql-general по дате отправления: