Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1ihZyH-00064i-SK
	for pgsql-bugs@arkaria.postgresql.org; Wed, 18 Dec 2019 14:06:38 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1ihZyG-0000MX-HV
	for pgsql-bugs@arkaria.postgresql.org; Wed, 18 Dec 2019 14:06:36 +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_SHA1:256)
	(Exim 4.89)
	(envelope-from <depesz@depesz.com>)
	id 1ihZyG-0000MQ-6c
	for pgsql-bugs@lists.postgresql.org; Wed, 18 Dec 2019 14:06:36 +0000
Received: from lana.depesz.com ([88.198.49.178] helo=depesz.com)
	by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <depesz@depesz.com>)
	id 1ihZyD-0000bd-38
	for pgsql-bugs@postgresql.org; Wed, 18 Dec 2019 14:06:35 +0000
DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=depesz.com;
	 s=20170201; h=In-Reply-To:Content-Type:MIME-Version:References:Reply-To:
	Message-ID:Subject:Cc:To:Sender:From:Date:Content-Transfer-Encoding:
	Content-ID:Content-Description:Resent-Date:Resent-From:Resent-Sender:
	Resent-To:Resent-Cc:Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:
	List-Subscribe:List-Post:List-Owner:List-Archive;
	bh=z4myWLCYirtJyrg5psK+NM8MGr6HcS7V5KEE+d6s3iM=; b=VsX7HNZSF1wZC6mgl3fa0dFfXJ
	rOO2Ajr6TIc4j1X65YevWs+r/qqZzcltWZohhK2+DtIy/XzgUr0zBx9KqK3wZ1S3jP7Ib3Vl7tB4K
	OFB177NArIXK1hEZrJ7XK6LeR35mIl2ICoCtky54zxLRGGrXmbce+OB11h8klqIF54uE=;
Received: from lana.depesz.com ([88.198.49.178] helo=depesz.com)
	by depesz.com with esmtpa (Exim 4.92)
	(envelope-from <depesz@depesz.com>)
	id 1ihZyB-0004f3-GK; Wed, 18 Dec 2019 15:06:31 +0100
Date: Wed, 18 Dec 2019 15:06:31 +0100
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: Kaijiang Chen <chenkaijiang@gmail.com>
Cc: Pg Bugs <pgsql-bugs@postgresql.org>
Subject: Re: Fwd: weird long time query
Message-ID: <20191218140631.GA1083@depesz.com>
Reply-To: depesz@depesz.com
References: 
 <CAAkGvS8ocfeVFOCsxoAY6suzMkrW+O9twRAQ2c4WrZNbQ=vSpg@mail.gmail.com>
 <CAAkGvS_FSZf=uzTwWbTnRe7hXruga_opSeCPDueNR09v9Ow47Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: 
 <CAAkGvS_FSZf=uzTwWbTnRe7hXruga_opSeCPDueNR09v9Ow47Q@mail.gmail.com>
User-Agent: Mutt/1.10.1 (2018-07-13)
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Precedence: bulk

On Wed, Dec 18, 2019 at 11:25:32AM +0800, Kaijiang Chen wrote:
> I'm using postgres 9.4.17 on centos 7.
> I check the running queries with the following SQL:
> SELECT
>     procpid,
>     start,
>     now() - start AS lap,
>     current_query
> FROM
>     (SELECT
>         backendid,
>         pg_stat_get_backend_pid(S.backendid) AS procpid,
>         pg_stat_get_backend_activity_start(S.backendid) AS start,
>         pg_stat_get_backend_activity(S.backendid) AS current_query
>     FROM
>         (SELECT pg_stat_get_backend_idset() AS backendid) AS S
>     ) AS S
> WHERE
>     current_query <> '<IDLE>'
> ORDER BY
>     lap DESC;
> 
> Then, I found a SQL that has run for some days (and still running):
> procpid       | 32638
> start         | 2019-11-25 16:29:29.529318+08
> lap           | 21 days 18:24:54.707369
> current_query | DEALLOCATE pdo_stmt_00000388
> 
> I tried to kill it with: SELECT pg_cancel_backend(32638) but it takes no
> effects.
> 
> What's this query and what shall I do for it?
> 
> I think it is a bug since logically, this query should be gone.

It's not a bug. Most likely this backend is not doing anything.

You're using old way to check if backend is working - current_query <>
'<IDLE>';

Check: select * from pg_stat_activity where pid = 32638 

Most likely you'll see state = 'idle'

In such cases, query just shows last executed query, not currently
running one.

Also - WHY are you calling internal pg* functions directly, instead of
using pg_stat_activity view?

Best regards,

depesz