bug change unique column to primary-key

[PennXuKF]

Docker image used: public.ecr.aws/docker/library/postgres:16.2
atlas version v0.28.2-78a8416-canary

files

• migrations/
  • 20241114014649_init.sql
  • 20241114015636_change_uniq_to_primary.sql
  • atlas.sum
• atlas.hcl
• schema.sql

## atlas.hcl
env "local" {
  src = "file://./schema.sql"
  dev = "docker://postgres/16/dev"
  migration {
    dir = "file://./migrations"
  }
  format {
    migrate {
      diff = "{{ sql . \"  \" }}"
    }
  }
}

## schema.sql  (note the "change")
CREATE TABLE a (
    -- a_id VARCHAR(32) NOT NULL  -- change: add "PRIMARY KEY"
    a_id VARCHAR(32) NOT NULL PRIMARY KEY
);
-- CREATE UNIQUE INDEX unique_a_id_idx ON a(a_id);  -- change: remove this index

CREATE TABLE b (
    a_id VARCHAR(32) NOT NULL REFERENCES a(a_id)
);

## migrations/20241114015636_change_uniq_to_primary.sql
-- Drop index "unique_a_id_idx" from table: "a"
DROP INDEX "public"."unique_a_id_idx";
-- Modify "a" table
ALTER TABLE "public"."a" ADD PRIMARY KEY ("a_id");

STEPS

• Run atlas migrate diff --env local change_uniq_to_primary
• Got file 20241114015636_change_uniq_to_primary.sql without any error output
• Now run atlas migrate lint --latest 1 --env local

Now we got error!

Analyzing changes from version 20241114014649 to 20241114015636 (1 migration in total):

  Error: executing statement: DROP INDEX "public"."unique_a_id_idx";: pq: cannot drop index unique_a_id_idx because other objects depend on it
  
# indeed if we try it on a Posgres DB 16.2, we get below
cannot drop index unique_a_id_idx because other objects depend on it

Do not try to hand edit and remove only DROP INDEX "public"."unique_a_id_idx";, it will keep coming back in future migration.

Workaround

• Run atlas migrate diff --env local change_uniq_to_primary
• Got file 20241114044833_change_uniq_to_primary.sql without any error output (same content as above)
• Now hand edit this file migrations/20241114015636_change_uniq_to_primary.sql (note Manually add blocks)

-- Manually add: First drop related foreign key constraints
ALTER TABLE "public"."b" DROP CONSTRAINT "b_a_id_fkey";

-- What Atlas generated:
-- Drop index "unique_a_id_idx" from table: "a"
DROP INDEX "public"."unique_a_id_idx";
-- Modify "a" table
ALTER TABLE "public"."a" ADD PRIMARY KEY ("a_id");

-- Manually add: Recreate the foreign key constraints to reference the primary key
ALTER TABLE "public"."b" 
    ADD CONSTRAINT "b_a_id_fkey" 
    FOREIGN KEY ("a_id") REFERENCES "public"."a"("a_id");

• Run atlas migrate hash --env local
• Run atlas migrate lint --latest 1 --env local (output has no Error)

# indeed if we try the migration on a Posgres DB 16.2 with data in both table a and b, we get success