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

     1  BEGIN;
     2    ALTER TABLE jobs
     3    ADD COLUMN latest_completed_build_id integer REFERENCES builds (id) ON DELETE SET NULL,
     4    ADD COLUMN next_build_id integer REFERENCES builds (id) ON DELETE SET NULL,
     5    ADD COLUMN transition_build_id integer REFERENCES builds (id) ON DELETE SET NULL;
     6  
     7    CREATE INDEX jobs_latest_completed_build_id ON jobs (latest_completed_build_id);
     8    CREATE INDEX jobs_next_build_id ON jobs (next_build_id);
     9    CREATE INDEX jobs_transition_build_id ON jobs (transition_build_id);
    10  
    11    UPDATE jobs j SET latest_completed_build_id = v.id FROM latest_completed_builds_per_job v WHERE v.job_id = j.id;
    12    UPDATE jobs j SET next_build_id = v.id FROM next_builds_per_job v WHERE v.job_id = j.id;
    13    UPDATE jobs j SET transition_build_id = v.id FROM transition_builds_per_job v WHERE v.job_id = j.id;
    14  
    15    -- these are to be done in a later release
    16    -- DROP MATERIALIZED VIEW transition_builds_per_job;
    17    -- DROP MATERIALIZED VIEW next_builds_per_job;
    18    -- DROP MATERIALIZED VIEW latest_completed_builds_per_job;
    19  COMMIT;