On Tue, Jun 27, 2017 at 12:40 PM, armand pirvu <
armand.pirvu@gmail.com> wrote:
so how is it working in fact ? Isn't it working like looping in the
IVEE.dim_company and for each company_id if the record does have a
correspondent in csischema.dim_company then update csischema.dim_company set
company_name = EXCLUDED.company_name where company_id=... ? If so isn't it
supposed to use the PK for each company_id ? Or is it more like building a
whole list from IVEE.dim_company and treat like a join ? Just trying to
understand
The processing here has to happen a tuple at a time. That's because
the index structure itself is the only authoritative source of truth
about whether or not there is a possible conflict. An MVCC snapshot
isn't good enough, because it sees a consistent view of things, not
the true physical reality of what exists or does not exist in the
index.
What you end up with here is a weird nested loop join, you might say.
The implementation couldn't do it any other way (this could never
behave more like a merge join), because we must eagerly check for
conflicts right as we insert (our insert would be registered by
*other* inserters/upserters as a conflict). If the implementation did
ever do it that way, it would break the important UPSERT guarantees
around concurrency.
MERGE does this in other systems, which is okay for those other
systems because MERGE makes no special promises about concurrency
(e.g., you can get a unique violation in the joined-on column with
MERGE). But, MERGE would be faster for bulk loading, which is what
MERGE is good for.
--
Peter Geoghegan