[BUGS] BUG #2671: incorrect return value by RULE
The following bug has been logged online: Bug reference: 2671 Logged by: Toru SHIMOGAKI Email address: [EMAIL PROTECTED] 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[EMAIL PROTECTED] NTT Open Source Software Center ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2671: incorrect return value by RULE
Toru SHIMOGAKI [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #2671: incorrect return value by RULE
On Tue, 2006-10-03 at 11:04 -0400, Tom Lane wrote: Toru SHIMOGAKI [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2671: incorrect return value by RULE
Simon Riggs [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #2671: incorrect return value by RULE
On Tue, 2006-10-03 at 13:56 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] drop view stalled during pg_dump
Hi guys, I just tried to do a drop view and it sat there for ages. I looked to see what else was going on and the only other thing of note was a COPY process being run as part of my daily backups (pg_dump). Why does this block drop view? It seems as though it shouldn't. Cheers, -- SeattleServer.com Mailing Lists - Casey Allen Shobe [EMAIL PROTECTED] - http://seattleserver.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
