Thread: How does PG know when a database is being accessed ?
Hi-
This is a problem I've observed on version 7.2.1.
I've been doing some setup work that involves dropping & recreating
databases a couple of times and I've noticed that once in a while, I'll get
the "database is being accessed by other users" message, even after
restarting the postmaster. This of course prevents me from dropping the DB.
I'd expect to get this if I've killed a client connection and the postmaster
hasn't had time to notice that the connection is defunct, but I was
surprised that restarting the postmaster didn't clear out the connections.
A couple of hours later, I can drop the database with no problem, and I
haven't done anything in the meantime, so I presume that a cleanup process
has run & tidied things up.
How are connections tracked & why does a record of a defunct client process
seem to persist through a restart?
Thanks
-Nick
---------------------------------------------------------------------
Nick Fankhauser
nickf@doxpop.com Phone 1.765.965.7363 Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/
"Nick Fankhauser" <nickf@ontko.com> writes:
> I'd expect to get this if I've killed a client connection and the postmaster
> hasn't had time to notice that the connection is defunct, but I was
> surprised that restarting the postmaster didn't clear out the connections.
It most certainly does. You sure you don't have new connections coming
in all the time?
regards, tom lane
> It most certainly does. You sure you don't have new connections coming > in all the time? Pretty sure- That was my first thought as well, so I checked for stray processes. I'm in the middle of something else right now, but this evening I'll see if I can reproduce the problem and supply the details. -Nick
Nick Fankhauser wrote:
In other words, I found that often I'm the culprit myself. I now check regularly by executing:
$ ps auxwww | grep "^postgres" | grep -v grep | grep [database name]
and that shows me who is connected to the DB.
Andrew
What happens to me occasionally, is that I open pgAdminII and (in some versions and/or configurations) that opens all the DBs and delete-locks them. Other times I connect via psql (on a linux box) to the actual DB I'm trying to drop.It most certainly does. You sure you don't have new connections coming in all the time?Pretty sure- That was my first thought as well, so I checked for stray processes. I'm in the middle of something else right now, but this evening I'll see if I can reproduce the problem and supply the details. -Nick ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
In other words, I found that often I'm the culprit myself. I now check regularly by executing:
$ ps auxwww | grep "^postgres" | grep -v grep | grep [database name]
and that shows me who is connected to the DB.
Andrew