Re: [HACKERS] MERGE SQL Statement for PG11

From: Peter Geoghegan <pg(at)bowt(dot)ie>
To: Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com>, Pavan Deolasee <pavan(dot)deolasee(at)2ndquadrant(dot)com>, Stephen Frost <sfrost(at)snowman(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] MERGE SQL Statement for PG11
Date: 2018-03-24 06:15:01
Message-ID: CAH2-Wz=h8kQ5CyBojOxJLSfwVrR6fnEbJ9e8JJYTqrJz5JCtww@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Fri, Mar 23, 2018 at 4:56 AM, Pavan Deolasee
<pavan(dot)deolasee(at)gmail(dot)com> wrote:
> postgres=# insert into target as t select sid from source s join target t on
> t.ttid = s.sid;
> ERROR: column t.ttid does not exist
> LINE 1: ...rget as t select sid from source join target t on t.ttid = s...
> ^
> HINT: Perhaps you meant to reference the column "t.tid" or the column
> "t.tid".
> postgres=#
>
> This produces a very similar looking HINT as your test above. I am certain
> that "target" table gets two RTEs, exactly via the same code paths as you
> discussed above. So if this is not a problem for INSERT, why it would be a
> problem for MERGE? May be I am missing a point here.

I agree that this is very similar, as far as the RTEs go. What is
dissimilar is the fact that there is hard-coded knowledge of both
through parsing, planning, and execution. It's everything, taken
together.

ResultRelInfo has a ri_mergeTargetRTI field, which seems to be used
instead of ri_RangeTableIndex in some contexts but not others. What
might the interactions with something like GetInsertedColumns() and
GetUpdatedColumns() be? Is that explained anywhere? In general, I
think that there is potential for things to break in subtle ways.

>> This seems like something that needs to be explained, at a minimum.
>> Even if I'm completely wrong about there being a security hazard,
>> maybe the suggestion that there might be still gives you some idea of
>> what I mean about unintended consequences.
>
>
> Ok. I will try to explain it better and also think about the security
> hazards.

I realize that I'm giving you a somewhat vague problem, without
offering any real help on a solution. For what it's worth, I don't
feel great about that, but I don't know enough about partitioning in
general and your approach to partitioning for MERGE in particular to
be more constructive. That said, checking that an issue like the one
fixed by 5f173040 cannot recur here is one concrete thing you could
do. Documenting/explaining the ri_RangeTableIndex/ri_mergeTargetRTI
divide is another. The comment above ri_mergeTargetRTI is totally
inadequate.

--
Peter Geoghegan

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2018-03-24 06:35:01 Re: [HACKERS] MERGE SQL Statement for PG11
Previous Message Amit Kapila 2018-03-24 04:25:08 Re: Re: GSOC 2018 Ideas