Skip to content

Commit d86f90c

Browse files
bunglebungle
committed
fix(migrations) postgres additional guards and better reentrancy
### Summary Adds some `IF EXISTS ONLY` clauses to protect postgres `ALTER` statements of reentrancy issues. Also fixes one real reentrancy issue where multiple foreign key constraints got added to postgres when forcing a migration (or perhaps if migration errored or was cancelled in a middle).
1 parent 2c49e71 commit d86f90c

File tree

2 files changed

+29
-11
lines changed

2 files changed

+29
-11
lines changed

‎kong/db/migrations/core/009_200_to_210.lua

Lines changed: 15 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,16 @@ local function pg_ca_certificates_migration(connector)
2828
end
2929
end
3030

31-
local _, err = connector:query('ALTER TABLE ca_certificates ALTER COLUMN cert_digest SET NOT NULL')
31+
local _, err = connector:query([[
32+
DO $$
33+
BEGIN
34+
ALTER TABLE IF EXISTS ONLY "ca_certificates" ALTER COLUMN "cert_digest" SET NOT NULL;
35+
EXCEPTION WHEN UNDEFINED_COLUMN THEN
36+
-- Do nothing, accept existing state
37+
END;
38+
$$;
39+
]])
40+
3241
if err then
3342
return nil, err
3443
end
@@ -148,19 +157,19 @@ return {
148157
postgres = {
149158
up = [[
150159
-- ca_certificates table
151-
ALTER TABLE ca_certificates DROP CONSTRAINT IF EXISTS ca_certificates_cert_key;
160+
ALTER TABLE IF EXISTS ONLY ca_certificates DROP CONSTRAINT IF EXISTS ca_certificates_cert_key;
152161
153162
DO $$
154163
BEGIN
155-
ALTER TABLE ca_certificates ADD COLUMN "cert_digest" TEXT UNIQUE;
164+
ALTER TABLE IF EXISTS ONLY ca_certificates ADD COLUMN "cert_digest" TEXT UNIQUE;
156165
EXCEPTION WHEN duplicate_column THEN
157166
-- Do nothing, accept existing state
158167
END;
159168
$$;
160169
161170
DO $$
162171
BEGIN
163-
ALTER TABLE services ADD COLUMN "tls_verify" BOOLEAN;
172+
ALTER TABLE IF EXISTS ONLY services ADD COLUMN "tls_verify" BOOLEAN;
164173
EXCEPTION WHEN duplicate_column THEN
165174
-- Do nothing, accept existing state
166175
END;
@@ -177,15 +186,15 @@ return {
177186
178187
DO $$
179188
BEGIN
180-
ALTER TABLE services ADD COLUMN "tls_verify_depth" SMALLINT;
189+
ALTER TABLE IF EXISTS ONLY services ADD COLUMN "tls_verify_depth" SMALLINT;
181190
EXCEPTION WHEN duplicate_column THEN
182191
-- Do nothing, accept existing state
183192
END;
184193
$$;
185194
186195
DO $$
187196
BEGIN
188-
ALTER TABLE services ADD COLUMN "ca_certificates" UUID[];
197+
ALTER TABLE IF EXISTS ONLY services ADD COLUMN "ca_certificates" UUID[];
189198
EXCEPTION WHEN duplicate_column THEN
190199
-- Do nothing, accept existing state
191200
END;

‎kong/db/migrations/operations/200_to_210.lua

Lines changed: 14 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -84,7 +84,7 @@ local postgres = {
8484
-- Ensure (id, ws_id) pair is unique
8585
DO $$
8686
BEGIN
87-
ALTER TABLE "$(TABLE)" ADD CONSTRAINT "$(TABLE)_id_ws_id_unique" UNIQUE ("id", "ws_id");
87+
ALTER TABLE IF EXISTS ONLY "$(TABLE)" ADD CONSTRAINT "$(TABLE)_id_ws_id_unique" UNIQUE ("id", "ws_id");
8888
EXCEPTION WHEN DUPLICATE_TABLE THEN
8989
-- Do nothing, accept existing state
9090
END$$;
@@ -103,12 +103,12 @@ local postgres = {
103103
return render([[
104104
105105
-- Make '$(TABLE).$(FIELD)' unique per workspace
106-
ALTER TABLE "$(TABLE)" DROP CONSTRAINT IF EXISTS "$(TABLE)_$(FIELD)_key";
106+
ALTER TABLE IF EXISTS ONLY "$(TABLE)" DROP CONSTRAINT IF EXISTS "$(TABLE)_$(FIELD)_key";
107107
108108
-- Ensure (ws_id, $(FIELD)) pair is unique
109109
DO $$
110110
BEGIN
111-
ALTER TABLE "$(TABLE)" ADD CONSTRAINT "$(TABLE)_ws_id_$(FIELD)_unique" UNIQUE ("ws_id", "$(FIELD)");
111+
ALTER TABLE IF EXISTS ONLY "$(TABLE)" ADD CONSTRAINT "$(TABLE)_ws_id_$(FIELD)_unique" UNIQUE ("ws_id", "$(FIELD)");
112112
EXCEPTION WHEN DUPLICATE_TABLE THEN
113113
-- Do nothing, accept existing state
114114
END$$;
@@ -131,8 +131,17 @@ local postgres = {
131131
return render([[
132132
133133
-- Update foreign key relationship
134-
ALTER TABLE "$(TABLE)" DROP CONSTRAINT IF EXISTS "$(TABLE)_$(FK)_id_fkey";
135-
ALTER TABLE "$(TABLE)" ADD FOREIGN KEY ("$(FK)_id", "ws_id") REFERENCES $(FOREIGN_TABLE)("id", "ws_id") $(CASCADE);
134+
ALTER TABLE IF EXISTS ONLY "$(TABLE)" DROP CONSTRAINT IF EXISTS "$(TABLE)_$(FK)_id_fkey";
135+
136+
DO $$
137+
BEGIN
138+
ALTER TABLE IF EXISTS ONLY "$(TABLE)"
139+
ADD CONSTRAINT "$(TABLE)_$(FK)_id_fkey"
140+
FOREIGN KEY ("$(FK)_id", "ws_id")
141+
REFERENCES $(FOREIGN_TABLE)("id", "ws_id") $(CASCADE);
142+
EXCEPTION WHEN DUPLICATE_OBJECT THEN
143+
-- Do nothing, accept existing state
144+
END$$;
136145
137146
]], {
138147
TABLE = table_name,

0 commit comments

Comments
 (0)