Re: conditional insert

On Wed, Sep 7, 2011 at 3:04 PM, Andrew Sullivan <ajs(at)crankycanuck(dot)ca> wrote:
> On Wed, Sep 07, 2011 at 02:51:32PM -0500, Merlin Moncure wrote:
>>
>> @andrew s: going SERIALIZABLE doesn't help if you trying to eliminate
>> cases that would push you into retrying the transaction.
>
> Well, no, of course.  But why not catch the failure and retry?  I
> guess I just don't get the problem, since I hear people say this all
> the time.  (I mean, I've also seen places where 'upsert' would be
> cool, but it doesn't seem trivial to do in a general way and you can
> do this with catch-serialization-error-and-retry, I think?)

good points, but consider that savepoints have a certain amount of
performance overhead, and there may be some dependent client side
processing that is non-trivial to roll back. Also, if you have a lot
of contention, things can get nasty very quickly -- a full lock is
reliable, simple, and fast, and can be done in one round trip.

Any solution that doesn't have loops is inherently more robust than
one that does. I'll rest my case on that point -- consider very
carefully that the upsert loop example presented in the docs for years
was vulnerable to an infinite loop condition that was caught by one of
our users in production. That completely turned me off towards that
general method of dealing with these types of problems unless there is
really no other reasonable way to do it.

merlin