including pid's for `There are XX other sessions using the database`

On Fri, Aug 19, 2022, at 2:10 PM, Zhihong Yu wrote:

I want to poll the community on whether including proc->pid's in the error message would be useful for troubleshooting.

Such message is only useful for a parameter into a pg_stat_activity query. You
don't need the PID list if you already have the most important information:
database name. I don't think revealing the current session PIDs from the
database you want to drop will buy you anything. It could be a long list and it
does not help you to solve the issue: why wasn't that database removed?

Besides that, if you know that there is a possibility that a connection is
open, you can always use the FORCE option. The old/other alternative is to use
a query like

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'foo';

(possibly combined with a REVOKE CONNECT or pg_hba.conf modification) before
executing DROP DATABASE.

--
Euler Taveira
EDB https://www.enterprisedb.com/

On Fri, Aug 19, 2022 at 9:31 PM Euler Taveira <euler@eulerto.com> wrote:

On Fri, Aug 19, 2022, at 2:10 PM, Zhihong Yu wrote:

I want to poll the community on whether including proc->pid's in the error
message would be useful for troubleshooting.

Such message is only useful for a parameter into a pg_stat_activity query.
You
don't need the PID list if you already have the most important information:
database name. I don't think revealing the current session PIDs from the
database you want to drop will buy you anything. It could be a long list
and it
does not help you to solve the issue: why wasn't that database removed?

Besides that, if you know that there is a possibility that a connection is
open, you can always use the FORCE option. The old/other alternative is to
use
a query like

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname =
'foo';

(possibly combined with a REVOKE CONNECT or pg_hba.conf modification)
before
executing DROP DATABASE.

--
Euler Taveira
EDB https://www.enterprisedb.com/

Thanks for responding.

Since pg_stat_activity shows fewer number of connections compared to the
number revealed in the error message,
I am not sure the above query would terminate all connections for the
database to be dropped.

Hi,

On Sat, Aug 20, 2022 at 02:52:29AM -0700, Zhihong Yu wrote:

On Fri, Aug 19, 2022 at 9:31 PM Euler Taveira <euler@eulerto.com> wrote:

Thanks for responding.

Since pg_stat_activity shows fewer number of connections compared to the
number revealed in the error message,
I am not sure the above query would terminate all connections for the
database to be dropped.

How exactly are you checking pg_stat_activity? If you query that view right
after a failed attempt at dropping a database, there's no guarantee to find the
exact same connections on the target database as client might connect or
disconnect.

If you prevent any further connection by e.g. tweaking the pg_hba.conf then you
have a guarantee that the query will terminate all conflicting connections.
Using the FORCE option is just a simpler way to do it, as dropdb() starts with
preventing any new connection on the target database.

Overall, I agree that adding the list of pid to the message error message
doesn't seem useful.

On Sun, Aug 21, 2022 at 6:39 AM Julien Rouhaud <rjuju123@gmail.com> wrote:

Hi,

On Sat, Aug 20, 2022 at 02:52:29AM -0700, Zhihong Yu wrote:

On Fri, Aug 19, 2022 at 9:31 PM Euler Taveira <euler@eulerto.com> wrote:

Thanks for responding.

Since pg_stat_activity shows fewer number of connections compared to the
number revealed in the error message,
I am not sure the above query would terminate all connections for the
database to be dropped.

How exactly are you checking pg_stat_activity? If you query that view
right
after a failed attempt at dropping a database, there's no guarantee to
find the
exact same connections on the target database as client might connect or
disconnect.

If you prevent any further connection by e.g. tweaking the pg_hba.conf
then you
have a guarantee that the query will terminate all conflicting connections.
Using the FORCE option is just a simpler way to do it, as dropdb() starts
with
preventing any new connection on the target database.

Overall, I agree that adding the list of pid to the message error message
doesn't seem useful.

Thanks for the comments, Euler and Julien.