pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRules/Trigges Trade-offs
14+ messages / 4 participants
[nested] [flat]
* Rules/Trigges Trade-offs
@ 2002-12-06 16:03 Jean-Luc Lachance <jllachan@nsd.ca>
2002-12-06 16:31 ` Re: Rules/Trigges Trade-offs Richard Huxton <dev@archonet.com>
2002-12-06 16:34 ` Re: Rules/Trigges Trade-offs Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 2 replies; 14+ messages in thread
From: Jean-Luc Lachance @ 2002-12-06 16:03 UTC (permalink / raw)
To: pgsql-sql
Hi all!
Is there a guideline on the use of rules compared to triggers when both
can be use to achieve the same result?
JLL
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Rules/Trigges Trade-offs
2002-12-06 16:03 Rules/Trigges Trade-offs Jean-Luc Lachance <jllachan@nsd.ca>
@ 2002-12-06 16:31 ` Richard Huxton <dev@archonet.com>
2002-12-06 17:30 ` Re: Rules/Trigges Trade-offs Jean-Luc Lachance <jllachan@nsd.ca>
1 sibling, 1 reply; 14+ messages in thread
From: Richard Huxton @ 2002-12-06 16:31 UTC (permalink / raw)
To: Jean-Luc Lachance <jllachan@nsd.ca>; pgsql-sql
On Friday 06 Dec 2002 4:03 pm, Jean-Luc Lachance wrote:
> Hi all!
>
> Is there a guideline on the use of rules compared to triggers when both
> can be use to achieve the same result?
If I can use rules I do. Rules rewrite the query so are processed once,
whereas triggers get processed for every row.
--
Richard Huxton
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Rules/Trigges Trade-offs
2002-12-06 16:03 Rules/Trigges Trade-offs Jean-Luc Lachance <jllachan@nsd.ca>
2002-12-06 16:31 ` Re: Rules/Trigges Trade-offs Richard Huxton <dev@archonet.com>
@ 2002-12-06 17:30 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-12-06 17:41 ` Re: Rules/Trigges Trade-offs Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 14+ messages in thread
From: Jean-Luc Lachance @ 2002-12-06 17:30 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
Thanks for the info.
Do you mean that if an update affects more than one row I should use
triggers because the rules will be executed only once?
JLL
Richard Huxton wrote:
>
> On Friday 06 Dec 2002 4:03 pm, Jean-Luc Lachance wrote:
> > Hi all!
> >
> > Is there a guideline on the use of rules compared to triggers when both
> > can be use to achieve the same result?
>
> If I can use rules I do. Rules rewrite the query so are processed once,
> whereas triggers get processed for every row.
> --
> Richard Huxton
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Rules/Trigges Trade-offs
2002-12-06 16:03 Rules/Trigges Trade-offs Jean-Luc Lachance <jllachan@nsd.ca>
2002-12-06 16:31 ` Re: Rules/Trigges Trade-offs Richard Huxton <dev@archonet.com>
2002-12-06 17:30 ` Re: Rules/Trigges Trade-offs Jean-Luc Lachance <jllachan@nsd.ca>
@ 2002-12-06 17:41 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 14+ messages in thread
From: Bruce Momjian @ 2002-12-06 17:41 UTC (permalink / raw)
To: Jean-Luc Lachance <jllachan@nsd.ca>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
No, the rule will affect all the rows using one query.
---------------------------------------------------------------------------
Jean-Luc Lachance wrote:
> Thanks for the info.
>
> Do you mean that if an update affects more than one row I should use
> triggers because the rules will be executed only once?
>
> JLL
>
>
> Richard Huxton wrote:
> >
> > On Friday 06 Dec 2002 4:03 pm, Jean-Luc Lachance wrote:
> > > Hi all!
> > >
> > > Is there a guideline on the use of rules compared to triggers when both
> > > can be use to achieve the same result?
> >
> > If I can use rules I do. Rules rewrite the query so are processed once,
> > whereas triggers get processed for every row.
> > --
> > Richard Huxton
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Rules/Trigges Trade-offs
2002-12-06 16:03 Rules/Trigges Trade-offs Jean-Luc Lachance <jllachan@nsd.ca>
@ 2002-12-06 16:34 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-12-06 23:25 ` Re: Rules/Trigges Trade-offs Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 14+ messages in thread
From: Bruce Momjian @ 2002-12-06 16:34 UTC (permalink / raw)
To: Jean-Luc Lachance <jllachan@nsd.ca>; +Cc: pgsql-sql
My book has a section on that:
http://www.postgresql.org/docs/awbook.html
Triggers are mostly for testing/modifying the row being
inserted/updated, while rules are better for affecting other rows or
other tables.
Jean-Luc Lachance wrote:
> Hi all!
>
> Is there a guideline on the use of rules compared to triggers when both
> can be use to achieve the same result?
>
> JLL
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Rules/Trigges Trade-offs
2002-12-06 16:03 Rules/Trigges Trade-offs Jean-Luc Lachance <jllachan@nsd.ca>
2002-12-06 16:34 ` Re: Rules/Trigges Trade-offs Bruce Momjian <pgman@candle.pha.pa.us>
@ 2002-12-06 23:25 ` Josh Berkus <josh@agliodbs.com>
2002-12-06 23:30 ` Re: Rules/Trigges Trade-offs Bruce Momjian <pgman@candle.pha.pa.us>
2002-12-09 16:11 ` Re: Rules/Trigges Trade-offs Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 2 replies; 14+ messages in thread
From: Josh Berkus @ 2002-12-06 23:25 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; Jean-Luc Lachance <jllachan@nsd.ca>; +Cc: pgsql-sql
Bruce, Richard,
> Triggers are mostly for testing/modifying the row being
> inserted/updated, while rules are better for affecting other rows or
> other tables.
Hmmm. Thought that there were also some other criteria:
1) Rules can't use indexes to do their processing, so Rules which query large
secondary tables can be a bad idea (maybe this has changed?)
2) Only Rules can "DO INSTEAD"; thus, only Rules are good for defining
Read/Write views.
3) There are no AFTER Rules, making, for example, a rule with a table check on
the new data impractical, so you'd want to use Triggers or Constraints
etc.
There are, IMHO, some things Rules are better for, and some things Triggers
are better for. I tend to use all Triggers except for updatable views,
simply because using a mix of Rules and Triggers can be very hard to keep
track of, but YMMV.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Rules/Trigges Trade-offs
2002-12-06 16:03 Rules/Trigges Trade-offs Jean-Luc Lachance <jllachan@nsd.ca>
2002-12-06 16:34 ` Re: Rules/Trigges Trade-offs Bruce Momjian <pgman@candle.pha.pa.us>
2002-12-06 23:25 ` Re: Rules/Trigges Trade-offs Josh Berkus <josh@agliodbs.com>
@ 2002-12-06 23:30 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-12-06 23:54 ` Re: Rules/Trigges Trade-offs Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 14+ messages in thread
From: Bruce Momjian @ 2002-12-06 23:30 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Jean-Luc Lachance <jllachan@nsd.ca>; pgsql-sql
Josh Berkus wrote:
>
> Bruce, Richard,
>
> > Triggers are mostly for testing/modifying the row being
> > inserted/updated, while rules are better for affecting other rows or
> > other tables.
>
> Hmmm. Thought that there were also some other criteria:
>
> 1) Rules can't use indexes to do their processing, so Rules which query large
> secondary tables can be a bad idea (maybe this has changed?)
I don't think this is true. Rewrite is before optimizer so it should be
optimized just the same.
>
> 2) Only Rules can "DO INSTEAD"; thus, only Rules are good for defining
> Read/Write views.
True.
> 3) There are no AFTER Rules, making, for example, a rule with a table check on
> the new data impractical, so you'd want to use Triggers or Constraints
We have changed ordering in 7.3 where I think INSERT rules are _after_
the insert.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Rules/Trigges Trade-offs
2002-12-06 16:03 Rules/Trigges Trade-offs Jean-Luc Lachance <jllachan@nsd.ca>
2002-12-06 16:34 ` Re: Rules/Trigges Trade-offs Bruce Momjian <pgman@candle.pha.pa.us>
2002-12-06 23:25 ` Re: Rules/Trigges Trade-offs Josh Berkus <josh@agliodbs.com>
2002-12-06 23:30 ` Re: Rules/Trigges Trade-offs Bruce Momjian <pgman@candle.pha.pa.us>
@ 2002-12-06 23:54 ` Josh Berkus <josh@agliodbs.com>
2002-12-07 02:24 ` Re: Rules/Trigges Trade-offs Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 14+ messages in thread
From: Josh Berkus @ 2002-12-06 23:54 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Jean-Luc Lachance <jllachan@nsd.ca>; pgsql-sql
Bruce,
> > 1) Rules can't use indexes to do their processing, so Rules which query
large
> > secondary tables can be a bad idea (maybe this has changed?)
>
> I don't think this is true. Rewrite is before optimizer so it should be
> optimized just the same.
I was speaking if the Rule has to do a lookup on a 2nd table in the course of
its processing; it used to be that such a lookup could not use indexes
because Rules are pre-optimizer. This could have changed since 7.1, though.
> > 3) There are no AFTER Rules, making, for example, a rule with a table
check on
> > the new data impractical, so you'd want to use Triggers or Constraints
>
> We have changed ordering in 7.3 where I think INSERT rules are _after_
> the insert.
How would that work? What if I want to reject the insert?
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Rules/Trigges Trade-offs
2002-12-06 16:03 Rules/Trigges Trade-offs Jean-Luc Lachance <jllachan@nsd.ca>
2002-12-06 16:34 ` Re: Rules/Trigges Trade-offs Bruce Momjian <pgman@candle.pha.pa.us>
2002-12-06 23:25 ` Re: Rules/Trigges Trade-offs Josh Berkus <josh@agliodbs.com>
2002-12-06 23:30 ` Re: Rules/Trigges Trade-offs Bruce Momjian <pgman@candle.pha.pa.us>
2002-12-06 23:54 ` Re: Rules/Trigges Trade-offs Josh Berkus <josh@agliodbs.com>
@ 2002-12-07 02:24 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-12-07 03:43 ` Re: Rules/Trigges Trade-offs Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 14+ messages in thread
From: Bruce Momjian @ 2002-12-07 02:24 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Jean-Luc Lachance <jllachan@nsd.ca>; pgsql-sql
Josh Berkus wrote:
>
> Bruce,
>
> > > 1) Rules can't use indexes to do their processing, so Rules which query
> large
> > > secondary tables can be a bad idea (maybe this has changed?)
> >
> > I don't think this is true. Rewrite is before optimizer so it should be
> > optimized just the same.
>
> I was speaking if the Rule has to do a lookup on a 2nd table in the course of
> its processing; it used to be that such a lookup could not use indexes
> because Rules are pre-optimizer. This could have changed since 7.1, though.
As I remember, the RULE is processed just like an ordinary query. I
wasn't aware it was looking up anything.
> > > 3) There are no AFTER Rules, making, for example, a rule with a table
> check on
> > > the new data impractical, so you'd want to use Triggers or Constraints
> >
> > We have changed ordering in 7.3 where I think INSERT rules are _after_
> > the insert.
>
> How would that work? What if I want to reject the insert?
Uh, good question. I can't remember now what we re-ordered in 7.3, but
I thought it had to do with rules.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Rules/Trigges Trade-offs
2002-12-06 16:03 Rules/Trigges Trade-offs Jean-Luc Lachance <jllachan@nsd.ca>
2002-12-06 16:34 ` Re: Rules/Trigges Trade-offs Bruce Momjian <pgman@candle.pha.pa.us>
2002-12-06 23:25 ` Re: Rules/Trigges Trade-offs Josh Berkus <josh@agliodbs.com>
2002-12-06 23:30 ` Re: Rules/Trigges Trade-offs Bruce Momjian <pgman@candle.pha.pa.us>
2002-12-06 23:54 ` Re: Rules/Trigges Trade-offs Josh Berkus <josh@agliodbs.com>
2002-12-07 02:24 ` Re: Rules/Trigges Trade-offs Bruce Momjian <pgman@candle.pha.pa.us>
@ 2002-12-07 03:43 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 14+ messages in thread
From: Bruce Momjian @ 2002-12-07 03:43 UTC (permalink / raw)
To: pgsql-sql; +Cc: josh@agliodbs.com, Jean-Luc Lachance <jllachan@nsd.ca>
Bruce Momjian wrote:
> > > > 3) There are no AFTER Rules, making, for example, a rule with a table
> > check on
> > > > the new data impractical, so you'd want to use Triggers or Constraints
> > >
> > > We have changed ordering in 7.3 where I think INSERT rules are _after_
> > > the insert.
> >
> > How would that work? What if I want to reject the insert?
>
> Uh, good question. I can't remember now what we re-ordered in 7.3, but
> I thought it had to do with rules.
OK, I found what I wanted. We made rules fire in alphabetical order in
7.3, and INSTEAD rules are not reordered to the font/back --- they are
treated just like any other rule and executed alphabetically.
I thought we had some problem with rules or triggers and OLD/NEW
handling, but I can't see any mention of that, and it must have been in
an older release.
The question of firing order is a good one and one that isn't asked very
often.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Rules/Trigges Trade-offs
2002-12-06 16:03 Rules/Trigges Trade-offs Jean-Luc Lachance <jllachan@nsd.ca>
2002-12-06 16:34 ` Re: Rules/Trigges Trade-offs Bruce Momjian <pgman@candle.pha.pa.us>
2002-12-06 23:25 ` Re: Rules/Trigges Trade-offs Josh Berkus <josh@agliodbs.com>
@ 2002-12-09 16:11 ` Jean-Luc Lachance <jllachan@nsd.ca>
1 sibling, 0 replies; 14+ messages in thread
From: Jean-Luc Lachance @ 2002-12-09 16:11 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-sql
Josh,
Thanks for the info.
I need to change an insert into an update when the key already exists.
I have been using a rules to test it on a small set (table) and it
works.
"Rules can't use indexes" just scared me. I will have to test on a
larger set.
Also, I had the impression that if a trigger returned NULL, one would
get the equivalent of "DO NOTHING".
Am I wrong with that assumption?
JLL
Josh Berkus wrote:
>
> Bruce, Richard,
>
> > Triggers are mostly for testing/modifying the row being
> > inserted/updated, while rules are better for affecting other rows or
> > other tables.
>
> Hmmm. Thought that there were also some other criteria:
>
> 1) Rules can't use indexes to do their processing, so Rules which query large
> secondary tables can be a bad idea (maybe this has changed?)
>
> 2) Only Rules can "DO INSTEAD"; thus, only Rules are good for defining
> Read/Write views.
>
> 3) There are no AFTER Rules, making, for example, a rule with a table check on
> the new data impractical, so you'd want to use Triggers or Constraints
>
> etc.
>
> There are, IMHO, some things Rules are better for, and some things Triggers
> are better for. I tend to use all Triggers except for updatable views,
> simply because using a mix of Rules and Triggers can be very hard to keep
> track of, but YMMV.
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Rules/Trigges Trade-offs
@ 2002-12-09 20:06 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 14+ messages in thread
From: Josh Berkus @ 2002-12-09 20:06 UTC (permalink / raw)
To: Ian Harding <ianh@tpchd.org>; jllachan@nsd.ca; +Cc: pgman@candle.pha.pa.us; pgsql-sql
Ian,
> Anyway, I have a similar requirement, to intercept insert/update/delete and
redirect the data if a condition is met. Right now I am trying an INSTEAD
rule that puts the condtion in the WHERE of the rule definition. It seems to
work OK, but if the condition has exeptions where I would like to throw an
error, I can't. And since it is an INSTEAD, I can't have a trigger on the
table fire to find the exception cases. Urgh.
I'd suggest a "DO INSTEAD SELECT some_function(NEW)"
where some_function is a function that tests for errors, throws and exception
if necessary, otherwise inserts. It's tricky, but probably the best way to
get trigger + rule functionality at once.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Rules/Trigges Trade-offs
@ 2002-12-09 20:40 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 14+ messages in thread
From: Josh Berkus @ 2002-12-09 20:40 UTC (permalink / raw)
To: Ian Harding <ianh@tpchd.org>; +Cc: pgsql-sql
Ian,
You're welcome. Replying to the list for the edification of other users.
-Josh
> I just figgered it out. I declared the function as
>
> create function some_func(test) returns int ...
>
> where test is the name of a table. The values are passed as a tcl array. I
will see if RECORD works too. I wonder if that would eliminate the problem I
ran into where I had dropped and recreated the table. The function barfed
since the OID for the user defined type "test" did not exist.
>
> This will work slick. Thanks!
>
> >>> Josh Berkus <josh@agliodbs.com> 12/09/02 12:29PM >>>
> Ian,
>
> > That makes sense! Is that psuedo code, or is there a way to send a
function
> the entire NEW array without specifying each column name and datatype
> specifically.
> >
> > I am using pltcl, if that matters..
>
> Not sure about pltcl. I'd say, try it, declare the function as:
>
> CREATE FUNCTION some_func (RECORD) and see how things go.
>
> --
> -Josh Berkus
>
> ______AGLIO DATABASE SOLUTIONS___________________________
> Josh Berkus
> Complete information technology josh@agliodbs.com
> and data management solutions (415) 565-7293
> for law firms, small businesses fax 621-2533
> and non-profit organizations. San Francisco
>
>
>
--
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Rules/Trigges Trade-offs
@ 2002-12-09 20:48 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 14+ messages in thread
From: Josh Berkus @ 2002-12-09 20:48 UTC (permalink / raw)
To: Ian Harding <ianh@tpchd.org>; +Cc: pgsql-sql
Ian,
> Thanks! I would have, but my messages bounce from SQL, even though I am
subscribed ( I get the messages, for crying out loud!)
send an e-mail to pgsql-sql-owner@postgresql.org.
>
> PS RECORD doesn't work but I think that is because I am on 7.2.1....
OK. I may have tested that on 7.4 devel by accident.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2002-12-09 20:48 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-06 16:03 Rules/Trigges Trade-offs Jean-Luc Lachance <jllachan@nsd.ca>
2002-12-06 16:31 ` Richard Huxton <dev@archonet.com>
2002-12-06 17:30 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-12-06 17:41 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-12-06 16:34 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-12-06 23:25 ` Josh Berkus <josh@agliodbs.com>
2002-12-06 23:30 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-12-06 23:54 ` Josh Berkus <josh@agliodbs.com>
2002-12-07 02:24 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-12-07 03:43 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-12-09 16:11 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-12-09 20:06 Re: Rules/Trigges Trade-offs Josh Berkus <josh@agliodbs.com>
2002-12-09 20:40 Re: Rules/Trigges Trade-offs Josh Berkus <josh@agliodbs.com>
2002-12-09 20:48 Re: Rules/Trigges Trade-offs Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox