Andres Freund <and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
