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@indiana.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin