Re: question on UPDATE rules

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: question on UPDATE rules
Дата
Msg-id 29468.956448367@sss.pgh.pa.us
обсуждение исходный текст
Ответ на question on UPDATE rules  (Matthew Denny <mdenny@CS.Berkeley.EDU>)
Список pgsql-sql
Matthew Denny <mdenny@CS.Berkeley.EDU> writes:
> I have a rule defined as the following:

> CREATE RULE fooBar AS ON
> UPDATE TO VectorMessageTable WHERE new.acks = (SELECT
> numReplicas FROM VectorTable vt WHERE vt.ID =
> new.ID) DO (DELETE FROM VectorMessageTable WHERE ID = new.ID AND
> versionNum = new.versionNum);

> if I call: 

> UPDATE VectorMessageTable SET acks =
> <corresponding-numReplicas-from-VectorTable>

> Then I the update goes through, but none of the records are
> deleted. However, if I run any other subsequent update on these tuples in
> VectorMessageTable (even ones that change the acks value) then the
> rule fires and the tuples are deleted.

Not sure, but maybe you want DO INSTEAD DELETE ... rather than just
DO DELETE ... ?  As it stands, you're commanding the machine to both
delete and update the tuples for which the rule fires.  I think that
will result in the old tuple being marked deleted (twice!), but the
update will still produce a new tuple that's not marked deleted.
That seems to match your symptoms...
        regards, tom lane


В списке pgsql-sql по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Date_part & cast.
Следующее
От: Peter Eisentraut
Дата:
Сообщение: Re: TOAST (was: BLOB)