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

Problem with rule and null value

From: Marc Boucher <pgml(at)gmx(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Problem with rule and null value
Date: 2004-10-22 22:19:02
Message-ID: 3.0.5.32.20041023001902.0090c7e0@mymail (view raw or flat)
Thread:
Lists: pgsql-bugs
This is a resent. Wrong email account the first time.

---

I'm using PostgreSQL 7.3.4.

I've set up a series of rules and triggers to complete an intermediate
table with similar informations from another table. This ensures better
performance over a solution with views.

Intermediate table:
   Table album_edit
   Field      Type     Length   Not Null    Default
   alb_id     int4        4       Yes
   ed_ref     varchar    30       Yes
   isbn       varchar    30       No
   flags      int2        2       No
   pls_id     int4        4       No

A set of rules is added to another table, "album". 
The general principle of the relation between "album" and "album_edit", is
that each record of "album" is duplicated in "album_edit" (and these record
are uniquely identified in "album_edit" with the use of column "ed_ref").
So insert, update and delete on "album" are passed to "album_edit".

One of the update rules is this one:

CREATE OR REPLACE RULE albed_updalb3_rl AS 
   ON UPDATE TO album WHERE new.pls_id != old.pls_id
  DO
   UPDATE album_edit SET pls_id=new.pls_id WHERE alb_id=new.id;

It works until new.pls_id or old.pls_id is null. The rule is still called
(based on my test), but the "DO" query is not executed correctly. The
values in "album_edit" are not updated.

To find the source of the problem I've modified the rule:

CREATE OR REPLACE RULE albed_updalb3_rl AS
   ON UPDATE TO album WHERE new.pls_id != old.pls_id
  DO
   select old.pls_id, new.pls_id;


Is this a bug or an intended behavior ?


Here is a log with some tests.

----------
bd=# CREATE OR REPLACE RULE albed_updalb3_rl AS ON UPDATE TO album WHERE
new.pls_id != old.pls_id DO select old.pls_id, new.pls_id;
CREATE RULE
bd=# update album set pls_id='666' where id='8838';
                                                  pls_id | pls_id 
--------+--------
    100 |    666
(1 row)

bd=# update album set pls_id=null where id='8838';     
 pls_id | pls_id 
--------+--------
(0 rows)

bd=# update album set pls_id='666' where id='8838';
 pls_id | pls_id 
--------+--------
(0 rows)

bd=# update album set pls_id='111' where id='8838';   
 pls_id | pls_id 
--------+--------
    666 |    111
(1 row)

bd=# update album set pls_id='0' where id='8838';   
 pls_id | pls_id 
--------+--------
    111 |      0
(1 row)

bd=# update album set pls_id='111' where id='8838';
 pls_id | pls_id 
--------+--------
      0 |    111
(1 row)

bd=# update album set pls_id=null where id='8838';     
 pls_id | pls_id 
--------+--------
(0 rows)



Responses

pgsql-bugs by date

Next:From: Kris JurkaDate: 2004-10-22 22:48:51
Subject: Re: AT TIME ZONE INTERVAL and prepared statements
Previous:From: Tom LaneDate: 2004-10-22 22:16:04
Subject: Re: AT TIME ZONE INTERVAL and prepared statements

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