autovacuum "connections" are hidden
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
Casey Duncan <casey@pandora.com> writes:
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
Hmm, autovacuum won't show up in pg_stat_activity because it never calls
pgstat_report_activity(). Seems like maybe it should, though.
regards, tom lane
On May 17, 2006, at 12:34 PM, Tom Lane wrote:
Casey Duncan <casey@pandora.com> writes:
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_seg1Hmm, autovacuum won't show up in pg_stat_activity because it never
calls
pgstat_report_activity(). Seems like maybe it should, though.
Is this worth filing a bug about for posterity?
-Casey
Tom Lane wrote:
Casey Duncan <casey@pandora.com> writes:
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_seg1Hmm, autovacuum won't show up in pg_stat_activity because it never calls
pgstat_report_activity(). Seems like maybe it should, though.
If we do that, would it help people who are trying to determine if
pg_autovacuum is running? Would the connection still appear while
pg_autovacuum is sleeping?
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +