Two rules on a view do not like each other :-(

[Dmitry Tkach]

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
---------------------------(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

[Stephan Szabo]

On Wed, 19 Nov 2003, Dmitry Tkach wrote:

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!

Actually, I believe it's happily inserting the row, and then deleting it
again. The order of application appears to be based on the name (I'm not
sure it was defined prior to that). Changing the name of the delete rule
to sort lower than the inserting rule appears to give the effect that the
old rows are deleted and then a new row is inserted.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Stephan Szabo]

On Wed, 19 Nov 2003, Dmitry Tkach wrote:

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);

As a side note, you might just want to write one rule with two actions to
do this because that gets you whatever ordering you want by ordering the
actions.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings