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