:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.
Python ab-testingai-analyticsanalyticscdpdata-warehouseexperimentsfeature-flagsjavascriptproduct-analyticspythonreactsession-replaysurveystypescriptweb-analytics
//! The data layer: every question the REST API and the MCP tools can answer, as
//! functions over the stats DB returning JSON. Keep SQL here so both surfaces stay
//! in lock-step and the API layer is thin.
use crate::db::Db;
use anyhow::{anyhow, Result};
use chrono::{DateTime, Utc};
use futures_util::future::{BoxFuture, FutureExt};
use futures_util::stream::{self, StreamExt};
use serde_json::{json, Value};
use std::collections::HashMap;
type Ts = DateTime<Utc>;
/// Read a bigint back out of a row `Db::query` produced. Values past 2^53 come back
/// as strings so JavaScript keeps them intact; query ids and fingerprints always do.
fn json_i64(v: &Value) -> Option<i64> {
v.as_i64()
.or_else(|| v.as_str().and_then(|s| s.parse().ok()))
}
/// Query a table that may not exist yet (created on first data by the collector).
async fn opt(
db: &Db,
sql: &str,
params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
) -> Result<Vec<Value>> {
match db.query(sql, params).await {
Ok(v) => Ok(v),
Err(e)
if format!("{e:#}").contains("42P01")
|| format!("{e:#}").contains("does not exist") =>
{
Ok(vec![])
}
Err(e) => Err(e),
}
}
pub async fn servers(db: &Db) -> Result<Value> {
let rows = db.query(
"SELECT s.server_id, s.version, s.version_num, s.aurora_version, s.instances, s.databases, s.first_seen, s.last_seen,
(SELECT max(started_at) FROM collector_runs r WHERE r.server_id = s.server_id) AS last_collected_at,
(SELECT count(*) FROM collector_runs r WHERE r.server_id = s.server_id AND r.started_at > now() - interval '10 minutes' AND r.error IS NOT NULL)::bigint AS errors_10m
FROM cur_servers s ORDER BY s.server_id", &[]).await?;
Ok(json!(rows))
}
pub async fn overview(db: &Db, server: &str, from: Ts, to: Ts) -> Result<Value> {
let dbs = opt(db, "SELECT datname, instance,
sum(xact_commit)::bigint AS xact_commit, sum(xact_rollback)::bigint AS xact_rollback,
sum(blks_hit)::bigint AS blks_hit, sum(blks_read)::bigint AS blks_read,
CASE WHEN sum(blks_hit + blks_read) > 0 THEN round((sum(blks_hit)::numeric / sum(blks_hit + blks_read)) * 100, 2)::float8 END AS cache_hit_pct,
sum(tup_inserted)::bigint AS tup_inserted, sum(tup_updated)::bigint AS tup_updated, sum(tup_deleted)::bigint AS tup_deleted,
sum(deadlocks)::bigint AS deadlocks, sum(temp_files)::bigint AS temp_files, sum(temp_bytes)::bigint AS temp_bytes,
max(xid_age)::bigint AS xid_age, max(numbackends)::bigint AS max_backends
FROM ts_database_stats WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3
GROUP BY 1, 2 ORDER BY 1, 2", &[&server, &from, &to]).await?;
let conns = opt(db, "SELECT instance, state, sum(backends)::bigint AS backends
FROM ts_activity_samples WHERE server_id = $1 AND collected_at = (SELECT max(collected_at) FROM ts_activity_samples WHERE server_id = $1 AND collected_at >= $2)
GROUP BY 1, 2 ORDER BY 1, 2", &[&server, &from]).await?;
let waits = opt(db, "SELECT wait_event_type, wait_event, sum(backends)::bigint AS samples
FROM ts_activity_samples WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 AND state = 'active'
GROUP BY 1, 2 ORDER BY 3 DESC LIMIT 15", &[&server, &from, &to]).await?;
let events = opt(db, "SELECT kind, count(*)::bigint AS n FROM events WHERE server_id = $1 AND at >= $2 AND at < $3 GROUP BY 1 ORDER BY 2 DESC", &[&server, &from, &to]).await?;
let top = top_queries(
db,
server,
from,
to,
QueryListOpts {
datname: None,
order: "total_exec_time",
limit: 5,
tags: &[],
},
)
.await?;
let vac = opt(db, "SELECT datname, relname, round(vacuum_ratio::numeric, 2)::float8 AS vacuum_ratio, round(freeze_ratio::numeric, 3)::float8 AS freeze_ratio, autovacuum_enabled
FROM ts_vacuum_needed WHERE server_id = $1 AND collected_at = (SELECT max(collected_at) FROM ts_vacuum_needed WHERE server_id = $1 AND collected_at >= $2)
AND (vacuum_ratio >= 1 OR freeze_ratio >= 0.5) ORDER BY greatest(vacuum_ratio, freeze_ratio * 2) DESC LIMIT 10", &[&server, &from]).await?;
Ok(
json!({ "server_id": server, "from": from, "to": to, "databases": dbs, "connections": conns, "top_wait_events": waits, "events": events, "top_queries": top, "vacuum_attention": vac }),
)
}
pub struct QueryListOpts<'a> {
pub datname: Option<&'a str>,
pub order: &'a str,
pub limit: i64,
pub tags: &'a [(String, String)],
}
pub async fn top_queries(
db: &Db,
server: &str,
from: Ts,
to: Ts,
opts: QueryListOpts<'_>,
) -> Result<Value> {
let QueryListOpts {
datname,
order,
limit,
tags,
} = opts;
let order_col = match order {
"calls" => "calls",
"mean_exec_time" => "mean_ms",
"rows" => "rows",
"shared_blks_read" => "shared_blks_read",
"wal_bytes" => "wal_bytes",
"storage_blks_read" => "storage_blks_read",
_ => "total_ms",
};
// pg_stat_statements cannot split a query id by caller, so a tag filter keeps the
// ids that were seen with those tags in any sampled source (activity, logs) or
// whose first-seen text carried them.
let mut tagged_sources = Vec::new();
if !tags.is_empty() {
if has_column(db, "ts_activity_samples", "tags").await {
tagged_sources.push("SELECT DISTINCT query_id AS queryid, datname FROM ts_activity_samples WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 AND query_id IS NOT NULL AND tags @> $6::jsonb");
}
if has_column(db, "ts_query_latency", "tags").await {
tagged_sources.push("SELECT DISTINCT query_id AS queryid, datname FROM ts_query_latency WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 AND query_id IS NOT NULL AND tags @> $6::jsonb");
tagged_sources.push("SELECT DISTINCT q.queryid, q.datname FROM ts_query_latency d JOIN cur_queries q ON q.server_id = $1 AND q.fingerprint = d.fingerprint AND q.datname = d.datname WHERE d.server_id = $1 AND d.collected_at >= $2 AND d.collected_at < $3 AND d.tags @> $6::jsonb");
}
if tagged_sources.is_empty() {
return Ok(json!([]));
}
}
let tag_filter = if tagged_sources.is_empty() {
String::new()
} else {
format!(
"AND (s.queryid, s.datname) IN ({})",
tagged_sources.join(" UNION ")
)
};
let tag_param: Option<Value> = (!tags.is_empty()).then(|| tags_object(tags));
let query_tags = if has_column(db, "cur_queries", "tags").await {
"q.tags"
} else {
"NULL::jsonb AS tags"
};
let sql = format!(
"WITH agg AS (
SELECT s.queryid, s.datname, s.rolname,
sum(s.calls)::bigint AS calls, sum(s.total_exec_time)::float8 AS total_ms, sum(s.rows)::bigint AS rows,
CASE WHEN sum(s.calls) > 0 THEN sum(s.total_exec_time) / sum(s.calls) END::float8 AS mean_ms,
CASE WHEN sum(s.calls) > 0 THEN sqrt(greatest(sum(s.sumsq_exec_time) / sum(s.calls) - power(sum(s.total_exec_time) / sum(s.calls), 2), 0)) END::float8 AS stddev_ms,
sum(s.shared_blks_hit)::bigint AS shared_blks_hit, sum(s.shared_blks_read)::bigint AS shared_blks_read,
sum(s.temp_blks_written)::bigint AS temp_blks_written, sum(s.wal_bytes)::bigint AS wal_bytes,
sum(s.blk_read_time)::float8 AS blk_read_ms,
{aurora}
count(DISTINCT s.instance)::bigint AS instances
FROM ts_query_stats s
WHERE s.server_id = $1 AND s.collected_at >= $2 AND s.collected_at < $3 AND ($4::text IS NULL OR s.datname = $4) {tag_filter}
GROUP BY 1, 2, 3)
SELECT a.*, q.query, q.fingerprint, {query_tags},
round((a.total_ms / nullif(sum(a.total_ms) OVER (), 0) * 100)::numeric, 2)::float8 AS pct_of_total_time
FROM agg a LEFT JOIN cur_queries q ON q.server_id = $1 AND q.queryid = a.queryid AND q.datname = a.datname
ORDER BY {order_col} DESC NULLS LAST LIMIT $5",
aurora = if has_column(db, "ts_query_stats", "storage_blks_read").await { "sum(s.storage_blks_read)::bigint AS storage_blks_read, max(s.max_exec_peakmem)::bigint AS max_exec_peakmem," } else { "NULL::bigint AS storage_blks_read, NULL::bigint AS max_exec_peakmem," },
);
// tokio-postgres rejects a parameter the statement never references.
let mut params: Vec<&(dyn tokio_postgres::types::ToSql + Sync)> =
vec![&server, &from, &to, &datname, &limit];
if tag_param.is_some() {
params.push(&tag_param);
}
Ok(json!(opt(db, &sql, ¶ms).await?))
}
async fn has_column(db: &Db, table: &str, col: &str) -> bool {
db.query(
"SELECT 1 FROM information_schema.columns WHERE table_name = $1 AND column_name = $2",
&[&table, &col],
)
.await
.map(|r| !r.is_empty())
.unwrap_or(false)
}
/// `tags, trace_id` when the table has them, NULL placeholders until the collector's
/// first tagged row adds the columns, so an older stats DB keeps serving.
async fn tag_cols(db: &Db, table: &str) -> &'static str {
if has_column(db, table, "tags").await {
"tags, trace_id"
} else {
"NULL::jsonb AS tags, NULL::text AS trace_id"
}
}
async fn tag_cols_of(db: &Db, table: &str, alias: &str) -> String {
if has_column(db, table, "tags").await {
format!("{alias}.tags, {alias}.trace_id")
} else {
"NULL::jsonb AS tags, NULL::text AS trace_id".into()
}
}
/// `route=/api/x,service=web` (or `key:value`) into pairs; keys are lower-cased and
/// values are percent-decoded, so a value holding a comma travels as `%2C`.
pub fn split_list(csv: Option<&str>) -> Vec<String> {
csv.unwrap_or("")
.split(',')
.map(str::trim)
.filter(|s| !s.is_empty())
.map(str::to_owned)
.collect()
}
pub fn parse_tag_filter(s: &str) -> Result<Vec<(String, String)>> {
let mut out = Vec::new();
for part in s.split(',').map(str::trim).filter(|p| !p.is_empty()) {
let (k, v) = part
.split_once('=')
.or_else(|| part.split_once(':'))
.ok_or_else(|| anyhow::anyhow!("tag filter {part:?} is not key=value"))?;
let k = k.trim().to_ascii_lowercase();
anyhow::ensure!(!k.is_empty(), "tag filter {part:?} has an empty key");
out.push((k, percent_decode(v.trim())));
}
Ok(out)
}
fn percent_decode(s: &str) -> String {
let b = s.as_bytes();
let mut out = Vec::with_capacity(b.len());
let mut i = 0;
while i < b.len() {
if b[i] == b'%' && i + 2 < b.len() {
if let Some(v) = std::str::from_utf8(&b[i + 1..i + 3])
.ok()
.and_then(|h| u8::from_str_radix(h, 16).ok())
{
out.push(v);
i += 3;
continue;
}
}
out.push(b[i]);
i += 1;
}
String::from_utf8_lossy(&out).into_owned()
}
fn tags_object(tags: &[(String, String)]) -> Value {
Value::Object(
tags.iter()
.map(|(k, v)| (k.clone(), Value::String(v.clone())))
.collect(),
)
}
/// Bucket width for time series; unknown values fall back to one minute.
fn bucket_interval(bucket: &str) -> &'static str {
match bucket {
"10s" => "10 seconds",
"5m" => "5 minutes",
"1h" => "1 hour",
_ => "1 minute",
}
}
fn bucket_expr(col: &str, interval: &str) -> String {
format!("to_timestamp(floor(extract(epoch FROM {col}) / extract(epoch FROM interval '{interval}')) * extract(epoch FROM interval '{interval}'))")
}
pub async fn query_detail(
db: &Db,
server: &str,
queryid: i64,
from: Ts,
to: Ts,
bucket: &str,
datname: Option<&str>,
) -> Result<Value> {
let interval = bucket_interval(bucket);
// Latency histograms are per minute, so the finest latency bucket is a minute.
let latency_interval = if interval == "10 seconds" {
"1 minute"
} else {
interval
};
let query_tags = if has_column(db, "cur_queries", "tags").await {
"tags"
} else {
"NULL::jsonb AS tags"
};
let texts = opt(db, &format!("SELECT datname, query, fingerprint, truncated, first_seen, last_seen, {query_tags} FROM cur_queries WHERE server_id = $1 AND queryid = $2 AND ($3::text IS NULL OR datname = $3)"), &[&server, &queryid, &datname]).await?;
let fingerprint: Option<i64> = texts.first().and_then(|t| json_i64(&t["fingerprint"]));
let series = opt(db, &format!("SELECT {b} AS bucket, instance, datname,
sum(calls)::bigint AS calls, sum(total_exec_time)::float8 AS total_ms,
CASE WHEN sum(calls) > 0 THEN sum(total_exec_time) / sum(calls) END::float8 AS mean_ms,
sum(rows)::bigint AS rows, sum(shared_blks_read)::bigint AS shared_blks_read, sum(shared_blks_hit)::bigint AS shared_blks_hit
FROM ts_query_stats WHERE server_id = $1 AND queryid = $2 AND collected_at >= $3 AND collected_at < $4 AND ($5::text IS NULL OR datname = $5)
GROUP BY 1, 2, 3 ORDER BY 1", b = bucket_expr("collected_at", interval)), &[&server, &queryid, &from, &to, &datname]).await?;
let sampling = log_sampling_settings(db, server).await?;
// Each histogram row carries the sample rate it was collected under: a sampled
// count stands for 1/rate statements, an always-logged one for itself. Edges
// mirror pgcollector's histogram module (array ordinal i starts at
// 10^((i-1)/20 - 2) ms); a quantile reports its bucket's upper edge, capped at the
// largest duration seen. One pass serves the per-bucket series and the
// whole-range row (bucket NULL).
let quantile_sql = format!(
"WITH l AS (
SELECT {bucket} AS bucket, max_ms, sampled_counts, logged_counts, sample_rate
FROM ts_query_latency WHERE server_id = $1 AND collected_at >= $3 AND collected_at < $4
AND (query_id = $2 OR fingerprint = $5)),
h AS (
SELECT bucket, u.i, sum(u.sc)::float8 AS sc, sum(u.lc)::float8 AS lc,
sum(CASE WHEN l.sample_rate > 0 THEN u.sc / l.sample_rate ELSE 0 END + u.lc)::float8 AS wc
FROM l, unnest(l.sampled_counts, l.logged_counts) WITH ORDINALITY AS u(sc, lc, i)
WHERE u.sc > 0 OR u.lc > 0 GROUP BY 1, 2),
r AS (
SELECT bucket, i, sc, lc, power(10, i / 20.0 - 2) AS hi,
sum(wc) OVER (PARTITION BY bucket ORDER BY i) AS cw, sum(wc) OVER (PARTITION BY bucket) AS tw,
sum(wc) OVER (ORDER BY i) AS cw_all, sum(wc) OVER () AS tw_all
FROM h),
q AS (
SELECT bucket, sum(sc + lc)::bigint AS samples, sum(sc)::bigint AS sampled,
min(hi) FILTER (WHERE cw >= 0.5 * tw) AS p50, min(hi) FILTER (WHERE cw >= 0.9 * tw) AS p90,
min(hi) FILTER (WHERE cw >= 0.95 * tw) AS p95, min(hi) FILTER (WHERE cw >= 0.99 * tw) AS p99
FROM r GROUP BY bucket
UNION ALL
SELECT NULL, sum(sc + lc)::bigint, sum(sc)::bigint,
min(hi) FILTER (WHERE cw_all >= 0.5 * tw_all), min(hi) FILTER (WHERE cw_all >= 0.9 * tw_all),
min(hi) FILTER (WHERE cw_all >= 0.95 * tw_all), min(hi) FILTER (WHERE cw_all >= 0.99 * tw_all)
FROM r),
m AS (SELECT bucket, max(max_ms) AS max_ms FROM l GROUP BY GROUPING SETS ((bucket), ()))
SELECT q.bucket, q.samples, q.sampled,
least(q.p50, m.max_ms)::float8 AS p50, least(q.p90, m.max_ms)::float8 AS p90,
least(q.p95, m.max_ms)::float8 AS p95, least(q.p99, m.max_ms)::float8 AS p99,
m.max_ms::float8 AS max_ms
FROM q JOIN m ON m.bucket IS NOT DISTINCT FROM q.bucket
WHERE q.samples > 0
ORDER BY q.bucket NULLS FIRST",
bucket = bucket_expr("minute", latency_interval)
);
let (quantiles, latency_series): (Vec<Value>, Vec<Value>) = opt(
db,
&quantile_sql,
&[&server, &queryid, &from, &to, &fingerprint],
)
.await?
.into_iter()
.partition(|r| r["bucket"].is_null());
let slow_samples = opt(
db,
&format!("SELECT d.log_time, d.log_stream, d.datname, d.usename, d.duration_ms, left(t.query, 500) AS query, {}
FROM ts_query_durations d
LEFT JOIN cur_query_texts t ON t.server_id = d.server_id AND t.instance = d.instance AND t.datname = coalesce(d.datname, '') AND t.fingerprint = d.fingerprint
WHERE d.server_id = $1 AND d.collected_at >= $3 AND d.collected_at < $4
AND (d.query_id = $2 OR d.fingerprint = $5) AND d.kind NOT IN ('parse', 'bind')
ORDER BY d.duration_ms DESC LIMIT 10", tag_cols_of(db, "ts_query_durations", "d").await),
&[&server, &queryid, &from, &to, &fingerprint],
)
.await?;
let callers = query_callers(db, server, queryid, fingerprint, from, to).await?;
let aurora_plans = opt(db, "SELECT p.planid, p.plan_type, p.plan_captured_time, p.explain_plan,
(SELECT sum(calls) FROM ts_aurora_plans a WHERE a.server_id = $1 AND a.planid = p.planid AND a.queryid = $2 AND a.collected_at >= $3 AND a.collected_at < $4)::bigint AS calls,
(SELECT sum(total_exec_time) FROM ts_aurora_plans a WHERE a.server_id = $1 AND a.planid = p.planid AND a.queryid = $2 AND a.collected_at >= $3 AND a.collected_at < $4)::float8 AS total_ms
FROM cur_query_plans p WHERE p.server_id = $1 AND p.queryid = $2 ORDER BY calls DESC NULLS LAST", &[&server, &queryid, &from, &to]).await?;
let logged_plans = opt(db, "SELECT log_time, datname, duration_ms, plan FROM ts_log_plans WHERE server_id = $1 AND collected_at >= $3 AND collected_at < $4
AND (query_id = $2 OR fingerprint = $5) ORDER BY duration_ms DESC LIMIT 5", &[&server, &queryid, &from, &to, &fingerprint]).await?;
let waits = opt(db, "SELECT wait_event_type, wait_event, sum(backends)::bigint AS samples FROM ts_activity_samples
WHERE server_id = $1 AND query_id = $2 AND collected_at >= $3 AND collected_at < $4 GROUP BY 1, 2 ORDER BY 3 DESC LIMIT 10", &[&server, &queryid, &from, &to]).await?;
Ok(
json!({ "queryid": queryid, "bucket": interval, "latency_bucket": latency_interval, "texts": texts, "series": series, "latency_series": latency_series,
// Always-logged slow statements alone are a tail, not a distribution.
"latency_from_logs": quantiles.into_iter().next().filter(|q| json_i64(&q["sampled"]).unwrap_or(0) > 0),
"log_sampling": sampling, "slowest_samples": slow_samples, "callers": callers,
"plans": aurora_plans, "logged_plans": logged_plans, "wait_events": waits }),
)
}
/// How the monitored server samples statement durations into its log. Drives the
/// quantile weighting and tells the UI what the samples cover.
#[derive(serde::Serialize, Clone)]
struct LogSampling {
/// `log_min_duration_sample`: statements below this are never sampled (-1 = off).
sample_floor_ms: f64,
/// `log_statement_sample_rate`, as configured.
rate: f64,
/// `log_min_duration_statement`: statements at or above this are always logged (-1 = off).
hard_threshold_ms: f64,
/// Sampling is on: a floor is set and the rate is above zero.
enabled: bool,
}
async fn log_sampling_settings(db: &Db, server: &str) -> Result<LogSampling> {
// The collector silences its own statement logging with session-level SETs, and
// the settings snapshot is taken from that session; reset_val is the server value.
let rows = opt(db, "SELECT name, CASE WHEN source = 'session' THEN reset_val ELSE setting END AS setting FROM cur_settings WHERE server_id = $1
AND name IN ('log_min_duration_sample', 'log_statement_sample_rate', 'log_min_duration_statement')
ORDER BY instance", &[&server]).await?;
let get = |name: &str, default: f64| -> f64 {
rows.iter()
.find(|r| r["name"] == name)
.and_then(|r| r["setting"].as_str()?.parse().ok())
.unwrap_or(default)
};
let rate = get("log_statement_sample_rate", 1.0);
let sample_floor_ms = get("log_min_duration_sample", -1.0);
Ok(LogSampling {
sample_floor_ms,
rate,
hard_threshold_ms: get("log_min_duration_statement", -1.0),
enabled: sample_floor_ms >= 0.0 && rate > 0.0,
})
}
/// Which code paths ran this query in the range: tag sets seen in activity samples
/// (weighted by backends seen) and in logged statements (one per statement).
async fn query_callers(
db: &Db,
server: &str,
queryid: i64,
fingerprint: Option<i64>,
from: Ts,
to: Ts,
) -> Result<Vec<Value>> {
let mut parts = Vec::new();
if has_column(db, "ts_activity_samples", "tags").await {
parts.push("SELECT tags, 'activity' AS source, sum(backends)::bigint AS samples FROM ts_activity_samples
WHERE server_id = $1 AND query_id = $2 AND collected_at >= $3 AND collected_at < $4 AND tags IS NOT NULL GROUP BY 1");
}
let latency = has_column(db, "ts_query_latency", "tags").await;
if latency {
parts.push("SELECT tags, 'log' AS source, sum(count)::bigint AS samples FROM ts_query_latency
WHERE server_id = $1 AND collected_at >= $3 AND collected_at < $4 AND (query_id = $2 OR fingerprint = $5)
AND tags IS NOT NULL GROUP BY 1");
}
if parts.is_empty() {
return Ok(vec![]);
}
// The share is over every tag set of the source, computed before the row cap.
let sql = format!(
"SELECT tags, source, samples,
round((samples::numeric / nullif(sum(samples) OVER (PARTITION BY source), 0)) * 100, 1)::float8 AS share_pct
FROM ({}) c ORDER BY samples DESC LIMIT 40",
parts.join(" UNION ALL ")
);
// tokio-postgres rejects a parameter the statement never references.
let mut params: Vec<&(dyn tokio_postgres::types::ToSql + Sync)> =
vec![&server, &queryid, &from, &to];
if latency {
params.push(&fingerprint);
}
opt(db, &sql, ¶ms).await
}
/// Without `key` only the tag keys seen in the range are listed. Duration metrics are
/// weighted for log sampling the same way `query_detail` weights them.
pub async fn tag_breakdown(
db: &Db,
server: &str,
from: Ts,
to: Ts,
key: Option<&str>,
datname: Option<&str>,
limit: i64,
) -> Result<Value> {
let activity = has_column(db, "ts_activity_samples", "tags").await;
let latency = has_column(db, "ts_query_latency", "tags").await;
let mut keys: std::collections::BTreeMap<String, Value> = Default::default();
if activity {
for r in opt(db, "SELECT k AS key, count(DISTINCT tags ->> k)::bigint AS values, sum(backends)::bigint AS active_samples
FROM ts_activity_samples, LATERAL jsonb_object_keys(tags) k
WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 AND tags IS NOT NULL AND ($4::text IS NULL OR datname = $4) GROUP BY 1", &[&server, &from, &to, &datname]).await? {
let k = r["key"].as_str().unwrap_or("").to_string();
keys.insert(k.clone(), json!({ "key": k, "values": r["values"], "active_samples": r["active_samples"], "logged_statements": 0 }));
}
}
if latency {
for r in opt(db, "SELECT k AS key, count(DISTINCT tags ->> k)::bigint AS values, sum(count)::bigint AS logged_statements
FROM ts_query_latency, LATERAL jsonb_object_keys(tags) k
WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 AND tags IS NOT NULL AND ($4::text IS NULL OR datname = $4) GROUP BY 1", &[&server, &from, &to, &datname]).await? {
let k = r["key"].as_str().unwrap_or("").to_string();
let e = keys.entry(k.clone()).or_insert_with(|| json!({ "key": k, "values": r["values"], "active_samples": 0 }));
e["logged_statements"] = r["logged_statements"].clone();
if json_i64(&r["values"]).unwrap_or(0) > json_i64(&e["values"]).unwrap_or(0) {
e["values"] = r["values"].clone();
}
}
}
let sampling = log_sampling_settings(db, server).await?;
let Some(key) = key else {
return Ok(
json!({ "keys": keys.into_values().collect::<Vec<_>>(), "groups": [], "log_sampling": sampling }),
);
};
let mut groups: std::collections::BTreeMap<String, Value> = Default::default();
if activity {
let ticks = opt(db, "SELECT count(DISTINCT collected_at)::bigint AS n FROM ts_activity_samples WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3", &[&server, &from, &to]).await?;
let ticks = ticks
.first()
.and_then(|r| json_i64(&r["n"]))
.unwrap_or(0)
.max(1) as f64;
for r in opt(db, "SELECT tags ->> $4 AS value, sum(backends)::bigint AS active_samples, sum(active_over_1s)::bigint AS active_over_1s, sum(active_over_10s)::bigint AS active_over_10s,
count(DISTINCT query_id)::bigint AS distinct_queries
FROM ts_activity_samples WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 AND state = 'active' AND tags ? $4::text
AND ($5::text IS NULL OR datname = $5) GROUP BY 1", &[&server, &from, &to, &key, &datname]).await? {
let v = r["value"].as_str().unwrap_or("").to_string();
let samples = json_i64(&r["active_samples"]).unwrap_or(0) as f64;
groups.insert(v.clone(), json!({ "value": v, "active_samples": r["active_samples"], "active_over_1s": r["active_over_1s"], "active_over_10s": r["active_over_10s"],
"avg_active_sessions": (samples / ticks * 100.0).round() / 100.0, "distinct_queries": r["distinct_queries"] }));
}
}
if latency {
// Same histogram arithmetic as query_detail, partitioned by tag value instead of
// time bucket. Estimated total time takes each bucket at its geometric middle.
let sql = "WITH l AS (
SELECT tags ->> $4 AS value, max_ms, sampled_counts, logged_counts, sample_rate, coalesce(query_id, fingerprint) AS q
FROM ts_query_latency WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 AND tags ? $4::text
AND ($5::text IS NULL OR datname = $5)),
h AS (
SELECT value, u.i, sum(u.sc)::float8 AS sc, sum(u.lc)::float8 AS lc,
sum(CASE WHEN l.sample_rate > 0 THEN u.sc / l.sample_rate ELSE 0 END + u.lc)::float8 AS wc
FROM l, unnest(l.sampled_counts, l.logged_counts) WITH ORDINALITY AS u(sc, lc, i)
WHERE u.sc > 0 OR u.lc > 0 GROUP BY 1, 2),
r AS (
SELECT value, i, sc, lc, wc, power(10, i / 20.0 - 2) AS hi,
sum(wc) OVER (PARTITION BY value ORDER BY i) AS cw, sum(wc) OVER (PARTITION BY value) AS tw
FROM h),
q AS (
SELECT value, sum(sc + lc)::bigint AS logged_statements, round(sum(wc)::numeric)::bigint AS est_calls,
sum(wc * hi / power(10, 0.025))::float8 AS est_total_ms,
min(hi) FILTER (WHERE cw >= 0.5 * tw) AS p50, min(hi) FILTER (WHERE cw >= 0.95 * tw) AS p95,
min(hi) FILTER (WHERE cw >= 0.99 * tw) AS p99
FROM r GROUP BY 1),
m AS (SELECT value, max(max_ms) AS max_ms, count(DISTINCT q)::bigint AS distinct_statements FROM l GROUP BY 1)
SELECT q.value, q.logged_statements, q.est_calls, q.est_total_ms, m.distinct_statements,
least(q.p50, m.max_ms)::float8 AS p50, least(q.p95, m.max_ms)::float8 AS p95, least(q.p99, m.max_ms)::float8 AS p99,
m.max_ms::float8 AS max_ms
FROM q JOIN m USING (value)";
for r in opt(db, sql, &[&server, &from, &to, &key, &datname]).await? {
let v = r["value"].as_str().unwrap_or("").to_string();
let e = groups.entry(v.clone()).or_insert_with(
|| json!({ "value": v, "active_samples": 0, "avg_active_sessions": 0.0 }),
);
for k in [
"logged_statements",
"est_calls",
"est_total_ms",
"distinct_statements",
"p50",
"p95",
"p99",
"max_ms",
] {
e[k] = r[k].clone();
}
}
}
let mut groups: Vec<Value> = groups.into_values().collect();
groups.sort_by(|a, b| {
let f = |v: &Value| {
(
json_i64(&v["active_samples"]).unwrap_or(0) as f64,
v["est_total_ms"].as_f64().unwrap_or(0.0),
)
};
f(b).partial_cmp(&f(a)).unwrap_or(std::cmp::Ordering::Equal)
});
groups.truncate(limit.clamp(1, 500) as usize);
Ok(
json!({ "key": key, "keys": keys.into_values().collect::<Vec<_>>(), "groups": groups, "log_sampling": sampling }),
)
}
pub async fn wait_events(db: &Db, server: &str, from: Ts, to: Ts, bucket: &str) -> Result<Value> {
let sql = format!("SELECT {b} AS bucket,
instance, coalesce(wait_event_type, 'CPU') AS wait_event_type, coalesce(wait_event, 'CPU') AS wait_event,
round(avg(backends)::numeric, 2)::float8 AS avg_active_sessions
FROM ts_activity_samples WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 AND state = 'active'
GROUP BY 1, 2, 3, 4 ORDER BY 1", b = bucket_expr("collected_at", bucket_interval(bucket)));
let sampled = opt(db, &sql, &[&server, &from, &to]).await?;
let measured = opt(db, "SELECT instance, type_name, event_name, sum(waits)::bigint AS waits, sum(wait_time)::bigint AS wait_time_us
FROM ts_aurora_system_waits WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 GROUP BY 1, 2, 3 ORDER BY 5 DESC LIMIT 30", &[&server, &from, &to]).await?;
Ok(json!({ "sampled": sampled, "measured_aurora": measured }))
}
/// Average active sessions per bucket, split by wait event type and summed over instances,
/// with the host core count as the line load is compared against.
pub async fn db_load(db: &Db, server: &str, from: Ts, to: Ts, bucket: &str) -> Result<Value> {
// Sample rows are split by database, user and query, so the average must divide the
// summed backends by the number of samples, not the number of rows.
let sql = format!(
"WITH samples AS (
SELECT {b} AS bucket, instance, count(DISTINCT collected_at) AS n
FROM ts_activity_samples WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 GROUP BY 1, 2),
active AS (
SELECT {b} AS bucket, instance, coalesce(wait_event_type, 'CPU') AS wait_event_type, sum(backends) AS backends
FROM ts_activity_samples WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 AND state = 'active' GROUP BY 1, 2, 3)
SELECT a.bucket, a.wait_event_type, round(sum(a.backends::float8 / s.n)::numeric, 2)::float8 AS avg_active_sessions
FROM active a JOIN samples s USING (bucket, instance) GROUP BY 1, 2 ORDER BY 1, 2",
b = bucket_expr("collected_at", bucket_interval(bucket))
);
let series = opt(db, &sql, &[&server, &from, &to]).await?;
let host = opt(db, "SELECT instance, round(sum(user_jiffies + nice_jiffies + system_jiffies + iowait_jiffies + idle_jiffies) / nullif(sum(interval_seconds), 0) / 100.0)::bigint AS ncpu
FROM ts_system_cpu WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 AND interval_seconds > 0 GROUP BY 1", &[&server, &from, &to]).await?;
Ok(json!({ "bucket": bucket_interval(bucket), "series": series, "host": host }))
}
pub async fn lock_waits(
db: &Db,
server: &str,
from: Ts,
to: Ts,
bucket: &str,
limit: i64,
) -> Result<Value> {
let b = bucket_expr("collected_at", bucket_interval(bucket));
let series = opt(db, &format!(
"WITH samples AS (
SELECT {b} AS bucket, instance, count(DISTINCT collected_at) AS n
FROM ts_activity_samples WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 GROUP BY 1, 2),
waiting AS (
SELECT {b} AS bucket, instance, coalesce(wait_event, 'other') AS locktype, sum(backends) AS backends
FROM ts_activity_samples WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 AND wait_event_type = 'Lock' GROUP BY 1, 2, 3)
SELECT w.bucket, w.locktype, round(sum(w.backends::float8 / s.n)::numeric, 2)::float8 AS avg_waiting_sessions
FROM waiting w JOIN samples s USING (bucket, instance) GROUP BY 1, 2 ORDER BY 1, 2"), &[&server, &from, &to]).await?;
// One row per blocked backend per 10 s sample, so summing the interval gives waiter-seconds.
let pairs = opt(db, "SELECT blocker_query, waiter_query, locktype, relation, wanted_mode,
count(*)::bigint AS samples, round(sum(interval_seconds)::numeric, 0)::float8 AS waited_s,
count(DISTINCT (instance, waiter_pid))::bigint AS waiters, count(DISTINCT (instance, blocker_pid))::bigint AS blockers,
max(waiting_s)::float8 AS max_waiting_s, max(blocker_xact_age_s)::float8 AS max_blocker_xact_age_s,
round(100.0 * sum(CASE WHEN blocker_state = 'idle in transaction' THEN 1 ELSE 0 END) / count(*), 0)::float8 AS blocker_idle_pct,
max(usename) AS usename, max(application_name) AS application_name,
max(blocker_usename) AS blocker_usename, max(blocker_application_name) AS blocker_application_name,
max(collected_at) AS last_seen
FROM ts_lock_waits WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3
GROUP BY 1, 2, 3, 4, 5 ORDER BY waited_s DESC LIMIT $4", &[&server, &from, &to, &limit]).await?;
// Consecutive samples of one (waiter, blocker) pair are one episode; a gap over 30 s (three
// missed samples) starts a new one, which is what a waiter that timed out and retried looks like.
// A backend is its pid plus backend_start. The backend_start columns arrive with a newer collector
// and pgapi can deploy first, so they are read through to_jsonb, which parses without them. The
// aliases differ from the column names because l.* carries the real columns once they exist.
let episodes = opt(db, "WITH w AS (
SELECT l.*, (to_jsonb(l) ->> 'waiter_backend_start') AS waiter_started, (to_jsonb(l) ->> 'blocker_backend_start') AS blocker_started,
CASE WHEN lag(collected_at) OVER pair IS NULL OR collected_at - lag(collected_at) OVER pair > interval '30 seconds'
OR blocker_xact_age_s < lag(blocker_xact_age_s) OVER pair OR waiting_s < lag(waiting_s) OVER pair THEN 1 ELSE 0 END AS starts
FROM ts_lock_waits l WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3
WINDOW pair AS (PARTITION BY instance, waiter_pid, (to_jsonb(l) ->> 'waiter_backend_start'), blocker_pid, (to_jsonb(l) ->> 'blocker_backend_start') ORDER BY collected_at)),
e AS (SELECT *, sum(starts) OVER (PARTITION BY instance, waiter_pid, waiter_started, blocker_pid, blocker_started ORDER BY collected_at) AS episode FROM w)
SELECT instance, datname, waiter_pid, waiter_started AS waiter_backend_start, blocker_pid, blocker_started AS blocker_backend_start, min(collected_at) AS first_seen, max(collected_at) AS last_seen,
count(*)::bigint AS samples, round(sum(interval_seconds)::numeric, 0)::float8 AS waited_s, max(waiting_s)::float8 AS max_waiting_s,
max(blocker_xact_age_s)::float8 AS blocker_xact_age_s, min(blocker_xact_age_s)::float8 AS blocker_xact_age_at_start_s,
(array_agg(blocker_state ORDER BY collected_at DESC))[1] AS blocker_state,
(array_agg(locktype ORDER BY collected_at DESC))[1] AS locktype, (array_agg(relation ORDER BY collected_at DESC))[1] AS relation,
(array_agg(wanted_mode ORDER BY collected_at DESC))[1] AS wanted_mode,
(array_agg(waiter_query ORDER BY collected_at DESC))[1] AS waiter_query, (array_agg(blocker_query ORDER BY collected_at DESC))[1] AS blocker_query,
max(usename) AS usename, max(application_name) AS application_name,
max(blocker_usename) AS blocker_usename, max(blocker_application_name) AS blocker_application_name
FROM e GROUP BY instance, datname, waiter_pid, waiter_started, blocker_pid, blocker_started, episode ORDER BY first_seen DESC LIMIT $4", &[&server, &from, &to, &limit]).await?;
let deadlocks = opt(db, "SELECT id, at, instance, datname, subject, after FROM events WHERE server_id = $1 AND at >= $2 AND at < $3 AND kind = 'log_deadlock' ORDER BY at DESC LIMIT 50", &[&server, &from, &to]).await?;
Ok(
json!({ "bucket": bucket_interval(bucket), "series": series, "pairs": pairs, "episodes": episodes, "deadlocks": deadlocks }),
)
}
/// Backends are only sampled once active for 5 s or idle in transaction for 60 s, so a short
/// transaction leaves no rows. Without `backend_start`, the backend sampled nearest to `at` is used.
pub struct SessionWindow {
pub from: Ts,
pub to: Ts,
pub backend_start: Option<Ts>,
pub at: Option<Ts>,
}
pub async fn session_history(
db: &Db,
server: &str,
instance: &str,
pid: i64,
w: SessionWindow,
) -> Result<Value> {
let SessionWindow {
from,
to,
backend_start,
at,
} = w;
let at = at.unwrap_or(to);
Ok(json!(opt(db, "WITH rows AS (
SELECT collected_at, datname, usename, application_name, client_addr, backend_start, state, wait_event_type, wait_event, blocked_by,
xact_start, xact_age_s, query_start, query_age_s, query_id, query, tags, trace_id
FROM ts_activity_sessions WHERE server_id = $1 AND instance = $2 AND pid = $3 AND collected_at >= $4 AND collected_at < $5),
backend AS (
SELECT coalesce($6::timestamptz, (SELECT backend_start FROM rows ORDER BY abs(extract(epoch FROM collected_at - $7::timestamptz)) LIMIT 1)) AS started)
SELECT * FROM (
SELECT rows.* FROM rows, backend WHERE rows.backend_start IS NOT DISTINCT FROM backend.started
ORDER BY collected_at DESC LIMIT 2000) newest ORDER BY collected_at", &[&server, &instance, &pid, &from, &to, &backend_start, &at]).await?))
}
pub async fn current_activity(db: &Db, server: &str) -> Result<Value> {
let sessions = opt(db, "SELECT * FROM ts_activity_sessions WHERE server_id = $1 AND collected_at = (SELECT max(collected_at) FROM ts_activity_sessions WHERE server_id = $1 AND collected_at > now() - interval '5 minutes') ORDER BY query_age_s DESC NULLS LAST", &[&server]).await?;
let locks = opt(db, "SELECT * FROM ts_lock_waits WHERE server_id = $1 AND collected_at = (SELECT max(collected_at) FROM ts_lock_waits WHERE server_id = $1 AND collected_at > now() - interval '5 minutes') ORDER BY waiting_s DESC", &[&server]).await?;
let counts = opt(db, "SELECT instance, datname, state, sum(backends)::bigint AS backends FROM ts_activity_samples WHERE server_id = $1 AND collected_at = (SELECT max(collected_at) FROM ts_activity_samples WHERE server_id = $1 AND collected_at > now() - interval '5 minutes') GROUP BY 1, 2, 3 ORDER BY 1, 2, 3", &[&server]).await?;
let memctx = opt(db, "SELECT * FROM ts_aurora_memctx WHERE server_id = $1 AND collected_at = (SELECT max(collected_at) FROM ts_aurora_memctx WHERE server_id = $1 AND collected_at > now() - interval '5 minutes') ORDER BY allocated_bytes DESC LIMIT 20", &[&server]).await?;
Ok(
json!({ "connections": counts, "sessions": sessions, "lock_waits": locks, "memory_hogs": memctx }),
)
}
pub async fn tables(
db: &Db,
server: &str,
datname: &str,
from: Ts,
to: Ts,
limit: i64,
) -> Result<Value> {
let rows = opt(db, "WITH stats AS (
SELECT schemaname, relname, sum(seq_scan)::bigint AS seq_scan, sum(seq_tup_read)::bigint AS seq_tup_read, sum(idx_scan)::bigint AS idx_scan,
sum(n_tup_ins)::bigint AS n_tup_ins, sum(n_tup_upd)::bigint AS n_tup_upd, sum(n_tup_del)::bigint AS n_tup_del, sum(n_tup_hot_upd)::bigint AS n_tup_hot_upd,
sum(heap_blks_read)::bigint AS heap_blks_read, sum(heap_blks_hit)::bigint AS heap_blks_hit,
sum(vacuum_count + autovacuum_count)::bigint AS vacuums, sum(analyze_count + autoanalyze_count)::bigint AS analyzes
FROM ts_table_stats WHERE server_id = $1 AND datname = $2 AND collected_at >= $3 AND collected_at < $4 GROUP BY 1, 2),
latest AS (
SELECT schemaname, relname, n_live_tup, n_dead_tup, n_mod_since_analyze, xid_age, reltuples, last_autovacuum, last_autoanalyze
FROM ts_table_stats WHERE server_id = $1 AND datname = $2 AND collected_at = (SELECT max(collected_at) FROM ts_table_stats WHERE server_id = $1 AND datname = $2 AND collected_at >= $3)),
sizes AS (
SELECT schemaname, relname, heap_bytes, index_bytes, toast_bytes, total_bytes
FROM ts_sizes WHERE server_id = $1 AND datname = $2 AND collected_at = (SELECT max(collected_at) FROM ts_sizes WHERE server_id = $1 AND datname = $2 AND collected_at >= $3))
SELECT coalesce(l.schemaname, s.schemaname, z.schemaname) AS schemaname, coalesce(l.relname, s.relname, z.relname) AS relname,
l.n_live_tup, l.n_dead_tup, l.n_mod_since_analyze, l.xid_age, l.last_autovacuum, l.last_autoanalyze,
s.seq_scan, s.seq_tup_read, s.idx_scan, s.n_tup_ins, s.n_tup_upd, s.n_tup_del, s.n_tup_hot_upd, s.heap_blks_read, s.heap_blks_hit, s.vacuums, s.analyzes,
z.heap_bytes, z.index_bytes, z.toast_bytes, z.total_bytes
FROM latest l FULL JOIN stats s USING (schemaname, relname) FULL JOIN sizes z USING (schemaname, relname)
ORDER BY z.total_bytes DESC NULLS LAST LIMIT $5", &[&server, &datname, &from, &to, &limit]).await?;
Ok(json!(rows))
}
pub async fn indexes(db: &Db, server: &str, datname: &str, from: Ts, to: Ts) -> Result<Value> {
let rows = opt(db, "WITH stats AS (
SELECT schemaname, relname, indexrelname, sum(idx_scan)::bigint AS idx_scan, sum(idx_tup_read)::bigint AS idx_tup_read, sum(idx_blks_read)::bigint AS idx_blks_read
FROM ts_index_stats WHERE server_id = $1 AND datname = $2 AND collected_at >= $3 AND collected_at < $4 GROUP BY 1, 2, 3)
SELECT d.schemaname, d.relname, d.indexname, d.definition, d.is_unique, d.is_primary, d.is_valid, d.access_method, d.columns,
coalesce(s.idx_scan, 0) AS idx_scan, s.idx_tup_read, s.idx_blks_read,
(coalesce(s.idx_scan, 0) = 0 AND NOT d.is_unique AND NOT d.is_primary) AS unused_in_range
FROM cur_schema_indexes d LEFT JOIN stats s ON s.schemaname = d.schemaname AND s.relname = d.relname AND s.indexrelname = d.indexname
WHERE d.server_id = $1 AND d.datname = $2 AND d.last_seen > now() - interval '3 hours'
ORDER BY unused_in_range DESC, idx_scan ASC", &[&server, &datname, &from, &to]).await?;
Ok(json!(rows))
}
pub async fn vacuum(
db: &Db,
server: &str,
datname: Option<&str>,
from: Ts,
to: Ts,
) -> Result<Value> {
let needed = opt(db, "SELECT * FROM ts_vacuum_needed WHERE server_id = $1 AND ($2::text IS NULL OR datname = $2)
AND collected_at = (SELECT max(collected_at) FROM ts_vacuum_needed WHERE server_id = $1 AND collected_at >= $3)
ORDER BY greatest(vacuum_ratio, analyze_ratio, freeze_ratio * 2) DESC", &[&server, &datname, &from]).await?;
let runs = opt(db, "SELECT log_time, log_stream, kind, relation, aggressive, index_scans, pages_removed, pages_remain, tuples_removed, tuples_remain, tuples_dead_not_removable,
buffer_hits, buffer_misses, wal_bytes, read_mb_s, write_mb_s, elapsed_s
FROM ts_autovacuum_runs WHERE server_id = $1 AND collected_at >= $3 AND collected_at < $4 AND ($2::text IS NULL OR relation LIKE $2 || '.%')
ORDER BY log_time DESC LIMIT 100", &[&server, &datname, &from, &to]).await?;
let progress = opt(db, "SELECT * FROM ts_vacuum_progress WHERE server_id = $1 AND collected_at = (SELECT max(collected_at) FROM ts_vacuum_progress WHERE server_id = $1 AND collected_at > now() - interval '5 minutes')", &[&server]).await?;
let xid = opt(db, "SELECT datname, max(xid_age)::bigint AS xid_age FROM ts_database_stats WHERE server_id = $1 AND collected_at >= $2 GROUP BY 1 ORDER BY 2 DESC", &[&server, &from]).await?;
Ok(
json!({ "needed": needed, "recent_runs": runs, "in_progress": progress, "database_xid_age": xid }),
)
}
pub async fn events(
db: &Db,
server: &str,
from: Ts,
to: Ts,
kind: Option<&str>,
exclude: &[String],
limit: i64,
) -> Result<Value> {
// Excluded kinds are dropped before the limit, so frequent routine kinds cannot crowd out the rest.
Ok(json!(opt(db, "SELECT id, at, instance, datname, kind, subject, before, after FROM events
WHERE server_id = $1 AND at >= $2 AND at < $3 AND ($4::text IS NULL OR kind LIKE $4) AND NOT (kind LIKE ANY($5::text[]))
ORDER BY at DESC LIMIT $6", &[&server, &from, &to, &kind, &exclude, &limit]).await?))
}
pub async fn settings(db: &Db, server: &str, non_default_only: bool) -> Result<Value> {
Ok(json!(opt(db, "SELECT instance, name, setting, unit, source, boot_val, pending_restart, category FROM cur_settings WHERE server_id = $1 AND ($2 = false OR source <> 'default') ORDER BY name", &[&server, &non_default_only]).await?))
}
pub async fn schema(db: &Db, server: &str, datname: &str, relname: Option<&str>) -> Result<Value> {
let rels = opt(db, "SELECT schemaname, relname, relkind, parent, partition_key, partition_bound, n_columns, columns, reloptions, view_definition, first_seen, last_seen
FROM cur_schema_relations WHERE server_id = $1 AND datname = $2 AND ($3::text IS NULL OR relname = $3) AND last_seen > now() - interval '3 hours' ORDER BY 1, 2", &[&server, &datname, &relname]).await?;
let idx = opt(db, "SELECT schemaname, relname, indexname, definition, is_unique, is_primary, is_valid, is_partial, columns FROM cur_schema_indexes WHERE server_id = $1 AND datname = $2 AND ($3::text IS NULL OR relname = $3) AND last_seen > now() - interval '3 hours' ORDER BY 1, 2, 3", &[&server, &datname, &relname]).await?;
let cons = opt(db, "SELECT schemaname, relname, conname, contype, definition, is_validated, referenced_table FROM cur_schema_constraints WHERE server_id = $1 AND datname = $2 AND ($3::text IS NULL OR relname = $3) AND last_seen > now() - interval '3 hours' ORDER BY 1, 2, 3", &[&server, &datname, &relname]).await?;
Ok(json!({ "relations": rels, "indexes": idx, "constraints": cons }))
}
pub async fn log_errors(db: &Db, server: &str, from: Ts, to: Ts, limit: i64) -> Result<Value> {
let errors = opt(db, &format!("SELECT log_time, log_stream, level, class, sqlstate, datname, usename, pid, query_id, message, detail, statement, {} FROM ts_log_errors
WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 ORDER BY log_time DESC LIMIT $4", tag_cols(db, "ts_log_errors").await), &[&server, &from, &to, &limit]).await?;
let summary = opt(db, "SELECT class, sqlstate, left(message, 120) AS message, count(*)::bigint AS n FROM ts_log_errors
WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 GROUP BY 1, 2, 3 ORDER BY 4 DESC LIMIT 20", &[&server, &from, &to]).await?;
let counts = opt(db, "SELECT level, class, sum(count)::bigint AS n FROM ts_logs WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 GROUP BY 1, 2 ORDER BY 3 DESC", &[&server, &from, &to]).await?;
let temp = opt(db, &format!("SELECT log_time, datname, usename, query_id, size_bytes, left(statement, 300) AS statement, {} FROM ts_temp_files WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 ORDER BY size_bytes DESC LIMIT 20", tag_cols(db, "ts_temp_files").await), &[&server, &from, &to]).await?;
Ok(json!({ "summary": summary, "counts": counts, "recent": errors, "temp_files": temp }))
}
/// Host CPU, CPU by user, code path and query, and the checkpoint, bgwriter and Aurora
/// panels, in one object for callers that want the whole picture.
pub async fn system(db: &Db, server: &str, from: Ts, to: Ts) -> Result<Value> {
// One after the other, so this request never holds more than CPU_PAGE_CONCURRENCY connections.
let mut all = cpu(db, server, from, to).await?;
let rest = checkpoints(db, server, from, to).await?;
if let (Some(a), Some(r)) = (all.as_object_mut(), rest.as_object()) {
a.extend(r.clone());
}
Ok(all)
}
pub async fn cpu(db: &Db, server: &str, from: Ts, to: Ts) -> Result<Value> {
let p: &[&(dyn tokio_postgres::types::ToSql + Sync)] = &[&server, &from, &to];
let cpu = opt(db, "SELECT collected_at, instance,
round(((user_jiffies + nice_jiffies + system_jiffies + iowait_jiffies)::numeric / nullif(user_jiffies + nice_jiffies + system_jiffies + iowait_jiffies + idle_jiffies, 0)) * 100, 2)::float8 AS cpu_pct,
round((iowait_jiffies::numeric / nullif(user_jiffies + nice_jiffies + system_jiffies + iowait_jiffies + idle_jiffies, 0)) * 100, 2)::float8 AS iowait_pct
FROM ts_system_cpu WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 ORDER BY 1", p);
let host = opt(db, "SELECT instance, round(sum(user_jiffies + nice_jiffies + system_jiffies + iowait_jiffies + idle_jiffies) / nullif(sum(interval_seconds), 0) / 100.0)::bigint AS ncpu,
sum(interval_seconds)::float8 AS covered_s
FROM ts_system_cpu WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 AND interval_seconds > 0 GROUP BY 1", p);
let mem = opt(db, "SELECT collected_at, instance, mem_used_kb, mem_free_kb, mem_cached_kb, swap_used_kb, load1, load5 FROM ts_system_memory WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 ORDER BY 1", p);
// A jiffy is 10 ms, and /proc/stat sums to 100 jiffies per core per second, which
// is where the core count comes from; `covered` counts each tick once.
const CPU_CTES: &str = "
WITH host AS (
SELECT instance, sum(user_jiffies + nice_jiffies + system_jiffies + iowait_jiffies + idle_jiffies) / nullif(sum(interval_seconds), 0) / 100.0 AS ncpu
FROM ts_system_cpu WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 AND interval_seconds > 0 GROUP BY 1),
covered AS (
SELECT instance, sum(interval_seconds) AS covered_s
FROM (SELECT DISTINCT instance, collected_at, interval_seconds FROM ts_backend_cpu WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3) t GROUP BY 1)";
let by_user_sql = format!("{CPU_CTES},
per_pid AS (
SELECT instance, datname, usename, application_name, backend_type, pid, backend_start, sum(utime_jiffies + stime_jiffies) AS jiffies
FROM ts_backend_cpu WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 GROUP BY 1, 2, 3, 4, 5, 6, 7)
SELECT p.instance, p.datname, p.usename, p.application_name, p.backend_type,
(sum(p.jiffies) / 100.0)::float8 AS cpu_seconds,
(sum(p.jiffies) / 100.0 / nullif(c.covered_s, 0))::float8 AS avg_cores,
(sum(p.jiffies) / nullif(c.covered_s, 0) / nullif(h.ncpu, 0))::float8 AS host_pct,
count(*)::bigint AS backends,
(max(p.jiffies) / 100.0 / nullif(c.covered_s, 0))::float8 AS hottest_backend_cores
FROM per_pid p JOIN covered c ON c.instance = p.instance LEFT JOIN host h ON h.instance = p.instance
GROUP BY 1, 2, 3, 4, 5, c.covered_s, h.ncpu ORDER BY 6 DESC LIMIT 20");
let by_user = opt(db, &by_user_sql, p);
let by_user_series = opt(db, "
WITH b AS (
SELECT collected_at, instance, interval_seconds,
coalesce(usename, backend_type) || CASE WHEN coalesce(application_name, '') <> '' THEN ' (' || application_name || ')' ELSE '' END AS label,
utime_jiffies + stime_jiffies AS jiffies
FROM ts_backend_cpu WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 AND interval_seconds > 0),
top AS (SELECT label FROM b GROUP BY 1 ORDER BY sum(jiffies) DESC LIMIT 7)
SELECT b.collected_at, b.instance, CASE WHEN t.label IS NULL THEN 'other' ELSE b.label END AS label,
(sum(b.jiffies) / 100.0 / max(b.interval_seconds))::float8 AS cores
FROM b LEFT JOIN top t ON t.label = b.label GROUP BY 1, 2, 3 ORDER BY 1", p);
let has_tags = has_column(db, "ts_backend_cpu", "tags").await;
let by_code_path_sql = format!("{CPU_CTES}
SELECT b.instance, b.tags,
(sum(b.utime_jiffies + b.stime_jiffies) / 100.0)::float8 AS cpu_seconds,
(sum(b.utime_jiffies + b.stime_jiffies) / 100.0 / nullif(c.covered_s, 0))::float8 AS avg_cores,
(sum(b.utime_jiffies + b.stime_jiffies) / nullif(c.covered_s, 0) / nullif(h.ncpu, 0))::float8 AS host_pct,
count(DISTINCT (b.pid, b.backend_start))::bigint AS backends
FROM ts_backend_cpu b JOIN covered c ON c.instance = b.instance LEFT JOIN host h ON h.instance = b.instance
WHERE b.server_id = $1 AND b.collected_at >= $2 AND b.collected_at < $3 AND b.tags IS NOT NULL AND b.tags <> '{{}}'::jsonb
GROUP BY 1, 2, c.covered_s, h.ncpu ORDER BY 3 DESC LIMIT 15");
let by_code_path = async {
if has_tags {
opt(db, &by_code_path_sql, p).await
} else {
Ok(vec![])
}
};
let by_query = cpu_by_query(db, server, from, to, CPU_CTES);
// Each panel scans a different table over the whole range. A few run side by side to
// cut the wait, but not all of them, so one page view leaves pool connections for others.
let panels: Vec<BoxFuture<'_, Result<Vec<Value>>>> = vec![
cpu.boxed(),
host.boxed(),
mem.boxed(),
by_user.boxed(),
by_user_series.boxed(),
by_code_path.boxed(),
by_query.boxed(),
];
let panels: Vec<Vec<Value>> = stream::iter(panels)
.buffered(CPU_PAGE_CONCURRENCY)
.collect::<Vec<_>>()
.await
.into_iter()
.collect::<Result<_>>()?;
let [cpu, host, mem, by_user, by_user_series, by_code_path, by_query]: [Vec<Value>; 7] = panels
.try_into()
.map_err(|_| anyhow!("the CPU page expects seven panels"))?;
Ok(json!({
"cpu": cpu, "host": host, "memory": mem,
"cpu_by_user": by_user, "cpu_by_user_series": by_user_series, "cpu_by_code_path": by_code_path, "cpu_by_query": by_query,
}))
}
/// How many of the CPU page's queries run at once against the shared pool.
const CPU_PAGE_CONCURRENCY: usize = 3;
/// One pg_stat_statements entry summed over the range, with the CPU its role used according to /proc.
#[derive(Debug, Clone, PartialEq)]
struct StatementCpu {
instance: String,
rolname: String,
datname: String,
queryid: i64,
calls: f64,
exec_ms: f64,
role_cpu_ms: Option<f64>,
}
#[derive(Debug, Clone, PartialEq)]
struct QueryCpu {
instance: String,
datname: String,
queryid: i64,
calls: f64,
cpu_ms: f64,
share_pct: f64,
}
/// pg_stat_statements counts every call, while the per-minute backend sample only sees the
/// statement running at the tick and misses almost all short queries. So each role's CPU from
/// /proc is split between its queries: execution time minus I/O time first, then the rest
/// (planning, parsing, protocol) by call count. When the role used less CPU than its execution
/// time (the time includes lock and other waits), the time is scaled down instead. A role with
/// no /proc rows keeps its execution time as the estimate. Largest first.
fn allocate_query_cpu(stmts: &[StatementCpu]) -> Vec<QueryCpu> {
let mut roles: HashMap<(&str, &str, &str), (f64, f64)> = HashMap::new();
for s in stmts {
let r = roles
.entry((&s.instance, &s.rolname, &s.datname))
.or_default();
r.0 += s.exec_ms;
r.1 += s.calls;
}
let mut queries: HashMap<(&str, &str, i64), (f64, f64)> = HashMap::new();
for s in stmts {
let (role_exec_ms, role_calls) =
roles[&(s.instance.as_str(), s.rolname.as_str(), s.datname.as_str())];
let cpu_ms = match s.role_cpu_ms {
None => s.exec_ms,
Some(cpu) if cpu >= role_exec_ms && role_calls > 0.0 => {
s.exec_ms + (cpu - role_exec_ms) * s.calls / role_calls
}
Some(_) if role_exec_ms <= 0.0 => 0.0,
Some(cpu) if cpu < role_exec_ms => s.exec_ms * cpu / role_exec_ms,
Some(_) => s.exec_ms,
};
let q = queries
.entry((&s.instance, &s.datname, s.queryid))
.or_default();
q.0 += s.calls;
q.1 += cpu_ms;
}
let mut instance_ms: HashMap<&str, f64> = HashMap::new();
for ((instance, _, _), (_, cpu_ms)) in &queries {
*instance_ms.entry(instance).or_default() += cpu_ms;
}
let mut out: Vec<QueryCpu> = queries
.into_iter()
.filter(|(_, (_, cpu_ms))| *cpu_ms > 0.0)
.map(|((instance, datname, queryid), (calls, cpu_ms))| QueryCpu {
share_pct: 100.0 * cpu_ms / instance_ms[instance],
instance: instance.to_string(),
datname: datname.to_string(),
queryid,
calls,
cpu_ms,
})
.collect();
out.sort_by(|a, b| b.cpu_ms.total_cmp(&a.cpu_ms));
out
}
async fn cpu_by_query(
db: &Db,
server: &str,
from: Ts,
to: Ts,
cpu_ctes: &str,
) -> Result<Vec<Value>> {
let p: &[&(dyn tokio_postgres::types::ToSql + Sync)] = &[&server, &from, &to];
let rows = opt(db, "
WITH stmts AS (
SELECT instance, rolname, datname, queryid, sum(calls)::float8 AS calls,
greatest(sum(total_exec_time + coalesce(total_plan_time, 0) - coalesce(blk_read_time, 0) - coalesce(blk_write_time, 0)), 0)::float8 AS exec_ms
FROM ts_query_stats WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 AND toplevel
GROUP BY 1, 2, 3, 4),
role_cpu AS (
SELECT instance, usename AS rolname, datname, (sum(utime_jiffies + stime_jiffies) * 10.0)::float8 AS cpu_ms
FROM ts_backend_cpu WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 AND backend_type IN ('client backend', 'parallel worker')
GROUP BY 1, 2, 3)
SELECT s.instance, s.rolname, s.datname, s.queryid, s.calls, s.exec_ms, rc.cpu_ms AS role_cpu_ms
FROM stmts s LEFT JOIN role_cpu rc USING (instance, rolname, datname)", p).await?;
let stmts: Vec<StatementCpu> = rows
.iter()
.filter_map(|r| {
Some(StatementCpu {
instance: r["instance"].as_str()?.to_string(),
rolname: r["rolname"].as_str()?.to_string(),
datname: r["datname"].as_str()?.to_string(),
queryid: json_i64(&r["queryid"])?,
calls: r["calls"].as_f64()?,
exec_ms: r["exec_ms"].as_f64()?,
role_cpu_ms: r["role_cpu_ms"].as_f64(),
})
})
.collect();
let mut top = allocate_query_cpu(&stmts);
top.truncate(15);
if top.is_empty() {
return Ok(vec![]);
}
let scale = opt(db, &format!("{cpu_ctes}
SELECT coalesce(c.instance, h.instance) AS instance, c.covered_s::float8 AS covered_s, h.ncpu::float8 AS ncpu
FROM covered c FULL JOIN host h ON h.instance = c.instance"), p).await?;
let scale: HashMap<&str, (Option<f64>, Option<f64>)> = scale
.iter()
.filter_map(|r| {
Some((
r["instance"].as_str()?,
(r["covered_s"].as_f64(), r["ncpu"].as_f64()),
))
})
.collect();
let query_tags = if has_column(db, "cur_queries", "tags").await {
"tags"
} else {
"NULL::jsonb AS tags"
};
let ids: Vec<i64> = top.iter().map(|q| q.queryid).collect();
let texts = opt(db, &format!("SELECT instance, datname, queryid, query, {query_tags} FROM cur_queries WHERE server_id = $1 AND queryid = ANY($2)"), &[&server, &ids]).await?;
let texts: HashMap<(&str, &str, i64), &Value> = texts
.iter()
.filter_map(|r| {
Some((
(
r["instance"].as_str()?,
r["datname"].as_str()?,
json_i64(&r["queryid"])?,
),
r,
))
})
.collect();
Ok(top
.iter()
.map(|q| {
let (covered_s, ncpu) = scale.get(q.instance.as_str()).copied().unwrap_or((None, None));
let text = texts.get(&(q.instance.as_str(), q.datname.as_str(), q.queryid));
// Same shape the SQL rows have: ids past 2^53 travel as strings.
let queryid = if q.queryid.unsigned_abs() > (1u64 << 53) { json!(q.queryid.to_string()) } else { json!(q.queryid) };
json!({
"instance": q.instance, "datname": q.datname, "queryid": queryid,
"query": text.map(|t| t["query"].clone()), "tags": text.map(|t| t["tags"].clone()),
"calls": q.calls.round() as i64,
"cpu_seconds": q.cpu_ms / 1000.0,
"avg_cores": covered_s.filter(|c| *c > 0.0).map(|c| q.cpu_ms / 1000.0 / c),
"host_pct": covered_s.zip(ncpu).filter(|(c, n)| *c > 0.0 && *n > 0.0).map(|(c, n)| q.cpu_ms / 10.0 / c / n),
"share_pct": q.share_pct,
})
})
.collect())
}
pub async fn checkpoints(db: &Db, server: &str, from: Ts, to: Ts) -> Result<Value> {
let checkpoints = opt(db, "SELECT log_time, log_stream, kind, buffers_written, buffers_pct, write_s, sync_s, total_s, distance_kb FROM ts_checkpoints WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 ORDER BY log_time DESC LIMIT 50", &[&server, &from, &to]).await?;
let bgw = opt(db, "SELECT instance, sum(checkpoints_timed)::bigint AS checkpoints_timed, sum(checkpoints_req)::bigint AS checkpoints_req, sum(buffers_checkpoint)::bigint AS buffers_checkpoint, sum(buffers_clean)::bigint AS buffers_clean, sum(buffers_alloc)::bigint AS buffers_alloc
FROM ts_bgwriter WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 GROUP BY 1", &[&server, &from, &to]).await?;
let repl = opt(db, "SELECT * FROM ts_aurora_replica_status WHERE server_id = $1 AND collected_at = (SELECT max(collected_at) FROM ts_aurora_replica_status WHERE server_id = $1 AND collected_at >= $2)", &[&server, &from]).await?;
let latency = opt(db, "SELECT datname, sum(commit_latency_us)::bigint AS commit_latency_us, sum(select_count)::bigint AS selects, sum(update_count)::bigint AS updates,
CASE WHEN sum(update_count) > 0 THEN sum(update_latency_us) / sum(update_count) END::float8 AS avg_update_latency_us
FROM ts_aurora_db_latency WHERE server_id = $1 AND collected_at >= $2 AND collected_at < $3 GROUP BY 1", &[&server, &from, &to]).await?;
Ok(json!({
"checkpoints": checkpoints, "bgwriter": bgw, "aurora_replicas": repl, "aurora_db_latency": latency,
}))
}
pub async fn collector_health(db: &Db) -> Result<Value> {
Ok(json!(db.query("SELECT server_id, instance, collector, count(*)::bigint AS ticks, count(error)::bigint AS errors, round(avg(duration_ms))::bigint AS avg_ms, max(duration_ms)::bigint AS max_ms, max(started_at) AS last_tick,
max(error) AS last_error
FROM collector_runs WHERE started_at > now() - interval '15 minutes' GROUP BY 1, 2, 3 ORDER BY errors DESC, 1, 2, 3", &[]).await?))
}
/// Free-form read-only SQL against the stats DB for agents.
///
/// Defence in depth, because a leading `SELECT` does not make a statement side-effect
/// free (`pg_advisory_lock()`, `pg_sleep()`, `set_config()` are all callable from one):
/// * only a single SELECT / WITH / EXPLAIN statement is accepted;
/// * it runs inside a read-only transaction with its own short statement timeout;
/// * the session is `DISCARD ALL`ed afterwards (drops advisory locks, temp tables,
/// session settings) and, if that fails, thrown away rather than returned to the pool;
/// * the DB user is a read-only role with no privileges beyond SELECT.
pub async fn raw_sql(db: &Db, sql: &str, limit: i64) -> Result<Value> {
let trimmed = sql.trim().trim_end_matches(';').trim();
let head = trimmed
.split_whitespace()
.next()
.unwrap_or("")
.to_ascii_uppercase();
anyhow::ensure!(
matches!(head.as_str(), "SELECT" | "WITH" | "EXPLAIN"),
"only SELECT / WITH / EXPLAIN statements are allowed"
);
anyhow::ensure!(!trimmed.contains(';'), "one statement only");
if let Some(f) = denied_function(trimmed) {
anyhow::bail!("function {f}() is not allowed here");
}
let statement = if head == "EXPLAIN" {
// EXPLAIN cannot be wrapped in a subquery; it returns text rows itself.
trimmed.to_string()
} else {
format!(
"SELECT * FROM ({trimmed}) _q LIMIT {}",
limit.clamp(1, 5000)
)
};
db.query_isolated(&statement).await
}
/// Functions that have effects beyond returning rows and are therefore refused in raw
/// SQL even though the statement is a SELECT. Session isolation (`query_isolated`)
/// already contains most of these; the denylist makes the intent explicit and stops
/// the cheap ones (sleeping, signalling backends, resetting stats) before they run.
const DENIED_FUNCTIONS: &[&str] = &[
"pg_sleep",
"pg_sleep_for",
"pg_sleep_until",
"pg_advisory_lock",
"pg_advisory_lock_shared",
"pg_advisory_xact_lock",
"pg_advisory_xact_lock_shared",
"pg_try_advisory_lock",
"pg_try_advisory_lock_shared",
"pg_try_advisory_xact_lock",
"pg_try_advisory_xact_lock_shared",
"pg_advisory_unlock",
"pg_advisory_unlock_all",
"pg_terminate_backend",
"pg_cancel_backend",
"pg_reload_conf",
"pg_rotate_logfile",
"pg_stat_reset",
"pg_stat_reset_shared",
"pg_stat_reset_single_table_counters",
"pg_stat_reset_single_function_counters",
"pg_stat_statements_reset",
"set_config",
"pg_notify",
"pg_switch_wal",
"pg_create_restore_point",
"pg_backup_start",
"pg_backup_stop",
"pg_start_backup",
"pg_stop_backup",
"pg_read_file",
"pg_read_binary_file",
"pg_ls_dir",
"pg_stat_file",
"lo_import",
"lo_export",
"lo_unlink",
"dblink",
"dblink_connect",
"dblink_exec",
"nextval",
"setval",
"txid_current",
"pg_current_xact_id",
];
fn denied_function(sql: &str) -> Option<&'static str> {
static COMMENT: once_cell::sync::Lazy<regex::Regex> =
once_cell::sync::Lazy::new(|| regex::Regex::new(r"(?s)/\*.*?\*/|--[^\n]*").unwrap());
static CALL: once_cell::sync::Lazy<regex::Regex> =
once_cell::sync::Lazy::new(|| regex::Regex::new(r"(?i)\b([a-z_][a-z0-9_]*)\s*\(").unwrap());
// Comments are legal between a function name and its parenthesis (`pg_sleep/**/(1)`),
// so scan the statement with them removed.
let stripped = COMMENT.replace_all(sql, " ");
CALL.captures_iter(&stripped).find_map(|c| {
let name = c[1].to_ascii_lowercase();
DENIED_FUNCTIONS.iter().copied().find(|d| *d == name)
})
}
pub async fn schema_of_stats_db(db: &Db) -> Result<Value> {
Ok(json!(db.query("SELECT c.relname AS table_name, string_agg(a.attname || ' ' || format_type(a.atttypid, a.atttypmod), ', ' ORDER BY a.attnum) AS columns
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped
WHERE n.nspname = 'public' AND c.relkind IN ('r', 'p') AND c.relname !~ '_[0-9]{8}$' GROUP BY 1 ORDER BY 1", &[]).await?))
}
#[cfg(test)]
mod tests {
use super::{allocate_query_cpu, denied_function, json_i64, parse_tag_filter, StatementCpu};
use serde_json::json;
#[test]
fn tag_filters_accept_both_pair_styles_and_reject_bare_words() {
assert_eq!(
parse_tag_filter("Route=/api/x, service:web").unwrap(),
vec![
("route".into(), "/api/x".into()),
("service".into(), "web".into())
]
);
assert_eq!(
parse_tag_filter("caller=a%2Cb").unwrap(),
vec![("caller".into(), "a,b".into())]
);
assert!(parse_tag_filter("web").is_err());
assert!(parse_tag_filter("=web").is_err());
}
#[test]
fn query_cpu_splits_each_roles_measured_cpu() {
let stmt =
|rolname: &str, queryid: i64, calls: f64, exec_ms: f64, role_cpu_ms: Option<f64>| {
StatementCpu {
instance: "writer".into(),
rolname: rolname.into(),
datname: "app".into(),
queryid,
calls,
exec_ms,
role_cpu_ms,
}
};
let out = allocate_query_cpu(&[
// More CPU than execution time: the 800 ms left over goes 10:30 by calls.
stmt("ingest", 1, 10.0, 100.0, Some(1000.0)),
stmt("ingest", 2, 30.0, 100.0, Some(1000.0)),
// Less CPU than execution time: execution time scales down to fit 50 ms.
stmt("api", 1, 1.0, 100.0, Some(50.0)),
stmt("api", 3, 1.0, 100.0, Some(50.0)),
// No /proc rows: execution time stands.
stmt("cron", 4, 5.0, 40.0, None),
]);
let got: Vec<(i64, f64, f64)> =
out.iter().map(|q| (q.queryid, q.calls, q.cpu_ms)).collect();
assert_eq!(
got,
vec![
(2, 30.0, 700.0),
(1, 11.0, 325.0),
(4, 5.0, 40.0),
(3, 1.0, 25.0)
]
);
let shares: f64 = out.iter().map(|q| q.share_pct).sum();
assert!((shares - 100.0).abs() < 1e-9);
}
#[test]
fn bigints_read_back_from_number_or_string() {
assert_eq!(json_i64(&json!(42)), Some(42));
assert_eq!(
json_i64(&json!("-9200948535353843818")),
Some(-9200948535353843818)
);
assert_eq!(json_i64(&json!(null)), None);
}
#[test]
fn denylist_catches_side_effecting_calls() {
assert_eq!(denied_function("select pg_sleep(20)"), Some("pg_sleep"));
assert_eq!(
denied_function("select PG_Advisory_Lock (1)"),
Some("pg_advisory_lock")
);
assert_eq!(
denied_function("select set_config('x', 'y', false)"),
Some("set_config")
);
assert_eq!(
denied_function("select count(*), max(collected_at) from ts_query_stats"),
None
);
assert_eq!(denied_function("select pg_sleepy from t"), None);
assert_eq!(denied_function("select pg_sleep/**/(1)"), Some("pg_sleep"));
assert_eq!(
denied_function("select pg_sleep -- x\n(1)"),
Some("pg_sleep")
);
assert_eq!(
denied_function("select pg_catalog.pg_advisory_lock/* c */ (1)"),
Some("pg_advisory_lock")
);
}
}