Generated migrations are sometimes non-rollback-able #595

Code of Conduct

• I agree to follow this project's Code of Conduct

AI Policy

• I agree to follow this project's AI Policy, or I agree that AI was not used while creating this issue.

Versions

Elixir 1.18.4 (compiled with Erlang/OTP 28)
Ash 3.5.28
ash_postgres 2.6.11

Operating system

macOS 13.6.3 (22G436)

Current Behavior

When creating a new resource and defining at least these blocks:

defmodule AshPostgresRollback.Domain.SecondResource do
  use Ash.Resource,
    otp_app: :ash_postgres_rollback,
    domain: AshPostgresRollback.Domain,
    data_layer: AshPostgres.DataLayer

  postgres do
    table "second_resources"
    repo AshPostgresRollback.Repo

    references do
      reference :first_resource, deferrable: :initially
    end
  end

# skip

  relationships do
    belongs_to :first_resource, AshPostgresRollback.Domain.FirstResource
  end
end

ash.codegen will bundle the creation of a table for the resource, as well as the creation of the foreign key & adjustment of the DEFERRABLE behaviour into the same migration. This work in the up direction just fine (it first creates the table, then the FK, then alters the constraint), but in the down direction, the generated migration first drops the constraint, then the table, then tries to alter the constraint on an already dropped table:

defmodule AshPostgresRollback.Repo.Migrations.SecondResource do
# skip
  def down do
    drop(constraint(:second_resources, "second_resources_first_resource_id_fkey"))

    drop(table(:second_resources))

    execute(
      "ALTER TABLE second_resources ALTER CONSTRAINT second_resources_first_resource_id_fkey DEFERRABLE INITIALLY DEFERRED"
    )
  end
end

Reproduction

I prepared a sample GitHub repo that demonstrates the issue, available here. The linked migration shows that it first attempts to drop the table second_resources, then it tries to alter a constraint on an already dropped table, resulting in a failure.

(Please excuse the nonsensical resources in the sample repo, and the strange formatting of migrations 😬)

Expected Behavior

I think there’s probably a bunch of solutions here, not sure which one is the best one…

• The ALTER CONSTRAINT statement could be placed at the top of the down block, thus making the down block truly in a reverse order from the up block
• The changes could be split into two migrations, one to create the tables, one to alter the constraint settings, then correct rollback behaviour would be achieved automatically?
• (Sidenote?) Perhaps it should be noted that both up and down blocks (as shown in the sample repo) make the constraint DEFERRABLE INITIALLY DEFERRED, so the rollback behaviour wouldn’t even truly restore the previous/original setting (I believe INITIALLY DEFERRED is not the default value… I think)