Problems with pg_stat_activity view
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.
Attachments:
bug.htmltext/html; charset=US-ASCII; name=bug.htmlDownload
=?ISO-8859-2?Q?mgr_in=BF=2E_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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
I've checked my code and due other bug(s) indeed I had hangoing connections
from already dropped users.
Maybe just fix pg_stat_activity so it uses LEFT JOIN, to cover such case. I
guess it shouldnt break any code?
jb:)
2013/11/2 Tom Lane <tgl@sss.pgh.pa.us>
Show quoted text
=?ISO-8859-2?Q?mgr_in=BF=2E_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
Import Notes
Reply to msg id not found: CA+FttVMjYff4irszrkMooW2bidvBm+txC7wkOh_3Uy-LYYgvMg@mail.gmail.com
=?ISO-8859-2?Q?mgr_in=BF=2E_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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Hi,
I think that this bug would affect at least couple of other people, but I
agree it is not worth to change deep internal mechanisms just for it.
Maybe changing pg_stat_activity would break existing code, but only for
edge case where this code already behaves incorrectly (by missing existing
connections) --- for example pgAdminIII won't show connections in this
case!
Also using other query wouldn't introduce NULL columns to this view:
SELECT ..., CASE WHEN u.rolname IS NULL THEN 'pg_user_removed` ELSE
u.rolname END, ... FROM ....;
This would break existing code only if they use this particular username,
which is not probable.
Other option I see is just to document two things:
1. How to drop connections connecting to database (there are several
questions on Stack Overflow regarding this, so it is worthwile )
2. Document this behaviour of pg_stat_activity.
I guess I could try to contribute these changes, but I'm not qualified to
decide whether they make sense.
PS. Sorry to be so persistent, but I spent this Friday afternoon and better
part of night chasing this bug!
jb:)
2013/11/3 Tom Lane <tgl@sss.pgh.pa.us>
Show quoted text
=?ISO-8859-2?Q?mgr_in=BF=2E_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
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.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Peter Eisentraut <peter_e@gmx.net> wrote:
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.
+1
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs