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-performance-owner+archive@lists.postgresql.org>)
	id 1ihGGy-0001nI-7Q
	for pgsql-performance@arkaria.postgresql.org; Tue, 17 Dec 2019 17:04:36 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1ihGGw-0001yE-79
	for pgsql-performance@arkaria.postgresql.org; Tue, 17 Dec 2019 17:04:34 +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 <tgl@sss.pgh.pa.us>)
	id 1ihGGv-0001wz-Tn
	for pgsql-performance@lists.postgresql.org; Tue, 17 Dec 2019 17:04:33 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1ihGGo-0003xl-5K
	for pgsql-performance@postgresql.org; Tue, 17 Dec 2019 17:04:32 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id xBHH4KZw005020;
	Tue, 17 Dec 2019 12:04:20 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kaijiang Chen <chenkaijiang@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: weird long time query
In-reply-to: 
 <CAAkGvS8ocfeVFOCsxoAY6suzMkrW+O9twRAQ2c4WrZNbQ=vSpg@mail.gmail.com>
References: 
 <CAAkGvS8ocfeVFOCsxoAY6suzMkrW+O9twRAQ2c4WrZNbQ=vSpg@mail.gmail.com>
Comments: In-reply-to Kaijiang Chen <chenkaijiang@gmail.com>
	message dated "Tue, 17 Dec 2019 10:58:17 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <5018.1576602260.1@sss.pgh.pa.us>
Date: Tue, 17 Dec 2019 12:04:20 -0500
Message-ID: <5019.1576602260@sss.pgh.pa.us>
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

Kaijiang Chen <chenkaijiang@gmail.com> writes:
> 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;

Don't know where you got this query from, but it's wrong for any PG
version more recent than (I think) 9.1.  We don't use "<IDLE>" as an
indicator of idle sessions anymore; rather, those can be identified
by having state = 'idle'.  What's in the query column for such a session
is its last query.

> 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

It's not running.  That was the last query it ran, back in November :-(
You could zap the session with pg_terminate_backend(), but
pg_cancel_backend() is not going to have any effect because there's
no active query.

			regards, tom lane