Action query_time alerts replication process in PostgreSQL 10

[lusor]

PostgreSQL 10 now lists the replication process in pg_stat_activity. This leads to check_postgres --action=query_time finding that process and reporting it as long running query.
Here's an example row of pg_stat_activity:

-[ RECORD 1 ]----+---------------------------------
datid            |
datname          |
pid              | 26414
usesysid         | 17326
usename          | somereplicationuser
application_name | myappname
client_addr      | 12.34.56.78
client_hostname  | host.example.com
client_port      | 35912
backend_start    | 2018-01-31 13:52:49.120219+01
xact_start       |
query_start      |
state_change     | 2018-01-31 13:52:49.13702+01
wait_event_type  | Activity
wait_event       | WalSenderMain
state            | active
backend_xid      |
backend_xmin     |
query            |
backend_type     | walsender

Would it be possible to exclude those processes?

Thanks a lot

[keithf4]

Also running into this issue as well. Looks like maybe the query_time monitor should ignore when backend_type = 'walsender;

[machack666]

Hi guys,

I've pushed d959449 which I believe should fix this issue.

Best,

David

[lusor]

Hey @machack666,

in your commit, you used ">10" which means, $SQL4 will only be used on versions greater then 10, not greater than or equal 10; i.e. starting from PostgreSQL 11.
As run_command only works with '<', '>' or '' (equal), I guess '>10' needs to be replaced by '>9.9' or similar.

Cheers

[machack666]

D'oh, fixed. Thanks for the report.

[jjjefff]

Hi

It seems that your commit still does not work at the following line in "sub check_txn_idle" :

my $info = run_command($SQL, { emptyok => 1 , version => [ "<8.3 $SQL2", ">9.1 $SQL3", ">9.6 $SQL4" ] } );

For Postgresql release >= 10 (I tested with 10.5), I am not a Perl expert but I guess that condition ">9.1 $SQL3" is first checked and verified, then $SQL3 is executed instead of $SQL4

Wherease if the line is :

my $info = run_command($SQL, { emptyok => 1 , version => [ "<8.3 $SQL2", ">9.6 $SQL4", ">9.1 $SQL3" ] } );
Then $SQL4 is executed and it is OK

Could you please confirm and update the code with changing the order of the conditions for the check of Postgresql release ?

Thank you

[machack666]

Hi @jjjefff — thanks for the report, that fix looks good. I also checked for any similar SQL version precedence bugs like this and this appears to be the only issue.

Fixed in fd9702c.