Re: Help: 8.0.3 Vacuum of an empty table never completes ...

As fate would have it, the vacuumdb frontend and backend which were
initially afflicted are still in existence:

sscadmin 19236 19235 0 Nov25 ? 00:00:00 /usr/local/pgsql/bin/
vacuumdb -U postgres --all --analyze --verbose
postgres 19244 3596 0 Nov25 ? 00:00:02 postgres: postgres
social [local] VACUUM

pid 19244.

And here's pg_locks:

social=# select * from pg_locks;
relation | database | transaction | pid | mode
| granted
----------+----------+-------------+-------+--------------------------
+---------
| | 38790657 | 19244 |
ExclusiveLock | t
6586066 | 6585892 | | 28406 |
ShareUpdateExclusiveLock | f
| | 39097312 | 28861 |
ExclusiveLock | t
| | 39089744 | 28756 |
ExclusiveLock | t
6586066 | 6585892 | | 28756 |
ShareUpdateExclusiveLock | f
6586066 | 6585892 | | 19244 |
ShareUpdateExclusiveLock | t
6586066 | 6585892 | | 19244 |
ShareUpdateExclusiveLock | t
8417138 | 6585892 | | 19244 |
ShareUpdateExclusiveLock | t
16839 | 6585892 | | 28861 |
AccessShareLock | t
| | 39063661 | 28560 |
ExclusiveLock | t
| | 39056736 | 28406 |
ExclusiveLock | t
6586066 | 6585892 | | 28560 |
ShareUpdateExclusiveLock | f
(12 rows)

pid
-------
19244
28406
28560
28756
28861
(5 rows)

Of those 5 pids:

19244 -- vaccuum backend initally afflicted -- status in argv:
'postgres: postgres social [local] VACUUM'
28406 -- a 10AM today vacuum started up by cron this morning after I
got things half-way working again early in the diagnosis of this
situation. args: 'postgres: postgres social [local] VACUUM waiting'
28560 -- a 10:16 today by-hand vacuum session futher in diagnosis
land. args: 'postgres: postgres social [local] VACUUM waiting'
28756 -- 11AM cron'd process. Yes, I see a quickly mounting issue
here. args: 'postgres: postgres social [local] VACUUM waiting'
28861 -- production servicing backend, now back in idle state. [ not
in tx idle by regular idle ].

On Nov 28, 2005, at 11:09 AM, Tom Lane wrote:

> James Robinson <jlrobins(at)socialserve(dot)com> writes:
>> Comparing the logs further with when it did complete, it seems that
>> one table in particular (at least) seems afflicted:
>
>> social=# vacuum verbose analyze agency.swlog_client;
>
>> hangs up forever -- have to control-c the client. Likewise for w/o
>> 'analyze'.
>
> Given that it's not eating CPU time, one would guess that it's blocked
> waiting for a lock. Can you find any relevant locks in pg_locks?
>
> regards, tom lane

----
James Robinson
Socialserve.com