In a previous post, we discussed how to optimize bulk insert/update with Ecto by separating the evaluation from the execution of a series of records.

We actually implemented this in Accent and it worked well to optimize the processing of large files or project with a large number of languages.

A path was left un-optimized. The bulk insert of cross-referring records. We want to insert in a single SQL transaction a translations record and an operations record referrencing the translations. Since we use UUID as primary keys, we can generate them in our codebase without having to wait for PostgreSQL.

But the way CONSTRAINT with REFERENCES are created in PostreSQL, we can’t do that

BEGIN
INSERT INTO operations ("action", "translation_id") VALUES ("version_new", "192eb96a-d6e0-4a91-b009-af61e12a1e9c")
INSERT INTO translations ("id", "key", "value") VALUES ("192eb96a-d6e0-4a91-b009-af61e12a1e9c", "key", "value")
COMMIT

The constraint on translation_id is validated before doing the second INSERT.

Thankfully, PostgreSQL got us covered!

ADD CONSTRAINT "translation_id_fkey" FOREIGN KEY ("translation_id") REFERENCES "public"."translations"("id") DEFERRABLE INITIALLY DEFERRED

The option DEFERRABLE INITIALLY DEFERRED will move the constraint validation on COMMIT.

For more options: PostgreSQL constraint docs