fix: disable statement timeout for subject ID FK validation migrations
What does this MR do?
We missed this during !1350 (merged), and as result applying the migrations in GitLab.com production has timed out (gitlab-com/gl-infra/production#16302 (closed)).
I can confirm this works as expected by using a local server with a very tight statement timeout:
Click to expand
2023-09-05 15:28:09.267 WEST [98607] LOG: statement: SELECT * FROM "schema_migrations"
2023-09-05 15:28:09.268 WEST [98607] LOG: statement: SET statement_timeout TO 0
2023-09-05 15:28:09.268 WEST [98607] LOG: statement: DO $$
BEGIN
IF EXISTS (
SELECT
1
FROM
pg_catalog.pg_constraint
WHERE
conrelid = 'partitions.manifests_p_13'::regclass
AND conname = 'fk_manifests_subject_id_manifests'
) THEN
ALTER TABLE partitions.manifests_p_13 VALIDATE CONSTRAINT fk_manifests_subject_id_manifests;
END IF;
END;
$$
2023-09-05 15:28:09.324 WEST [98607] LOG: statement: DO $$
BEGIN
IF EXISTS (
SELECT
1
FROM
pg_catalog.pg_constraint
WHERE
conrelid = 'partitions.manifests_p_14'::regclass
AND conname = 'fk_manifests_subject_id_manifests'
) THEN
ALTER TABLE partitions.manifests_p_14 VALIDATE CONSTRAINT fk_manifests_subject_id_manifests;
END IF;
END;
$$
2023-09-05 15:28:09.337 WEST [98607] LOG: statement: DO $$
BEGIN
IF EXISTS (
SELECT
1
FROM
pg_catalog.pg_constraint
WHERE
conrelid = 'partitions.manifests_p_15'::regclass
AND conname = 'fk_manifests_subject_id_manifests'
) THEN
ALTER TABLE partitions.manifests_p_15 VALIDATE CONSTRAINT fk_manifests_subject_id_manifests;
END IF;
END;
$$
2023-09-05 15:28:09.350 WEST [98607] LOG: statement: DO $$
BEGIN
IF EXISTS (
SELECT
1
FROM
pg_catalog.pg_constraint
WHERE
conrelid = 'partitions.manifests_p_16'::regclass
AND conname = 'fk_manifests_subject_id_manifests'
) THEN
ALTER TABLE partitions.manifests_p_16 VALIDATE CONSTRAINT fk_manifests_subject_id_manifests;
END IF;
END;
$$
2023-09-05 15:28:09.365 WEST [98607] LOG: statement: DO $$
BEGIN
IF EXISTS (
SELECT
1
FROM
pg_catalog.pg_constraint
WHERE
conrelid = 'partitions.manifests_p_17'::regclass
AND conname = 'fk_manifests_subject_id_manifests'
) THEN
ALTER TABLE partitions.manifests_p_17 VALIDATE CONSTRAINT fk_manifests_subject_id_manifests;
END IF;
END;
$$
2023-09-05 15:28:09.377 WEST [98607] LOG: statement: DO $$
BEGIN
IF EXISTS (
SELECT
1
FROM
pg_catalog.pg_constraint
WHERE
conrelid = 'partitions.manifests_p_18'::regclass
AND conname = 'fk_manifests_subject_id_manifests'
) THEN
ALTER TABLE partitions.manifests_p_18 VALIDATE CONSTRAINT fk_manifests_subject_id_manifests;
END IF;
END;
$$
2023-09-05 15:28:09.390 WEST [98607] LOG: statement: DO $$
BEGIN
IF EXISTS (
SELECT
1
FROM
pg_catalog.pg_constraint
WHERE
conrelid = 'partitions.manifests_p_19'::regclass
AND conname = 'fk_manifests_subject_id_manifests'
) THEN
ALTER TABLE partitions.manifests_p_19 VALIDATE CONSTRAINT fk_manifests_subject_id_manifests;
END IF;
END;
$$
2023-09-05 15:28:09.404 WEST [98607] LOG: statement: DO $$
BEGIN
IF EXISTS (
SELECT
1
FROM
pg_catalog.pg_constraint
WHERE
conrelid = 'partitions.manifests_p_20'::regclass
AND conname = 'fk_manifests_subject_id_manifests'
) THEN
ALTER TABLE partitions.manifests_p_20 VALIDATE CONSTRAINT fk_manifests_subject_id_manifests;
END IF;
END;
$$
2023-09-05 15:28:09.415 WEST [98607] LOG: statement: DO $$
BEGIN
IF EXISTS (
SELECT
1
FROM
pg_catalog.pg_constraint
WHERE
conrelid = 'partitions.manifests_p_21'::regclass
AND conname = 'fk_manifests_subject_id_manifests'
) THEN
ALTER TABLE partitions.manifests_p_21 VALIDATE CONSTRAINT fk_manifests_subject_id_manifests;
END IF;
END;
$$
2023-09-05 15:28:09.428 WEST [98607] LOG: statement: DO $$
BEGIN
IF EXISTS (
SELECT
1
FROM
pg_catalog.pg_constraint
WHERE
conrelid = 'partitions.manifests_p_22'::regclass
AND conname = 'fk_manifests_subject_id_manifests'
) THEN
ALTER TABLE partitions.manifests_p_22 VALIDATE CONSTRAINT fk_manifests_subject_id_manifests;
END IF;
END;
$$
2023-09-05 15:28:09.445 WEST [98607] LOG: statement: DO $$
BEGIN
IF EXISTS (
SELECT
1
FROM
pg_catalog.pg_constraint
WHERE
conrelid = 'partitions.manifests_p_23'::regclass
AND conname = 'fk_manifests_subject_id_manifests'
) THEN
ALTER TABLE partitions.manifests_p_23 VALIDATE CONSTRAINT fk_manifests_subject_id_manifests;
END IF;
END;
$$
2023-09-05 15:28:09.474 WEST [98607] LOG: statement: DO $$
BEGIN
IF EXISTS (
SELECT
1
FROM
pg_catalog.pg_constraint
WHERE
conrelid = 'partitions.manifests_p_24'::regclass
AND conname = 'fk_manifests_subject_id_manifests'
) THEN
ALTER TABLE partitions.manifests_p_24 VALIDATE CONSTRAINT fk_manifests_subject_id_manifests;
END IF;
END;
$$
2023-09-05 15:28:09.492 WEST [98607] LOG: statement: DO $$
BEGIN
IF EXISTS (
SELECT
1
FROM
pg_catalog.pg_constraint
WHERE
conrelid = 'partitions.manifests_p_25'::regclass
AND conname = 'fk_manifests_subject_id_manifests'
) THEN
ALTER TABLE partitions.manifests_p_25 VALIDATE CONSTRAINT fk_manifests_subject_id_manifests;
END IF;
END;
$$
2023-09-05 15:28:09.504 WEST [98607] LOG: statement: RESET statement_timeout
2023-09-05 15:28:09.505 WEST [98607] LOG: statement: insert into "schema_migrations" ("id","applied_at") values ('20230724040949_post_validate_fk_manifests_subject_id_manifests_batch_2','2023-09-05 15:28:09.505045+01:00:00');Author checklist
-
Feature flags
-
Added feature flag: -
This feature does not require a feature flag
-
-
I added unit tests or they are not required -
I added documentation (or it's not required) -
I followed code review guidelines -
I followed Go Style guidelines -
For database changes including schema migrations: -
Manually run up and down migrations in a postgres.ai production database clone and post a screenshot of the result here. -
If adding new queries, extract a query plan from postgres.ai and post the link here. If changing existing queries, also extract a query plan for the current version for comparison. -
Do not include code that depends on the schema migrations in the same commit. Split the MR into two or more.
-
-
Ensured this change is safe to deploy to individual stages in the same environment ( cny->prod). State-related changes can be troublesome due to having parts of the fleet processing (possibly related) requests in different ways.
Reviewer checklist
-
Ensure the commit and MR tittle are still accurate. -
If the change contains a breaking change, apply the breaking change label. -
If the change is considered high risk, apply the label high-risk-change -
Identify if the change can be rolled back safely. (note: all other reasons for not being able to rollback will be sufficiently captured by major version changes).
If the MR introduces database schema migrations:
-
Ensure the commit and MR tittle start with fix:,feat:, orperf:so that the change appears on the Changelog
If the changes cannot be rolled back follow these steps:
-
If not, apply the label cannot-rollback. -
Add a section to the MR description that includes the following details: -
The reasoning behind why a release containing the presented MR can not be rolled back (e.g. schema migrations or changes to the FS structure) -
Detailed steps to revert/disable a feature introduced by the same change where a migration cannot be rolled back. (note: ideally MRs containing schema migrations should not contain feature changes.) -
Ensure this MR does not add code that depends on these changes that cannot be rolled back.
-
Edited by João Pereira