autovacuum "connections" are hidden

From: Casey Duncan <casey(at)pandora(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: autovacuum "connections" are hidden
Date: 2006-05-17 17:15:57
Message-ID: 6F2B41C1-F869-4BFB-A15D-5CBDD1A83DC1@pandora.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Trying to drop a database, this morning I ran into the not so unusual
error:

dropdb: database removal failed: ERROR: database "test_seg1" is
being accessed by other users

however, when I did "select * from pg_stat_activity" on the pg
server, it showed no connection to that db. Then I looked at the
processes:

tmp0% ps ax | grep test_seg1
10317 ? D 0:36 postgres: autovacuum process test_seg1

I imagine this doesn't show up because there is no connection, per
se, the autovacuum process is a bon-a-fide backend process in its own
right. It's just a bit confusing in this circumstance.

I guess this is more of a heads up than a question.

Another circumstance where this has bitten me is in doing a slony
SUBSCRIBE_SET. At least in 1.1.5 the autovac daemon can deadlock with
slony and cause the subscriber operation to fail. When this happened
to me it was somewhat baffling at first because I had altered
pg_hba.conf to forbid all non-superuser connections and there were no
other connections visible at the time (in pg_stat_activity). Turning
off autovacuum during the subscribe fixed this, but not without a
little head-scratching (and maybe some luck).

No way comes to my mind to improve the visiblity of autovac
"connections" but I thought I would throw this out here in case
anyone had any thoughts on the matter.

-Casey

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ed L. 2006-05-17 17:44:39 Re: Why won't it index scan?
Previous Message Tom Lane 2006-05-17 17:01:28 Re: DB structure of PostGRE