-- OPTION 1: System-wide (VERY VERBOSE !)
/*
ALTER SYSTEM SET log_autovacuum_min_duration = 0;        -- Log all autovacuum activity
ALTER SYSTEM SET log_min_messages = 'DEBUG1';            -- More detailed logging
ALTER SYSTEM SET log_line_prefix = '%t [%p] %q%u@%d ';   -- Better log format
ALTER SYSTEM SET log_checkpoints = off;                   -- Log checkpoints
ALTER SYSTEM SET log_statement = 'mod';                  -- Log modifications
SELECT pg_reload_conf();
*/

-- Verify settings
SELECT name, setting FROM pg_settings WHERE name IN (
    'log_autovacuum_min_duration',
    'log_min_messages',
    'log_statement',
    'log_checkpoints'
);

-- Clean up any existing test table

-- Create test table with VERY AGGRESSIVE autovacuum settings
CREATE TABLE toast_truncate_test (
    id SERIAL PRIMARY KEY,
    large_data JSONB,
    created_at TIMESTAMP DEFAULT NOW()
) WITH (
    -- Make autovacuum extremely aggressive for fast testing
    autovacuum_vacuum_threshold = 0,        -- Vacuum after just 5 dead tuples
    autovacuum_vacuum_scale_factor = 0.01,  -- Very low scale factor
    autovacuum_vacuum_cost_delay = 0,       -- No delay
    autovacuum_vacuum_cost_limit = 1000
);

ALTER TABLE toast_truncate_test SET (toast.vacuum_truncate = false);

-- Generate large JSONB data that will definitely use TOAST (>8KB each)
INSERT INTO toast_truncate_test (large_data)
SELECT jsonb_build_object(
    'id', i,
    'large_text', repeat('This is test data for TOAST storage testing. ', 10000),
    'more_data', repeat('Additional filler data to ensure TOAST usage. ', 10000),
    'timestamp', NOW(),
    'random_data', ARRAY[random()::text, random()::text, random()::text, random()::text]
)
FROM generate_series(1, 25000) i;

-- Delete majority of data to trigger autovacuum
DELETE FROM toast_truncate_test WHERE id > 100;

-- Test manually
-- vacuum verbose toast_truncate_test;

-- check dead tuples
SELECT schemaname, relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE relname LIKE 'pg_toast%';

-- Verify TOAST table was created and check settings
SELECT
    'TABLE CREATED' as status,
    c.relname AS main_table,
    t.relname AS toast_table,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS main_table_size,
    pg_size_pretty(pg_total_relation_size(t.oid)) AS toast_table_size
FROM pg_class c
JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relname = 'toast_truncate_test';

-- 4. Check storage parameters - IMPORTANT: Note TOAST table has NO explicit settings
SELECT
    'STORAGE PARAMETERS' as info,
    c.relname AS table_name,
    c.reloptions AS table_options,
    CASE
        WHEN c.reloptions IS NULL THEN 'NO EXPLICIT SETTINGS - SHOULD INHERIT FROM PARENT'
        ELSE 'HAS EXPLICIT SETTINGS'
    END as inheritance_status
FROM pg_class c
WHERE c.relname = 'toast_truncate_test'
UNION ALL
SELECT
    'STORAGE PARAMETERS' as info,
    t.relname AS table_name,
    t.reloptions AS table_options,
    CASE
        WHEN t.reloptions IS NULL THEN 'NO EXPLICIT SETTINGS - SHOULD INHERIT FROM PARENT'
        ELSE 'HAS EXPLICIT SETTINGS'
    END as inheritance_status
FROM pg_class c
JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relname = 'toast_truncate_test';
