Details
-
Bug
-
Status: Closed (View Workflow)
-
P2
-
Resolution: Done
-
29.2.2, 29.4.0, 30.0.0, 30.0.3
-
-
CP: sprint 89, CP: sprint 90, CP: sprint 91
-
2
-
Core: Platform
Description
How to reproduce:
create table a (id uuid primary key); create table b (id uuid primary key); alter table b add column if not exists aid uuid references a(id); alter table b add column if not exists aid uuid references a(id); \d b
Table "public.b" Column | Type | Collation | Nullable | Default --------+------+-----------+----------+--------- id | uuid | | not null | aid | uuid | | | Indexes: "b_pkey" PRIMARY KEY, btree (id) Foreign-key constraints: "b_aid_fkey" FOREIGN KEY (aid) REFERENCES a(id) "b_aid_fkey1" FOREIGN KEY (aid) REFERENCES a(id)
The alter statement adds the foreign key constraint even if the column already exists. This creates a new constraint on each module upgrade.
Suggested solution:
alter table b add column if not exists aid; do $$ begin begin alter table b add constraint b_aid_fkey foreign key (aid) references a(id); exception when duplicate_object or duplicate_table then null; end; end $$;
This is needed because PostgreSQL doesn't have an "add constraint if not exists" statement. Reference: https://stackoverflow.com/a/32526723
In addition drop existing duplicate constraints:
alter table b drop constraint if exists b_aid_fkey1; alter table b drop constraint if exists b_aid_fkey2; alter table b drop constraint if exists b_aid_fkey3;
TestRail: Results
Attachments
Issue Links
- is duplicated by
-
RMB-597 Duplicate fkey constraints created upon module upgrade
-
- Closed
-
- relates to
-
MODFISTO-97 Issues upgrading module from Edelweiss to Fameflower
-
- Closed
-
-
RMB-649 Remove duplicate foreign key constraints for unchanged tables
-
- Closed
-