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

     1  BEGIN;
     2    CREATE OR REPLACE FUNCTION on_team_insert() RETURNS TRIGGER AS $$
     3    BEGIN
     4      EXECUTE format('CREATE TABLE IF NOT EXISTS team_build_events_%s () INHERITS (build_events)', NEW.id);
     5      RETURN NULL;
     6    END;
     7    $$ LANGUAGE plpgsql;
     8  
     9    CREATE OR REPLACE FUNCTION on_pipeline_insert() RETURNS TRIGGER AS $$
    10    BEGIN
    11      EXECUTE format('CREATE TABLE IF NOT EXISTS pipeline_build_events_%s () INHERITS (build_events)', NEW.id);
    12      EXECUTE format('CREATE INDEX pipeline_build_events_%s_build_id ON pipeline_build_events_%s (build_id)', NEW.id, NEW.id);
    13      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);
    14      RETURN NULL;
    15    END;
    16    $$ LANGUAGE plpgsql;
    17  
    18    DO $$
    19    DECLARE
    20      team record;
    21    BEGIN
    22    FOR team IN
    23      SELECT id, name FROM teams
    24    LOOP
    25      RAISE NOTICE 'creating new indexes for team % (%)', team.id, team.name;
    26      EXECUTE format('DROP INDEX team_build_events_%s_build_id', team.id);
    27      EXECUTE format('DROP INDEX team_build_events_%s_build_id_event_id', team.id);
    28    END LOOP;
    29    END
    30    $$ LANGUAGE plpgsql;
    31  
    32    DO $$
    33    DECLARE
    34      pipeline record;
    35    BEGIN
    36    FOR pipeline IN
    37      SELECT id, name FROM pipelines
    38    LOOP
    39      RAISE NOTICE 'dropping new indexes for pipeline % (%)', pipeline.id, pipeline.name;
    40      EXECUTE format('DROP INDEX pipeline_build_events_%s_build_id', pipeline.id);
    41      EXECUTE format('DROP INDEX pipeline_build_events_%s_build_id_event_id', pipeline.id);
    42  
    43      RAISE NOTICE 'renaming old indexes for pipeline % (%)', pipeline.id, pipeline.name;
    44      EXECUTE format('ALTER INDEX pipeline_build_events_%s_build_id_old RENAME TO pipeline_build_events_%s_build_id', pipeline.id, pipeline.id);
    45      EXECUTE format('ALTER INDEX pipeline_build_events_%s_build_id_old_event_id RENAME TO pipeline_build_events_%s_build_id_event_id', pipeline.id, pipeline.id);
    46    END LOOP;
    47    END
    48    $$ LANGUAGE plpgsql;
    49  COMMIT;