Re: Show dropped users' backends in pg_stat_activity

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>, Oskari Saarenmaa <os(at)ohmu(dot)fi>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Show dropped users' backends in pg_stat_activity
Date: 2016-03-25 02:33:47
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

* 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.



In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2016-03-25 03:27:36 Re: Alter or rename enum value
Previous Message Jim Nasby 2016-03-25 02:30:00 Re: avg,first,last,median in one query