Re: [HACKERS] MERGE SQL Statement for PG11

On 3 February 2018 at 23:17, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> There are 3 specific issues on query structure, that together paint a
> picture about what you're not doing in the optimizer:
>
> 1. Whether or not subselects in the UPDATE targetlist are supported.
>
> 2. Whether or not subselects in the WHEN ... AND quals support subselects.
>
> 3. Whether or not subselects are possible within the main ON () join.
>
> I gave a lukewarm endorsement of not supporting #3, was unsure with
> #2, and was very clear on #1 as soon as I saw the restriction: UPDATE
> targetlist in a MERGE are *not* special, and so must support
> subselects, just like ON CONFLICT DO UPDATE, for example.

All three of the above give errors in the current patch, as we already
discussed for (1) and (2).

I've added these to the tests so we can track support for them explicitly.

The current patch runs one query then executes the quals
post-execution as we do for check constraints and RLS. Changes would
be required to support subselects.

Changes to support sub-selects don't invalidate what is there now in
the current patch with regard to query representation or optimization.
So support of those extra features can be added later if we choose.
Frankly, whatever we do now, I'm sure we will discover cases that need
further optimization, just as we have done with RLS and Partitioning,
so the query representation was likely to change over time anyway.

Whatever we decide for concurrent behavior will affect how we support
them. We can't move forwards on them until we have that nailed down.

I could give a longer technical commentary but I will be unavailable
now for some time, so unable to give further discussion.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services