Re: [HACKERS] MERGE SQL Statement for PG11

On Sat, Mar 10, 2018 at 9:22 PM, Pavan Deolasee
<pavan(dot)deolasee(at)gmail(dot)com> wrote:
> Ok. I will look at it. I think it shouldn't be too difficult and the
> original restriction was mostly a fallout of expecting CHECK constraint
> style expressions there.

Good, thanks.

> Ok. OVERRIDING is done. I think we can support ruleutils easily too. I don't
> know how to test that though.

Glad to hear it.

> I thought for a while about this and even tried multiple approaches before
> settling for what we have today. The biggest challenge is that
> inheritance/partition tables take completely different paths for INSERTs and
> UPDATE/DELETE. The RIGHT OUTER JOIN makes it kinda difficult because the
> regular UPDATE/DELETE code path ends up throwing duplicates when the source
> table is joined with individual partitions. IIRC that's the sole reason why
> I'd to settle on pushing the JOIN underneath, give it SELECT like treatment
> and then handle UPDATE/DELETE in the executor.

It sounds like we should try to thoroughly understand why these
duplicates arose. Did you actually call EvalPlanQualSetPlan() for all
subplans at the time?

> Ok. If you've something which is workable, then great. But AFAICS this is
> what the original patch was doing until we came to support partitioning.
> Even with partitioning I could get everything to work, without duplicating
> the RTE, except the duplicate rows issue. I don't know how to solve that
> without doing what I've done or completely rewriting UPDATE/DELETE handling
> for inheritance/partition table. If you or others have better ideas, they
> are most welcome.

I don't claim that what I wrote was workable with partitioning. But
I'm not getting how we can get away with not calling
EvalPlanQualSetPlan() for child plans, or something like it, as things
are.

> Right. The entire purpose of having two different RTEs is to work around
> this problem. I explained this approach here [1]. I didn't receive any
> objections then, but that's mostly because nobody read it carefully. As I
> said, if we have an alternate feasible and better mechanism, let's go for it
> as long as efforts are justifiable.

FWIW, you're right that I didn't give that aspect much thought until
quite recently. I'm no expert on partitioning.

As you know, there is an ON CONFLICT DO UPDATE + partitioning patch in
the works from Alvaro. In your explanation about that approach that
you cited, you wondered what the trouble might have been with ON
CONFLICT + partitioning, and supposed that the issues were similar
there. Are they? Has that turned up much?

--
Peter Geoghegan