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

From: Dmitry Tkach <dmitry(at)openratings(dot)com>
To: pg_general <pgsql-general(at)postgresql(dot)org>
Subject: Two rules on a view do not like each other :-(
Date: 2003-11-20 00:49:07
Message-ID: 3FBC0F83.50300@openratings.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Eisentraut 2003-11-20 00:59:27 Re: Pg_dumpall problem
Previous Message Patrick Hatcher 2003-11-20 00:34:50 Pg_dumpall problem