pg_profile parameters are not visible

[nikhil-postgres]

PostgresSQL: 15.6 pg_profile: 4.6

I have installed the extension and I am able to take samples but I dont see the parameters associated with pg_profile.

Parameter name pg_profile.topn pg_profile.max_sample_age pg_profile.track_sample_timings pg_profile.max_query_length

postgres=# select * from pg_settings where name like '%pg_profile%';
 name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart 
------+---------+------+----------+------------+------------+---------+---------+--------+---------+---------+----------+----------+-----------+------------+------------+-----------------
(0 rows)

postgres=# select * from pg_settings where name like '%max_sample_age%';
 name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart 
------+---------+------+----------+------------+------------+---------+---------+--------+---------+---------+----------+----------+-----------+------------+------------+-----------------
(0 rows)

postgres=# \dx pg_profile 
                             List of installed extensions
    Name    | Version | Schema |                      Description                      
------------+---------+--------+-------------------------------------------------------
 pg_profile | 4.6     | public | PostgreSQL load profile repository and report builder
(1 row)

show command also does not show anything

postgres=# show pg_profile.track_sample_timings;
ERROR:  unrecognized configuration parameter "pg_profile.track_sample_timings"
postgres=# show pg_profile.max_query_length;
ERROR:  unrecognized configuration parameter "pg_profile.max_query_length"
postgres=# 

[zubkov-andrei]

Hello!

pg_profile is pl/pgsql extension so it is unable to defilne its own settings in postgres. This is why you don't see them in pg_settings view. Use the current_setting() function instead.

[nikhil-postgres]

Hi ,

It is not working with current_setting. Is there any other way to check ?

postgres=# select current_setting('pg_profile.track_sample_timings');
ERROR:  unrecognized configuration parameter "pg_profile.track_sample_timings"
postgres=# 

[nikhil-postgres]

One more thing apart from above issue is that below function is not working as expected

postgres=# select set_server_subsampling('local',true,'3s','3s',10000,'3s');
ERROR:  column "min_wait_dur" of relation "server_subsample" does not exist
LINE 8:       min_wait_dur
              ^
QUERY:  INSERT INTO server_subsample(
      server_id,
      subsample_enabled,
      min_query_dur,
      min_xact_dur,
      min_xact_age,
      min_idle_xact_dur,
      min_wait_dur
    )

Looks like this is a known issue - https://github.com/zubkov-andrei/pg_profile/issues/92 Is it fixed in 4.6? I took the package from the postgres repository.

[nikhil-postgres]

Hi @zubkov-andrei , I still cannot check the pg_profile settings and also there seems to be a bug in set_server_subsampling. Could you please help

[nikhil-postgres]

I am able to see the parameters now after adding it in postgresql.conf. Bug in set_server_subsampling is still an issue

[zubkov-andrei]

It seems set_server_subsampling was fixed in 4.6.

[nikhil-postgres]

I am using version 4.6