From: | "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com> |
---|---|
To: | "Holger Klawitter" <info(at)klawitter(dot)de> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #3945: unexpected ON INSERT rule behaviour |
Date: | 2008-02-10 06:13:47 |
Message-ID: | 65937bea0802092213g62038a2dxe47abdbe8b792b74@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Feb 8, 2008 2:20 PM, Holger Klawitter <info(at)klawitter(dot)de> wrote:
>
> The following bug has been logged online:
>
> Bug reference: 3945
> Logged by: Holger Klawitter
> Email address: info(at)klawitter(dot)de
> PostgreSQL version: 8.2.6
> Operating system: Linux/i386
> Description: unexpected ON INSERT rule behaviour
> Details:
>
> Well,
> this is probably not really a bug, more a feature
> deeply buried in the query-tree-concept worth placed as a pitfall warning
> in
> the documentation :-)
>
You are correct, it's not a bug, but a feature request that won't be
entertained.
postgres=# explain INSERT INTO a VALUES ( 1 );
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
Result (cost=0.00..0.01 rows=1 width=0)
(3 rows)
postgres=# explain INSERT INTO a VALUES ((SELECT max(a)+1 from a));
QUERY PLAN
-------------------------------------------------------------------
Result (cost=40.01..40.02 rows=1 width=0)
InitPlan
-> Aggregate (cost=40.00..40.01 rows=1 width=4)
-> Seq Scan on a (cost=0.00..34.00 rows=2400 width=4)
Result (cost=40.01..40.02 rows=1 width=0)
InitPlan
-> Aggregate (cost=40.00..40.01 rows=1 width=4)
-> Seq Scan on a (cost=0.00..34.00 rows=2400 width=4)
(9 rows)
The docs are pretty clear on this; Rules are applied at parse time (' The
rule system is located between the parser and the planner'), and hence can
only work with hard-coded values in the VALUES clause.
Personally, although much slower, I prefer using triggers if the logic
depends on NEW/OLD values. Rules are perfect fit only if
1) Your operations do not depend on NEW/OLD pseudo relations. For eg., plain
unconditional INSERT/UPDATE/DELETE on another relation.
2) You are *absolutely* sure that no app will use prepared statements, and
will always provide values in the statements, and not something like you did
above.
Best regards,
--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco *
Mail sent from my BlackLaptop device
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2008-02-10 09:37:48 | Re: BUG #3948: date/time functions returning wrong value |
Previous Message | Tom Lane | 2008-02-10 02:37:13 | Re: 8.3.0: vacuum full analyze: "invalid memory alloc request size" |