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
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
