/**
* postgres-mcp - pg_stat_kcache Extension Tools Unit Tests
*
* Tests for OS-level performance monitoring tools.
*/
import { describe, it, expect, vi, beforeEach } from 'vitest';
import type { PostgresAdapter } from '../../PostgresAdapter.js';
import { createMockPostgresAdapter, createMockRequestContext } from '../../../../__tests__/mocks/index.js';
import { getKcacheTools } from '../kcache.js';
describe('Kcache Tools', () => {
let mockAdapter: ReturnType<typeof createMockPostgresAdapter>;
let mockContext: ReturnType<typeof createMockRequestContext>;
let tools: ReturnType<typeof getKcacheTools>;
beforeEach(() => {
vi.clearAllMocks();
mockAdapter = createMockPostgresAdapter();
mockContext = createMockRequestContext();
tools = getKcacheTools(mockAdapter as unknown as PostgresAdapter);
});
const findTool = (name: string) => tools.find(t => t.name === name);
describe('pg_kcache_create_extension', () => {
it('should fail if pg_stat_statements not installed', async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ installed: false }]
});
const tool = findTool('pg_kcache_create_extension');
const result = await tool!.handler({}, mockContext) as { success: boolean; error?: string };
expect(result.success).toBe(false);
expect(result.error).toContain('pg_stat_statements');
});
it('should create extension when pg_stat_statements exists', async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ installed: true }] })
.mockResolvedValueOnce({ rows: [] });
const tool = findTool('pg_kcache_create_extension');
const result = await tool!.handler({}, mockContext) as { success: boolean; message: string };
expect(result.success).toBe(true);
expect(result.message).toContain('pg_stat_kcache');
expect(mockAdapter.executeQuery).toHaveBeenLastCalledWith(
expect.stringContaining('CREATE EXTENSION IF NOT EXISTS pg_stat_kcache')
);
});
});
describe('pg_kcache_query_stats', () => {
it('should return query stats with CPU/IO metrics', async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{
queryid: '12345',
query_preview: 'SELECT * FROM users',
calls: 100,
total_time_ms: 5000,
user_time: 2.5,
system_time: 0.5,
reads: 1024000,
writes: 512000
}]
});
const tool = findTool('pg_kcache_query_stats');
const result = await tool!.handler({}, mockContext) as {
queries: unknown[];
count: number;
orderBy: string;
};
expect(result.count).toBe(1);
expect(result.orderBy).toBe('total_time');
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining('pg_stat_kcache()'),
[]
);
});
it('should order by CPU time when specified', async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = findTool('pg_kcache_query_stats');
await tool!.handler({ orderBy: 'cpu_time' }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining('(k.user_time + k.system_time)'),
[]
);
});
it('should filter by minimum calls', async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = findTool('pg_kcache_query_stats');
await tool!.handler({ minCalls: 10 }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining('s.calls >='),
[10]
);
});
it('should apply limit', async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = findTool('pg_kcache_query_stats');
await tool!.handler({ limit: 5 }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining('LIMIT 5'),
[]
);
});
it('should order by reads when specified (orderBy branch)', async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = findTool('pg_kcache_query_stats');
await tool!.handler({ orderBy: 'reads' }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining('k.reads'),
[]
);
});
it('should order by writes when specified (orderBy branch)', async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = findTool('pg_kcache_query_stats');
await tool!.handler({ orderBy: 'writes' }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining('k.writes'),
[]
);
});
});
describe('pg_kcache_top_cpu', () => {
it('should return top CPU-consuming queries', async () => {
// First call: column detection (empty = old version)
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
// Second call: actual query
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{
queryid: '12345',
query_preview: 'SELECT complex_function()',
user_time: 10.5,
system_time: 2.5,
total_cpu_time: 13
}]
});
const tool = findTool('pg_kcache_top_cpu');
const result = await tool!.handler({}, mockContext) as {
topCpuQueries: unknown[];
description: string;
};
expect(result.topCpuQueries).toHaveLength(1);
expect(result.description).toContain('CPU');
});
it('should apply custom limit', async () => {
// First call: column detection
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
// Second call: actual query
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = findTool('pg_kcache_top_cpu');
await tool!.handler({ limit: 5 }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenLastCalledWith(
expect.stringContaining('LIMIT 5')
);
});
});
describe('pg_kcache_top_io', () => {
it('should return top I/O queries by default (both)', async () => {
// First call: column detection
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
// Second call: actual query
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{
queryid: '12345',
read_bytes: 1024000,
write_bytes: 512000,
total_io_bytes: 1536000
}]
});
const tool = findTool('pg_kcache_top_io');
const result = await tool!.handler({}, mockContext) as {
topIoQueries: unknown[];
ioType: string;
};
expect(result.topIoQueries).toHaveLength(1);
expect(result.ioType).toBe('both');
});
it('should filter by reads only', async () => {
// First call: column detection
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
// Second call: actual query
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = findTool('pg_kcache_top_io');
await tool!.handler({ type: 'reads' }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenLastCalledWith(
expect.stringContaining('ORDER BY k.reads DESC')
);
});
it('should filter by writes only', async () => {
// First call: column detection
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
// Second call: actual query
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = findTool('pg_kcache_top_io');
await tool!.handler({ type: 'writes' }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenLastCalledWith(
expect.stringContaining('ORDER BY k.writes DESC')
);
});
it('should support ioType alias for type parameter', async () => {
// First call: column detection
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
// Second call: actual query
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ queryid: '1', read_bytes: 1000 }]
});
const tool = findTool('pg_kcache_top_io');
const result = await tool!.handler({ ioType: 'reads' }, mockContext) as {
topIoQueries: unknown[];
ioType: string;
};
expect(result.ioType).toBe('reads');
expect(mockAdapter.executeQuery).toHaveBeenLastCalledWith(
expect.stringContaining('ORDER BY k.reads DESC')
);
});
});
describe('pg_kcache_database_stats', () => {
it('should return aggregated stats for all databases', async () => {
// First call: column detection
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
// Second call: actual query
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [
{ database: 'testdb', total_cpu_time: 100.5, total_reads: 1024000 },
{ database: 'proddb', total_cpu_time: 500.2, total_reads: 5120000 }
]
});
const tool = findTool('pg_kcache_database_stats');
const result = await tool!.handler({}, mockContext) as {
databaseStats: unknown[];
count: number;
};
expect(result.count).toBe(2);
expect(mockAdapter.executeQuery).toHaveBeenLastCalledWith(
expect.stringContaining('GROUP BY datname'),
[]
);
});
it('should filter by specific database', async () => {
// First call: column detection
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
// Second call: actual query
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ database: 'testdb', total_cpu_time: 100.5 }]
});
const tool = findTool('pg_kcache_database_stats');
await tool!.handler({ database: 'testdb' }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenLastCalledWith(
expect.stringContaining('d.datname = $1'),
['testdb']
);
});
});
describe('pg_kcache_resource_analysis', () => {
it('should classify queries as CPU-bound or I/O-bound', async () => {
// First call: column detection
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
// Second call: actual query
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [
{ queryid: '1', resource_classification: 'CPU-bound', cpu_time: 10, io_bytes: 1000 },
{ queryid: '2', resource_classification: 'I/O-bound', cpu_time: 1, io_bytes: 10000000 },
{ queryid: '3', resource_classification: 'Balanced', cpu_time: 5, io_bytes: 5000000 }
]
});
const tool = findTool('pg_kcache_resource_analysis');
const result = await tool!.handler({}, mockContext) as {
queries: unknown[];
summary: { cpuBound: number; ioBound: number; balanced: number };
};
expect(result.summary.cpuBound).toBe(1);
expect(result.summary.ioBound).toBe(1);
expect(result.summary.balanced).toBe(1);
});
it('should filter by specific queryId', async () => {
// First call: column detection
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
// Second call: actual query
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = findTool('pg_kcache_resource_analysis');
await tool!.handler({ queryId: '12345' }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenLastCalledWith(
expect.stringContaining("s.queryid::text = $1"),
['12345']
);
});
it('should apply custom limit', async () => {
// First call: column detection
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
// Second call: actual query
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = findTool('pg_kcache_resource_analysis');
await tool!.handler({ limit: 5 }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenLastCalledWith(
expect.stringContaining('LIMIT 5'),
[]
);
});
it('should provide recommendations based on analysis', async () => {
// First call: column detection
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
// Second call: actual query
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [
{ resource_classification: 'CPU-bound' },
{ resource_classification: 'CPU-bound' },
{ resource_classification: 'I/O-bound' }
]
});
const tool = findTool('pg_kcache_resource_analysis');
const result = await tool!.handler({}, mockContext) as {
recommendations: string[];
};
expect(result.recommendations[0]).toContain('CPU');
});
it('should recommend I/O optimization when I/O-bound > CPU-bound', async () => {
// First call: column detection
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
// Second call: actual query
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [
{ resource_classification: 'I/O-bound' },
{ resource_classification: 'I/O-bound' },
{ resource_classification: 'I/O-bound' },
{ resource_classification: 'CPU-bound' }
]
});
const tool = findTool('pg_kcache_resource_analysis');
const result = await tool!.handler({}, mockContext) as {
recommendations: string[];
summary: { cpuBound: number; ioBound: number };
};
expect(result.summary.ioBound).toBe(3);
expect(result.summary.cpuBound).toBe(1);
expect(result.recommendations[0]).toContain('I/O');
});
it('should recommend balanced optimization when workload is balanced', async () => {
// First call: column detection
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
// Second call: actual query
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [
{ resource_classification: 'Balanced' },
{ resource_classification: 'Balanced' },
{ resource_classification: 'Balanced' }
]
});
const tool = findTool('pg_kcache_resource_analysis');
const result = await tool!.handler({}, mockContext) as {
recommendations: string[];
summary: { balanced: number };
};
expect(result.summary.balanced).toBe(3);
// When balanced is dominant, recommendation should be about balance
expect(result.recommendations.length).toBeGreaterThanOrEqual(0);
});
});
describe('pg_kcache_reset', () => {
it('should reset kcache statistics', async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = findTool('pg_kcache_reset');
const result = await tool!.handler({}, mockContext) as {
success: boolean;
message: string;
note: string;
};
expect(result.success).toBe(true);
expect(result.message).toContain('reset');
expect(result.note).toContain('pg_stat_statements');
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining('pg_stat_kcache_reset()')
);
});
});
it('should export all 7 kcache tools', () => {
expect(tools).toHaveLength(7);
const toolNames = tools.map(t => t.name);
expect(toolNames).toContain('pg_kcache_create_extension');
expect(toolNames).toContain('pg_kcache_query_stats');
expect(toolNames).toContain('pg_kcache_top_cpu');
expect(toolNames).toContain('pg_kcache_top_io');
expect(toolNames).toContain('pg_kcache_database_stats');
expect(toolNames).toContain('pg_kcache_resource_analysis');
expect(toolNames).toContain('pg_kcache_reset');
});
describe('No-Arg Calls (undefined params)', () => {
it('pg_kcache_top_cpu should work with undefined params', async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [{ queryid: '1', total_cpu_time: 10 }] });
const tool = findTool('pg_kcache_top_cpu');
const result = await tool!.handler(undefined, mockContext) as { topCpuQueries: unknown[] };
expect(result.topCpuQueries).toHaveLength(1);
});
it('pg_kcache_top_io should work with undefined params', async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [{ queryid: '1', total_io_bytes: 1000 }] });
const tool = findTool('pg_kcache_top_io');
const result = await tool!.handler(undefined, mockContext) as { topIoQueries: unknown[]; ioType: string };
expect(result.topIoQueries).toHaveLength(1);
expect(result.ioType).toBe('both');
});
it('pg_kcache_database_stats should work with undefined params', async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [{ database: 'testdb' }] });
const tool = findTool('pg_kcache_database_stats');
const result = await tool!.handler(undefined, mockContext) as { databaseStats: unknown[] };
expect(result.databaseStats).toHaveLength(1);
});
it('pg_kcache_query_stats should work with undefined params', async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [{ queryid: '1' }] });
const tool = findTool('pg_kcache_query_stats');
const result = await tool!.handler(undefined, mockContext) as { queries: unknown[] };
expect(result.queries).toHaveLength(1);
});
it('pg_kcache_resource_analysis should work with undefined params', async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [{ resource_classification: 'Balanced' }] });
const tool = findTool('pg_kcache_resource_analysis');
const result = await tool!.handler(undefined, mockContext) as { queries: unknown[] };
expect(result.queries).toHaveLength(1);
});
});
});
