Re: BUG #3945: unexpected ON INSERT rule behaviour

["Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com>]

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 *

http://gurjeet.frihost.net

Mail sent from my BlackLaptop device