How to set PRAGMA foreign_keys=OFF in Ecto migration?

Questions / Help
,
1

Hello,

Using SQLite, in order to remove a NOT NULL constraint on a column, I’m trying to follow this procedure: https://www.sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes

The problem is, we must execute PRAGMA foreign_keys=OFF before the transaction, because SQLite’s documentation says:

PRAGMA foreign_keys = boolean ;
This pragma is a no-op within a transaction; foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT.

I’ve tried this approach:

defmodule Brinjel.Repo.Migrations.RelaxNotNullConstraintForOwnerId do
  use Ecto.Migration

  @disable_ddl_transaction true

  def change do
    execute("PRAGMA foreign_keys=OFF;")

    execute("PRAGMA busy_timeout=5000;")

    execute("BEGIN TRANSACTION")

    create table(:new_farms, primary_key: false, options: "STRICT") do
      add :farm_id, :bigserial, primary_key: true
      add :name, :text, null: false
      add :slug, :text, null: false
      add :default_provider_id, :integer
      add :locked, :boolean, null: false, default: false
      add :trial_expiry_date, :date, null: false

      add :owner_id, references(:users)

      timestamps()
    end

    execute """
      INSERT INTO new_farms(farm_id, name, slug, default_provider_id, locked, trial_expiry_date, owner_id, inserted_at, updated_at)
      SELECT farm_id, name, slug, default_provider_id, locked, trial_expiry_date, owner_id, inserted_at, updated_at 
      FROM farms;
    """

    flush()

    drop table("farms")

    rename table("new_farms"), to: table("farms")

    create unique_index(:farms, [:slug])

    execute("COMMIT")

    execute("PRAGMA foreign_keys=ON")
  end
end

and unfortunately got this error:

17:20:26.716 [info] execute "PRAGMA foreign_keys=OFF;"

17:20:26.716 [info] execute "PRAGMA busy_timeout=5000;"

17:20:26.716 [info] execute "BEGIN TRANSACTION"

17:20:26.716 [info] create table new_farms

17:20:26.720 [info] execute "  INSERT INTO new_farms(farm_id, name, slug, default_provider_id, locked, trial_expiry_date, owner_id, inserted_at, updated_at)\n  SELECT farm_id, name, slug, default_provider_id, locked, trial_expiry_date, owner_id, inserted_at, updated_at \n  FROM farms;\n"

17:20:26.721 [info] drop table farms
** (Exqlite.Error) Database busy
DROP TABLE "farms"
    (ecto_sql 3.11.3) lib/ecto/adapters/sql.ex:1054: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.16.1) lib/enum.ex:1700: Enum."-map/2-lists^map/1-1-"/2
    (ecto_sql 3.11.3) lib/ecto/adapters/sql.ex:1161: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.11.3) lib/ecto/migration/runner.ex:348: Ecto.Migration.Runner.log_and_execute_ddl/3
    (elixir 1.16.1) lib/enum.ex:1700: Enum."-map/2-lists^map/1-1-"/2
    (stdlib 5.2) timer.erl:270: :timer.tc/2
    (ecto_sql 3.11.3) lib/ecto/migration/runner.ex:25: Ecto.Migration.Runner.run/8
    (ecto_sql 3.11.3) lib/ecto/migrator.ex:365: Ecto.Migrator.attempt/8

Any idea why dropping the table produces a “Database busy” error?