powa_wait_sampling_snapshot(0) query is running by active state for a long time

[fatihmtekin]

Firstly, thank you for your help.
Im using PostgreSQL 12.5 on RHEL7.9 with Powa 4.0.1.2 and pg_wait_sampling 1.1.2-1 versions.
The PoWa query is running for more than 30 hours active state in the pg_stat_activity view. Also, added pg_stat_activity view info below.
There is no blocking/blocked transaction in the database. But, when I open the PoWa page I get an error with
"Error while calling public.powa_wait_sampling_src: canceling statement due to lock timeout CONTEXT: PL/pgSQL function powa_wait_sampling_src(integer) line 4 at RETURN QUERY"

datid | 16387
datname | powa
pid | 164496
usesysid | 10
usename | postgres
application_name | PoWA - powa_wait_sampling_snapshot(0)
client_addr |
client_hostname |
client_port |
backend_start | 2021-05-04 15:35:51.532214+03
xact_start | 2021-05-04 15:35:51.535921+03
query_start | 2021-05-04 15:35:51.535921+03
state_change | 2021-05-04 15:35:51.535934+03
wait_event_type | IPC
wait_event | MessageQueueInternal
state | active
backend_xid | 3737818164
backend_xmin | 3734534556
query | SELECT public.powa_take_snapshot()
backend_type | powa

[rjuju]

Hi,

It looks like a long-standing bug in pg_wait_sampling that has been fixed in 1.1.3. See for instance a previous report at powa-team/powa#142.

Could you try to update pg_wait_sampling?

[fatihmtekin]

Thank you for your information. I updated the pg_wait_sampling rpm package with rpm Uvh, then I update the extension in the database with "ALTER EXTENTION pg_wait_sampling UPDATE;", but I got a notice :
"NOTICE: version "1.1" of extension "pg_wait_sampling" is already installed"
Should I restart the database for the update process? I cannot restart the database now, because it is a production system.

[rjuju]

Yes, a restart is always required to apply modification in the C code of an extension in shared_preload_libraries. An ALTER EXTENSION ... UPDATE will only change the SQL definition, but won't reload the shared library.

Let me know once you get a maintenance window to restart the database. For the record the bug you hit isn't critical, as it won't lead to a crash or extra resource consumption. It will only make pg_wait_sampling unavailable until the database is restarted. That's not ideal but usually not critical.

[fatihmtekin]

Ok, thank you again for clear and detailed information. I will let you know after restarting the database.

[fatihmtekin]

Hi again,
I updated the pg_wait_sampling extension and restarted the database. The problem is solved. Thank you for your help.