Do you need NOT VALID when adding new foreign key column
2
votes
2
answers
560
views
I know that adding a foreign key constraint requires a table scan and a
SHARE ROW EXCLUSIVE
lock on both tables.
To prevent the possibly lengthy table scan the constraint can be added with the NOT VALID
approach. But I'm wondering, when adding a new column, should you also use NOT VALID
or is Postgres smart enough to recognize it's a new column and thus the whole table doesn't need to be scanned?
I'm using Django, and the generated SQL of adding a foreign key column looks like this:
ALTER TABLE
"example"
ADD
COLUMN "new_column_id" integer NULL CONSTRAINT "example_new_column_id_fk" REFERENCES "another_table"("id") DEFERRABLE INITIALLY DEFERRED;
SET
CONSTRAINTS "example_new_column_id_b781b6be_fk" IMMEDIATE;
Asked by Ruud van den Boomen
(123 rep)
Nov 22, 2024, 12:35 PM
Last activity: Nov 24, 2024, 09:13 PM
Last activity: Nov 24, 2024, 09:13 PM