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

Erik Jones wrote:
>
> On Jan 10, 2008, at 6:01 AM, Simon Riggs wrote:
>
>> On Thu, 2008-01-10 at 11:18 +0100, Gábor Farkas wrote:
>>> Simon Riggs wrote:
>>>>
>>>>> also, even if it is wrong, can an 'idle-in-transaction' connection
>>>>> that
>>>>> was opened today block the vacuuming of rows that were deleted
>>>>> yesterday?
>>>>
>>>> Yes, if the rows were deleted after the connection started.
>>>>
>>>
>>> to avoid any potential misunderstandings, i will summarize the
>>> situation:
>>>
>>> 1. the vacuum-cronjob refuses to remove dead rows since 1.jan.2008.
>>>
>>> 2. i know that no postgres-process is older than 7.jan.2008. (from "ps
>>> aux | grep postgres", and except the postgres-system-processes)
>>>
>>> how can this happen?
>>
>> They might be different set of dead rows, just roughly the same numbers
>> each day.
>
> Or, put another way, this is probably the same problem recurring, not
> one constant instance of the issue.

unfortunately, i do not think that's the case, here is why:

this vacuum-process is running every hour, and i have the logs from
roughly 450 vacuum runs.

so, for one specific table, that had these unremovable rows:

the number of "removable dead rows" was between 0 and 11,
and the number of "unremovable dead rows" grew by a number between 0 and
41106 every hour (it was three times zero, and the rest was between 86
and 41106).

so i do not think it happened with different rows, just roughly the same
number.

on the good side, we changed the code for that one process, that kept
being in "idle in transaction", and now the vacuuming works nicely.

and this is still a mystery for me, because i understand that
idle-in-transaction is wrong, but even so, a process that i start today,
in my opinion simply cannot block the recovery of dead rows, that were
deleted yesterday.

but i'm probably misunderstanding something, so if i will have some more
time for this in the future, i will read more about mvcc, and maybe
start a thread here :-)

thanks for all your help,

gabor