From: | Marc Boucher <marcb(at)box100(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Problem with rule and null value |
Date: | 2004-10-22 12:01:42 |
Message-ID: | 3.0.5.32.20041022140142.008ef210@mymail |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Jorge Godoy | 2004-10-22 12:04:53 | Re: Instalation ! |
Previous Message | Davide Negri | 2004-10-22 06:59:56 | Question on the 8.0Beta Version |