Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On Tue, Sep 30, 2014 at 02:57:43PM -0700, Josh Berkus wrote:
> I don't know that that is the *expectation*. However, I personally
> would find it *acceptable* if it meant that we could get efficient merge
> semantics on other aspects of the syntax, since my primary use for MERGE
> is bulk loading.
>
> Regardless, I don't think there's any theoretical way to support UPSERT
> without a unique constraint. Therefore eventual support of this would
> require a full table lock. Therefore having it use the same command as
> UPSERT with a unique constraint is a bit of a booby trap for users.
> This is a lot like the "ADD COLUMN with a default rewrites the whole
> table" booby trap which hundreds of our users complain about every
> month. We don't want to add more such unexpected consequences for users.

I think if we use the MERGE command for this feature we would need to
use a non-standard keyword to specify that we want OLTP/UPSERT
functionality. That would allow us to mostly use the MERGE standard
syntax without having surprises about non-standard behavior. I am
thinking of how CONCURRENTLY changes the behavior of some commands.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +