Skip to content

Commit 448585d

Browse files
vbilopavvbilopav
committed
add better interval and logging to stat queries
1 parent 74b27a8 commit 448585d

3 files changed

Lines changed: 65 additions & 23 deletions

File tree

‎NpgsqlRestClient/StatsEndpoints.cs‎

Lines changed: 63 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,7 @@
33
using Microsoft.AspNetCore.Http;
44
using Microsoft.Extensions.Logging;
55
using Npgsql;
6+
using NpgsqlRest;
67

78
namespace NpgsqlRestClient;
89

@@ -15,7 +16,7 @@ public static class StatsEndpoints
1516
private const string RoutinesQuery = """
1617
select
1718
funcid as oid,
18-
a.schemaname as schema,
19+
schemaname as schema,
1920
b.proname as name,
2021
case
2122
when b.prokind = 'f' then 'function'
@@ -25,11 +26,13 @@ public static class StatsEndpoints
2526
else b.prokind::text
2627
end || ' ' || funcid::regprocedure as signature,
2728
calls,
28-
total_time as total_time_ms,
29-
self_time as self_time_ms
29+
ltrim(justify_interval(total_time * interval '1 ms')::text, '@ ') as total_time,
30+
ltrim(justify_interval(self_time * interval '1 ms')::text, '@ ') as self_time
3031
from pg_stat_user_functions a join pg_proc b on a.funcid = b.oid
31-
where $1 is null or a.schemaname similar to $1
32-
order by a.schemaname, b.proname
32+
where $1 is null or schemaname similar to $1
33+
order by
34+
a.schemaname,
35+
b.proname
3336
""";
3437

3538
private const string TablesQuery = """
@@ -86,10 +89,10 @@ from pg_stat_user_indexes s
8689
state,
8790
wait_event_type,
8891
wait_event,
89-
now() - state_change as state_duration,
90-
now() - backend_start as connection_age,
91-
now() - xact_start as transaction_duration,
92-
now() - query_start as query_duration,
92+
ltrim(justify_interval(now() - state_change)::text, '@ ') as state_duration,
93+
ltrim(justify_interval(now() - backend_start)::text, '@ ') as connection_age,
94+
ltrim(justify_interval(now() - xact_start)::text, '@ ') as transaction_duration,
95+
ltrim(justify_interval(now() - query_start)::text, '@ ') as query_duration,
9396
left(query, 100) as query_preview,
9497
query
9598
from pg_stat_activity
@@ -102,54 +105,89 @@ order by state_duration desc nulls last
102105
/// </summary>
103106
public static async Task HandleRoutinesStats(HttpContext context, string connectionString, string outputFormat, string? schemaSimilarTo, ILogger? logger)
104107
{
105-
await ExecuteQueryAndRespond(context, connectionString, RoutinesQuery, outputFormat, schemaSimilarTo, logger);
108+
await ExecuteQueryAndRespond(context, connectionString, RoutinesQuery, outputFormat, schemaSimilarTo, logger, "Stats.Routines", setupCommands: ["set local intervalstyle = 'postgres_verbose'"]);
106109
}
107110

108111
/// <summary>
109112
/// Returns statistics for user tables including size, tuple counts, and vacuum info.
110113
/// </summary>
111114
public static async Task HandleTablesStats(HttpContext context, string connectionString, string outputFormat, string? schemaSimilarTo, ILogger? logger)
112115
{
113-
await ExecuteQueryAndRespond(context, connectionString, TablesQuery, outputFormat, schemaSimilarTo, logger);
116+
await ExecuteQueryAndRespond(context, connectionString, TablesQuery, outputFormat, schemaSimilarTo, logger, "Stats.Tables");
114117
}
115118

116119
/// <summary>
117120
/// Returns statistics for user indexes including scan counts and definitions.
118121
/// </summary>
119122
public static async Task HandleIndexesStats(HttpContext context, string connectionString, string outputFormat, string? schemaSimilarTo, ILogger? logger)
120123
{
121-
await ExecuteQueryAndRespond(context, connectionString, IndexesQuery, outputFormat, schemaSimilarTo, logger);
124+
await ExecuteQueryAndRespond(context, connectionString, IndexesQuery, outputFormat, schemaSimilarTo, logger, "Stats.Indexes");
122125
}
123126

124127
/// <summary>
125128
/// Returns current database activity from pg_stat_activity.
126129
/// </summary>
127130
public static async Task HandleActivityStats(HttpContext context, string connectionString, string outputFormat, ILogger? logger)
128131
{
129-
await ExecuteQueryAndRespond(context, connectionString, ActivityQuery, outputFormat, null, logger);
132+
await ExecuteQueryAndRespond(context, connectionString, ActivityQuery, outputFormat, null, logger, "Stats.Activity", setupCommands: ["set local intervalstyle = 'postgres_verbose'"]);
130133
}
131134

132-
private static async Task ExecuteQueryAndRespond(HttpContext context, string connectionString, string query, string outputFormat, string? schemaSimilarTo, ILogger? logger)
135+
private static async Task ExecuteQueryAndRespond(
136+
HttpContext context,
137+
string connectionString,
138+
string query, string outputFormat,
139+
string? schemaSimilarTo,
140+
ILogger? logger,
141+
string logName,
142+
string[]? setupCommands = null)
133143
{
134144
try
135145
{
136146
await using var connection = new NpgsqlConnection(connectionString);
137147
await connection.OpenAsync(context.RequestAborted);
138148

139-
await using var command = new NpgsqlCommand(query, connection);
140-
if (query.Contains("$1"))
149+
// If setup commands are provided, wrap in a transaction with rollback
150+
if (setupCommands is { Length: > 0 })
141151
{
142-
command.Parameters.Add(new NpgsqlParameter { Value = schemaSimilarTo ?? (object)DBNull.Value, NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Text });
152+
await using var beginCmd = new NpgsqlCommand("begin", connection);
153+
CommandLogger.LogCommand(beginCmd, logger, logName);
154+
await beginCmd.ExecuteNonQueryAsync(context.RequestAborted);
155+
156+
foreach (var setupSql in setupCommands)
157+
{
158+
await using var setupCmd = new NpgsqlCommand(setupSql, connection);
159+
CommandLogger.LogCommand(setupCmd, logger, logName);
160+
await setupCmd.ExecuteNonQueryAsync(context.RequestAborted);
161+
}
143162
}
144-
await using var reader = await command.ExecuteReaderAsync(context.RequestAborted);
145163

146-
if (string.Equals(outputFormat, "html", StringComparison.OrdinalIgnoreCase))
164+
try
147165
{
148-
await WriteAsHtml(context, reader);
166+
await using var command = new NpgsqlCommand(query, connection);
167+
if (query.Contains("$1"))
168+
{
169+
command.Parameters.Add(new NpgsqlParameter { Value = schemaSimilarTo ?? (object)DBNull.Value, NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Text });
170+
}
171+
CommandLogger.LogCommand(command, logger, logName);
172+
await using var reader = await command.ExecuteReaderAsync(context.RequestAborted);
173+
174+
if (string.Equals(outputFormat, "html", StringComparison.OrdinalIgnoreCase))
175+
{
176+
await WriteAsHtml(context, reader);
177+
}
178+
else
179+
{
180+
await WriteAsJson(context, reader);
181+
}
149182
}
150-
else
183+
finally
151184
{
152-
await WriteAsJson(context, reader);
185+
if (setupCommands is { Length: > 0 })
186+
{
187+
await using var rollbackCmd = new NpgsqlCommand("rollback", connection);
188+
CommandLogger.LogCommand(rollbackCmd, logger, logName);
189+
await rollbackCmd.ExecuteNonQueryAsync(CancellationToken.None);
190+
}
153191
}
154192
}
155193
catch (OperationCanceledException)
@@ -286,7 +324,9 @@ private static async Task WriteAsHtml(HttpContext context, NpgsqlDataReader read
286324
context.Response.ContentType = "text/html; charset=utf-8";
287325

288326
var sb = new StringBuilder();
289-
sb.AppendLine("<table style=\"font-family: Calibri, Arial, sans-serif; font-size: 11pt;\">");
327+
sb.AppendLine(
328+
"<style>table{font-family:Calibri,Arial,sans-serif;font-size:11pt;border-collapse:collapse}th,td{border:1px solid #d4d4d4;padding:4px 8px}th{background-color:#f5f5f5;font-weight:600}</style>");
329+
sb.AppendLine("<table>");
290330

291331
// Write header row
292332
sb.Append("<tr>");

‎NpgsqlRestClient/appsettings.json‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1319,6 +1319,7 @@
13191319
// Returns data from pg_stat_user_functions including call counts and execution times.
13201320
// Note: Requires track_functions = 'pl' or 'all' in postgresql.conf.
13211321
// Enable with: alter system set track_functions = 'all'; select pg_reload_conf();
1322+
// Or set track_functions = 'all' directly in postgresql.conf and restart/reload.
13221323
//
13231324
"RoutinesStatsPath": "/stats/routines",
13241325
//

‎changelog.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -295,6 +295,7 @@ Configuration:
295295
// Returns data from pg_stat_user_functions including call counts and execution times.
296296
// Note: Requires track_functions = 'pl' or 'all' in postgresql.conf.
297297
// Enable with: alter system set track_functions = 'all'; select pg_reload_conf();
298+
// Or set track_functions = 'all' directly in postgresql.conf and restart/reload.
298299
//
299300
"RoutinesStatsPath": "/stats/routines",
300301
//

0 commit comments

Comments
 (0)