test-patch-comprehensive.sql

-- ============================================================================
-- Test Script: Add temp table monitoring columns to pg_stat_database
--
-- Patch: v1-0001-Add-temp-table-monitoring-columns-to-pg_stat_data.patch
-- Thread: [PATCH v1] Add temp table monitoring columns to pg_stat_database
--
-- This script verifies the new columns added to pg_stat_database:
--   temp_tables        - number of temporary tables created
--   local_blks_hit     - local buffer cache hits (temp tables)
--   local_blks_read    - local blocks read from disk (temp tables)
--   local_blks_dirtied - local blocks dirtied (temp tables)
--   local_blks_written - local blocks written to disk (temp tables)
--
-- Prerequisites:
--   - PostgreSQL 19devel built with the patch applied
--   - Connected to a test database
--
-- Usage:
--   createdb tempt
--   psql -d tempt -f test-patch-comprehensive.sql
-- ============================================================================

\echo '============================================================'
\echo 'Temp Table Monitoring Patch - Comprehensive Test Suite'
\echo '============================================================'
\echo ''

\x on

-- Reset stats for clean baseline
SELECT pg_stat_reset();
SELECT pg_sleep(2);

-- ============================================================================
\echo '--- Test 1: Verify new columns exist in pg_stat_database ---'
-- ============================================================================

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'pg_stat_database'
  AND column_name IN ('temp_tables', 'local_blks_hit', 'local_blks_read',
                      'local_blks_dirtied', 'local_blks_written')
ORDER BY column_name;

-- Expected: 5 rows showing all new columns as bigint

-- ============================================================================
\echo ''
\echo '--- Test 2: Baseline - all counters start at zero ---'
-- ============================================================================

SELECT datname,
       temp_tables,
       local_blks_hit,
       local_blks_read,
       local_blks_dirtied,
       local_blks_written
FROM pg_stat_database
WHERE datname = current_database();

-- Expected: All zeros after reset

-- ============================================================================
\echo ''
\echo '--- Test 3: temp_tables increments on CREATE TEMP TABLE ---'
-- ============================================================================

CREATE TEMP TABLE t1 (id int);
CREATE TEMP TABLE t2 (id int, data text);
CREATE TEMP TABLE t3 AS SELECT generate_series(1, 100) AS id;

SELECT pg_sleep(2);

SELECT datname, temp_tables
FROM pg_stat_database
WHERE datname = current_database();

-- Expected: temp_tables = 3

-- ============================================================================
\echo ''
\echo '--- Test 4: Regular tables do NOT increment temp_tables ---'
-- ============================================================================

CREATE TABLE regular_test (id int);
CREATE UNLOGGED TABLE unlogged_test (id int);

SELECT pg_sleep(2);

SELECT datname, temp_tables
FROM pg_stat_database
WHERE datname = current_database();

