Thread: BUG #2671: incorrect return value by RULE
The following bug has been logged online:
Bug reference: 2671
Logged by: Toru SHIMOGAKI
Email address: shimogaki.toru@oss.ntt.co.jp
PostgreSQL version: 8.1.4/8.2beta1
Operating system: Red Hat Enterprise Linux AS4
Description: incorrect return value by RULE
Details:
Hi, all;
It seems a bug that incorrect return value is displayed if RULE is applied
(RULE is always used when users use table partitioning). This is undesirable
for some users and applications that want to check return value.
The following is the procedure:
=====================================================================
postgres=# \d test_p;
Table "public.test_p"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
Rules:
rule_1 AS
ON INSERT TO test_p
WHERE new.a >= 0 DO INSTEAD INSERT INTO test_c1 (a)
VALUES (new.a)
rule_2 AS
ON INSERT TO test_p
WHERE new.a < 0 DO INSTEAD INSERT INTO test_c2 (a)
VALUES (new.a)
postgres=# \d test_c1;
Table "public.test_c1"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
Inherits: test_p
postgres=# \d test_c2;
Table "public.test_c2"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
Inherits: test_p
postgres=# INSERT INTO test_p VALUES (1);
INSERT 0 0
^^^ The expected result is "INSERT 0 1"
=====================================================================
At least, this behavior is different from the following discription of
INSERT manual;
=====================================================================
...
Outputs
On successful completion, an INSERT command returns a command tag of the
form
INSERT oid count
The count is the number of rows inserted. If count is exactly one, and the
target table has OIDs, then oid is the OID assigned to the inserted row.
Otherwise oid is zero.
...
=====================================================================
We need some specifications to solve this problem. I think that to fix it
seems not so easy, because RULE has DO ALSO/DO INSTEAD and we have to
consider them for a query multiple RULES are applied.
Are there any good ideas to avoid or fix it?
Best regards,
--
Toru SHIMOGAKI<shimogaki.toru@oss.ntt.co.jp>
NTT Open Source Software Center
"Toru SHIMOGAKI" <shimogaki.toru@oss.ntt.co.jp> writes: > It seems a bug that incorrect return value is displayed if RULE is applied > (RULE is always used when users use table partitioning). This is undesirable > for some users and applications that want to check return value. This is the long-ago-agreed-to behavior, see http://www.postgresql.org/docs/8.1/static/rules-status.html regards, tom lane
On Tue, 2006-10-03 at 11:04 -0400, Tom Lane wrote: > "Toru SHIMOGAKI" <shimogaki.toru@oss.ntt.co.jp> writes: > > It seems a bug that incorrect return value is displayed if RULE is applied > > (RULE is always used when users use table partitioning). This is undesirable > > for some users and applications that want to check return value. > > This is the long-ago-agreed-to behavior, see > > http://www.postgresql.org/docs/8.1/static/rules-status.html > Understood this is not-a-bug, but it is an opportunity for the TODO. IMHO when we have a set of mutually exclusive conditional RULEs that it would be possible to identify the correct return value and display it. Or another way would be to have a CASE statement instead of a WHERE clause to allow one of many outcomes to occur. I'm not that excited about the usefulness of using RULEs with partitioning, as you know, but it does seem as if everybody else *is*. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs <simon@2ndquadrant.com> writes: > On Tue, 2006-10-03 at 11:04 -0400, Tom Lane wrote: >> This is the long-ago-agreed-to behavior, see >> http://www.postgresql.org/docs/8.1/static/rules-status.html > Understood this is not-a-bug, but it is an opportunity for the TODO. > IMHO when we have a set of mutually exclusive conditional RULEs that it > would be possible to identify the correct return value and display it. What makes you think there is a single "correct" return value? If multiple rows are being inserted/updated it's entirely possible that some of them will be in different child partitions. If we were interested in changing the status behavior, I'd be inclined to think about something like adding up the rowcounts from all the replacement queries that're of the same type as the original. However, I have some recollection that this was proposed and shot down in the discussions that led to the current solution --- as a counterexample consider an ON INSERT DO ALSO that inserts rows into a logging table. This should be hidden from the user but would not be if we added its effects to the result tag. regards, tom lane
On Tue, 2006-10-03 at 13:56 -0400, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.com> writes: > > On Tue, 2006-10-03 at 11:04 -0400, Tom Lane wrote: > >> This is the long-ago-agreed-to behavior, see > >> http://www.postgresql.org/docs/8.1/static/rules-status.html > > > Understood this is not-a-bug, but it is an opportunity for the TODO. > > > IMHO when we have a set of mutually exclusive conditional RULEs that it > > would be possible to identify the correct return value and display it. > > What makes you think there is a single "correct" return value? If > multiple rows are being inserted/updated it's entirely possible that > some of them will be in different child partitions. > > If we were interested in changing the status behavior, I'd be inclined > to think about something like adding up the rowcounts from all the > replacement queries that're of the same type as the original. However, > I have some recollection that this was proposed and shot down in the > discussions that led to the current solution --- as a counterexample > consider an ON INSERT DO ALSO that inserts rows into a logging table. > This should be hidden from the user but would not be if we added its > effects to the result tag. Good point, and you make me feel better about ignoring that since. Those thoughts inch forward the idea that partitions aren't quite on the same level as full tables, even if they have many similarities. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com