mirror of
https://github.com/dredx/prole.git
synced 2026-09-23 12:03:59 +00:00
86 lines
3.8 KiB
PL/PgSQL
86 lines
3.8 KiB
PL/PgSQL
-- 03_broken_migration.sql
|
|
-- THE BROKEN MIGRATION — do not run this on production.
|
|
-- This is the verbatim migration from the change request, with annotations.
|
|
-- It is intentionally left broken to demonstrate the failure modes.
|
|
--
|
|
-- Run in Session A. Watch Session B (monitoring) while this runs.
|
|
--
|
|
-- ISSUES DEMONSTRATED:
|
|
-- 1. Single transaction wrapping the entire backfill → MVCC bloat
|
|
-- 2. Partition range only covers FUTURE months → immediate INSERT failure
|
|
-- 3. CREATE INDEX without CONCURRENTLY inside the transaction → ShareLock
|
|
-- 4. No chunking → all-or-nothing, no progress on failure
|
|
--
|
|
-- EXPECTED OUTCOME:
|
|
-- The INSERT will fail with:
|
|
-- ERROR: no partition of relation "ci_job_artifacts" found for row
|
|
-- But before it fails, the open BEGIN will already be visible in:
|
|
-- demo.long_running_tx, demo.xmin_horizon
|
|
-- And dead tuples in demo.ci_build_status will be climbing because
|
|
-- autovacuum cannot advance past our snapshot.
|
|
|
|
-- ── DROP target if it exists from a previous run ─────────────────────────────
|
|
DROP TABLE IF EXISTS demo.ci_job_artifacts CASCADE;
|
|
|
|
BEGIN;
|
|
|
|
-- ── CREATE partitioned table ──────────────────────────────────────────────────
|
|
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);
|
|
|
|
-- ISSUE 2: Creates partitions from CURRENT_DATE forward (future months only).
|
|
-- The backfill below selects rows from the PAST 6 months.
|
|
-- These rows have no matching partition → every INSERT row will error.
|
|
DO $$
|
|
DECLARE
|
|
start_date DATE := DATE_TRUNC('month', CURRENT_DATE);
|
|
partition_name TEXT;
|
|
i INT;
|
|
BEGIN
|
|
FOR i IN 0..5 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 $$;
|
|
|
|
-- ISSUE 3: Indexes created inside the transaction, without CONCURRENTLY.
|
|
-- These hold ShareLock for the entire transaction duration.
|
|
CREATE INDEX idx_ci_job_artifacts_job_id ON demo.ci_job_artifacts (job_id);
|
|
CREATE INDEX idx_ci_job_artifacts_project_id ON demo.ci_job_artifacts (project_id);
|
|
CREATE INDEX idx_ci_job_artifacts_expire_at ON demo.ci_job_artifacts (expire_at);
|
|
CREATE INDEX idx_ci_job_artifacts_checksum ON demo.ci_job_artifacts (checksum);
|
|
|
|
-- ISSUE 1 + 2: Backfill inside a single transaction, targeting past 6 months.
|
|
-- Will fail because no partition exists for those months.
|
|
-- Even if we fix the partition range, running this as a single INSERT on
|
|
-- 5M rows (or 10TB at real scale) holds the transaction open for hours,
|
|
-- bloating MVCC dead tuples on every table touched by concurrent sessions.
|
|
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';
|
|
|
|
-- This COMMIT will never be reached due to the partition error above.
|
|
COMMIT;
|