Re: selects during vacuum

Joe Maldonado <jmaldonado(at)webehosting(dot)biz> writes:
>>> This table contains 1 record allways.
>>> The record is updated once per second.
>>> Every 167 seconds a vacuum analyze is run on the table
>>> After some time it hangs in the analyze and blocks all
>>> access to that table including selects.
>>
>> Someone else suggested reindexing (which frankly is what I suspected) but I
>> don't see an index mentioned above. Must admit I'm puzzled - does the fact
>> you had 80 tuples above make any sense to you?

> There are no indexes because this table consited on only 1 tuple and is
> not joined with any other tables during the queries. Is there anything
> to be gained from indexing this table?

Probably not.

> as far as the 80 tuples, that does not make sense to me since the table
> is vacuumed regularly and we do not insert into it...though I can go
> back and double check...

Given that you update once per second, that just says that your oldest
open transaction was eighty seconds old when VACUUM ran. VACUUM won't
reclaim tuples that *might* be visible to some other open transaction.
But its method of detecting this is not exact enough to determine
exactly which tuples are visible to exactly which transactions. Most
likely, only a few of the last eighty updates are actually still visible
to any live transaction, but VACUUM is not going to realize that. It
just keeps everything newer than the oldest open transaction.

What I don't understand is how the ANALYZE step could hang up. It
acquires ACCESS SHARE lock on the target table, and I think in the
version you are running it temporarily acquires EXCLUSIVE (*not* ACCESS
EXCLUSIVE) lock on pg_statistic. Neither of those could create any
deadlock unless you have other transactions doing strange things, like
trying to acquire ACCESS EXCLUSIVE lock on the target table, or perhaps
acquiring locks on pg_statistic.

regards, tom lane