Re: vacuum, dead rows, usual solutions didn't help

Tom Lane wrote:
> =?iso-8859-1?Q?G=E1bor?= Farkas <gabor@nekomancer.net> writes:
>> basically, the problem is this part of the vacuum-output:
>
>> INFO:  "sessions": found 2 removable, 6157654 nonremovable row versions
>> in 478069 pages
>> DETAIL:  6155746 dead row versions cannot be removed yet.
>
> The problem is that you've got some old open transactions that could
> potentially see those recently-dead rows, so VACUUM can't remove the
> rows without breaking MVCC rules for those transactions.
>
> Find the clients that are holding open transactions, and zap 'em.

well, that's the problem :-)

if i do a "ps aux | grep postgres" on the db-server, then

(ignoring the bin/postgres, writer-process and stats-collector-process)

then the oldest process is 3 days old.

but this dead-row-issue we have since weeks.

is it possible that a recently opened connection is blocking the
vacuuming of older-dead-rows?

>
>> previously we were running this application with postgresql-7.4, and
>> there the vacuuming worked fine. now we migrated this to
>> postgresql-8.2.4, and it does not want to vacuum it properly.
>
> I wonder whether you updated the client-side support libraries?

yes, they were updated too.


a related question:

on the db-server, let's say i have 3 databases: A, B, C.

if i have problems vacuuming B, then i only have to look for
processes/transactions touching B, correct? in other words, if  i do a
"ps aux | grep postgres", then i see also the username and the
database-name in the process-list. and if a postgres-process is not
touching B, then i do not have to check it. is this correct?

gabor