This message is in MIME format. The first part should be readable text,
while the remaining parts are likely unreadable without MIME-aware tools.

--34246414-891624080-1100078483=:4884
Content-Type: TEXT/PLAIN; CHARSET=US-ASCII; FORMAT=flowed
Content-ID: <Pine.LNX.4.61.0411101021251.4884 (AT) sablons (DOT) cri.ensmp.fr>


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.
--34246414-891624080-1100078483=:4884
Content-Type: TEXT/PLAIN; CHARSET=US-ASCII; NAME="multi_rule_eval.sql"
Content-Transfer-Encoding: BASE64
Content-ID: <Pine.LNX.4.61.0411101021230.4884 (AT) sablons (DOT) cri.ensmp.fr>
Content-Description:
Content-Disposition: ATTACHMENT; FILENAME="multi_rule_eval.sql"

RFJPUCBUQUJMRSB0Ow0KDQpDUkVBVEUgVEFCTEUgdA0KKCBpZC BTRVJJQUwg
UFJJTUFSWSBLRVksDQogIGRhdGEgVEVYVCBOT1QgTlVMTCwNCi AgaXNMYXN0
IEJPT0xFQU4gTk9UIE5VTEwgREVGQVVMVCBUUlVFDQopOw0KDQ pDUkVBVEUg
UlVMRSB0X3VwZGF0ZSBBUw0KT04gSU5TRVJUIFRPIHQgRE8gQU xTTw0KVVBE
QVRFIHQgU0VUIGlzTGFzdD1GQUxTRSANCldIRVJFIGlkPG5ldy 5pZDsNCg0K
SU5TRVJUIElOVE8gdChkYXRhKSBWQUxVRVMoJ29uZScpOw0KU0 VMRUNUIGxh
c3RfdmFsdWUgRlJPTSB0X2lkX3NlcTsNCg0KSU5TRVJUIElOVE 8gdChkYXRh
KSBWQUxVRVMoJ3R3bycpOw0KU0VMRUNUIGxhc3RfdmFsdWUgRl JPTSB0X2lk
X3NlcTsNCg0KSU5TRVJUIElOVE8gdChkYXRhKSBWQUxVRVMoJ3 RocmVlJyk7
DQpTRUxFQ1QgbGFzdF92YWx1ZSBGUk9NIHRfaWRfc2VxOw0KDQ pJTlNFUlQg
SU5UTyB0KGRhdGEpIFZBTFVFUygnZm91cicpOw0KU0VMRUNUIG xhc3RfdmFs
dWUgRlJPTSB0X2lkX3NlcTsNCg0KU0VMRUNUICogRlJPTSB0Ow 0K

--34246414-891624080-1100078483=:4884
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

--34246414-891624080-1100078483=:4884--

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 (AT) cri (DOT) ensmp.fr

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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

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

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/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Michael Fuhr <mike (AT) fuhr (DOT) org> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Dear Tom,

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 (AT) cri (DOT) ensmp.fr

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Dear Michael,

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.

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

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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend