test-patch-results.out

============================================================
Temp Table Monitoring Patch - Comprehensive Test Suite
============================================================

Expanded display is on.
-[ RECORD 1 ]-+-
pg_stat_reset | 

-[ RECORD 1 ]
pg_sleep | 

--- Test 1: Verify new columns exist in pg_stat_database ---
-[ RECORD 1 ]-------------------
column_name | local_blks_dirtied
data_type   | bigint
-[ RECORD 2 ]-------------------
column_name | local_blks_hit
data_type   | bigint
-[ RECORD 3 ]-------------------
column_name | local_blks_read
data_type   | bigint
-[ RECORD 4 ]-------------------
column_name | local_blks_written
data_type   | bigint
-[ RECORD 5 ]-------------------
column_name | temp_tables
data_type   | bigint


--- Test 2: Baseline - all counters start at zero ---
-[ RECORD 1 ]------+------
datname            | tempt
temp_tables        | 0
local_blks_hit     | 0
local_blks_read    | 0
local_blks_dirtied | 0
local_blks_written | 0


--- Test 3: temp_tables increments on CREATE TEMP TABLE ---
CREATE TABLE
CREATE TABLE
SELECT 100
-[ RECORD 1 ]
pg_sleep | 

-[ RECORD 1 ]------
datname     | tempt
temp_tables | 3


--- Test 4: Regular tables do NOT increment temp_tables ---
CREATE TABLE
CREATE TABLE
-[ RECORD 1 ]
pg_sleep | 

-[ RECORD 1 ]------
datname     | tempt
temp_tables | 3

DROP TABLE
DROP TABLE

--- Test 5: local_blks_written tracks temp table disk writes ---
-[ RECORD 1 ]-+-
pg_stat_reset | 

-[ RECORD 1 ]
pg_sleep | 

SELECT 10000
-[ RECORD 1 ]
pg_sleep | 

-[ RECORD 1 ]------+--------
datname            | tempt
temp_tables        | 1
local_blks_written | 128
written_size       | 1024 kB


--- Test 6: local_blks_hit tracks cache hits ---
-[ RECORD 1 ]
count | 10000

-[ RECORD 1 ]
count | 5000

-[ RECORD 1 ]--------------
avg | 5000.5000000000000000

-[ RECORD 1 ]
pg_sleep | 

-[ RECORD 1 ]------+-------
datname            | tempt
local_blks_hit     | 460
local_blks_read    | 0
temp_hit_ratio_pct | 100.00


--- Test 7: local_blks_dirtied tracks UPDATE operations ---
-[ RECORD 1 ]-+-
pg_stat_reset | 

-[ RECORD 1 ]
pg_sleep | 

CREATE TABLE
INSERT 0 5000
UPDATE 2500
-[ RECORD 1 ]
pg_sleep | 

-[ RECORD 1 ]------+------
datname            | tempt
local_blks_dirtied | 34
local_blks_written | 37


--- Test 8: local_blks_read with small temp_buffers ---
You are now connected to database "tempt" as user "mohamed".
Expanded display is on.
-[ RECORD 1 ]-+-
pg_stat_reset | 

-[ RECORD 1 ]
pg_sleep | 

  Setting temp_buffers = 800kB (minimum allowed) to force disk reads
  when temp table data exceeds the local buffer cache size.
SET
SELECT 20000
-[ RECORD 1 ]
count | 20000

-[ RECORD 1 ]
count | 10000

-[ RECORD 1 ]
pg_sleep | 

-[ RECORD 1 ]------+------
datname            | tempt
temp_cache_hits    | 573
temp_disk_reads    | 1280
temp_disk_writes   | 1818
temp_hit_ratio_pct | 30.92


--- Test 9: pg_stat_reset clears all counters ---
-[ RECORD 1 ]-+-
pg_stat_reset | 

-[ RECORD 1 ]
pg_sleep | 

-[ RECORD 1 ]------+------
datname            | tempt
temp_tables        | 0
local_blks_hit     | 0
local_blks_read    | 0
local_blks_dirtied | 0
local_blks_written | 0


--- Test 10: Temp table with ON COMMIT DROP still counts ---
BEGIN
CREATE TABLE
INSERT 0 3
COMMIT
-[ RECORD 1 ]
pg_sleep | 

-[ RECORD 1 ]------
datname     | tempt
temp_tables | 1


--- Test 11: Multiple temp tables in one transaction ---
BEGIN
CREATE TABLE
CREATE TABLE
CREATE TABLE
COMMIT
-[ RECORD 1 ]
pg_sleep | 

-[ RECORD 1 ]------
datname     | tempt
temp_tables | 4


--- Test 12: Use Case - Detecting Undersized temp_buffers ---

  Scenario A: Small cache (800kB) - same workload
You are now connected to database "tempt" as user "mohamed".
Expanded display is on.
-[ RECORD 1 ]-+-
pg_stat_reset | 

-[ RECORD 1 ]
pg_sleep | 

SET
SELECT 30000
-[ RECORD 1 ]
count | 30000

-[ RECORD 1 ]
count | 15000

-[ RECORD 1 ]-----------
avg | 15000.500000000000

-[ RECORD 1 ]
pg_sleep | 

-[ RECORD 1 ]------+--------------------
scenario           | Small Cache (800kB)
temp_tables        | 1
local_blks_hit     | 531
local_blks_read    | 1728
local_blks_written | 1668
hit_ratio          | 23.51


  Scenario B: Large cache (10MB) - same workload
You are now connected to database "tempt" as user "mohamed".
Expanded display is on.
-[ RECORD 1 ]-+-
pg_stat_reset | 

-[ RECORD 1 ]
pg_sleep | 

SET
SELECT 30000
-[ RECORD 1 ]
count | 30000

-[ RECORD 1 ]
count | 15000

-[ RECORD 1 ]-----------
avg | 15000.500000000000

-[ RECORD 1 ]
pg_sleep | 

-[ RECORD 1 ]------+-------------------
scenario           | Large Cache (10MB)
temp_tables        | 1
local_blks_hit     | 2259
local_blks_read    | 0
local_blks_written | 576
hit_ratio          | 100.00


============================================================
All tests complete!

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