Re: Performance issues on FK Triggers after replacing a primary column

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(dot)klaver(at)aklaver(dot)com