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

On Wed, Aug 27, 2014 at 10:43 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> Example usage:
>
> INSERT INTO upsert(key, val) VALUES(1, 'insert') ON CONFLICT UPDATE
> SET val = 'update';

I think that syntax is a dramatic improvement over your previous
proposals. The only part I don't entire like is this:

> INSERT INTO upsert(key, val) VALUES(1, 'insert') ON CONFLICT WITHIN
> upsert_pkey UPDATE SET val = 'update';

It seems to me that it would be better to specify a conflicting column
set rather than a conflicting index name.

I don't have much in the way of comments about the implementation, at
least not right at the moment, but...

> Essentially, the implementation has all stages of query processing
> During the execution of the parent ModifyTable, a special auxiliary
> subquery (the UPDATE ModifyTable) is considered as a special case.
> This is not a subplan of the ModifyTable node in the conventional
> sense, and so does not appear within EXPLAIN output.

...that sounds wonky.

> I already mentioned the inability to reference rejected rows in an
> UPDATE, as well as my unease about VACUUM interlocking, both of which
> are open item. Also, some of the restrictions that I already mentioned
> - on updatable views, inheritance, and foreign tables - are probably
> unnecessary. We should be able to come with reasonable behavior for at
> least some of those.

If you've noted my comments on the UPDATE/DELETE .. ORDER BY .. LIMIT
thread, you won't be surprised to hear that I think those restrictions
will need to be lifted - especially for inheritance, but probably the
others as well.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company