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

Peter Geoghegan <pg(at)heroku(dot)com> wrote:

> I think the fact that no MERGE implementation does what you want
> should be convincing. It is *horrifically* complicated to make
> what you want work, if indeed it is technically feasible at all.
> Isn't this already complicated enough?

What about the MERGE syntax I posted makes it hard to implement the
statement validation and execution code you already have? (I'm
asking about for the UPSERT case only, not an implementation of all
aspects of the standard syntax.)

To recap, in summary that would be:

MERGE INTO tablename [ alias ]
USING ( relation ) [ alias ]
ON ( boolean-expression )
WHEN MATCHED THEN
UPDATE SET target-column = expression
[ , target-column = expression ] ...
WHEN NOT MATCHED THEN
INSERT ( target-columns ) VALUES ( expressions )

The initial implementation could restrict to these exact clauses
and require that the boolean-expression used equality-quals on all
columns of a unique index on only NOT NULL columns. I think the
relation could be a VALUES clause or any SELECT statement without
causing problems; do you think that would need to be constrained in
some way? It would be wonderful if the expressions could be any
arbitrary expressions assignable to the target columns; do you see
a need to constrain that?

If we later expand the MERGE statement to more general cases, I
don't see why statements of this form could not be treated as a
special case. Personally, I'm dubious that we would want to
compromise transactional integrity to achieve the broader case, but
doubt that we would need to do so. I won't say it is just a SMOP,
because there would need to be some careful design first. ;-)

> We use different access techniques as you say. We do not use
> different types of snapshots. That seems like a pretty
> fundamental distinction.

We use special types of snapshots in running DML that fires certain
types of constraints, like FKs.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company