Peter Geoghegan <pg@heroku.com> wrote:
> On Mon, Sep 29, 2014 at 3:08 PM, Kevin Grittner <kgrittn@ymail.com> 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.
>
> I want an implementation that doesn't have unique violations,
> unprincipled deadlocks, or serialization failures at READ COMMITTED. I
> want it because that's what the majority of users actually want. It
> requires no theoretical justification.
Sure. I'm not suggesting otherwise.
>> 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.
>
> That seems like a very impractical attitude. I cannot simulate what
> I've been doing with unique indexes without taking an exclusive table
> lock. That is a major footgun, so it isn't going to happen.
There are certainly other ways to do it, although they require more
work. As far as UPSERT goes, I agree that we should require such
an index, at least for the initial implementation and into the
foreseeable future. What I'm saying is that if we implement it
using the standard MERGE syntax, then if the features of MERGE are
extended it will continue to work even in the absence of such an
index. The index becomes a way of optimizing access rather than
defining what access is allowed.
At the risk of pushing people away from this POV, I'll point out
that this is somewhat similar to what we do for unlogged bulk loads
-- if all the conditions for doing it the fast way are present, we
do it the fast way; otherwise it still works, but slower.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company