Columns on some tables lack sequence generators

Summary

Using certain features (such as the CI config needs: keyword that uses the ci_build_needs table) may fail with the following backend error:

WARN: ActiveRecord::NotNullViolation: PG::NotNullViolation: ERROR: null value in column "id" violates not-null constraint 

The error occurs because the ID column on this table is marked non-null, but since an automatic sequence generator is absent on it, Rails is unable to insert with null values.

Steps to reproduce

Uncertain. Upgrades of schema from very old versions such as 9 or 10 to latest. Or, migration of schema between two Postgres versions / MySQL and Postgres.

What is the current bug behavior?

Tables after upgrade can lack sequence relations on their ID columns, breaking all inserts.

What is the expected correct behavior?

All new introduced tables could have their schema inspected for accuracy after upgrade.

Relevant logs and/or screenshots

WARN: ActiveRecord::NotNullViolation: PG::NotNullViolation: ERROR: null value in column "id" violates not-null constraint 

Output of checks

This was observed on a 13.12 install, but one that had been upgraded from a very old release over time.

Possible fixes

Rough steps to add back sequence generators to an identified table (example uses table ci_build_needs):

Step 1: Create a GitLab backup
(or at least a Database SQL backup) before proceeding.

Step 2: In a psql session connected to
database gitlabhq_production, run steps:

Step 2.1: Find the current maximum value for
affected column in affected table,
and note it down:

-- Get the current id in the ci_builds_needs table
-- Please take note of this value

SELECT max(id) from ci_build_needs;

Step 2.2: Create a sequence object with
the (value + 1) of the value we discovered above:

-- Create a sequence based on the value above,
-- incrementing the value by 1 before paste:

CREATE SEQUENCE ci_build_needs_id_seq AS BIGINT
START WITH <PASTE_VALUE_FROM_ABOVE_PLUS_1>
INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

Step 2.3: Alter the affected table
to use this new sequence object for its id column:

-- Alter the ci_build_needs table

ALTER TABLE ci_build_needs ALTER COLUMN id
SET DEFAULT nextval('ci_build_needs_id_seq'::regclass);