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

     1  BEGIN;
     2    -- these are to be done in a later release
     3    -- CREATE MATERIALIZED VIEW latest_completed_builds_per_job AS
     4    --  WITH latest_build_ids_per_job AS (
     5    --          SELECT max(b_1.id) AS build_id
     6    --            FROM (builds b_1
     7    --              JOIN jobs j ON ((j.id = b_1.job_id)))
     8    --           WHERE (b_1.status <> ALL (ARRAY['pending'::build_status, 'started'::build_status]))
     9    --           GROUP BY b_1.job_id
    10    --         )
    11    --  SELECT b.id,
    12    --     b.name,
    13    --     b.status,
    14    --     b.scheduled,
    15    --     b.start_time,
    16    --     b.end_time,
    17    --     b.engine,
    18    --     b.engine_metadata,
    19    --     b.completed,
    20    --     b.job_id,
    21    --     b.reap_time,
    22    --     b.team_id,
    23    --     b.manually_triggered,
    24    --     b.interceptible,
    25    --     b.nonce,
    26    --     b.public_plan,
    27    --     b.pipeline_id,
    28    --     b.tracked_by
    29    --    FROM (builds b
    30    --      JOIN latest_build_ids_per_job l ON ((l.build_id = b.id)))
    31    --   WITH NO DATA;
    32    -- CREATE UNIQUE INDEX latest_completed_builds_per_job_id ON latest_completed_builds_per_job USING btree (id);
    33    -- REFRESH MATERIALIZED VIEW latest_completed_builds_per_job;
    34  
    35    -- CREATE MATERIALIZED VIEW next_builds_per_job AS
    36    --  WITH latest_build_ids_per_job AS (
    37    --          SELECT min(b_1.id) AS build_id
    38    --            FROM (builds b_1
    39    --              JOIN jobs j ON ((j.id = b_1.job_id)))
    40    --           WHERE (b_1.status = ANY (ARRAY['pending'::build_status, 'started'::build_status]))
    41    --           GROUP BY b_1.job_id
    42    --         )
    43    --  SELECT b.id,
    44    --     b.name,
    45    --     b.status,
    46    --     b.scheduled,
    47    --     b.start_time,
    48    --     b.end_time,
    49    --     b.engine,
    50    --     b.engine_metadata,
    51    --     b.completed,
    52    --     b.job_id,
    53    --     b.reap_time,
    54    --     b.team_id,
    55    --     b.manually_triggered,
    56    --     b.interceptible,
    57    --     b.nonce,
    58    --     b.public_plan,
    59    --     b.pipeline_id,
    60    --     b.tracked_by
    61    --    FROM (builds b
    62    --      JOIN latest_build_ids_per_job l ON ((l.build_id = b.id)))
    63    --   WITH NO DATA;
    64    -- CREATE UNIQUE INDEX next_builds_per_job_id ON next_builds_per_job USING btree (id);
    65    -- REFRESH MATERIALIZED VIEW next_builds_per_job;
    66  
    67    -- CREATE MATERIALIZED VIEW transition_builds_per_job AS
    68    --  WITH builds_before_transition AS (
    69    --          SELECT b_1.job_id,
    70    --             max(b_1.id) AS max
    71    --            FROM ((builds b_1
    72    --              LEFT JOIN jobs j ON ((b_1.job_id = j.id)))
    73    --              LEFT JOIN latest_completed_builds_per_job s ON ((b_1.job_id = s.job_id)))
    74    --           WHERE ((b_1.status <> s.status) AND (b_1.status <> ALL (ARRAY['pending'::build_status, 'started'::build_status])))
    75    --           GROUP BY b_1.job_id
    76    --         )
    77    --  SELECT DISTINCT ON (b.job_id) b.id,
    78    --     b.name,
    79    --     b.status,
    80    --     b.scheduled,
    81    --     b.start_time,
    82    --     b.end_time,
    83    --     b.engine,
    84    --     b.engine_metadata,
    85    --     b.completed,
    86    --     b.job_id,
    87    --     b.reap_time,
    88    --     b.team_id,
    89    --     b.manually_triggered,
    90    --     b.interceptible,
    91    --     b.nonce,
    92    --     b.public_plan,
    93    --     b.pipeline_id,
    94    --     b.tracked_by
    95    --    FROM (builds b
    96    --      LEFT JOIN builds_before_transition ON ((b.job_id = builds_before_transition.job_id)))
    97    --   WHERE (((builds_before_transition.max IS NULL) AND (b.status <> ALL (ARRAY['pending'::build_status, 'started'::build_status]))) OR (b.id > builds_before_transition.max))
    98    --   ORDER BY b.job_id, b.id
    99    --   WITH NO DATA;
   100    -- CREATE UNIQUE INDEX transition_builds_per_job_id ON transition_builds_per_job USING btree (id);
   101    -- REFRESH MATERIALIZED VIEW transition_builds_per_job;
   102  
   103    ALTER TABLE jobs
   104    DROP COLUMN latest_completed_build_id,
   105    DROP COLUMN next_build_id,
   106    DROP COLUMN transition_build_id;
   107  COMMIT;