Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fSPQs-0004in-Ko for pgsql-admin@arkaria.postgresql.org; Mon, 11 Jun 2018 16:12:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fSPQr-0001pB-BZ for pgsql-admin@arkaria.postgresql.org; Mon, 11 Jun 2018 16:12:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fSPQr-0001p0-1h for pgsql-admin@lists.postgresql.org; Mon, 11 Jun 2018 16:12:37 +0000 Received: from mail-ot0-x243.google.com ([2607:f8b0:4003:c0f::243]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fSPQm-00074T-Rv for pgsql-admin@postgresql.org; Mon, 11 Jun 2018 16:12:36 +0000 Received: by mail-ot0-x243.google.com with SMTP id q1-v6so24409114oth.13 for ; Mon, 11 Jun 2018 09:12:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=tpeyyKD+anRk2NypMV0huf1ofml7bDNJcGTLT5UXAkw=; b=kJlyQhVdYrAfcvnJeuFCzFsVYPQbLJUsbeQ2l4LS/EjJ0JovXbowKkRSK/c2LRa7j/ Vk5dfZTRO9+G2xrzbvXfnGgyhy3mV0r00vPJLfHVgbtxVmlts4UzOfqVljeIoOMI9PZd LXEFThJQ9DmZ20EQupAIW+rYv/OM4FddN7uV9d2yUq4GWHEFquL3fvYCtE7VocMju3n6 Lt8o538jCvL8pnNsy2HTpQjpMQtzHACioIh2xT8bnuEHHBOUrkUEtyrKoEG3yGRi9cGz VIr928YxqMLkLOLXe42n9HjZOhpX5kd4X6IqNLTD9UsWB6VkfSR74hiAaVw/wAmmhiYW cECg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=tpeyyKD+anRk2NypMV0huf1ofml7bDNJcGTLT5UXAkw=; b=CHAQa3vlzJlD8Y0uLl8tHPnXWBsK+fBsQ4dC/FXgStGh4gDrCxUZnjzd1ibClFg5Ha YIGGea8D21MNx5dtRdmXZAEpBNdRV4agj1113gzdyqpwvNaBMgATCplTeTp8vXtOWnCs At+NDak9dI7aUFFxtHTqm88yRIv/gAc1bt2fo5tI6ugokEZfYm+OEqPjuaH2VK9Tfk6p XkfAisgbgfmJX0RyOdnvN/Cqdjsv8Tr9mzB8mIt5FO30/lHvEJtHQ/UfcnrrDe2zlJoP dNGNWMQTPB2VNr7KQcsyCf8Zc0EbVdqT3vsvPmGbcSPdZmKmzZfH4pxN8+0NPPnw/FXa ZUJA== X-Gm-Message-State: APt69E3PFvU5BmC+1Ai05fsUEmDDkJw4QIpt+ClSsZltiFYxpV3IZNTV Oy1FWdLBtYhJA7ZCVmSztTGu6w== X-Google-Smtp-Source: ADUXVKLWwZm2CcMA9ER697VNIzB2ZJt0ShBOA2gJl0CR1hcvs0GAp1wYQTsXbRLfl2Nqi6vKdF8xUg== X-Received: by 2002:a9d:2d72:: with SMTP id v105-v6mr10842379ota.227.1528733550477; Mon, 11 Jun 2018 09:12:30 -0700 (PDT) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id a76-v6sm13265290oii.37.2018.06.11.09.12.28 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 11 Jun 2018 09:12:29 -0700 (PDT) Subject: Re: pg_stat_activity To: pgsql-admin References: <4405957b-2e0f-bfc5-eaf6-5e2864da2796@gmail.com> <9884a24a-c129-a1a9-1901-2a9c8987331e@gmail.com> <211b19ea-18dc-6897-3c53-8d994550cd15@gmail.com> From: Ron Message-ID: <6ef6f956-595e-d0c4-36bd-dd9d10772b11@gmail.com> Date: Mon, 11 Jun 2018 11:12:28 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.8.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------AFA9EF6618BCBEF3CEB1808C" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------AFA9EF6618BCBEF3CEB1808C Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 06/11/2018 10:21 AM, David G. Johnston wrote: > On Mon, Jun 11, 2018 at 8:07 AM, Ron >wrote: > > That won't work, because that would catch any query on a table which > happens to have the string "select" in a table, index or column name. > > > ​I don't believe what you are asking is possible using only > pg_stat_activity.  One thought that comes to mind is to identify, via > pg_locks, sessions that hold non-read-only locks and then exclude the rows > in pg_stat_activity that match up with those sessions. > > David J. > ​ Thanks.  I'll try that. -- Angular momentum makes the world go 'round. --------------AFA9EF6618BCBEF3CEB1808C Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 06/11/2018 10:21 AM, David G. Johnston wrote:
On Mon, Jun 11, 2018 at 8:07 AM, Ron <ronljohnsonjr@gmail.com> wrote:
That won't work, because that would catch any query on a table which happens to have the string "select" in a table, index or column name.

​I don't believe what you are asking is possible using only pg_stat_activity.  One thought that comes to mind is to identify, via pg_locks, sessions that hold non-read-only locks and then exclude the rows in pg_stat_activity that match up with those sessions.

David J.
​

Thanks.  I'll try that.

--
Angular momentum makes the world go 'round.
--------------AFA9EF6618BCBEF3CEB1808C--