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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"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/s...es-status.html

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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/s...es-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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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/s...es-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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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/s...es-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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster