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

On Fri, Aug 19, 2022 at 9:31 PM Euler Taveira <euler(at)eulerto(dot)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.