-- Expected: temp_tables still = 3 (regular/unlogged don't count)

DROP TABLE regular_test;
DROP TABLE unlogged_test;

-- ============================================================================
\echo ''
\echo '--- Test 5: local_blks_written tracks temp table disk writes ---'
-- ============================================================================

SELECT pg_stat_reset();
SELECT pg_sleep(2);

CREATE TEMP TABLE write_test AS
SELECT i, md5(i::text) as hash
FROM generate_series(1, 10000) i;

SELECT pg_sleep(2);

SELECT datname,
       temp_tables,
       local_blks_written,
       pg_size_pretty(local_blks_written * 8192) as written_size
FROM pg_stat_database
WHERE datname = current_database();

-- Expected: temp_tables = 1, local_blks_written > 0

-- ============================================================================
\echo ''
\echo '--- Test 6: local_blks_hit tracks cache hits ---'
-- ============================================================================

-- Scan the table multiple times (data should be in cache)
SELECT count(*) FROM write_test;
SELECT count(*) FROM write_test WHERE i > 5000;
SELECT avg(i) FROM write_test;

SELECT pg_sleep(2);

SELECT datname,
       local_blks_hit,
       local_blks_read,
       CASE
           WHEN (local_blks_hit + local_blks_read) > 0
           THEN round(100.0 * local_blks_hit / (local_blks_hit + local_blks_read), 2)
           ELSE 0
       END as temp_hit_ratio_pct
FROM pg_stat_database
WHERE datname = current_database();

-- Expected: local_blks_hit > 0, hit ratio should be high (near 100%)

-- ============================================================================
\echo ''
\echo '--- Test 7: local_blks_dirtied tracks UPDATE operations ---'
-- ============================================================================

SELECT pg_stat_reset();
SELECT pg_sleep(2);

CREATE TEMP TABLE dirty_test (id int, value int);
INSERT INTO dirty_test SELECT i, i * 2 FROM generate_series(1, 5000) i;

-- Update rows to dirty blocks
UPDATE dirty_test SET value = value + 100 WHERE id <= 2500;

SELECT pg_sleep(2);

SELECT datname,
       local_blks_dirtied,
       local_blks_written
FROM pg_stat_database
WHERE datname = current_database();

-- Expected: local_blks_dirtied > 0

-- ============================================================================
\echo ''
\echo '--- Test 8: local_blks_read with small temp_buffers ---'
-- ============================================================================

-- Reconnect to get a fresh session. temp_buffers can only be set before
-- first temp table access in a session. Without \connect you get:
--
--   ERROR:  invalid value for parameter "temp_buffers": 100
--   DETAIL:  "temp_buffers" cannot be changed after any temporary tables
--            have been accessed in the session.
--
\connect
\x on

SELECT pg_stat_reset();
SELECT pg_sleep(2);

\echo '  Setting temp_buffers = 800kB (minimum allowed) to force disk reads'
\echo '  when temp table data exceeds the local buffer cache size.'
SET temp_buffers = '800kB';

CREATE TEMP TABLE read_test AS
SELECT i, repeat('data', 50) as padding
FROM generate_series(1, 20000) i;

-- Scan to force reads from disk (exceeds 800kB cache)
SELECT count(*) FROM read_test;
SELECT count(*) FROM read_test WHERE i > 10000;

SELECT pg_sleep(2);

SELECT datname,
       local_blks_hit AS temp_cache_hits,
       local_blks_read AS temp_disk_reads,
       local_blks_written AS temp_disk_writes,
       CASE
           WHEN (local_blks_hit + local_blks_read) > 0
           THEN round(100.0 * local_blks_hit / (local_blks_hit + local_blks_read), 2)
           ELSE 0
       END as temp_hit_ratio_pct
FROM pg_stat_database
WHERE datname = current_database();

-- Expected: local_blks_read > 0 (cache too small for data)
-- Expected: hit ratio < 100% (some reads had to go to disk)

-- ============================================================================
\echo ''
\echo '--- Test 9: pg_stat_reset clears all counters ---'
-- ============================================================================

SELECT pg_stat_reset();
SELECT pg_sleep(2);

SELECT datname,
       temp_tables,
       local_blks_hit,
       local_blks_read,
       local_blks_dirtied,
       local_blks_written
FROM pg_stat_database
WHERE datname = current_database();

-- Expected: All zeros after reset

-- ============================================================================
\echo ''
\echo '--- Test 10: Temp table with ON COMMIT DROP still counts ---'
-- ============================================================================

BEGIN;
CREATE TEMP TABLE on_commit_test (id int) ON COMMIT DROP;
INSERT INTO on_commit_test VALUES (1), (2), (3);
COMMIT;

SELECT pg_sleep(2);

SELECT datname, temp_tables
FROM pg_stat_database
WHERE datname = current_database();

-- Expected: temp_tables = 1 (creation counted even though table was dropped)

-- ============================================================================
\echo ''
\echo '--- Test 11: Multiple temp tables in one transaction ---'
-- ============================================================================

BEGIN;
CREATE TEMP TABLE multi1 (id int);
CREATE TEMP TABLE multi2 (id int);
CREATE TEMP TABLE multi3 (id int);
COMMIT;

SELECT pg_sleep(2);

SELECT datname, temp_tables
FROM pg_stat_database
WHERE datname = current_database();

-- Expected: temp_tables = 4 (1 from test 10 + 3 from this test)

-- ============================================================================
\echo ''
\echo '--- Test 12: Use Case - Detecting Undersized temp_buffers ---'
-- ============================================================================

-- This test demonstrates the primary use case: comparing cache efficiency
-- with different temp_buffers settings to identify sizing issues.

\echo ''
\echo '  Scenario A: Small cache (800kB) - same workload'

-- Fresh session needed for SET temp_buffers
\connect
\x on

SELECT pg_stat_reset();
SELECT pg_sleep(2);

SET temp_buffers = '800kB';

CREATE TEMP TABLE sizing_test_small AS
SELECT i, md5(i::text) as hash, repeat('x', 80) as padding
FROM generate_series(1, 30000) i;

SELECT count(*) FROM sizing_test_small;
SELECT count(*) FROM sizing_test_small WHERE i > 15000;
SELECT avg(i) FROM sizing_test_small;

SELECT pg_sleep(2);

SELECT
    'Small Cache (800kB)' as scenario,
    temp_tables,
    local_blks_hit,
    local_blks_read,
    local_blks_written,
    CASE
        WHEN (local_blks_hit + local_blks_read) > 0
        THEN round(100.0 * local_blks_hit / (local_blks_hit + local_blks_read), 2)
        ELSE 0
    END as hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

-- Expected: hit_ratio < 100% (cache too small, disk reads needed)

\echo ''
\echo '  Scenario B: Large cache (10MB) - same workload'

-- Fresh session needed for SET temp_buffers
\connect
\x on

SELECT pg_stat_reset();
SELECT pg_sleep(2);

SET temp_buffers = '10MB';

CREATE TEMP TABLE sizing_test_large AS
SELECT i, md5(i::text) as hash, repeat('x', 80) as padding
FROM generate_series(1, 30000) i;

SELECT count(*) FROM sizing_test_large;
SELECT count(*) FROM sizing_test_large WHERE i > 15000;
SELECT avg(i) FROM sizing_test_large;

SELECT pg_sleep(2);

SELECT
    'Large Cache (10MB)' as scenario,
    temp_tables,
    local_blks_hit,
    local_blks_read,
    local_blks_written,
    CASE
        WHEN (local_blks_hit + local_blks_read) > 0
        THEN round(100.0 * local_blks_hit / (local_blks_hit + local_blks_read), 2)
        ELSE 0
    END as hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

-- Expected: hit_ratio = 100% (cache large enough, no disk reads)
-- Conclusion: If hit_ratio is low, increase temp_buffers!

-- ============================================================================
\echo ''
\echo '============================================================'
\echo 'All tests complete!'
\echo ''
\echo 'Summary of what was tested:'
\echo '  1. New columns exist in pg_stat_database'
\echo '  2. Counters start at zero after reset'
\echo '  3. temp_tables increments on CREATE TEMP TABLE'
\echo '  4. Regular/unlogged tables do NOT increment'
\echo '  5. local_blks_written tracks disk writes'
\echo '  6. local_blks_hit tracks cache hits'
\echo '  7. local_blks_dirtied tracks UPDATE operations'
\echo '  8. local_blks_read tracks disk reads (small cache)'
\echo '  9. pg_stat_reset clears all counters'
\echo ' 10. ON COMMIT DROP still counts creation'
\echo ' 11. Multiple temp tables in one transaction'
\echo ' 12. Use Case: Detecting undersized temp_buffers (small vs large)'
\echo '============================================================'

-- Final cleanup
SELECT pg_stat_reset();