Andres Freund <andres@2ndquadrant.com> wrote:
> On 2014-09-30 14:57:43 -0700, Josh Berkus wrote:
>
>> Regardless, I don't think there's any theoretical way to support
>> UPSERT without a unique constraint.
>
> You can do stuff like blocking predicate locking. But without indexes to
> support it that gets awfully complicated and unfunny. I don't think we
> want to go there. So essentially I agree with that statement.
Well, as you seem to be saying, it's not to bad with even an
non-unique index if we wanted to do a little extra work; and there
are a lot of ways to potentially deal with it even without that.
Theoretically, the number of ways to do this is limited only by
time available to brainstorm.
That said, at no time have I advocated that we try to implement
UPSERT in this release with anything but a UNIQUE index. The issue
I raised was whether a subset of the MERGE syntax should be used to
specify UPSERT rather than inventing our own syntax -- which
doesn't seem in any way incompatible requiring a unique index to
match the expression. Given subsequent discussion, perhaps we
could decorate it with something to indicate which manner of
concurrency handling is desired? Techniques discussed so far are
- UPSERT style- Hold an EXCLUSIVE lock on the table- Allow "native" concurrency management
An alternative which seems to be on some people's minds is to use a
different command name for the first option (but why not keep the
rest of the standard syntax?) and to require an explicit LOCK TABLE
statement at the start of the transaction if you want the second
option.
My preference, after this discussion, would be to default to UPSERT
style if the appropriate conditions are met, and to default to the
third option otherwise. If you want an exclusive lock, ask for it
with the LOCK TABLE statement.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company