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

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: Per Kaminsky <per(dot)kaminsky(at)hawk-intech(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Performance issues on FK Triggers after replacing a primary column
Date: 2022-03-28 16:49:22
Message-ID: 04bce16b-7bf4-e74a-e3c7-f6e369592cca@aklaver.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Philip Semanchuk 2022-03-28 17:35:03 Re: Leading comments and client applications
Previous Message Adrian Klaver 2022-03-28 16:41:25 Re: Performance issues on FK Triggers after replacing a primary column