Thread: Two rules on a view do not like each other :-(
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
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.
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.
Stephan Szabo wrote: >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. > > Yeah... I know. This was just a test example. In real life, I have two different delete rules with different conditions (mutually exclusive), and one unconditional rule, that actually inserts the new row... Thanks for your help with that ordering problem though! I changed the name of my rules and it now works just fine. Do you guys have any plans to change it again in the future? Also, have anything similar been done in 7.3 regarding the order in which *triggers* are executed. I know that in 7.2.4 the order was oficcially undefined, but they were actually getting executed in the reversed creation order (the trigger that was created more recently would get executed first). Has that changed in 7.3 as well? Thanks again for your help! Dima
Dima Tkach <dmitry@openratings.com> writes:
> Also, have anything similar been done in 7.3 regarding the order in
> which *triggers* are executed.
Yes --- by name.
regards, tom lane
Tom Lane wrote: > Dima Tkach <dmitry@openratings.com> writes: > >>Also, have anything similar been done in 7.3 regarding the order in >>which *triggers* are executed. > > > Yes --- by name. Why not implement in SQL standard way ? I'm against this alphabetic order firing. I's not safe develop a new trigger and completely change the trigger firing order. Suppose that I want multiply for 2 a field of a table for each insert. What happen if that field is manipulated already by another trigger calculating let me say: sqrt ? Before my new trigger: Only first trigger: insert a -> insert sqrt(a) and I want: first + second trigger: insert a -> insert sqrt(a) -> insert 2*sqrt(a) if my trigger name is aaaaaaa insert a -> insert 2*a -> insert sqrt( 2*a ) What shall I do to be safe ? Name my trigger zzzzzzz ? I think is more natural that my last trigger developped is the last to be fired. Regards Gaetano Mendola
Gaetano Mendola <mendola@bigfoot.com> writes:
> Tom Lane wrote:
>> Yes --- by name.
> Why not implement in SQL standard way ?
> I'm against this alphabetic order firing.
You think order-of-creation has something to recommend it? I don't see
what. It just makes it extremely painful to control the firing order
when you need to --- you end up dropping and recreating all the
triggers, which is a tedious and error-prone approach.
> I's not safe develop a new trigger and completely change
> the trigger firing order. Suppose that I want multiply for
> 2 a field of a table for each insert. What happen if that field
> is manipulated already by another trigger calculating let me say:
> sqrt ?
You can equally easily run into similar problems with creation-order
firing as well. There's no substitute for actually thinking about the
interactions of multiple triggers on the same event...
regards, tom lane
Tom Lane wrote: > Gaetano Mendola <mendola@bigfoot.com> writes: > >>Tom Lane wrote: >> >>>Yes --- by name. > > >>Why not implement in SQL standard way ? >>I'm against this alphabetic order firing. > > > You think order-of-creation has something to recommend it? I don't see > what. It just makes it extremely painful to control the firing order > when you need to --- you end up dropping and recreating all the > triggers, which is a tedious and error-prone approach. > > >>I's not safe develop a new trigger and completely change >>the trigger firing order. Suppose that I want multiply for >>2 a field of a table for each insert. What happen if that field >>is manipulated already by another trigger calculating let me say: >>sqrt ? > > > You can equally easily run into similar problems with creation-order > firing as well. There's no substitute for actually thinking about the > interactions of multiple triggers on the same event... Agreed but why don't follow the standard ? Regards Gaetano Mendola