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.
