Thread: Problems with pg_stat_activity view
I develop some tool that tests SQL code, which involves creating (and dropping) a lot of databases.
- 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:
Here is psql log that shows the problem: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');
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)
- 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.
Attachment
mgr inż. Jacek Bzdak <jbzdak@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
mgr inż. Jacek Bzdak <jbzdak@gmail.com> writes:If you look at the definition of the pg_stat_activity view, you'll see
> 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.
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
mgr inż. Jacek Bzdak <jbzdak@gmail.com> writes:
> Maybe just fix pg_stat_activity so it uses LEFT JOIN, to cover such case. I
> guess it shouldnt break any code?
Meh ... that would result in the usename column being possibly NULL,
which I bet applications aren't expecting either. I'm not necessarily
against this, but I'm not sure it'll make things much better.
Anybody else have an opinion?
(Of course, the real fix would be to prevent dropping user IDs that
correspond to any live session, but that's not terribly easy either,
especially if you think about transient settings such as SET ROLE.
Overall I'm not convinced this is a case worth spending a lot of
effort on.)
regards, tom lane
SELECT ..., CASE WHEN u.rolname IS NULL THEN 'pg_user_removed` ELSE u.rolname END, ... FROM ....;
- How to drop connections connecting to database (there are several questions on Stack Overflow regarding this, so it is worthwile )
- Document this behaviour of pg_stat_activity.
mgr inż. Jacek Bzdak <jbzdak@gmail.com> writes:Meh ... that would result in the usename column being possibly NULL,
> Maybe just fix pg_stat_activity so it uses LEFT JOIN, to cover such case. I
> guess it shouldnt break any code?
which I bet applications aren't expecting either. I'm not necessarily
against this, but I'm not sure it'll make things much better.
Anybody else have an opinion?
(Of course, the real fix would be to prevent dropping user IDs that
correspond to any live session, but that's not terribly easy either,
especially if you think about transient settings such as SET ROLE.
Overall I'm not convinced this is a case worth spending a lot of
effort on.)
regards, tom lane
On 11/2/13, 10:50 PM, Tom Lane wrote: > Meh ... that would result in the usename column being possibly NULL, > which I bet applications aren't expecting either. I'm not necessarily > against this, but I'm not sure it'll make things much better. > Anybody else have an opinion? Left join sounds better than accidentally hiding stuff.
Peter Eisentraut <peter_e@gmx.net> wrote:=0A> On 11/2/13, 10:50 PM, Tom Lan= e wrote:=0A>> Meh ... that would result in the usename column being possibl= y=0A>> NULL, which I bet applications aren't expecting either.=C2=A0 I'm no= t=0A>> necessarily against this, but I'm not sure it'll make things=0A>> mu= ch better.=C2=A0 Anybody else have an opinion?=0A>=0A> Left join sounds bet= ter than accidentally hiding stuff.=0A=0A+1=0A=0A--=0AKevin Grittner=0AEDB:= http://www.enterprisedb.com=0AThe Enterprise PostgreSQL Company