Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id BAB211337B67 for ; Thu, 21 Oct 2010 15:19:01 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 34065-03-2 for ; Thu, 21 Oct 2010 18:18:53 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 5556C1337B69 for ; Thu, 21 Oct 2010 15:18:53 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o9LIIqK1027774; Thu, 21 Oct 2010 14:18:52 -0400 (EDT) To: Ken Lalonde cc: pgsql-admin@postgresql.org Subject: Re: DROP ROLE: how to detect active sessions? In-reply-to: <61A975CD-61CC-40CF-87CA-46BEEC1CEEAF@gap.ca> References: <61A975CD-61CC-40CF-87CA-46BEEC1CEEAF@gap.ca> Comments: In-reply-to Ken Lalonde message dated "Thu, 21 Oct 2010 14:04:11 -0400" Date: Thu, 21 Oct 2010 14:18:52 -0400 Message-ID: <27773.1287685132@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/162 X-Sequence-Number: 35903 Ken Lalonde writes: > Is there any way to determine if a given role has any active sessions? Not if you're using SET ROLE. pg_stat_activity will show you the login role names for active sessions. Do you really need SET ROLE rather than logging in with the appropriate username? There are going to be race conditions in any case, of course. Probably the best solution is to just not try to drop roles except when the system is idle. regards, tom lane