Thread: including pid's for `There are XX other sessions using the database`
Hi,
Currently errdetail_busy_db() only shows the number of other sessions using the database but doesn't give any detail about them.
For one of the customers,
pg_stat_activity is showing lower number of connections compared to the number revealed in the error message.Looking at CountOtherDBBackends(), it seems proc->pid is available when nbackends is incremented.
I want to poll the community on whether including proc->pid's in the error message would be useful for troubleshooting.
Thanks
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.
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. Youdon'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 thedatabase you want to drop will buy you anything. It could be a long list and itdoes 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 isopen, you can always use the FORCE option. The old/other alternative is to usea query likeSELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'foo';(possibly combined with a REVOKE CONNECT or pg_hba.conf modification) beforeexecuting DROP DATABASE.
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.