Re: Database in use?

On Mar 4, 2009, at 4:53 PM, Scott Marlowe wrote:

> On Wed, Mar 4, 2009 at 2:40 PM, Carol Walter <walterc(at)indiana(dot)edu>
> wrote:
>> This has happened or is happening to me again, only this time, it's a
>> database that I just created. I restored another database into a
>> test
>> database. One of the tables is empty. I want to drop the test
>> database and
>> create a new one. When I try to drop the test database, I get this
>> error -
>> template1=# drop database km_tezt;
>> ERROR: database "km_tezt" is being accessed by other users
>>
>> template1=# select * from pg_prepared_xacts;
>> transaction | gid | prepared | owner | database
>> -------------+-----+----------+-------+----------
>> (0 rows)
>
> What does "select * from pg_stat_activity say about this db?
>
>> This database is one that I created just a few hours ago just to
>> test some
>> things myself. There can be no one in it except me. I completely
>> closed
>
> Correct me if I'm wrong, but isn't that enough ((just you) to cause
> the drop database to fail?
>
Yes, of course, "just me" is enough for it to fail. I thought that I
wasn't in it, either. That's what I meant. As it turned out, I was in
it, having used Aqua Data Studio to draw an ERD. Everything worked
after I got back into Aqua Data and disconnected from the database. I
was able to drop the database.

I still have my original question though. Is there a graceful way to
close all connections to a database from the server side. I'm in an
academic environment and I can count on people not writing bad code.
I don't want it to close down in the midst of someone's query, but I
would like to be able to disconnect users if the aren't running
queries. We have regular system maintenance on Friday evenings.
Currently, the only way I have to break these connections is to stop
the database. That stops it for all databases, when only one may be
the problem. I would like to be able to do this when there is a
problem with a database and not have to wait until my scheduled
maintenance.
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin