-- 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;
    base_payload TEXT := 'payload_data_';
    random_chars TEXT := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()_+{}|[]\:";''<>,.?/';
    payload_segment TEXT;
BEGIN
    RAISE NOTICE 'Starting data insertion...';
    FOR i IN 1..1000000 LOOP
        payload_segment := '';
        FOR j IN 1..200 LOOP
             payload_segment := payload_segment || base_payload || (random() * 1000000)::INT::TEXT || SUBSTRING(random_chars FROM (random() * length(random_chars) + 1)::INT FOR 10);
        END LOOP;
        large_text := rpad(payload_segment, 10000, CHR((random()*26)::INT + 65));

        INSERT INTO toast_target_table (payload, filler) VALUES (large_text, 'filler_data');
        IF i % 10000 = 0 THEN
            RAISE NOTICE 'Inserted % rows, % percent complete', i, (i*100/1000000);
        END IF;
    END LOOP;
    RAISE NOTICE 'Finished data insertion.';
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;
