Thread: what are rules for?

In another thread, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Well, the rule system is fundamentally a macro-expansion mechanism,
> and multiple-evaluation risks come with that territory.  There are
> things you can do with macro expansion that can't be done any other
> way, so I don't think that that decision was wrong on its face, but
> certainly we've seen plenty of traps for the unwary in it.

Can you describe, or point me to somewhere which describes, all the
things you can do with a rule that you can't do with a trigger?  The
only examples of rules in the manual are (1) logging, which I've just
been told is much better done with a trigger, and (2) making update,
insert, and delete work for a view, which is the only way to do it
because views are not allowed to have update, insert, or delete
triggers.  However, as I have learned in several recent threads, this
use of rules is fraught with difficulties, especially when the view
has more than one table, and it seems that it would be much easier if
triggers were just allowed on views.  What is the real purpose of the
rule system?

Mike

>
> Can you describe, or point me to somewhere which describes, all the
> things you can do with a rule that you can't do with a trigger?  The
> only examples of rules in the manual are (1) logging, which I've just
> been told is much better done with a trigger, and (2) making update,
> insert, and delete work for a view, which is the only way to do it
> because views are not allowed to have update, insert, or delete
> triggers.  However, as I have learned in several recent threads, this
> use of rules is fraught with difficulties, especially when the view
> has more than one table, and it seems that it would be much easier if
> triggers were just allowed on views.  What is the real purpose of the
> rule system?
>

You can read more about rules here:

http://www.postgresql.org/docs/8.3/interactive/rules.html

The documentation calls rules a "query rewrite" system, which helped
me understand their use.  Whereas triggers are called once per row
modified, rules can modify or replace the actual query tree being
executed.  There are some fine examples here:

http://www.postgresql.org/docs/8.3/interactive/rules-triggers.html

Rules can be used to change a SELECT statement in-flight.  This is
actually how views are implemented in postgresql.

One interesting example is having rules and triggers watching for
deletes or updates on a table.  If many rows are modified, rules
can be faster.  Take this statement:

DELETE FROM mydata WHERE idval BETWEEN 10000 and 20000;

Say this statement deletes 10,000 rows.  The delete trigger would
get called 10,000 times whereas the rule is essentially executed
once, since it can share the WHERE clause of the user's query.

Adam Rich wrote:

> One interesting example is having rules and triggers watching for
> deletes or updates on a table.  If many rows are modified, rules
> can be faster.  Take this statement:
>
> DELETE FROM mydata WHERE idval BETWEEN 10000 and 20000;
>
> Say this statement deletes 10,000 rows.  The delete trigger would
> get called 10,000 times whereas the rule is essentially executed
> once, since it can share the WHERE clause of the user's query.

This is a use case we should be able to better implement using FOR EACH
STATEMENT triggers, it seems.  We just need to be able to pass the list
of affected tuples to the trigger function, which until now has remained
unimplemented.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, Jun 23, 2008 at 11:54 PM, Adam Rich <adam.r@sbcglobal.net> wrote:
>> Can you describe, or point me to somewhere which describes, all the
>> things you can do with a rule that you can't do with a trigger?  The
>> only examples of rules in the manual are (1) logging, which I've just
>> been told is much better done with a trigger, and (2) making update,
>> insert, and delete work for a view, which is the only way to do it
>> because views are not allowed to have update, insert, or delete
>> triggers.  However, as I have learned in several recent threads, this
>> use of rules is fraught with difficulties, especially when the view
>> has more than one table, and it seems that it would be much easier if
>> triggers were just allowed on views.  What is the real purpose of the
>> rule system?
>
> You can read more about rules here:
>
> http://www.postgresql.org/docs/8.3/interactive/rules.html

I have read the manual.  As I pointed out, the only examples of rules
in the manual are (1) something which may be better done with a
trigger, and certainly *can* be done with a trigger, whatever the
speed considerations may be, and (2) something which doesn't seem to
work very well, and seemingly (from my PoV) *would* be done better
with triggers if triggers were allowed in that situation.

