pg_wait_sampling_get_profile() invalid memory alloc request

[aleszeleny]

One of several Db server instances returns the following error:

powa=# SELECT * FROM pg_wait_sampling_get_profile();
ERROR:  invalid memory alloc request size 1194769944

Instance and extension information:

powa=# select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20231218 (Red Hat 11.4.1-3), 64-bit
(1 row)

powa=# \dx pg_wait_sampling
                          List of installed extensions
       Name       | Version | Schema |               Description                
------------------+---------+--------+------------------------------------------
 pg_wait_sampling | 1.1     | public | sampling based statistics of wait events
(1 row)

powa=# show shared_buffers ;
 shared_buffers 
----------------
 10GB
(1 row)

OS seem to have enough RAM available:

free -h
               total        used        free      shared  buff/cache   available
Mem:            30Gi        15Gi       2.1Gi       393Mi        13Gi        14Gi
Swap:             0B          0B          0B

All the packages were installed from RPMs (AlmaLinux9):

cat /etc/almalinux-release 
AlmaLinux release 9.3 (Shamrock Pampas Cat)

rpm -qa | grep -e postgresql -e wait_sampling
postgresql16-libs-16.3-1PGDG.rhel9.x86_64
postgresql16-debugsource-16.3-1PGDG.rhel9.x86_64
postgresql16-16.3-1PGDG.rhel9.x86_64
postgresql16-server-16.3-1PGDG.rhel9.x86_64
postgresql16-contrib-16.3-1PGDG.rhel9.x86_64
postgresql16-llvmjit-16.3-1PGDG.rhel9.x86_64
postgresql16-plpython3-16.3-1PGDG.rhel9.x86_64
postgresql16-debuginfo-16.3-1PGDG.rhel9.x86_64
pg_wait_sampling_16-1.1.6-1PGDG.rhel9.x86_64

Is there something more I can provide (set some logging settings) to provide more details to diagnose the issue?

Kind regards Ales

[Medvecrab]

Hello!

Can you please provide uptime of your server, even an approximaton will do. We suspect that you have a LOT of data in your profile and might have to reset it using pg_wait_sampling_reset_profile() function

[aleszeleny]

Sure, it runs for quite a while: `

powa=# select now() - pg_postmaster_start_time();
         ?column?         
--------------------------
 146 days 09:03:54.092795
(1 row)

[aleszeleny]

Thanks a lot, the call of pg_wait_sampling_reset_profile() resolved the issue!

[Medvecrab]

@aleszeleny, I've recieved additional hints for your situation

It seems that you could be using POWA. In their docs it is said to set pg_wait_sampling.profile_pid to false. Please check if that is the case.

If pg_wait_sampling.profile_pid is now set to true but you are sure that you have set it to false as recomended, we advise you to upgrade from pg_wait_sampling 1.1.6 to pg_wait_sampling 1.1.8 where we have fixed a problem of GUC values resetting to default values (was also found thanks to POWA). You can read about this issue in #85 and #86

[aleszeleny]

Thanks for the update! Yes, we are using POWA.
The pg_wait_sampling.profile_pid = on so, I'll update our configuration to off as advised.