github.com/pf-qiu/concourse/v6@v6.7.3-0.20201207032516-1f455d73275f/atc/db/migration/migrations/1606068653_build_events_partitions_bigint.up.sql (about)

     1  BEGIN;
     2    -- migrate each pipeline partition
     3    DO $$
     4    DECLARE
     5      pipeline record;
     6    BEGIN
     7    FOR pipeline IN
     8      SELECT id, name FROM pipelines
     9    LOOP
    10      RAISE NOTICE 'renaming old indexes for pipeline % (%)', pipeline.id, pipeline.name;
    11      EXECUTE format('ALTER INDEX pipeline_build_events_%s_build_id RENAME TO pipeline_build_events_%s_build_id_old', pipeline.id, pipeline.id);
    12      EXECUTE format('ALTER INDEX pipeline_build_events_%s_build_id_event_id RENAME TO pipeline_build_events_%s_build_id_old_event_id', pipeline.id, pipeline.id);
    13  
    14      RAISE NOTICE 'creating new indexes for pipeline % (%)', pipeline.id, pipeline.name;
    15      EXECUTE format('CREATE INDEX pipeline_build_events_%s_build_id ON pipeline_build_events_%s (build_id)', pipeline.id, pipeline.id);
    16      EXECUTE format('CREATE UNIQUE INDEX pipeline_build_events_%s_build_id_event_id ON pipeline_build_events_%s (build_id, event_id)', pipeline.id, pipeline.id);
    17    END LOOP;
    18    END;
    19    $$ LANGUAGE plpgsql;
    20  
    21    -- backfill indexes for each team partition (these are for one-off builds)
    22    DO $$
    23    DECLARE
    24      team record;
    25    BEGIN
    26    FOR team IN
    27      SELECT id, name FROM teams
    28    LOOP
    29      RAISE NOTICE 'creating new indexes for team % (%)', team.id, team.name;
    30      EXECUTE format('CREATE INDEX team_build_events_%s_build_id ON team_build_events_%s (build_id)', team.id, team.id);
    31      EXECUTE format('CREATE UNIQUE INDEX team_build_events_%s_build_id_event_id ON team_build_events_%s (build_id, event_id)', team.id, team.id);
    32    END LOOP;
    33    END;
    34    $$ LANGUAGE plpgsql;
    35  
    36    -- set up both old and new indexes on pipeline creation
    37    --
    38    -- this is really just to maintain consistency with old and new pipelines
    39    CREATE OR REPLACE FUNCTION on_pipeline_insert() RETURNS TRIGGER AS $$
    40    BEGIN
    41      EXECUTE format('CREATE TABLE IF NOT EXISTS pipeline_build_events_%s () INHERITS (build_events)', NEW.id);
    42      EXECUTE format('CREATE INDEX pipeline_build_events_%s_build_id ON pipeline_build_events_%s (build_id)', NEW.id, NEW.id);
    43      EXECUTE format('CREATE UNIQUE INDEX pipeline_build_events_%s_build_id_event_id ON pipeline_build_events_%s (build_id, event_id)', NEW.id, NEW.id);
    44      EXECUTE format('CREATE INDEX pipeline_build_events_%s_build_id_old ON pipeline_build_events_%s (build_id_old)', NEW.id, NEW.id);
    45      EXECUTE format('CREATE UNIQUE INDEX pipeline_build_events_%s_build_id_old_event_id ON pipeline_build_events_%s (build_id_old, event_id)', NEW.id, NEW.id);
    46      RETURN NULL;
    47    END;
    48    $$ LANGUAGE plpgsql;
    49  
    50    -- set up indexes on team creation
    51    --
    52    -- don't bother setting up old indexes since this never existed before
    53    CREATE OR REPLACE FUNCTION on_team_insert() RETURNS TRIGGER AS $$
    54    BEGIN
    55      EXECUTE format('CREATE TABLE IF NOT EXISTS team_build_events_%s () INHERITS (build_events)', NEW.id);
    56      EXECUTE format('CREATE INDEX team_build_events_%s_build_id ON team_build_events_%s (build_id)', NEW.id, NEW.id);
    57      EXECUTE format('CREATE UNIQUE INDEX team_build_events_%s_build_id_event_id ON team_build_events_%s (build_id, event_id)', NEW.id, NEW.id);
    58      RETURN NULL;
    59    END;
    60    $$ LANGUAGE plpgsql;
    61  COMMIT;