Re: XMIN semantic at peril ?

On Oct 11, 2007, at 11:03 AM, Tom Lane wrote:

> Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
>> On Thu, Oct 11, 2007 at 10:44:17AM -0400, Tom Lane wrote:
>>> One question I'd have though is whether "freezing" of old tuples is
>>> likely to confuse your app.
>
>> Well, what we do is this:
>
>> - read row including XMIN
>> - do some UI stuff without open transactions
>> - update row with "... where pk = ... and XMIN = old_xmin_from_read"
>
>> If in the meantime another writer changed the data we
>> originally read we would detect that by xmin having changed
>> hence no row to be updated. So, yes, there is a *tiny*
>> failure condition:
>
> Hmm.  I think the failure condition is not what you are thinking: in
> your example, you'd correctly conclude that some other transaction
> modified the row.  The problem case is
>
> - read (a rather old) row including XMIN
> - VACUUM comes along and decides to set XMIN = FrozenTransactionId
> - update row with "... where pk = ... and XMIN = old_xmin_from_read"
> - update fails, when there is no need to fail
>
> As long as the failure is "soft", ie, you recover reasonably, this
> shouldn't be a big problem.  But it's certainly not a scenario you
> should dismiss as not credible because of timescales.

If the query is always based on a primary key + XMIN, and since
vacuum is the only thing that sets FrozenTransactionId, would it be
unsane to change the update to

- update row with "... where pk=... and XMIN IN (old_xmin_from_read,
FrozenTransactionId)

?

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com