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

From: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
To: PostgreSQL Bugs List <pgsql-bugs(at)postgresql(dot)org>
Subject: "strange" rule behavior with nextval on new.* fields
Date: 2004-11-10 09:23:41
Message-ID: Pine.LNX.4.61.0411101005280.4884@sablons.cri.ensmp.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


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.

Attachment Content-Type Size
multi_rule_eval.sql text/plain 534 bytes

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andrew McMillan 2004-11-10 10:30:32 Re: ALTER USER SET log_* not allowed...
Previous Message Kristian Jörg 2004-11-10 09:22:27 PostgresQL 8 beta 4 bugs