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

On 09/30/2014 06:08 AM, Kevin Grittner wrote:
> Well, unless we abandon transactional semantics for other MERGE
> statements, we should have a way that UPSERT logic continues to
> work if you don't match a suitable index; it will just be slower --
> potentially a lot slower, but that's what indexes are for.

That would probably lead to MERGE taking different lock strengths based
on index availability, having different failure modes, etc.

The less internal magic inside what's already a complicated and
confusing area for users, the better.

> I don't
> think we need a separate statement type for the one we "do well",
> because I don't think we should do the other one without proper
> transactional semantics.

"Proper transactional semantics" isn't the same as "free from all forms
of race condition".

Sometimes you want or need to do things that can't be made
concurrency-safe, or would perform unacceptably if done in a
concurrency-safe manner. That's why we have LOCK TABLE, among other things.

We have READ COMMITTED for a reason. We have SELECT without FOR SHARE
for a reason.

MERGE seems to be specified as more of an OLAP / ETL operation than an
OLTP one, and I think we should probably respect that - and the way
other RDBMSes have already implemented it.

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