Re: dropdb: database removal failed: active sessions

On Mon, 2011-08-29 at 15:22 -0400, JD Wong wrote:
> Hi,
>
> I cannot dropdb. Postgres throws me:
> dropdb: database removal failed: ERROR: database "database1" is being
> accessed by other users
> DETAIL: There are 1 other session(s) using the database.
>
> the "select datname,current_query,query_start from pg_stat_activity ;" query
> gives me:
>
> datname |
> current_query |
> query_start
> -------------------------+--------------------------------------------------------------------------------------------------------------------------+-------------------------------
> database2 | CREATE INDEX location_object_bioseg ON location
> USING gist (locatedonid, bioseg_create(intermine_start, intermine_end)); |
> 2011-08-29 14:43:40.856594-04
> database3 | <IDLE>
> |
> 2011-08-29 15:08:17.469927-04
> database4 | <IDLE>
> |
> 2011-08-29 15:07:09.484543-04
> database3 | <IDLE>
> |
> 2011-08-29 15:07:09.901601-04
> database2 | select datname,current_query,query_start from
> pg_stat_activity ; |
> 2011-08-29 15:12:39.811168-04
> database3 | <IDLE>
> |
> 2011-08-29 15:08:17.476254-04
> database3 | <IDLE>
> |
> 2011-08-29 15:07:10.422579-04
> database4 | <IDLE>
> |
> 2011-08-29 15:07:10.515946-04
> database1 | <IDLE>
> |
> 2011-08-29 15:07:31.423596-04
>
> Is the drop being prevented by the active query from database2?

No, by the IDLE one from database1.

> If not how
> can I kill the <IDLE> query that must be preventing deletion.
>

You have to use the pg_terminate_backend on this connection.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com