Re: MERGE ... RETURNING

On Mon, Jul 17, 2023 at 12:43 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>
> On Fri, 2023-07-14 at 09:55 +0100, Dean Rasheed wrote:
> > I found a 10-year-old thread discussing adding support for OLD/NEW to
> > RETURNING [1], but it doesn't look like anything close to a
> > committable solution was developed, or even a design that might lead
> > to one. That's a shame, because there seemed to be a lot of demand
> > for
> > the feature, but it's not clear how much effort it would be to
> > implement.
>
> It looks like progress was made in the direction of using a table alias
> with executor support to bring the right attributes along.

That patch introduced RTE_ALIAS to carry that info through to the
executor, and having to special-case that that in many places was seen
as a bad thing.

> There was some concern about how exactly the table alias should work
> such that it doesn't look too much like a join. Not sure how much of a
> problem that is.

My understanding of that thread is that the join example Robert shared
was for illustrative purposes only, to show that executor already has
enough information to produce the desired output, and to show that
it's a matter of tweaking the parser and planner to tell the executor
what output to produce. But later reviewers pointed out that it's not
that simple (example was given of ExecDelete performing
pullups/working hard to get the correct values of the old version of
the row).

The concerns were mainly around use of OLD.* and NEW.*, too much
special-casing of RTE_ALIAS, and then the code quality of the patch
itself.

> > > Maybe instead of a function it could be a special table reference
> > > like:
> > >
> > > MERGE ... RETURNING MERGE.action, MERGE.action_number, id, val?
> > >
> >
> > Well, that's a little more concise, but I'm not sure that it really
> > buys us that much, to be worth the extra complication. Presumably
> > something in the planner would turn that into something the executor
> > could handle, which might just end up being the existing functions
> > anyway.
>
> The benefits are:
>
> 1. It is naturally constrained to the right context.

+1

> I'm not sure how much extra complication it would cause, though.

If that attempt with UPDATE RETURNING a decade ago is any indication,
it's probably a tough one.

Best regards,
Gurjeet
http://Gurje.et