Something went wrong. Try again.
Something went wrong. Try again.
14 kB · 436 lines
SQL
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437-- 04_medium_tests.sql: MEDIUM severity health checksBEGIN;SELECT plan(22);
-- =============================================================================-- Helper: dblink connection string for background sessions-- =============================================================================DO $$BEGIN PERFORM dblink_connect('test_conn', 'dbname=' || current_database() || ' user=' || current_user);EXCEPTION WHEN OTHERS THEN PERFORM dblink_disconnect('test_conn'); PERFORM dblink_connect('test_conn', 'dbname=' || current_database() || ' user=' || current_user);END $$;
-- =============================================================================-- Blocked/Blocking Queries Tests-- =============================================================================
-- Fixture: Table for lock contention testingCREATE TABLE pgfirstaid_test.lock_test_table (id integer PRIMARY KEY, data text);INSERT INTO pgfirstaid_test.lock_test_table VALUES (1, 'test');
-- Start a background transaction that holds a lock via dblinkSELECT dblink_exec('test_conn', 'BEGIN');SELECT dblink_exec('test_conn', 'UPDATE pgfirstaid_test.lock_test_table SET data = ''blocked'' WHERE id = 1');
-- The actual check uses transactionid-based lock detection which requires-- actual transaction ID conflicts. We verify the check executes without error.
-- Test 1: Structural test - check query is valid (no errors)SELECT ok( (SELECT count(*) >= 0 FROM pg_firstAid() WHERE check_name = 'Current Blocked/Blocking Queries'), 'Blocked/Blocking Queries check executes without error (function)');
-- Test 2: View parity structural testSELECT ok( (SELECT count(*) >= 0 FROM v_pgfirstaid WHERE check_name = 'Current Blocked/Blocking Queries'), 'Blocked/Blocking Queries check executes without error (view)');
-- Clean up background connection lockSELECT dblink_exec('test_conn', 'ROLLBACK');
-- =============================================================================-- Outdated Statistics Tests-- =============================================================================
-- Fixture: Table with outdated statisticsCREATE TABLE pgfirstaid_test.outdated_stats_table ( id serial PRIMARY KEY, data text) WITH (autovacuum_enabled = false);
-- Insert initial data and analyzeINSERT INTO pgfirstaid_test.outdated_stats_table (data)SELECT md5(g::text) FROM generate_series(1, 1000) g;ANALYZE pgfirstaid_test.outdated_stats_table;
-- Make many modifications to exceed thresholds-- Default threshold: reltuples * 0.1 + 50 for analyze-- With 1000 rows: 1000 * 0.1 + 50 = 150 modifications neededUPDATE pgfirstaid_test.outdated_stats_table SET data = md5(data) WHERE id <= 500;DELETE FROM pgfirstaid_test.outdated_stats_table WHERE id > 800;
-- Test 3: Outdated statistics detected by functionSELECT ok( EXISTS( SELECT 1 FROM pg_firstAid() WHERE check_name = 'Outdated Statistics' AND object_name LIKE '%outdated_stats_table%' ), 'Function detects table with outdated statistics');
-- Test 4: View paritySELECT ok( EXISTS( SELECT 1 FROM v_pgfirstaid WHERE check_name = 'Outdated Statistics' AND object_name LIKE '%outdated_stats_table%' ), 'View detects table with outdated statistics');
-- =============================================================================-- Low Index Efficiency Tests-- =============================================================================
-- Fixture: Table with low-selectivity indexCREATE TABLE pgfirstaid_test.low_eff_table ( id serial PRIMARY KEY, flag boolean, padding text);
-- Insert rows with low cardinality on flag columnINSERT INTO pgfirstaid_test.low_eff_table (flag, padding)SELECT (g % 2 = 0), repeat('x', 100)FROM generate_series(1, 100000) g;
CREATE INDEX idx_low_eff ON pgfirstaid_test.low_eff_table(flag);ANALYZE pgfirstaid_test.low_eff_table;
-- Run >100 queries that use the index but read many tuples per scan-- Each scan reads ~50,000 tuples (half the table)DO $$DECLARE i integer;BEGIN FOR i IN 1..110 LOOP PERFORM count(*) FROM pgfirstaid_test.low_eff_table WHERE flag = true; END LOOP;END $$;
-- Test 5: Low index efficiency detected by function-- Threshold: idx_scan > 100 AND idx_tup_read/idx_scan > 1000SELECT ok( EXISTS( SELECT 1 FROM pg_firstAid() WHERE check_name = 'Low Index Efficiency' AND object_name LIKE '%idx_low_eff%' ), 'Function detects low index efficiency (>100 scans, ratio >1000)');
-- Test 6: View paritySELECT ok( EXISTS( SELECT 1 FROM v_pgfirstaid WHERE check_name = 'Low Index Efficiency' AND object_name LIKE '%idx_low_eff%' ), 'View detects low index efficiency');
-- =============================================================================-- Replication Slots Near Max WAL Size (Structural Test)-- =============================================================================
-- Test 7: No false positives for replication WAL checkSELECT ok( (SELECT count(*) >= 0 FROM pg_firstAid() WHERE check_name = 'Replication Slots Near Max Wal Size'), 'Replication Slots Near Max Wal Size check executes without error (function)');
-- Test 8: View paritySELECT ok( (SELECT count(*) >= 0 FROM v_pgfirstaid WHERE check_name = 'Replication Slots Near Max Wal Size'), 'Replication Slots Near Max Wal Size check executes without error (view)');
-- =============================================================================-- Excessive Sequential Scans Tests-- =============================================================================
-- Fixture: Table with no indexes on queried columnCREATE TABLE pgfirstaid_test.seq_scan_table ( id serial, category text, value integer);
INSERT INTO pgfirstaid_test.seq_scan_table (category, value)SELECT 'cat_' || (g % 10), gFROM generate_series(1, 5000) g;
-- Run >1000 sequential scans with WHERE on non-indexed columnDO $$DECLARE i integer;BEGIN FOR i IN 1..1100 LOOP PERFORM count(*) FROM pgfirstaid_test.seq_scan_table WHERE category = 'cat_5'; END LOOP;END $$;
-- Test 9: Excessive sequential scans detected by functionSELECT ok( EXISTS( SELECT 1 FROM pg_firstAid() WHERE check_name = 'Excessive Sequential Scans' AND object_name LIKE '%seq_scan_table%' ), 'Function detects excessive sequential scans (>1000 scans, high tuple ratio)');
-- Test 10: View paritySELECT ok( EXISTS( SELECT 1 FROM v_pgfirstaid WHERE check_name = 'Excessive Sequential Scans' AND object_name LIKE '%seq_scan_table%' ), 'View detects excessive sequential scans');
-- =============================================================================-- Tables With 50-199 Columns Tests-- =============================================================================
-- Fixture: Table with 75 columnsDO $$DECLARE col_list text := ''; i integer;BEGIN FOR i IN 1..75 LOOP IF i > 1 THEN col_list := col_list || ', '; END IF; col_list := col_list || 'col_' || i || ' integer'; END LOOP; EXECUTE 'CREATE TABLE pgfirstaid_test.wide_table_75 (' || col_list || ')';END $$;
-- Test 11: 75-column table detected at MEDIUM severity by functionSELECT ok( EXISTS( SELECT 1 FROM pg_firstAid() WHERE check_name = 'Table with more than 50 columns' AND severity = 'MEDIUM' AND object_name LIKE '%wide_table_75%' ), 'Function detects table with 50-199 columns at MEDIUM severity');
-- Test 12: View paritySELECT ok( EXISTS( SELECT 1 FROM v_pgfirstaid WHERE check_name = 'Table with more than 50 columns' AND severity = 'MEDIUM' AND object_name LIKE '%wide_table_75%' ), 'View detects table with 50-199 columns at MEDIUM severity');
-- Test 13: 75-column table NOT in >200 column HIGH checkSELECT ok( NOT EXISTS( SELECT 1 FROM pg_firstAid() WHERE check_name = 'Table with more than 200 columns' AND object_name LIKE '%wide_table_75%' ), '75-column table not flagged for >200 column check');
-- =============================================================================-- High Connection Count Tests-- =============================================================================
-- Disconnect test_conn before opening many connectionsSELECT dblink_disconnect('test_conn');
-- Open 50+ connections via dblink to trigger the thresholdDO $$DECLARE i integer; conn_name text; conn_str text;BEGIN conn_str := 'dbname=' || current_database() || ' user=' || current_user; FOR i IN 1..52 LOOP conn_name := 'conn_' || i; BEGIN PERFORM dblink_connect(conn_name, conn_str); -- Start an active query on each connection PERFORM dblink_send_query(conn_name, 'SELECT pg_sleep(30)'); EXCEPTION WHEN OTHERS THEN -- Skip if max_connections reached EXIT; END; END LOOP;END $$;
-- Test 14: High connection count detected by functionSELECT ok( EXISTS( SELECT 1 FROM pg_firstAid() WHERE check_name = 'High Connection Count' ), 'Function detects high connection count (>50 active)');
-- Test 15: View paritySELECT ok( EXISTS( SELECT 1 FROM v_pgfirstaid WHERE check_name = 'High Connection Count' ), 'View detects high connection count');
-- Clean up all dblink connectionsDO $$DECLARE i integer; conn_name text;BEGIN FOR i IN 1..52 LOOP conn_name := 'conn_' || i; BEGIN PERFORM dblink_cancel_query(conn_name); PERFORM dblink_disconnect(conn_name); EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP;END $$;
-- =============================================================================-- Tables Larger Than 50GB (Structural Test)-- =============================================================================
-- Test 16: No false positives for 50GB check on small test tablesSELECT ok( NOT EXISTS( SELECT 1 FROM pg_firstAid() WHERE check_name = 'Tables larger than 100GB' AND severity = 'MEDIUM' AND object_name LIKE '%pgfirstaid_test%' ), 'No false positive 50GB warnings for test tables (function)');
-- Test 17: View paritySELECT ok( NOT EXISTS( SELECT 1 FROM v_pgfirstaid WHERE check_name = 'Tables larger than 100GB' AND severity_order = 3 AND object_name LIKE '%pgfirstaid_test%' ), 'No false positive 50GB warnings for test tables (view)');
-- =============================================================================-- Long Running Queries Tests-- =============================================================================
-- Re-establish a connection for long running query testDO $$BEGIN PERFORM dblink_connect('long_query_conn', 'dbname=' || current_database() || ' user=' || current_user);EXCEPTION WHEN OTHERS THEN PERFORM dblink_disconnect('long_query_conn'); PERFORM dblink_connect('long_query_conn', 'dbname=' || current_database() || ' user=' || current_user);END $$;
-- Start a long-running query (pg_sleep for 10 minutes)-- Sent async so it runs in backgroundSELECT dblink_send_query('long_query_conn', 'SELECT pg_sleep(600)');
-- Wait briefly to ensure the query registers in pg_stat_activitySELECT pg_sleep(2);
-- Test 18: Newly started query (< 5 min) not flagged as long runningSELECT ok( NOT EXISTS( SELECT 1 FROM pg_firstAid() WHERE check_name = 'Long Running Queries' AND current_value LIKE '%pg_sleep(600)%' ), 'Newly started query (< 5 min) not flagged as long running');
-- Test 19: View paritySELECT ok( NOT EXISTS( SELECT 1 FROM v_pgfirstaid WHERE check_name = 'Long Running Queries' AND current_value LIKE '%pg_sleep(600)%' ), 'View: Newly started query (< 5 min) not flagged as long running');
-- Test 20: Verify the check logic is valid SQLSELECT ok( (SELECT count(*) >= 0 FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '5 minutes'), 'Long running query detection logic is valid SQL');
-- Clean up background connectionDO $$BEGIN PERFORM dblink_cancel_query('long_query_conn'); PERFORM dblink_disconnect('long_query_conn');EXCEPTION WHEN OTHERS THEN NULL;END $$;
-- =============================================================================-- Negative Tests-- =============================================================================
-- Fixture: Table with proper index (should not trigger seq scan warning)CREATE TABLE pgfirstaid_test.well_indexed_table ( id serial PRIMARY KEY, category text);INSERT INTO pgfirstaid_test.well_indexed_table (category)SELECT 'cat_' || g FROM generate_series(1, 100) g;CREATE INDEX idx_well_indexed_cat ON pgfirstaid_test.well_indexed_table(category);ANALYZE pgfirstaid_test.well_indexed_table;
-- Test 21: Well-indexed table not flagged for sequential scansSELECT ok( NOT EXISTS( SELECT 1 FROM pg_firstAid() WHERE check_name = 'Excessive Sequential Scans' AND object_name LIKE '%well_indexed_table%' ), 'Well-indexed table not flagged for excessive sequential scans');
-- Test 22: Small table not flagged for low index efficiencySELECT ok( NOT EXISTS( SELECT 1 FROM pg_firstAid() WHERE check_name = 'Low Index Efficiency' AND object_name LIKE '%well_indexed_table%' ), 'Table with efficient index not flagged for low efficiency');
SELECT * FROM finish();ROLLBACK;