Thread: Docs patch to note that rules only get run once per query.
I've made the following patch to the current CVS docs. This is to
explain some confusion I ran into when trying to use rules. After
reading the documentation on rules, I was expecting my rule to get run
at the end of a query where I deleted multiple rows, not after the first
row was deleted, then not any further.
I think the below text makes it more clear that this may happen and is
not a bug.
Sean
--- rules.sgml.old 2003-04-13 16:58:27.000000000 -0600
+++ rules.sgml 2003-04-13 17:26:53.000000000 -0600
@@ -32,6 +32,15 @@
linkend="ONG90">.
</para>
+<Note>
+ <Para>
+ Note that rules are only invoked once per query. This may be a problem
+ in instances where the rule is updating a table based on another table's
+ contents, and you do a delete or update that changes multiple rows. In
+ this case, you will have to use a trigger.
+ </Para>
+</Note>
+
<Sect1 id="querytree">
<Title>The Query Tree</Title>
--
Do one thing every day that scares you.
-- Mary Schmich
Sean Reifschneider, Inimitably Superfluous <jafo@tummy.com>
tummy.com, ltd. - Linux Consulting since 1995. Qmail, Python, SysAdmin
Sean Reifschneider <jafo@tummy.com> writes:
> I've made the following patch to the current CVS docs.
> + Note that rules are only invoked once per query.
<<itch>> This still seems like a serious oversimplification or
misexplanation to me. It's true that a rule creates only one new query
to execute alongside each user-supplied query --- but that additional
query can execute upon many individual rows. So I feel dissatisfied
with the above "clarification". Can you think of another way to explain
it?
regards, tom lane
On Sun, Apr 13, 2003 at 11:47:32PM -0400, Tom Lane wrote:
>Sean Reifschneider <jafo@tummy.com> writes:
>> I've made the following patch to the current CVS docs.
>
>> + Note that rules are only invoked once per query.
>
><<itch>> This still seems like a serious oversimplification or
>misexplanation to me. It's true that a rule creates only one new query
>to execute alongside each user-supplied query --- but that additional
>query can execute upon many individual rows. So I feel dissatisfied
>with the above "clarification". Can you think of another way to explain
>it?
Unfortunately, I really don't understand it... My first thought was
that it was a bug, but when I presented it to the bugs list they said
that the rule only gets executed once per query and that triggers should
be used if you want to run on every impacted row.
Perhaps this re-wording make is more clear?
<Note>
<Para>
Note that rules are only invoked once per query. This may be a problem
in instances where the rule is updating table A based on table B's
contents. If you do a multi-row delete on B, the rule may get run
only after the delete of the first row, not after all deletes finish.
In this case, you will have to use a trigger.
</Para>
</Note>
Sean
--
*** Quits: TITANIC (Excess Flood)
Sean Reifschneider, Inimitably Superfluous <jafo@tummy.com>
tummy.com, ltd. - Linux Consulting since 1995. Qmail, Python, SysAdmin
Back off man. I'm a scientist. http://HackingSociety.org/
Sean Reifschneider <jafo@tummy.com> writes:
> On Sun, Apr 13, 2003 at 11:47:32PM -0400, Tom Lane wrote:
>> So I feel dissatisfied with the above "clarification". Can you think
>> of another way to explain it?
> Unfortunately, I really don't understand it... My first thought was
> that it was a bug, but when I presented it to the bugs list they said
> that the rule only gets executed once per query and that triggers should
> be used if you want to run on every impacted row.
Well, it could be argued that the present rule behavior is buggy, not
because there's anything wrong with it on its own terms, but just
because too many people don't understand it :-(
Jan, can you offer any words of wisdom here?
regards, tom lane
Tom Lane wrote: > > Sean Reifschneider <jafo@tummy.com> writes: > > On Sun, Apr 13, 2003 at 11:47:32PM -0400, Tom Lane wrote: > >> So I feel dissatisfied with the above "clarification". Can you think > >> of another way to explain it? > > > Unfortunately, I really don't understand it... My first thought was > > that it was a bug, but when I presented it to the bugs list they said > > that the rule only gets executed once per query and that triggers should > > be used if you want to run on every impacted row. > > Well, it could be argued that the present rule behavior is buggy, not > because there's anything wrong with it on its own terms, but just > because too many people don't understand it :-( > > Jan, can you offer any words of wisdom here? I'm not subscribed to docs, so I missed the original discussion. My guess is that the concept of query rewriting, or productional rules it was originally called, is just not that easy to understand. It can do something else, it can do it conditionally, it can do it for multiple rows at once and you have access to NEW and OLD ... the natural conclusion is that it works on the row level ... but it doesn't. As a matter of fact "rules" are not "executed" at all. They describe "modifications" that are applied to queries before anything is executed. It is allways a mixture between the original query and the rule(s) that get's executed. And in the case of updateable views, the views RIR rule is *allways* part of that. My hardest attempt to describe how it works is Chapter 13 of Section II of the PostgreSQL Programmers Guide, also known as the Al Bundy database. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Sean Reifschneider writes: > <Note> > <Para> > Note that rules are only invoked once per query. This may be a problem > in instances where the rule is updating table A based on table B's > contents. If you do a multi-row delete on B, the rule may get run > only after the delete of the first row, not after all deletes finish. > In this case, you will have to use a trigger. > </Para> > </Note> This can't possibly be true. Rules are never invoked "after" any deletion of any row. Take a close look at the examples of update rules in the documentation. Read how the references to NEW and OLD are resolved. Play out the expansion of your example of paper. The rule will expand your initial command to a big and ugly set of commands. But all those commands are applied like any normal command that you could have entered by hand. So if too few or too many rows are affected, that's because of the conditions attached to the command. -- Peter Eisentraut peter_e@gmx.net
On Mon, Apr 14, 2003 at 06:38:16PM +0200, Peter Eisentraut wrote: >This can't possibly be true. Rules are never invoked "after" any deletion >of any row. Take a close look at the examples of update rules in the I'm open to you suggesting a different way of saying the above. I understand that rules aren't really invoked, but that's what I came up with to describe what I was seeing. After a review of my SQL, I made a post to the bugs list about it and the only reply I got was that the problem I was seeing was due to rules only doing their thing once per query. In my case, it seemed like the rule SQL was being executed after the first deletion. This worked fine if I was doing a single deletion, but would muck things up if I was doing a multi-column deletion. A full description including a sample of how to reproduce it is in my original bugs posting, available at: http://archives.postgresql.org/pgsql-bugs/2003-03/msg00098.php >The rule will expand your initial command to a big and ugly set of >commands. But all those commands are applied like any normal command that >you could have entered by hand. So if too few or too many rows are >affected, that's because of the conditions attached to the command. I would welcome a solution. I tried a number of different mechanisms for doing the updates I want using rules, but wasn't able to find one that would do the right thing. Thanks, Sean -- The question of whether a computer can think is no more interesting than the question of whether a submarine can swim." -- Edsgar W. Dijkstra (1930-2002) Sean Reifschneider, Inimitably Superfluous <jafo@tummy.com> tummy.com, ltd. - Linux Consulting since 1995. Qmail, Python, SysAdmin