Re: Persistent dead rows

Malcolm McLean wrote:
> Richard Huxton wrote:
>> Malcolm McLean wrote:
>>> I tested this theory by stopping java applications that were
> connected
>>> to the database and all other connections that were using
> transactions
>>> and the full vacuum was still unable to remove the dead rows.
>>>
>>> What I'm still wondering about, is why the dead row count rises
>>> incredibly high, then all of a sudden drops to 0 again when the java
>>> apps never stop running.
>> Are you certain there's no open transaction? Perhaps keep an eye on
>> SELECT * FROM pg_stat_activity - there might be something you don't
> know
>> about.
>
> Okay, I just stopped all java processes again and all pg_stat_activity
> returned were IDLE's and no IDLE in transactions. The strange this is
> that a cluster command removes the dead rows, and this can only be run
> when all the java apps have been stopped.

Well, both cluster and vacuum full will require a lock on the table. But
they're more or less doing the same thing, so why the one should work
and the other not I don't know.

>> If it was the autovacuum interfering, I'd expect a lock failure.
>
> I doubt autovacuum is interfering, I think it is the process that is
> clearing the dead rows. If that is the case, then why is only autovacuum
> able to clear them and not a manual vacuum.

Makes no sense to me - autovacuum has no magic about it, it just runs
vacuum.

You've got the permissions to vacuum the table, otherwise you'd get an
error. You've checked for transactions in pg_stat_activity. Don't see
how it's worth checking pg_locks if you don't have any transactions.
This one's puzzling me.

I've had a quick look at the release notes for 8.1.x and can't see
anything obvious on this issue, but it might be worth upgrading to 8.1.8
to see if that just makes it go away.

--
Richard Huxton
Archonet Ltd