[BUG] Deferrable unique constraint produces error and spurious diff in plan

[felamaslen]

Describe the bug

Adding a table with a unique, deferrable constraint prevents the creation of a plan. An error is thrown with "diff detected".

Expected behavior

No diff is detected and no error thrown.

To Reproduce

1. Add file schema/schema.sql, with content:

CREATE TABLE "Foo" (
  "id" UUID,
  "rank" INTEGER NOT NULL
);

ALTER TABLE ONLY "Foo"
ADD CONSTRAINT "Foo_id_rank_key" UNIQUE ("id", "rank") DEFERRABLE
;

2. Create database:

createdb -U postgres -h localhost foo
psql 'postgres://postgres@localhost/foo' < schema/schema.sql

3. Attempt pg-schema-diff plan:

pg-schema-diff plan --from-dsn postgres://postgres@localhost/foo --to-dir schema --output-format sql

Expected outcome

No diff: you just created the database in its current state using schema.sql.

Actual outcome

The command exits with non-zero status, and outputs:

Error: generating plan: validating migration plan: validating plan failed. diff detected:
ALTER INDEX "public"."Foo_id_rank_key" RENAME TO "pgschemadiff_tmpidx_Foo_id_rank_key_CjcsS9vpQn$53gM5bMnOcw"
CREATE UNIQUE INDEX CONCURRENTLY "Foo_id_rank_key" ON public."Foo" USING btree (id, rank)
ALTER TABLE "public"."Foo" ADD CONSTRAINT "Foo_id_rank_key" UNIQUE USING INDEX "Foo_id_rank_key"
ALTER TABLE "public"."Foo" DROP CONSTRAINT "pgschemadiff_tmpidx_Foo_id_rank_key_CjcsS9vpQn$53gM5bMnOcw"
diff.Plan{
    Statements:        nil,
    CurrentSchemaHash: "fd706c7a554333f4",
}

Context

pg-schema-diff version: v1.0.5

pg-schema-diff usage: CLI
Postgres version: 17.9