Thread: "strange" rule behavior with nextval on new.* fields

Dear debuggers,

I'd like to report the following "strange" behavior that I encountered
while trying (a bad idea, I know) to use a rule as a "poor man sql-trigger".

It seems that "on update do also" rules the new.* fields are evaluated
several times instead of being computed once, which is a bad idea, esp.
for "nextval".

As a consequence, my update does not work, i.e. new.id is actually
different from the id being used by the insert in the example so the
expected behavior that previous . [Note that even if it would work as I
expected, concurrent transaction could break the property.]

Anyway, it really looks like a strange behavior to me, so misleading and
unexpected that it could be qualified as a bug rather than a feature. I
did not spot such caveats by a quick look thru the documentation.

In the attached file, the result of 4 inserts is:

  id | data  | islast
----+-------+--------
   1 | one   | f
   3 | two   | f
   6 | three | f
  10 | four  | f

last_val is 14

Where I would rather have expected:

  id | data  | islast
----+-------+--------
   1 | one   | f
   2 | two   | f
   3 | three | f
   4 | four  | t

last_val should be 4

Have a nice day,

--
Fabien.

> I'd like to report the following "strange" behavior that I encountered
> while trying (a bad idea, I know) to use a rule as a "poor man sql-trigger".

I noticed that I forget about the bug report guidelines...

The above mentionned strange behavior is with a recent cvs tree, between
beta3 and beta4, on a debian linux box under an intel architecture.

--
Fabien Coelho - coelho@cri.ensmp.fr

On Wed, Nov 10, 2004 at 10:23:41AM +0100, Fabien COELHO wrote:

> It seems that "on update do also" rules the new.* fields are evaluated
> several times instead of being computed once, which is a bad idea, esp.
> for "nextval".

This comes up often and the response is usually something like,
"Rules are macros, which is why referring to NEW.id causes another
evaluation of nextval().  If you don't want that to happen then use
a trigger."

> Anyway, it really looks like a strange behavior to me, so misleading and
> unexpected that it could be qualified as a bug rather than a feature. I
> did not spot such caveats by a quick look thru the documentation.

This comes up often enough that maybe it warrants a "Caveats with
Rules" section in "The Rule System" chapter and a link to that
section in the CREATE RULE documentation, as well as mention in the
FAQ.

Thoughts from the developers?  I'd offer to write the additional
documentation but my understanding of the rule system is pretty
shallow.  Nevertheless, maybe I could at least write something
that others could build on.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr <mike@fuhr.org> writes:
> This comes up often enough that maybe it warrants a "Caveats with
> Rules" section in "The Rule System" chapter and a link to that
> section in the CREATE RULE documentation, as well as mention in the
> FAQ.

Yeah.  I have also thought about reorganizing the docs so that triggers
are presented as being simpler than rules (come first, etc).  I think
right now the docs actively mislead newbies into choosing rules in cases
where triggers would be much better.

            regards, tom lane

Dear Tom,

>> This comes up often enough that maybe it warrants a "Caveats with
>> Rules" section in "The Rule System" chapter and a link to that section
>> in the CREATE RULE documentation, as well as mention in the FAQ.
>
> Yeah.  I have also thought about reorganizing the docs so that triggers
> are presented as being simpler than rules (come first, etc).  I think
> right now the docs actively mislead newbies into choosing rules in cases
> where triggers would be much better.

ISTM that having SQL as a language for trivial triggers would also help.
RULEs are SQL, although triggers must be C or PL*.

That could also be a candidate TODO, next to "improve the doc"?

Thanks for your answer,

--
Fabien Coelho - coelho@cri.ensmp.fr

Dear Michael,

> This comes up often and the response is usually something like,
> "Rules are macros, which is why referring to NEW.id causes another
> evaluation of nextval().  If you don't want that to happen then use
> a trigger."

That is indeed what I gathered;-)

It might be sensible to avoid multiple evaluations for the new.*
attributes when possible. I can foresee that it makes the implementation
less straightforward wrt to a pure tree-rewriting stuff.

Also, there is no such thing as a SQL trigger, so one needs to install
plpgsql to develop a simple trigger.

> This comes up often enough that maybe it warrants a "Caveats with
> Rules" section in "The Rule System" chapter

There is neither such section nor subsection in chapter "the rule system".
Maybe a sub-sub-section somewhere?

Table of Contents
32.1. The Query Tree
32.2. Views and the Rule System
     32.2.1. How SELECT Rules Work
     32.2.2. View Rules in Non-SELECT Statements
     32.2.3. The Power of Views in PostgreSQL
     32.2.4. Updating a View
32.3. Rules on INSERT, UPDATE, and DELETE
     32.3.1. How Update Rules Work
     32.3.2. Cooperation with Views
32.4. Rules and Privileges
32.5. Rules and Command Status
32.6. Rules versus Triggers

> and a link to that section in the CREATE RULE documentation, as well as
> mention in the FAQ.

I must admit that I did not look at the FAQ.

The general tone of the rule documentation is "look how great and powerful
it is", and somehow it is nearly as great as promised, but nearly only.

Well, thanks for your comment,

--
Fabien.