Search Postgresql Archives

Re: Logical Replication: SELECT pg_catalog.set_config Statement

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello Jeremy,

thanks for your input (and sorry for the delay).

for our monitoring we query like this

SELECT EXTRACT(epoch FROM (LOCALTIMESTAMP - pg_stat_activity.query_start))::integer AS age
FROM pg_stat_activity
WHERE pg_stat_activity.state = 'active' AND query NOT LIKE 'autovacuum:%'
ORDER BY pg_stat_activity.query_start ASC
LIMIT 1

but we stumble over the query nontheless, its state being active

+---------+---------------------------------------------------------+
| Zustand | Laufende Abfrage                                        |
+---------+---------------------------------------------------------+
| active  | SELECT pg_catalog.set_config('search_path', '', false); |
+---------+---------------------------------------------------------+


is there another good way to exclude it?

regards

Hannes


Am 18.05.2021 um 17:52 schrieb Jeremy Smith:




We found out because we are monitoring long running queries, and saw it had been running for a month before the restart yesterday.
I just queried pg_stat_activity and it seems to be running since then.

taimusz=# SELECT pid, query_start, usename, left(query,70)
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start;
   pid   |          query_start          |  usename   |                                  left
---------+-------------------------------+------------+------------------------------------------------------------------------
 2321161 | 2021-05-17 16:15:13.906679+02 | subscriber | SELECT pg_catalog.set_config('search_path', '', false);



You should add: AND state != 'idle' to filter out queries that are no longer running and don't have an open transaction.  Your query is finding long running sessions, not necessarily long running queries. 
--








[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux