On 3/27/22 23:53, Per Kaminsky wrote:
The table structure looks (roughly) like this:
* Table "Base": (id, created, deleted, origin, ...) ~3m rows
* Table "A": (id as FK on "Base", ...) ~400k rows
* Table "B": (id, ref_a as FK on "A", type, ...) ~2m rows
Swapping the PK of "A" happens as following, the FK is dropped during
the process since otherwise the performance issues also happen here when
updating the PK. The update calls do normally utilize a file based
import into a temporary table from which i do the actual update:
ALTER TABLE "B" DROP CONSTRAINT "B_to_A_fkey";
ALTER TABLE "A" ADD COLUMN id_temp BIGINT;
// fill id_temp with new IDs
UPDATE "B" SET ref_a = "A".id_temp WHERE "B".ref_a= "A".id;
UPDATE "A" SET id = id_temp;
ALTER TABLE "B" ADD CONSTRAINT "B_to_A_fkey" FOREIGN KEY (ref_a)
REFERENCES A(id);
Now that the morning coffee has taken effect, I'm wondering why the
above is necessary at all?
If "B_to_A_fkey" where to have ON UPDATE CASCADE then you could
eliminate the dropping/adding back of the FK. The process would be:
ALTER TABLE "A" ADD COLUMN id_temp BIGINT;
// fill id_temp with new IDs
UPDATE "A" SET id = id_temp;
UPDATE "B" SET type = 2 WHERE type ISNULL;
It might even be possible to further simplify depending on what '// fill
id_temp with new IDs' actually does?
And then the new occuring step, in the same transaction, which then also
has shown the performance issues described if i would not remove the FK
temporarily:
ALTER TABLE "B" DROP CONSTRAINT "B_to_A_fkey";
UPDATE "B" SET type = 2 WHERE type ISNULL;
ALTER TABLE "B" ADD CONSTRAINT "B_to_A_fkey" FOREIGN KEY (ref_a)
REFERENCES A(id);
**
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
