Rules and WITH and LATERAL
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
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