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.klaver@aklaver.com