Re: Half-applied UPDATE rule on view

On 2 July 2010 23:27, A.M. <agentm@themactionfaction.com> wrote:
> Hello,
>
> I have encountered an odd behavior involving rules which the following script demonstrates (in postgresql 8.4.3).
Notethat at the end of the run, the "dud" table contains one row "spam1" when the update rule clearly contains two
insertsto the "dud" table. It seems that the update rule on "test.job" cuts off execution after the first update
executes(and succeeds)- why? 

The problem is that after the first update, "deprecated" is non-NULL
and so no longer matches the view definition. All subsequent actions
in the rule are combined with the view definition, and so find no
matching rows.

Rules are total pain, full of gotchas like this. You're almost
certainly better off using triggers on your tables. You could patch
your rule by moving the update that marks the row as deprecated to the
end and changing its WHERE clause, but really you're just inviting
further pain by continuing to use rules IMO.

Regards,
Dean