prole/demo/ci-migration/03b_broken_migration_long_tx.sql

-- 03b_broken_migration_long_tx.sql
-- MVCC BLOAT DEMONSTRATION — the backfill that survives (partition bug fixed).
-- This version creates the correct historical partitions so the INSERT runs,
-- but keeps the fatal single-transaction pattern to demonstrate bloat.
--
-- Run in Session A. While it runs, hammer Session B (04_traffic_sim.sh),
-- and watch Session C (05_monitor.sql) for dead tuple accumulation.
--
-- The ~5M row INSERT will take several minutes.
-- Every UPDATE to demo.ci_build_status during that window creates a dead tuple
-- that autovacuum cannot collect because our open snapshot holds the xmin horizon.
--
-- Watch for:
--   demo.mvcc_bloat_monitor  → dead_rows climbing on ci_build_status
--   demo.long_running_tx     → this session visible for the duration
--   demo.xmin_horizon        → our xmin locking autovacuum out
--   demo.wal_progress        → WAL size growing at alarming rate

DROP TABLE IF EXISTS demo.ci_job_artifacts CASCADE;

BEGIN;

CREATE TABLE demo.ci_job_artifacts (
    id              BIGSERIAL,
    job_id          BIGINT    NOT NULL,
    project_id      BIGINT    NOT NULL,
    file_type       SMALLINT  NOT NULL DEFAULT 0,
    size_bytes      BIGINT,
    file_store      SMALLINT  NOT NULL DEFAULT 1,
    checksum        TEXT,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
    expire_at       TIMESTAMP,
    locked          BOOLEAN   DEFAULT FALSE,
    partition_key   DATE      NOT NULL DEFAULT CURRENT_DATE,
    PRIMARY KEY (id, partition_key)
) PARTITION BY RANGE (partition_key);

-- Partitions covering past 6 months AND next 6 months (12 total).
DO $$
DECLARE
    start_date      DATE := DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 months');
    partition_name  TEXT;
    i               INT;
BEGIN
    FOR i IN 0..11 LOOP
        partition_name := 'ci_job_artifacts_' || TO_CHAR(start_date + (i || ' months')::INTERVAL, 'YYYY_MM');
        EXECUTE FORMAT(
            'CREATE TABLE demo.%I PARTITION OF demo.ci_job_artifacts
             FOR VALUES FROM (%L) TO (%L)',
            partition_name,
            start_date + (i || ' months')::INTERVAL,
            start_date + ((i + 1) || ' months')::INTERVAL
        );
    END LOOP;
END $$;

-- Indexes in the same transaction (ShareLock held while INSERT runs below).
CREATE INDEX idx_cia_job_id     ON demo.ci_job_artifacts (job_id);
CREATE INDEX idx_cia_project_id ON demo.ci_job_artifacts (project_id);
CREATE INDEX idx_cia_expire_at  ON demo.ci_job_artifacts (expire_at);
CREATE INDEX idx_cia_checksum   ON demo.ci_job_artifacts (checksum);

-- THE PROBLEM: 5 million rows in one statement, one transaction.
-- At 10 TB real scale this runs for hours.
-- Autovacuum cannot advance past our xmin for the entire duration.
INSERT INTO demo.ci_job_artifacts
SELECT
    id, job_id, project_id, file_type, size_bytes, file_store,
    checksum, created_at, expire_at, locked,
    DATE_TRUNC('month', created_at)::DATE AS partition_key
FROM demo.ci_job_artifacts_legacy
WHERE created_at >= NOW() - INTERVAL '6 months';

COMMIT;

\echo 'Migration committed. Check demo.mvcc_bloat_monitor for bloat residue.'