Skip site navigation (1) Skip section navigation (2)

BUG #2563: In rules: recalculation of input expression on each access

From: "ivankob" <ivankob(at)front(dot)ru>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2563: In rules: recalculation of input expression on each access
Date: 2006-08-04 07:12:53
Message-ID: 200608040712.k747Cr2I067915@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      2563
Logged by:          ivankob
Email address:      ivankob(at)front(dot)ru
PostgreSQL version: 8.1.4
Operating system:   Linux
Description:        In rules: recalculation of input expression on each 
access
Details: 

For a testcase like :

CREATE SEQUENCE seq1;

CREATE TABLE t1 (
 id INTEGER DEFAULT NEXTVAL('"seq1"'::text),
 code INTEGER,
 data TEXT
);

CREATE TABLE s1 (
 id INTEGER,
 code INTEGER,
 state BOOLEAN
);

INSERT INTO t1 (code,data) VALUES (100,'Green elaphant');
INSERT INTO s1 VALUES (currval('"seq1"'::text),100,'t');

INSERT INTO t1 (code,data) VALUES (101,'Mad tortoise');
INSERT INTO s1 VALUES (currval('"seq1"'::text),101,'f');

-----------
CREATE VIEW v1 AS 
SELECT a.code, a.data FROM t1 a, s1 b WHERE a.id=b.id AND a.code=b.code AND
b.state = 't';
------------
CREATE RULE rule1 ON INSERT TO v1 DO INSTEAD (
  INSERT INTO t1 ( id, code, data) VALUES ( nextval('"seq1"'::text),
NEW.code, NEW.data );
  INSERT INTO s1 ( id, code, state ) VALUES ( currval('"seq1"'::text),
NEW.code, 't' );
);
------------

INSERT INTO v1 (code,data) VALUES (
 (SELECT MAX(code) FROM t1)+1,
 'Penguin-sprinter'
);

after run, it seems that "(SELECT MAX(code) FROM t1)+1" is performed twice
(
on each NEW.code ) in the rule since "s1.code" results in greater than
"t1.code" by "1" what's wrong. 
Access by reference not value ?

pgsql-bugs by date

Next:From: Nikolay SamokhvalovDate: 2006-08-04 09:50:42
Subject: Fwd: Strange behaviour of RULE (selecting last inserted ID of 'sequenced' column)
Previous:From: Joe ConwayDate: 2006-08-03 23:00:55
Subject: Re: [BUGS] Patch to allow C extension modules to initialize/finish

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group