rule system re-evaluates expressions

From: jan pfaler <jan_pfaler(at)yahoo(dot)se>
To: pgsql-bugs(at)postgresql(dot)org
Subject: rule system re-evaluates expressions
Date: 2005-01-02 13:57:26
Message-ID: 20050102135726.80124.qmail@web25110.mail.ukl.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The rule-system seems to evaluate a functional value
of a column anew. Please confim if this is a
documented/intentional behaviour of the rule system?

The order of evaluation of the expresion(s) within
original statemant and rule differ between "on insert"
and "on update" rules.

For details see the following artificial example
logging actions on "tbl" to "log" and using nextval as
an expression (code and output below). As a result the
columns tbl.id and log.id differ, unexpectedly, by
+-1.

regards, Jan

=== SQL ================================

-- EXAMPLE: rule-system revaluates
-- the functionale expressions

CREATE sequence sq ;
CREATE table tbl (
id integer default nextval('sq'),
da char(1) default '-',
ts timestamp default now() );
-- log=tbl without defaults
CREATE table log (
id integer,
da char(1),
ts timestamp );

-- rules for logging actions on "tbl" to "log"
CREATE rule tblog1 AS ON INSERT TO tbl
do insert INTO log (id,da,ts)
values (NEW.id, NEW.da, NEW.ts);
CREATE rule tblog2 AS ON UPDATE TO tbl
do insert INTO log (id,da,ts)
values (NEW.id, NEW.da, NEW.ts);

-- inserts/updates using defaul values
-- using default id :
insert into tbl (da) values ('a');
-- using default id :
insert into tbl (da) values ('b');
-- using explicit expression for id :
insert into tbl (id,da) values (nextval('sq'),'c');
-- using explicit fixed id :
insert into tbl (id,da) values (0,'d');
-- using an expressional but explicit id
update tbl set id=nextval('sq') where id=0;

-- As a result the columns tbl.id and log.id
-- differ by one :
select * from tbl;
select * from log;

-- drop
drop table tbl cascade;
drop table log cascade;
drop sequence sq;

==== OUTPUT ============================

=# select * from tbl;
id | da | ts
----+----+----------------------------
1 | a | 2004-12-14 11:15:21.996594
3 | b | 2004-12-14 11:15:22.002465
5 | c | 2004-12-14 11:15:22.00822
8 | d | 2004-12-14 11:15:22.012329
(4 rows)

=# select * from log;
id | da | ts
----+----+----------------------------
2 | a | 2004-12-14 11:15:21.996594
4 | b | 2004-12-14 11:15:22.002465
6 | c | 2004-12-14 11:15:22.00822
0 | d | 2004-12-14 11:15:22.012329
7 | d | 2004-12-14 11:15:22.012329
(5 rows)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David Bowen 2005-01-03 03:11:05 BUG #1370: Problem with arrays in PL/PGSQL
Previous Message Magnus Hagander 2005-01-02 13:31:19 Re: BUG #1369: RC3 Compilation fails on mingw