I was looking into some issues we recently had when dropping db users
and was surprised to see that dropped users' sessions and transactions
continue to work after the role is dropped.
Since dropping a role requires dropping all grants it has (using DROP
OWNED BY ...) the dropped role can't start new transactions that do a
whole lot unless there are objects with access granted to PUBLIC, but
any running transactions remain running and can write to the database.
They can also hold locks which interfere with other backends without
showing up in most activity or lock monitoring tools as they won't
appear in pg_stat_activity.
IMO any open sessions for a dropped user should be automatically
terminated when the role is dropped, but that would probably be a bigger
change so attached a proposed patch for using left joins in
pg_stat_activity and pg_stat_replication to show activity by dropped roles.
/ Oskari
