-- 04_pgbench_workload.sql
-- pgbench custom script for OLTP traffic simulation.
-- Run with:
--   pgbench -h 127.0.0.1 -p 15432 -U postgres knoe-db \
--     -c 10 -j 2 -T 600 \
--     -f demo/ci-migration/04_pgbench_workload.sql
--
-- Each worker randomly UPDATEs and SELECTs ci_build_status rows.
-- This creates a steady stream of dead tuples that autovacuum must collect.
-- When a long migration transaction holds an old xmin, these dead tuples
-- accumulate unboundedly — the core of the MVCC bloat problem.

\set job_id random(1, 999999)
\set row_id random(1, 100000)

UPDATE demo.ci_build_status
SET    status     = CASE (:job_id % 3)
                        WHEN 0 THEN 'running'
                        WHEN 1 THEN 'completed'
                        ELSE        'failed'
                   END,
       updated_at = NOW()
WHERE  id = :row_id;

SELECT count(*)
FROM   demo.ci_build_status
WHERE  status = 'running'
  AND  updated_at > NOW() - INTERVAL '1 minute';