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@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +