search

powa-team / powa-archivist

powa-archivist: the powa PostgreSQL extension
http://powa.readthedocs.io/
PostgreSQL License
51 stars 20 forks source link

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

Closed fatihmtekin closed 3 years ago

fatihmtekin commented 3 years ago

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_statactivity 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_samplingsrc(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 commented 3 years ago

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 https://github.com/powa-team/powa/issues/142.

Could you try to update pg_wait_sampling?

fatihmtekin commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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

fatihmtekin commented 3 years ago

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