Missing Application ID string column for pg_stat_kcache_detail view

[devizer]

Can we have application id string column for pg_stat_kcache_detail view?

P.S. we need the details view bound to a logical block of code on the application side. That is the reason.

[rjuju]

Hi,

Are you talking about the application_name setting? This is a dynamic setting that can be changed at any time (I think it could even change during a query execution), so the only way to store it would be to make it part of the key, like the queryid. Unfortunately it means that it could highly bloat the list of entries if this value is changed dynamically, and also increase the memory consumption by 64B per entry. It seems like a quite high price to pay.

[devizer]

application_name setting

In terms of .NET Client it is the "Application Name" field:

var connectionString = "Host=dbserver;Port=5432;Database=WebStore;Application Name=\"FooWeb WebApi Backend {unique id}\"";
var connection = new NpgsqlConnection(connectionString);

The idea is to use a unique Application Name for an invocation of block of code and Select + Delete at the end of such block
Select * From pg_stat_kcache_detail where Application_Name = 'Foo WebApi {unique id}'
Delete From pg_stat_kcache_detail where Application_Name = 'Foo WebApi {unique id}'

Both MongoDB and MS SQL Server provide such column.
By the way, such functionality as pg_stat_kcache_detail is a built-in feature of MongoDB and MS SQL Server instead of an extension.

P.S. Regarding the mutability of the application name we do not want to change it ever.

[rjuju]

The idea is to use a unique Application Name for an invocation of block of code and Select + Delete at the end of such block

I'm not sure what you mean. Are you going to use a different connection (with a different application_name) for each block of code?

Select * From pg_stat_kcache_detail where Application_Name = 'Foo WebApi {unique id}'
Delete From pg_stat_kcache_detail where Application_Name = 'Foo WebApi {unique id}'

Note that you can't delete from pg_stat_kcache_detail.

P.S. Regarding the mutability of the application name we do not want to change it ever.

Whether or not you want to change it doesn't change the fact that many people already do so already, which could lead to performance issue with this extension. Similarly, the extra memory-consumption isn't great for the many people who use this extension and don't need this information.

[devizer]

Note that you can't delete from pg_stat_kcache_detail.

The bad design is another issue and it is out of the scope of the issue.
Regarding deletion my strong recommendation is to review two proven solutions:
On MongoDB: https://www.mongodb.com/docs/manual/tutorial/manage-the-database-profiler/ https://www.mongodb.com/docs/manual/core/capped-collections/
On Sql Server: sp_trace_create

Let's focus on the missing application name column.

Unfortunately it means that it could highly bloat the list of entries if this value is changed dynamically, and also increase the memory consumption by 64B per entry. It seems like a quite high price to pay.

Additional memory for the additional column is only the insurmountable hurdle?

Whether or not you want to change it doesn't change the fact that many people already do so already, which could lead to performance issue with this extension. Similarly, the extra memory-consumption isn't great for the many people who use this extension and don't need this information.

We can have either a single detail row with the initial application_name or multiple detail rows. Depends on needs.
Regarding extra memory consumption, can't we switch off storage for some columns depending on the necessity of the columns?

I'm not sure what you mean. Are you going to use a different connection (with a different application_name) for each block of code?

Nope. I'm going to have just application name column with pg_stat_kcache_detail view. Let's focus on the missing application name column again.

I already shared to you links for SQL Server and MongoDB with proven solution of the memory consumption and performance. What is the problem to use additional memory for the additional column with pg_stat_kcache_detail? Do you think postgres is not good enough to have application_name with the detail view? Do you think only Mongo DB and MS SQL Server are pretty perfect for application_name column?

[rjuju]

I know nothing about mongodb and those links don't seem even related to what you're asking. The SQL server link is in russian so I can't do anything with it.

Other than that, I'm not happy at all with your tone so I'm not going to keep discussing with you. This tool is open source so you're free to fork it and implement yourself any feature you want.