From: Ken Lalonde <kenl@gap.ca>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Subject: DROP ROLE: how to detect active sessions?
Date: Thu, 21 Oct 2010 14:04:11 -0400
Message-Id: <61A975CD-61CC-40CF-87CA-46BEEC1CEEAF@gap.ca>
To: pgsql-admin@postgresql.org
Mime-Version: 1.0 (Apple Message framework v1081)

I need to track db activity by role.   All web-related connections to =
the db use the same user name.
When a user logs in via the web, the application code runs:
	SET ROLE "n"
where n is the ID value for the user in the "users" table.
(These numeric roles are created dynamically as needed).
We can then use the value of "current_user" to track activity.

So far so good.

The problem happens when we periodically drop roles for inactive users.
If I run DROP ROLE "n", and there happens to be an active session for =
that role,
we have a problem: in that session, "select current_user" fails with =
"invalid role OID: nnnn".

Is there any way to determine if a given role has any active sessions?

Thanks a bunch,

Ken Lalonde
Gap Adventures, Toronto=