Re: Show dropped users' backends in pg_stat_activity

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > I am not really in favor of half-fixing this. If we can't
> > conveniently wait until a dropped role is completely out of the
> > system, then I don't see a lot of point in trying to do it in the
> > limited cases where we can. If LEFT JOIN is the way to go, then,
> > blech, but, so be it.
>
> I concur. Let's put the left join(s) into those views and call it
> good.

I'd suggest we also add some notes to the documentation that the correct
approach to dropping users is to disallow access first, then kill any
existing backends, and then drop the user. That, plus the left joins,
seems like it's good enough.

> BTW, I think we would need the left joins even if we had interlocking
> in DROP, just to protect ourselves against race conditions. Remember
> that what pg_stat_activity shows is a snapshot, which might be more or
> less out of date compared to the catalog contents.

True, though that would likely be a much smaller set of cases that might
also be short lived.

Might be good to also note in the docs how to kill off sessions which
are regular users but which no longer have a username, for folks who end
up in this situation that they managed to drop a role which still had
connections to the system.

Thanks!

Stephen