I use PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit.
I develop some tool that tests SQL code, which involves creating (and dropping) a lot of databases.
As of today I can reproduce following situation in 100% times:
- A database is created.
- Then I try to drop it, which fails because there is a hanging session (up to this point there is no incorrect behaviour).
Every tutorial seems to give following advice, to issue following statement:
SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = ...
which I did, and still the database wouldn't drop.
After some tinkering I found that:
SELECT * FROM pg_stat_activity WHERE datname = 'drop-me7e2b9b29-12ae-43f9-94c3-1f28cdd0d781';
returns no results ('drop-me7e2b9b29-12ae-43f9-94c3-1f28cdd0d781' being name of the database).
I guess (I have no idea really) that there is some bug in pg_stat_activity view, because if I use function pg_stat_get_activity(NULL::integer) I will get one connection. If I drop this connection using pg_terminate_backend I can drop the database:
select pg_terminate_backend(procpid) from pg_stat_get_activity(NULL::integer) where datid=(SELECT oid from pg_database where datname='drop-me7e2b9b29-12ae-43f9-94c3-1f28cdd0d781');
Here is psql log that shows the problem:
postgres=# SELECT COUNT(*) FROM pg_stat_activity WHERE datname = 'drop-me7e2b9b29-12ae-43f9-94c3-1f28cdd0d781';
count
-------
0
(1 row)
postgres=# DROP DATABASE "drop-me7e2b9b29-12ae-43f9-94c3-1f28cdd0d781";
ERROR: database "drop-me7e2b9b29-12ae-43f9-94c3-1f28cdd0d781" is being accessed by other users
DETAIL: There are 1 other session(s) using the database.
postgres=# SELECT oid from pg_database where datname = 'drop-me7e2b9b29-12ae-43f9-94c3-1f28cdd0d781';
oid
--------
131765
(1 row)
postgres=# SELECT COUNT(*) FROM pg_stat_get_activity(NULL::integer) WHERE datid=131765;
count
-------
1
(1 row)
I also attached html dump of three tables (in this order): pg_stat_get_activity(NULL::integer), pg_database, pg_stat_activity.
I'm not sure where the buggy behavioiur is:
- Either pg_stat_activity does not work as intended (I guess that it might be the case)
- Or you really should document how one should drop database with connections (preferably in the DROP DATABASE document), as every source I found said just do: SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = ..., and if this the incorrect answer it would be good to have correct one in the documentation.
