The following bug has been logged online:

Bug reference: 2102
Logged by: Brent Reid
Email address: bfraci (AT) aol (DOT) com
PostgreSQL version: 8.0.2
Operating system: Linux dl380 2.6.14.2 #2 SMP Wed Nov 16 09:51:56 MST 2005
i686 i686 i386 GNU/Linux
Description: Backend reports wrong number of affected rows for a
table that uses rules
Details:

Our Java application depends upon the return values from the various JDBC
insert, update, and delete routines. We noticed that the value is always
zero when the table that is referenced has rules associated with it. In
particular, we do an update and if the return value is zero, we then insert
the row, generally the row already exists. We are getting duplicate key
errors on the insert. See this link
http://archives.postgresql.org/pgsql...2/msg00020.php

The following demonstrates the issue:

c60=# begin work;
BEGIN
c60=# create table foo( col1 int primary key, col2 text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey"
for table "foo"
CREATE TABLE
c60=# CREATE TABLE foo1 () INHERITS (foo) WITHOUT OIDS;
CREATE TABLE
c60=# ALTER TABLE foo1 ADD PRIMARY KEY (col1);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"foo1_pkey" for table "foo1"
ALTER TABLE
c60=# CREATE TABLE foo2 () INHERITS (foo) WITHOUT OIDS;
CREATE TABLE
c60=# ALTER TABLE foo2 ADD PRIMARY KEY (col1);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"foo2_pkey" for table "foo2"
ALTER TABLE
c60=#-- Perform DML on foo without any rules
c60=# insert into foo values ( 1, 'abc');
INSERT 24731 1
-- The value returned from the insert is the number of rows inserted
c60=# select * from foo;
col1 | col2
------+------
1 | abc
(1 row)

c60=# update foo set col2 = 'xyz' where col1 = 1;
UPDATE 1
-- Notice the 1 following UPDATE above
c60=# select * from foo;
col1 | col2
------+------
1 | xyz
(1 row)

c60=# delete from foo where col1 = 1;
DELETE 1
-- Notice the 1 following DELETE above
c60=# select * from foo;
col1 | col2
------+------
(0 rows)

c60=#-- Now add some rules
c60=# CREATE OR REPLACE RULE insrule1 AS ON INSERT TO foo WHERE
((new.col1::bigint & 4096::bigint) = 0) DO INSTEAD INSERT INTO foo1
VALUES(new.*);
CREATE RULE
c60=# CREATE OR REPLACE RULE insrule2 AS ON INSERT TO foo WHERE
((new.col1::bigint & 4096::bigint) = 4096) DO INSTEAD INSERT INTO foo2
VALUES(new.*);
CREATE RULE
c60=# CREATE OR REPLACE RULE updrule1 AS ON UPDATE TO foo WHERE
((old.col1::bigint & 4096::bigint) = 0) DO INSTEAD UPDATE foo1 SET col1 =
new.col1, col2 = new.col2 WHERE col1 = old.col1;
CREATE RULE
c60=# CREATE OR REPLACE RULE updrule2 AS ON UPDATE TO foo WHERE
((old.col1::bigint & 4096::bigint) = 4096) DO INSTEAD UPDATE foo2 SET col1 =
new.col1, col2 = new.col2 WHERE col1 = old.col1;
CREATE RULE
c60=# CREATE OR REPLACE RULE delrule1 AS ON DELETE TO foo WHERE
((old.col1::bigint & 4096::bigint) = 0) DO INSTEAD DELETE FROM foo1 WHERE
col1 = old.col1;
CREATE RULE
c60=# CREATE OR REPLACE RULE delrule2 AS ON DELETE TO foo WHERE
((old.col1::bigint & 4096::bigint) = 4096) DO INSTEAD DELETE FROM foo2 WHERE
col1 = old.col1;
CREATE RULE
c60=#
c60=# insert into foo values ( 1, 'abc');
INSERT 0 0
-- Notice the zeros
c60=# select * from foo;
col1 | col2
------+------
1 | abc
(1 row)

c60=# update foo set col2 = 'xyz' where col1 = 1;
UPDATE 0
-- Notice the zeros
c60=# select * from foo;
col1 | col2
------+------
1 | xyz
(1 row)

c60=# delete from foo where col1 = 1;
DELETE 0
-- Notice the zeros
c60=# select * from foo;
col1 | col2
------+------
(0 rows)

c60=#-- Now lets try one of the inherited tables
c60=# insert into foo1 values ( 1, 'abc');
INSERT 24739 1
-- Notice the lack of zeros
c60=# select * from foo1;
col1 | col2
------+------
1 | abc
(1 row)

c60=# update foo1 set col2 = 'xyz' where col1 = 1;
UPDATE 1
-- Notice the lack of zeros
c60=# select * from foo1;
col1 | col2
------+------
1 | xyz
(1 row)

c60=# delete from foo1 where col1 = 1;
DELETE 1
-- Notice the lack of zeros
c60=# select * from foo1;
col1 | col2
------+------
(0 rows)

c60=#
c60=# rollback work;
ROLLBACK
c60=#
c60=#

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

"Brent Reid" <bfraci (AT) aol (DOT) com> writes:
Have you read
http://www.postgresql.org/docs/8.0/s...es-status.html

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings