rule system re-evaluates expressions

Поиск
Список
Период
Сортировка
От jan pfaler
Тема rule system re-evaluates expressions
Дата
Msg-id 20050102135726.80124.qmail@web25110.mail.ukl.yahoo.com
обсуждение исходный текст
Ответы Re: rule system re-evaluates expressions  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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)

В списке pgsql-bugs по дате отправления:

Предыдущее
От: "Michiel Tas"
Дата:
Сообщение: BUG #1368: initdb fails
Следующее
От: "Elvis E. Henriquez A."
Дата:
Сообщение: Wrong messages running on Windows - Part 2