[BUGS] Problems with pg_stat_activity view

[mgr inż. Jacek Bzdak]

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.
   

[Tom Lane]

=?ISO-8859-2?Q?mgr_in=BF=2E_Jacek_Bzdak?= <jbz...@gmail.com> writes:
> 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 you look at the definition of the pg_stat_activity view, you'll see
it's just a join of pg_stat_get_activity's output against pg_database and
pg_authid. It sounds like you had a row that had failed to join against
pg_authid --- maybe you dropped a user that still had a running session?

regards, tom lane


--
Sent via pgsql-bugs mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs