Thread

1. Rules and WITH and LATERAL

   Tom Lane <tgl@sss.pgh.pa.us> — 2012-08-19T16:06:30Z

   Six years ago, we punted on allowing rules to use OLD and NEW in
   multi-row VALUES constructs, because we didn't have LATERAL:
   http://archives.postgresql.org/pgsql-hackers/2006-08/msg00044.php
   
   I thought maybe that restriction could be fixed now that we do have
   LATERAL, and indeed the attached quick-and-dirty POC seems to make it
   work.  Barring objection I'll clean this up and apply it.
   
   While poking at this, though, I noticed that the workaround proposed
   in the aforementioned thread does not actually work, and AFAICT never
   has:
   
   regression=# create rule r2 as on update to src do also insert into log select old.*, 'old' union all select new.*, 'new';
   ERROR:  42P10: UNION/INTERSECT/EXCEPT member statement cannot refer to other relations of same query level
   LINE 1: ...s on update to src do also insert into log select old.*, 'ol...
                                                                ^
   LOCATION:  transformSetOperationTree, analyze.c:1629
   
   I tried hacking transformSetOperationTree in the same fashion, to set
   the subquery RTE's lateral flag instead of throwing an error.  That
   just moved the problem though:
   
   regression=# create rule r2 as on update to src do also insert into log select old.*, 'old' union all select new.*, 'new';
   ERROR:  0A000: conditional UNION/INTERSECT/EXCEPT statements are not implemented
   LOCATION:  transformRuleStmt, parse_utilcmd.c:2255
   
   transformRuleStmt's problem seems much more fundamental: it has noplace
   to inject the extra jointree entry needed for the relation the rule is
   attached to.  So fixing that looks like a dead end.
   
   While thinking about this I wondered whether it might be possible to
   clean up the implementation of rules, and perhaps also get rid of some
   of their semantic issues, by making the rule rewriter rely on WITH
   and/or LATERAL, neither of which we had back in the dark ages when the
   current rules implementation was built.  In particular, WITH might offer
   a fix for the multiple-evaluation gotchas that people so often trip
   over.  For instance, perhaps an UPDATE with rules could be rewritten
   into something like
   
   WITH data_src AS (
   	SELECT ctid, all-old-values, all-new-values FROM target_rel FOR UPDATE
   ),
   rule_1 AS (
   	... rule body here ...
   ),
   rule_2 AS (
   	... rule body here ...
   )
   UPDATE target_rel SET col1 = newval1, col2 = newval2, ...
   FROM data_src WHERE ctid = data_src.ctid;
   
   Rewriting the rule rewriter would be a fairly sizable project of course,
   and it's not one I have much interest in tackling personally.  I'm just
   throwing it out there as a possible TODO.
   
   			regards, tom lane
   
   

2. Re: Rules and WITH and LATERAL

   Martijn van Oosterhout <kleptog@svana.org> — 2012-08-20T18:52:38Z

   On Sun, Aug 19, 2012 at 12:06:30PM -0400, Tom Lane wrote:
   > While thinking about this I wondered whether it might be possible to
   > clean up the implementation of rules, and perhaps also get rid of some
   > of their semantic issues, by making the rule rewriter rely on WITH
   > and/or LATERAL, neither of which we had back in the dark ages when the
   > current rules implementation was built.  In particular, WITH might offer
   > a fix for the multiple-evaluation gotchas that people so often trip
   > over.  For instance, perhaps an UPDATE with rules could be rewritten
   > into something like
   
   Making the rule system use WITH always seemed like a good idea to me.
   ISTM though that it would tax the optimiser, as it would need to become
   much more clever at pushing conditions down. For example, on 9.1 at
   least you still get this:
   
   $ explain with x as (select * from pg_class) select * from x where relname = 'test';
                                QUERY PLAN                              
   ---------------------------------------------------------------------
    CTE Scan on x  (cost=14.15..23.49 rows=2 width=189)
      Filter: (relname = 'test'::name)
      CTE x
        ->  Seq Scan on pg_class  (cost=0.00..14.15 rows=415 width=194)
   (4 rows)
   
   whereas without the with you get an index scan.
   
   So in its current form you can't use WITH to simplify the
   implementation of views because performence would suck.  OTOH, the
   intelligence in the current rule system may be a good guide to optimise
   WITH statements.
   
   Have a nice day,
   -- 
   Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
   > He who writes carelessly confesses thereby at the very outset that he does
   > not attach much importance to his own thoughts.
      -- Arthur Schopenhauer