According to the documentation at Rules and Command Status
<http://www.postgresql.org/docs/8.2/static/rules-status.html> when
creating rules, the resulting command status from the rules executed is
dependent upon the rules that were added.
According to the documentation:=20
"If there is any unconditional INSTEAD rule for the query, then the
original query will not be executed at all. In this case, the server
will return the command status for the last query that was inserted by
an INSTEAD rule (conditional or unconditional) and is of the same
command type (INSERT, UPDATE, or DELETE) as the original query. If no
query meeting those requirements is added=20
by any rule, then the returned command status shows the original query
type and zeroes for the row-count and OID fields."
=20
My understanding (and that of at least several others) indicates that
only those conditional rules that are true should be added to the query
plan. So if there are 3 rules, and 2 of them are false and only one is
true, then the rule that met the "WHERE" criteria should be the only one
added to the query plan. In the case described in the documentation, if
there are one or more unconditional rules, then the result of the query
will be the result of the last rule that was added to the query plan.
Therefore if there is an unconditional rule, and several conditional
rules, of which only one of those whose where clause is true, then the
result of the query should be that of the single conditional query that
should have been executed (taking into account that it must be
alphabetically after the unconditional rule).
=20
The behavior I am seeing is not following this type of activity,
consider the following case:
test=3D# create table foo (x integer);
CREATE TABLE
test=3D# create table foo1 (x integer);
CREATE TABLE
test=3D# create table foo2 (x integer);
CREATE TABLE
test=3D# create rule foo_0 as on insert to foo do instead nothing;
CREATE RULE
test=3D# create rule foo_1 as on insert to foo where NEW.x =3D 1 do instead
insert into foo1 (x) values (NEW.x);
CREATE RULE
test=3D# create rule foo_2 as on insert to foo where NEW.x =3D 2 do instead
insert into foo2 (x) values (NEW.x);
CREATE RULE
test=3D# insert into foo (x) values (0);
INSERT 0 0
test=3D# insert into foo (x) values (1);
INSERT 0 0
test=3D# insert into foo (x) values (2);
INSERT 0 1
test=3D# insert into foo (x) values (3);
INSERT 0 0
=20
The line highlighted in red (annotated with >>) indicates a return of
"INSERT 0 0" where it should have returned "INSERT 0 1" due to the fact
that only one rule should have been added to the query plan. However,
the rule foo_2 was added to the query plan and inserted no rows and
therefore the result was insert 0 0.
=20
Refer to this postgresql forums thread
<http://www.postgresqlforums.com/forums/viewtopic.php?f=3D42&t=3D606> for
more discussion on this topic.
=20
Shawn Chasse
ExaGrid Systems, Inc.
2000 West Park Drive
Westborough, MA 01581
Office: 508-898-2872 Ext 332
schasse@exagrid.com <mailto:schasse@exagrid.com>=20
www.exagrid.com <http://www.exagrid.com/>=20
=20