Subject: Docs patch to note that rules only get run once per query.

From: Sean Reifschneider <[email-masked]>
Date: Sun, 13 Apr 2003 17:43:49 -0600

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 <[email-masked]>
tummy.com, ltd. - Linux Consulting since 1995.  Qmail, Python, SysAdmin

---

From: Tom Lane <[email-masked]>
Date: Sun, 13 Apr 2003 23:47:32 -0400

Sean Reifschneider <[email-masked]> 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

---

From: Sean Reifschneider <[email-masked]>
Date: Sun, 13 Apr 2003 21:58:29 -0600

>with the above "clarification".  Can you think of another way to explain

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 <[email-masked]>
tummy.com, ltd. - Linux Consulting since 1995.  Qmail, Python, SysAdmin
      Back off man. I'm a scientist.   http://HackingSociety.org/

---

From: Tom Lane <[email-masked]>
Date: Mon, 14 Apr 2003 00:11:55 -0400

Sean Reifschneider <[email-masked]> writes:
>> So I feel dissatisfied with the above "clarification".  Can you think
>> of another way to explain it?

> 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

---

From: Jan Wieck <[email-masked]>
Date: Mon, 14 Apr 2003 07:21:01 -0400

> 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.                                  #
#================================================== [email-masked] #

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 14 Apr 2003 18:38:16 +0200 (CEST)

>   In this case, you will have to use a trigger.

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   [email-masked]

---

From: Sean Reifschneider <[email-masked]>
Date: Mon, 14 Apr 2003 11:53:25 -0600

>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

>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 <[email-masked]>
tummy.com, ltd. - Linux Consulting since 1995.  Qmail, Python, SysAdmin