On Oct 11, 2007, at 11:03 AM, Tom Lane wrote:
Karsten Hilbert <Karsten.Hilbert@xxxxxxx> 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@xxxxxxxxxx
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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