My question was, what else *can* you do with a rule that you *can't*
do with a trigger?  Are rules only a way to speed up things that could
also be done with triggers?

Thanks!
Mike

On Tue, Jun 24, 2008 at 10:28:00AM -0500, Michael Shulman wrote:
> I have read the manual.  As I pointed out, the only examples of rules
> in the manual are (1) something which may be better done with a
> trigger, and certainly *can* be done with a trigger, whatever the
> speed considerations may be, and (2) something which doesn't seem to
> work very well, and seemingly (from my PoV) *would* be done better
> with triggers if triggers were allowed in that situation.

It possible that if rules wern't the only way currently to implement
(updateable) views, then they might have been ripped out a long time
ago. Thing is, people like views and there is currently no other way to
do them.

> My question was, what else *can* you do with a rule that you *can't*
> do with a trigger?  Are rules only a way to speed up things that could
> also be done with triggers?

Well, views for one. Rules work by manipulating queries, trigger work
by manipulating tuples. Rules have their problems, but sometimes
they're the only way. Updateable views are the major use case.

FWIW, I wonder if the multiple evaluation problem could be solved by
the WITH patch, since the original rule could be stuffed in a WITH
clause and then the rule applied on top of it.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Tue, Jun 24, 2008 at 10:28:00AM -0500, Michael Shulman wrote:
>> My question was, what else *can* you do with a rule that you *can't*
>> do with a trigger?  Are rules only a way to speed up things that could
>> also be done with triggers?

> Well, views for one.

