Re: INSERT ... ON CONFLICT () SELECT

On Sat, Jun 17, 2017 at 7:49 AM, Matt Pulver <mpulver(at)unitytechgroup(dot)com> wrote:
> With the proposed "INSERT ... ON CONFLICT () SELECT" feature, the
> get_or_create_id() function is simplified to:

Are you locking the existing rows? Because otherwise, the
determination that they're conflicting can become obsolete immediately
afterwards. (I guess you would be.)

The problem with this design and similar designs is that presumably
the user is sometimes projecting the conflicting rows with the
intention of separately updating them in a wCTE. That might not work,
because only ON CONFLICT doesn't use the MVCC snapshot, in order to
ensure that an UPDATE is guaranteed when an INSERT cannot go ahead.
That isn't what you're doing in the example you gave, but surely some
users would try to do things like that, and get very confused.

I think that what you propose to do here would likely create a lot of
confusion by mixing MVCC semantics with special UPSERT visibility
semantics ("show me the latest row version visible to any possible
snapshot for the special update") even without a separate UPDATE, in
fact. Would you be okay if "id" appeared duplicated in the rows you
project in your new syntax, even when there is a separate unique
constraint on that column? I suppose that there is some risk of things
like that today, but this would make the "sleight of hand" used by ON
CONFLICT DO UPDATE more likely to cause problems.

--
Peter Geoghegan