#SQLITE UPSERT UPDATE#
#SQLITE UPSERT PATCH#

Don't burn through XIDs with expensive subtransactions - the existing, subxact-looping approach to UPSERT implies that XIDs are consumed at an intractably high rate (intractable for at least some use cases, like the multi-master replication conflict resolution use case).

Avoid duplicate key violations that the implementation must trap and handle.

Advance usability over the status quo - don't introduce a feature with significant foot-guns, unnecessary subtle behaviors, or big performance surprisesĪs outlined below, SQL MERGE seemingly doesn't meet this standard (principally because it lacks 'the essential property of UPSERT', which appears to be in tension with supporting a fully flexible join).

Again, this goal is a special case of our first primary goal.

Avoid 'unprincipled deadlocks' - deadlocks that the user has no reasonable way of avoiding.

This goal is a special case of our first primary goal.

Avoid having to invent ' READ COMMITTED serialization failures'.

Don't be inferior in any way to existing, subxact-looping approach to UPSERT.

In general, guarantee insert-or-update 'atomicity' for the simple cases - guarantee one or the other of those two outcomes ('the essential property of UPSERT').

The absence of this feature from Postgres has been a long-standing complaint from Postgres users. Examples include MySQL's INSERT.ON DUPLICATE KEY UPDATE, or VoltDB's UPSERT statement. One of those two outcomes must be guaranteed, regardless of concurrent activity, which has been called 'the essential property of UPSERT'.

#SQLITE UPSERT UPDATE#

'UPSERT' is a DBMS feature that allows a DML statement's author to atomically either insert a row, or on the basis of the row already existing, UPDATE that existing row instead, while safely giving little to no further thought to concurrency.

6.2.3 Theoretical lock starvation hazards.

6.2.2 Visibility issues and the proposed syntax (WHERE clause/predicate stuff).

6.2.1 Why still lock row when UPDATE predicate isn't satisfied?.