Postgrex errors with "cached plan must not change result type" during migration

Questions / Help
,
1

I have a migration that modifies a table, flushes, and then migrates the data in that table. This worked fine locally, but in CI it fails during tests.

The line in question is:

users = Ido.Repo.all(from(u in "users", select: %{id: u.id}))

The error in CI is: (Postgrex.Error) ERROR 0A000 (feature_not_supported) cached plan must not change result type

All the explanations I’ve found for this error are when you have multiple connections open to the same database. This doesn’t feel like it should apply for me when it’s a single migration file. Does anyone know how to resolve this?

Here’s the full migration (some fields omitted for brevity):

defmodule Ido.Repo.Migrations.CreateUsersAuthTables do
  use Ecto.Migration
  import Ecto.Query

  def change do
    execute("CREATE EXTENSION IF NOT EXISTS citext", "")

    alter table(:users) do
      # temporarily not making password required, as we need to fill in defaults for existing users
      add(:hashed_password, :string)
    end

    # generate random password for existing users
    flush()
    add_placeholder_passwords()

    alter table(:users) do
      modify(:hashed_password, :string, null: false)
    end
  end

  defp add_placeholder_passwords do
    users = Ido.Repo.all(from(u in "users", select: %{id: u.id}))

    # generate random password for existing users
    for %{id: id} <- users do
      # use a unique uuid for password, users will have to reset password to login
      password = Ecto.UUID.generate()
      hashed_password = Bcrypt.hash_pwd_salt(password)

      Ido.Repo.update_all(
        from(u in "users",
          where: u.id == ^id
        ),
        set: [hashed_password: hashed_password]
      )
    end
  end
end
2

Strangely I fixed this by using a schema in my migration. (Just a local schema- not making the mistake of using my model’s schema…)

Updated migration that works:

defmodule Ido.Repo.Migrations.CreateUsersAuthTables do
  import Ecto.Changeset
  use Ecto.Migration

  defmodule User do
    use Ido.Schema

    schema "users" do
      field(:hashed_password, :string)
    end

    def changeset(element, attrs) do
      element
      |> cast(attrs, [
        :hashed_password
      ])
    end
  end

  def change do
    execute("CREATE EXTENSION IF NOT EXISTS citext", "")

    alter table(:users) do
      # temporarily not making password required, as we need to fill in defaults for existing users
      add(:hashed_password, :string)
    end

    # generate random password for existing users
    flush()
    add_placeholder_passwords()

    alter table(:users) do
      modify(:hashed_password, :string, null: false)
    end
  end

  defp add_placeholder_passwords do
    # generate random password for existing users
    for user <- Ido.Repo.all(User) do
      # use a unique uuid for password, users will have to reset password to login
      password = Ecto.UUID.generate()
      hashed_password = Bcrypt.hash_pwd_salt(password)

      user
      |> User.changeset(%{hashed_password: hashed_password})
      |> Ido.Repo.update()
    end
  end
end