Tom Lane wrote:
> It's the oldest xmin of any transaction that's local to your database,
> but those xmin values themselves were computed globally --- so what
> matters is the oldest transaction that was running when any local
> transaction started.  In this case I expect it's the VACUUM's own
> transaction that's seeing the other guy as determining its xmin.
> 
> We could fix this by making every transaction compute, and advertise in
> the PGPROC array, both local and global xmin values.  In previous
> iterations of this discussion we concluded that the extra cycles (which
> would be spent in *every* transaction start) could not be justified by
> making VACUUM better able to reclaim space in the face of misbehaving
> clients.

I don't suppose it is possible to find out to which database a 
transaction was local after it was committed?


> That conclusion might be wrong, but it's not instantly obvious
> that it is...

Would it be possible to find out how long a transaction has been 
open already? It is quite simple to find the oldest uncommitted 
transaction using the pg_locks table, but from there we don't 
know yet how old it is. If it were possible to determine when it 
started the vacuum verbose output could perhaps include something 
like :
DETAIL:  113590 dead row versions cannot be removed yet.
Transaction 1234567 is has been in progress for 01:45:21,
only dead row versions committed before that are removable.
Nonremovable row versions range from 64 to 88 bytes long.

Jochem

PS Sorry about messing up the threading, I read the archives.

-- 
I don't get it
immigrants don't work
and steal our jobs
     - Loesje



Sat, 03 Apr 2004 17:21:08 +0200
Jochem van Dieten <jochemd@oli.tudelft.nl>
Re: Problems Vacuum'ing