Thread: BUG #2102: Backend reports wrong number of affected rows for a table that uses rules
BUG #2102: Backend reports wrong number of affected rows for a table that uses rules
From
"Brent Reid"
Date:
The following bug has been logged online: Bug reference: 2102 Logged by: Brent Reid Email address: bfraci@aol.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-jdbc/2005-12/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=#
Re: BUG #2102: Backend reports wrong number of affected rows for a table that uses rules
From
Tom Lane
Date:
"Brent Reid" <bfraci@aol.com> writes: > 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. Have you read http://www.postgresql.org/docs/8.0/static/rules-status.html regards, tom lane
Thanks for your response. Let me see if I understand this correctly. If the original query is an update and the instead rule replaces it with an update,then I should get a return status that is not zeros. And if the original query was an update and the instead rulereplaced it with something other than an update, then I should expect the return status to be zeros. In my case we have an unconditional instead rule. The original query was an update and the instead rule replaced it withan update of another table; an update was replaced with an update. Then I should expect to see the status of the lastquery that was inserted by the instead rule. So I should not see a status of zero. Is that correct? Thanks for all your help, Brent -----Original Message----- From: Tom Lane <tgl@sss.pgh.pa.us> To: Brent Reid <bfraci@aol.com> Cc: pgsql-bugs@postgresql.org Sent: Fri, 09 Dec 2005 11:41:26 -0500 Subject: Re: [BUGS] BUG #2102: Backend reports wrong number of affected rows for a table that uses rules "Brent Reid" <bfraci@aol.com> writes: > 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. Have you read http://www.postgresql.org/docs/8.0/static/rules-status.html regards, tom lane
bfraci@aol.com writes:
> In my case we have an unconditional instead rule. The original query was an update and the instead rule replaced it
withan update of another table; an update was replaced with an update. Then I should expect to see the status of the
lastquery that was inserted by the instead rule. So I should not see a status of zero.
Well, you'll see the status of the last UPDATE executed due to a rule
... but that doesn't mean it couldn't have updated zero rows. It might
be worth pointing out here that conditional rules insert queries that
have the condition added to their WHERE clause; if the condition is
false then no rows are going to get processed.
regards, tom lane