PostgreSQL replication is displayed as long query in PostgreSQL 14

[okbob]

Describe the bug
PostgreSQL 14 shows replication's slots in pg_stat_activity - like START_REPLICATION SLOT "readonly02" 6DA6/E9000000 TIMELINE 1. The state is active. The column backend_type = walsender can be used for filtering.

To Reproduce
Steps to reproduce the behavior:

1. check munin against PostgreSQL 14
2. start replication by using replication slot
3. check pg_stat_activity table

Expected behavior
This command is permanent, and I don't want to see this command in munin long queries

Desktop (please complete the following information):

• Linux, Fedora 36, Firefox
• This page was generated by Munin version 2.0.66 at 2022-04-06 06:50:21+0200 (CEST).

[sumpfralle]

Thanks for reporting this isse!

Could you prepare a diff for this plugin, which would fix this issue for your?

[okbob]

I am not an expert on Munin or perl, so it should be rechecked. The column backend_type exists from pg 10. Diff against file postgres_querylength_

<         "SELECT 'query',COALESCE(max(extract(epoch FROM CURRENT_TIMESTAMP-query_start)),0) FROM pg_stat_activity WHERE state NOT LIKE 'idle%' %%FILTER%%
---
>         "SELECT 'query',COALESCE(max(extract(epoch FROM CURRENT_TIMESTAMP-query_start)),0) FROM pg_stat_activity WHERE backend_type = 'client backend' and state NOT LIKE 'idle%' %%FILTER%%

[sumpfralle]

Thank you for preparing the diff!

I created a pull request for this change (#1466).

It would be great, if you could download the adjusted plugin file, fix the shebang line (the first one in the file) from @@PERL@@ to /usr/bin/perl (this substitution is usually part of the build process) and test, whether this updated plugin works for you.

My change should lead to the new query being used for all versions of the postgresql server starting from 10.0. I guess, this is correct?

[okbob]

čt 7. 4. 2022 v 14:44 odesílatel Lars Kruse ***@***.***> napsal:

Thank you for preparing the diff! I created a pull request for this change (#1466 <#1466>). It would be great, if you could download the adjusted plugin file <https://github.com/sumpfralle/munin/raw/82b83f3381c416c41039376e1355453937e85e00/plugins/node.d/postgres_querylength_.in>, fix the shebang line (the first one in the file) from @@Perl@@ to /usr/bin/perl (this substitution is usually part of the build process) and test, whether this updated plugin works for you. My change should lead to the new query being used for all versions of the postgresql server starting from 10.0. I guess, this is correct?

there is not any specification so this change should be applied for pg 10 and higher, so or I don't see it

…

— Reply to this email directly, view it on GitHub <#1465 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEFO42ZZ3HGGBBZQWN2K33VD3KA5ANCNFSM5SUY7KBQ> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[sumpfralle]

there is not any specification so this change should be applied for pg 10 and higher, so or I don't see it

I understand the version specifications as "use this query, if the version is equal or below ...".
In order to avoid any potential confusion or mistakes, I would be happy, if you could test the plugin.

[sumpfralle]

@okbob: where you able to test my proposal?

[okbob]

pá 22. 4. 2022 v 12:56 odesílatel Lars Kruse ***@***.***> napsal:

@okbob <https://github.com/okbob>: where you able to test my proposal?

I am not able to test it. I'll ask to my colleague next week

…

— Reply to this email directly, view it on GitHub <#1465 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEFO424XK4XI4QGODRDBDLVGKAV7ANCNFSM5SUY7KBQ> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[ingvarha]

Any news on this bug? We found the same in one of our setups, with Ubuntu 20.04, postgresql-13, munin-2.0.56. @okbob's patch works for us.