[Dbix-class] ANNOUNCE: 0.08099_08 (0.08100_RC2)

[Peter Corlett]

On Wed, Apr 08, 2009 at 06:23:39PM +0200, Andreas Mock wrote:
[...]
>> I was pleased to see that there's some support for savepoints, which
>> leads to the possibility of finally having an atomic create_or_update().
> I'm interested in that. How are you doing it? 
> Why do you need savepoints to do a atomic update-or-create?
> I'm interested in the theory behind that.

Note that I'm doing this on PostgreSQL; other databases may well do things
differently. For example, MySQL's REPLACE INTO covers similar ground.

The current implementation does a SELECT followed by an INSERT or UPDATE as
appropriate. This introduces a race condition. The whole thing is a critical
section and needs to be wrapped in a transaction or savepoint.

I implemented it by starting a savepoint, then just trying the INSERT. If
that fails (normally due to duplicate UNIQUE keys) then the savepoint is
rolled back and the SELECT and UPDATE is done as before. After the UPDATE,
the savepoint is committed.

One slight perk is that my benchmarks show that using this to insert new
rows is about 50% faster; the downside is that if the row already exists,
the performance drops by 30%. This is intuitive: we've swapped two queries
for a single query to insert or three to update.

However, I'm not quite sure this trick completely retains the semantics of
create_or_update, so it wouldn't be a drop-in replacement. Never mind that
my code doesn't work *outside* of a transaction, and probably doesn't work
on all of the supported database engines.

I also suspect that while savepoints may be necessary, they're not
sufficient, and one also needs SELECT FOR UPDATE. How might I express that
in DBIC?