Re: Syntax of INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

On 01/12/2014 06:42 AM, Peter Geoghegan wrote:
> Someone suggested to me that I solicit opinion on the chosen syntax of
> INSERT...ON DUPLICATE KEY LOCK FOR UPDATE on a separate thread. I'll
> do that here, while also giving a formal user-level definition of the
> feature. I'd like to solicit feedback from a wider set of people than
> those participating in the main thread, while avoiding talking about
> arcane details around locking which have dominated discussions up
> until this point.

After reading the following trimmed text I can see that the idea behind
this solves a real user problem.

It's not beautiful - but we've been rejecting things that solve the
upsert problem for users for ages. This looks technically solid enough
to do the job, useful, and reasonably usable with guidance from the
documentation.

My main question is how (if) this interacts with COPY. Would users need
to continue to COPY to an UNLOGGED or TEMP table, then use this for an
upsert-like operation into the target table? Or is it likely that COPY
can be extended to expose similar functionality - or wrap this into a
copy-on-duplicate-key-update ?

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services