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?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: "Scott Marlowe"
Дата:
Сообщение: Re: Need Some Explanation of an EXPLAIN
Следующее
От: "Gauthier, Dave"
Дата:
Сообщение: dynamic sql in PL-Pgsql ?