Re: what are rules for? - Mailing list pgsql-general

----------------------------------------
> Date: Thu, 26 Jun 2008 12:47:04 -0500
> From: shulman@mathcamp.org
> To: dean_rasheed@hotmail.com
> Subject: Re: what are rules for?
> CC: pgsql-general@postgresql.org; tgl@sss.pgh.pa.us; kleptog@svana.org; adam.r@sbcglobal.net
>
> On Thu, Jun 26, 2008 at 12:11 PM, Dean Rasheed  wrote:
>> This can almost be implemented in PostgreSQL right now, using a rule of
>> the form "... do instead select trigger_fn()" - except, as you point out, the
>> caller won't know how many rows were actually updated. As far as the
>> top-level query knows, it didn't update anything, which will break some
>> (most?) clients. Apart from that, this does actually work!
>
> Yeah, I actually thought of that.  But as you point out, many clients
> would get confused.  Someone pointed out in an earlier thread that a
> way to fix this, for updates on a multi-table view (where most of the
> complication lies), is to write a "trigger" function that updates all
> the constituent tables except for one, and then write a rule that
> calls that function and then updates the one remaining table itself.
> This seems to work okay although I have not tested it with many
> clients.
>
> Mike

Yes that would seem to work. For UPDATE anyway. Although if it were purely
DML that you were doing, you would probably be better off just having multiple
UPDATE statements in the rule body. Then they would stand a better chance
of being rewritten and executed more efficiently.

The problem is that the rule system has a lot of subtle pitfalls waiting to trip
you up. Suppose for example that your view did an inner join on the PK of
2 tables, and you tried to use that trick to implement a DELETE "trigger" to
delete from both. After the first deletion, no rows in the view would match and
the second delete wouldn't happen. OK, so there's an easy fix to this, but it is
easy to overlook.

In my case, I wanted to invoke a function after the delete, which did some
complex logic relying on the tables being in their final state. So I really needed
an "after delete" trigger, and this didn't seem possible with the rule system.

As the documentation points out, there are some things that can't be done
with rules (and also with triggers). Each has its own pros and cons in different
situations. So I for one would love to see both available for views.

I've used Oracle's "instead of" triggers, and they work really well*, but maybe
there is some ever better way of implementing triggers on views.

Dean.

* Better in fact than their before and after triggers on tables, which in Oracle
are much more prone to mutating table errors.

_________________________________________________________________
Welcome to the next generation of Windows Live
http://www.windowslive.co.uk/get-live