Problems with pg_stat_activity view

Поиск
Список
Период
Сортировка
От mgr inż. Jacek Bzdak
Тема Problems with pg_stat_activity view
Дата
Msg-id CA+FttVMXuF7XbEpp4ekTZGzExLyS13xjbV=2jYk2kFRZX5Xqdw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Problems with pg_stat_activity view
Список pgsql-bugs
Дерево обсуждения
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.
     
Вложения

В списке pgsql-bugs по дате отправления: