Re: XID wraparound and busy databases

On Wed, Aug 15, 2007 at 12:49:52PM -0400, Tom Lane wrote:
> Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
> > Maybe we can do something to reduce the xid consumption? For example,
> > reuse xids for read-only queries.
>
> Hmm, that's an idea.
>
> More simply, just keep the current transaction open (resetting
> everything but the XID) if we have made no changes by the time we're
> told to commit or rollback ... which is something we track already,
> so as not to waste cycles on useless commit XLOG records.
>
> You'd want some upper limit on transaction lifetime, so as to avoid the
> "long lived transactions hurt VACUUM" problem, but even reusing a single
> xact for a few seconds would pretty much eliminate this issue, I bet.
> It's hard to see how anyone could be doing 6K xacts/sec unless most are
> read-only.

Aren't there potential issues with keeping the same XID if a transaction
in the middle has modified data? IE:

SELECT * FROM a
DELETE FROM a ...
SELECT * FROM a

I'm thinking that in any system that's doing an enormous transaction
rate, most will be read-only, which means there'd still be a lot of
benefit to simply using one transaction until the next write transaction
came along.

Something else to think about... any app that's doing that kind of
transaction rate is likely going to have a large number of backends, so
it would be even better if one XID could be shared across backends.
--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)