To expand on that: it's pretty hard to see how update or delete triggers
on a view would work.  Insert is easy, because if left to its own
devices the system would in fact try to insert a tuple into the view
relation, and that action could fire a trigger which could redirect the
insertion someplace else.  But updates and deletes require a
pre-existing target tuple, and there just aren't any of those in a view
relation.  (Another way to say it is that update/delete require a CTID
column, which a view hasn't got.)

So view update/delete appear to require a transformational-rule kind
of approach instead of an actions-on-physical-tuples kind of approach.

If you've got a better idea we're all ears ...

            regards, tom lane

Tom Lane wrote:
> To expand on that: it's pretty hard to see how update or delete triggers
> on a view would work.  Insert is easy, because if left to its own
> devices the system would in fact try to insert a tuple into the view
> relation, and that action could fire a trigger which could redirect the
> insertion someplace else.  But updates and deletes require a
> pre-existing target tuple, and there just aren't any of those in a view
> relation.  (Another way to say it is that update/delete require a CTID
> column, which a view hasn't got.)
>
> So view update/delete appear to require a transformational-rule kind
> of approach instead of an actions-on-physical-tuples kind of approach.
>
> If you've got a better idea we're all ears ...

Would it be any easier to implement Oracle-style "instead of" triggers for views, instead of before and after triggers?
Notionallythis seems like a "do instead select trigger_fn()" rule, with the trigger function having complete
responsibilityfor updating the underlying table(s). 

The difficultly I can see is what data to pass to the trigger function, since just passing the old and new values from
theview may not be enough to work out which rows to update. But then, this is no worse than what Oracle currently does,
andfor many data models it is very useful. 

I've used rules to implement updateable views, and I would certainly have found triggers much easier to work with. In
particular,certain things didn't seem to be possible at all with rules, such as "before insert" and "after delete"
actions,because the "where" clause doesn't match anything at those points. With an "instead of" trigger you can
obviouslydo whatever you want, in any order. 

Dean.

_________________________________________________________________
Great deals on almost anything at eBay.co.uk. Search, bid, find and win on eBay today!
http://clk.atdmt.com/UKM/go/msnnkmgl0010000004ukm/direct/01/

On Tue, Jun 24, 2008 at 11:08 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> To expand on that: it's pretty hard to see how update or delete triggers
> on a view would work.  Insert is easy, because if left to its own
> devices the system would in fact try to insert a tuple into the view
> relation, and that action could fire a trigger which could redirect the
> insertion someplace else.  But updates and deletes require a
> pre-existing target tuple, and there just aren't any of those in a view
> relation.  (Another way to say it is that update/delete require a CTID
> column, which a view hasn't got.)

But isn't the CTID column only required in order for the executor to
actually *do* the update or delete?  And since with a view, there is
nothing to actually update or delete in the view itself, the trigger
would be doing the only actual updating or deleting, so where would
the CTID column be needed?

Perhaps this is the same as the "instead trigger" suggestion -- I'm
not familiar with Oracle.

Mike

"Michael Shulman" <shulman@mathcamp.org> writes:
> On Tue, Jun 24, 2008 at 11:08 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> ... But updates and deletes require a
>> pre-existing target tuple, and there just aren't any of those in a view
>> relation.  (Another way to say it is that update/delete require a CTID
>> column, which a view hasn't got.)

> But isn't the CTID column only required in order for the executor to
> actually *do* the update or delete?  And since with a view, there is
> nothing to actually update or delete in the view itself, the trigger
> would be doing the only actual updating or deleting, so where would
> the CTID column be needed?

Well, both the trigger call API and the underlying implementation deal
in CTIDs, so just airily saying "we don't need 'em" doesn't obviously
work.  (Note I did not say "obviously doesn't work".  Whether this is
feasible depends on much closer analysis than any of the hand-waving
that we've done so far.)

To my mind there are two really fundamental issues underlying this.
One, which is what CTID fixes, is that a view doesn't have any primary
key by which to identify which row you're talking about.  (Even if
there is a candidate key implicit in the view semantics, we don't
have any way for the system to know what it is.)  The other nasty little
issue is that if the view involves any non-immutable functions, it's
not necessarily the case that you can recompute the OLD row at all.

Also, if the view involves expensive functions, you'd probably rather
the system *didn't* recompute them unless absolutely needed, even if
they're immutable.  A transform-based approach can succeed at that, but
a trigger-based approach really can't since it needs to see materialized
OLD and NEW rows.

            regards, tom lane

Tom Lane wrote:
> Well, both the trigger call API and the underlying implementation deal
> in CTIDs, so just airily saying "we don't need 'em" doesn't obviously
> work.  (Note I did not say "obviously doesn't work".  Whether this is
> feasible depends on much closer analysis than any of the hand-waving
> that we've done so far.)

> To my mind there are two really fundamental issues underlying this.
> One, which is what CTID fixes, is that a view doesn't have any primary
> key by which to identify which row you're talking about.  (Even if
> there is a candidate key implicit in the view semantics, we don't
> have any way for the system to know what it is.)

The Oracle "instead of" trigger ducks this issue completely. The
trigger is called once per row in the view that matches the top-level
"where" clause, and it is entirely up to the author of the trigger
function to work out what to update (if anything). In fact the trigger
is free to update an entirely different set of rows if it wants to!

An obvious problem with this is that if the view has no unique key,
the trigger may end up doing the same work several times over. Say I
do "update my_view set a=10 where b=5", and 20 rows match "b=5". Then
the trigger function will get called 20 times, and it will probably
just do the same thing each time. I'm not aware of a good solution to
this, other than "don't write views like that".


> The other nasty little
> issue is that if the view involves any non-immutable functions, it's
> not necessarily the case that you can recompute the OLD row at all.

Surely the results of updating a view containing non-immutable
functions are going to be pretty unpredictable anyway.


> Also, if the view involves expensive functions, you'd probably rather
> the system *didn't* recompute them unless absolutely needed, even if
> they're immutable.  A transform-based approach can succeed at that, but
> a trigger-based approach really can't since it needs to see materialized
> OLD and NEW rows.

Yes that's true. You could perhaps mitigate against this sort of
performance problem by providing some mechanism for the trigger
definer to select which columns to pass to trigger function.

Even if the view doesn't contain expensive functions, I would expect
a trigger to perform worse than a query-rewrite in cases such as a
single update statement which affects multiple rows. So triggers might
not be suitable for such cases, but there would also be many other
cases where the performance would be similar, and then the ease-of-use
and greater flexibility of triggers compared to rules would make them
preferable (IMO).

Dean.

_________________________________________________________________

http://clk.atdmt.com/UKM/go/msnnkmgl0010000002ukm/direct/01/

On Thu, Jun 26, 2008 at 5:08 AM, Dean Rasheed <dean_rasheed@hotmail.com> wrote:
> The Oracle "instead of" trigger ducks this issue completely. The
> trigger is called once per row in the view that matches the top-level
> "where" clause, and it is entirely up to the author of the trigger
> function to work out what to update (if anything).

That sounds like exactly the sort of thing I was envisioning.
Although from what Tom said, it sounds as though "instead of" triggers
in PostgreSQL would have to be implemented in a significantly
different way from other triggers.

How does an Oracle "instead of" trigger decide how many rows to tell
the caller were updated?  Can this "return value" be modified
programmatically by the trigger?

Mike

Mike-

If I understand your question you could use a 'row-trigger'
http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c18trigs.htm#1907
specificy initial filtering thru 'Trigger Restriction'

consequent specifics of which row to process can be handled in the 'Trigger Action'

Anyone else?
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.


> Date: Thu, 26 Jun 2008 10:29:30 -0500
> From: shulman@mathcamp.org
> To: dean_rasheed@hotmail.com
> Subject: Re: [GENERAL] 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 5:08 AM, Dean Rasheed <dean_rasheed@hotmail.com> wrote:
> > The Oracle "instead of" trigger ducks this issue completely. The
> > trigger is called once per row in the view that matches the top-level
> > "where" clause, and it is entirely up to the author of the trigger
> > function to work out what to update (if anything).
>
> That sounds like exactly the sort of thing I was envisioning.
> Although from what Tom said, it sounds as though "instead of" triggers
> in PostgreSQL would have to be implemented in a significantly
> different way from other triggers.
>
> How does an Oracle "instead of" trigger decide how many rows to tell
> the caller were updated? Can this "return value" be modified
> programmatically by the trigger?
>
> Mike
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Need to know now? Get instant answers with Windows Live Messenger. IM on your terms.

> On Thu, Jun 26, 2008 at 5:08 AM, Dean Rasheed  wrote:
>> The Oracle "instead of" trigger ducks this issue completely. The
>> trigger is called once per row in the view that matches the top-level
>> "where" clause, and it is entirely up to the author of the trigger
>> function to work out what to update (if anything).
>
> That sounds like exactly the sort of thing I was envisioning.
> Although from what Tom said, it sounds as though "instead of" triggers
> in PostgreSQL would have to be implemented in a significantly
> different way from other triggers.
>
> How does an Oracle "instead of" trigger decide how many rows to tell
> the caller were updated?  Can this "return value" be modified
> programmatically by the trigger?
>
> Mike

AFAIK Oracle's "instead of" trigger has no mechanism for returning the
actual number of rows updated (the trigger itself has no return value). So
I guess that they are just assuming that it matches the number of times
the trigger was executed, and returning that to the caller.

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!

Dean

_________________________________________________________________

http://clk.atdmt.com/UKM/go/msnnkmgl0010000002ukm/direct/01/

On Thu, Jun 26, 2008 at 12:11 PM, Dean Rasheed <dean_rasheed@hotmail.com> 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

----------------------------------------
> 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

On Fri, Jun 27, 2008 at 3:06 AM, Dean Rasheed <dean_rasheed@hotmail.com> wrote:
>> 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.
>
> 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.

As Richard Broersma pointed out in the earlier thread, this approach
has a tendency to result in "partial updates" if the WHERE clause in
the UPDATE statement issued on the view includes more than just the
primary key.

http://archives.postgresql.org/pgsql-general/2008-06/msg00479.php
http://archives.postgresql.org/pgsql-general/2006-12/msg01048.php

This is probably another one of the "subtle pitfalls" you mentioned,
but to me it means that using multiple UPDATE statements in the rule
body is unacceptable.

Mike