-- This script is designed to test the VACUUM TRUNCATE behavior,
-- particularly how it interacts with concurrent lock requests.
-- It first creates a table for test data population and manipulation,
-- then populates it with a large number of rows, many of which will have
-- TOASTed data. A significant portion of these rows are then deleted.
-- Finally, we simulate a concurrent workloads a ruby script, and execute a VACUUM VERBOSE
-- to observe the truncation attempts and any interruptions.

CREATE TABLE IF NOT EXISTS toast_target_table (
    id SERIAL PRIMARY KEY,
    payload TEXT,
    filler CHAR(100)
);

ALTER TABLE toast_target_table SET (autovacuum_enabled = false);

DO $$
DECLARE
    i INT;
    large_text TEXT;
    template_payload_base TEXT := '';
    padding_chunk TEXT := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()_+{}|[]\:";''<>,.?/-=';
    fixed_filler CHAR(100) := 'constant_filler_data_padding_to_ensure_consistent_row_size_excluding_toast_data_more_chars';
BEGIN
    FOR _ IN 1..11 LOOP
        template_payload_base := template_payload_base || padding_chunk;
    END LOOP;

    FOR i IN 1..7500000 LOOP
        large_text := rpad(template_payload_base || i::TEXT, 10000, padding_chunk);
        INSERT INTO toast_target_table (payload, filler) VALUES (large_text, fixed_filler);

        IF i % 250000 = 0 THEN
            RAISE NOTICE 'Inserted % rows (% percent complete)', i, (i*100/7500000);
        END IF;
    END LOOP;
END $$;

-- SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) as total_size,
--        pg_size_pretty(pg_relation_size(oid)) as table_size
-- FROM pg_class WHERE relname = 'toast_target_table';

-- SELECT t.relname AS toast_table_name,
--        pg_size_pretty(pg_total_relation_size(t.oid)) AS toast_total_size,
--        pg_size_pretty(pg_relation_size(t.oid)) AS toast_table_only_size
-- FROM pg_class p
-- JOIN pg_class t ON p.reltoastrelid = t.oid
-- WHERE p.relname = 'toast_target_table';

-- Delete data to cause enough truncation attempts
DELETE FROM toast_target_table
WHERE id > (SELECT (MAX(id) * 0.051)::INT FROM toast_target_table);


-- Run the ruby script before running this VACUUM
-- ruby script.rb
VACUUM (VERBOSE) toast_target_table;
