From: | Hannes Kühtreiber <h(dot)kuehtreiber(at)synedra(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Logical Replication: SELECT pg_catalog.set_config Statement |
Date: | 2021-06-10 16:06:15 |
Message-ID: | 1d25f81f-2473-f463-a709-837c0ddd4292@synedra.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
--
From | Date | Subject | |
---|---|---|---|
Next Message | Basques, Bob (CI-StPaul) | 2021-06-10 16:08:14 | Re: bottom / top posting |
Previous Message | Hannes Kühtreiber | 2021-06-10 16:06:00 | Re: Logical Replication: SELECT pg_catalog.set_config Statement appears to be hanging |