Hi, everybody!
Here is a weird problem I ran into with 7.3.4.
This is the complete test case:
rapidb=# select version ();
version
-------------------------------------------------------------
PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
rapidb=# create table test (x int primary key, y int);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'test_pkey' for table 'test'
CREATE TABLE
rapidb=# create view test_proxy as select * from test;
CREATE VIEW
rapidb=# create rule new_test_proxy as on insert to test_proxy do delete
from test where x=new.x;
CREATE RULE
rapidb=# create rule new_test as on insert to test_proxy do instead
insert into test values (new.x, new.y);
CREATE RULE
rapidb=# insert into test_proxy values (1,1);
INSERT 663399483 1
rapidb=# select * from test;
x | y
---+---
(0 rows)
I create a table "test", and a view "test_proxy", then it create two on
insert rules on test proxy - first rule deletes the row with the same
PK as the one being inserted from test (so that I don't need to check
for it before hand if I want to replace the row), the second - INSTEAD
rule just does the insert on the actual table.
The problem is that the new row seems to NEVER get inserted - the last
two commands try to insert a row into test_proxy, and then look at it -
the table is empty!
This used to work in 7.2:
rapidb=# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.2.4 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
rapidb=# create table test (x int, y int);
CREATE
rapidb=# create view test_proxy as select * from test;
CREATE
rapidb=# create rule new_test as on insert to test_proxy do instead
insert into test values (new.x, new.y);
CREATE
rapidb=# create rule new_test_proxy as on insert to test_proxy do delete
from test where x=new.x;
CREATE
rapidb=# insert into test_proxy values (1,1);
INSERT 0 0
rapidb=# select * from test;
x | y
---+---
1 | 1
(1 row)
Does anyone have any idea what is going on here?
I suspect, my problem is that the rules get executed in the wrong order
- so that a row gets inserted first, and then deleted right away...
Is that right?
If so, was this change from 7.2.4 done intentionally, or is it a bug?
If the former, is there any way (a config option or something) to get
the old behaviour back?
Thanks a lot for your help!
Dima
