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
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 6c3d89a14f6b1f19176864af4a0ea18eebd9f4bd..4db57a704a46a4737f951e5d8543fcc21676472c 100644
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
*************** transformInsertStmt(ParseState *pstate,
*** 652,657 ****
--- 652,658 ----
* to ensure that the values would be available while evaluating the
* VALUES RTE. This is a shame. FIXME
*/
+ #if 0
if (list_length(pstate->p_rtable) != 1 &&
contain_vars_of_level((Node *) exprsLists, 0))
ereport(ERROR,
*************** transformInsertStmt(ParseState *pstate,
*** 660,665 ****
--- 661,667 ----
errhint("Use SELECT ... UNION ALL ... instead."),
parser_errposition(pstate,
locate_var_of_level((Node *) exprsLists, 0))));
+ #endif
/*
* Generate the VALUES RTE
*************** transformInsertStmt(ParseState *pstate,
*** 672,677 ****
--- 674,683 ----
Assert(rte == rt_fetch(rtr->rtindex, pstate->p_rtable));
pstate->p_joinlist = lappend(pstate->p_joinlist, rtr);
+ if (list_length(pstate->p_rtable) != 1 &&
+ contain_vars_of_level((Node *) exprsLists, 0))
+ rte->lateral = true;
+
/*
* Generate list of Vars referencing the RTE
*/
*************** transformValuesClause(ParseState *pstate
*** 1222,1233 ****
--- 1228,1242 ----
*/
if (list_length(pstate->p_rtable) != 1 &&
contain_vars_of_level((Node *) exprsLists, 0))
+ rte->lateral = true;
+ #if 0
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("VALUES must not contain OLD or NEW references"),
errhint("Use SELECT ... UNION ALL ... instead."),
parser_errposition(pstate,
locate_var_of_level((Node *) exprsLists, 0))));
+ #endif
qry->rtable = pstate->p_rtable;
qry->jointree = makeFromExpr(pstate->p_joinlist, NULL);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
