Re: UPDATE SET (a,b,c) = (SELECT ...) versus rules

On 06/14/2014 09:35 PM, Tom Lane wrote:
> As I mentioned awhile ago, I'm thinking about implementing the
> SQL-standard construct
>
> UPDATE foo SET ..., (a,b,...) = (SELECT x,y,...), ...
>
> I've run into a rather nasty problem, which is how does this interact
> with expansion of NEW references in ON UPDATE rules?
Was'nt there a plan (consensus?) about deprecating rules altogether ?

Cheers
Hannu
> For example,
> suppose foo has a rule
>
> ON UPDATE DO ALSO INSERT INTO foolog VALUES (new.a, new.b, ...);
>
> The existing implementation relies on being able to pull expressions
> for individual fields' new values out of the UPDATE targetlist; but
> there is no independent expression for the new value of "a" here.
> Worse yet, the NEW references might be in WHERE quals, or some other
> place outside the targetlist of the rule query, which pretty much
> breaks the implementation I'd sketched earlier.
>
> The best that I think is reasonable to do in such cases is to pull out
> a separate copy of the sub-select for each actual NEW reference in a
> rule query. So the example above would give rise to an expanded
> rule query along the lines of
>
> INSERT INTO foolog VALUES ( (SELECT x as a, y as b, ...).a,
> (SELECT x as a, y as b, ...).b,
> ... );
>
> which would work, but it would re-evaluate the sub-select more times
> than the user might be hoping. (Of course, if there are volatile
> functions in the sub-select, he's screwed, but that's not a new
> problem with rules.)
>
> Given that ON UPDATE rules are close to being a deprecated feature,
> it doesn't seem appropriate to work harder than this; and frankly
> I don't see how we could avoid multiple sub-select evaluations anyway,
> if the NEW references are in WHERE or other odd places.
>
> Another possible answer is to just throw a "not implemented" error;
> but that doesn't seem terribly helpful, and I think it wouldn't save
> a lot of code anyway.
>
> Thoughts?
>
> regards, tom lane
>
>

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