Possibly use raw SQL to convert to jsonb (maybe not always)

[dmitriid]

Describe the bug

When converting a text column to jsonb,

ERROR 42804 (datatype_mismatch) column "<column name>" cannot be cast automatically to type jsonb

hint: You might need to specify "USING <column name>::jsonb".

The migration for the column is

defmodule Telepai.Repo.Migrations.UpdateSessionSettingField do
  use Ecto.Migration

  def up do
    alter table(:sessions) do
      modify :settings, :map
    end
  end

  def down do
    alter table(:sessions) do
      modify :settings, :text
    end
  end
end

I honestly can't tell you if this is always the case, but it looks like at least for text columns the conversion should include USING <column name>::JSONB

ALTER TABLE sessions ALTER COLUMN settings TYPE JSONB USING settings::JSONB

If I'm not mistaken, the only way to do that with migrations is to use a raw execute

  def up do
    execute "..."
  end

To Reproduce

• Create migrations for the resource before and after the change:

defmodule Telepai.AI.Session do
  use Ash.Resource,
    data_layer: AshPostgres.DataLayer

  postgres do
    table("sessions")
    repo(Telepai.Repo)
  end

  attributes do
    uuid_primary_key(:id)

-   attribute :settings, :string do
+   attribute :settings, :map do 
      allow_nil?(false)
    end

    create_timestamp :inserted_at
  end
end

• Run migrations

Expected behavior

Migration should run with no issue

Runtime

• Elixir version: Elixir 1.14.4
• Erlang version: Erlang/OTP 25 [erts-13.2] [source] [64-bit]
• OS: MacOS Ventura 13.4 (22F66)
• Ash version: {:ash, "~> 2.9.6"}
• any related extension versions: {:ash_postgres, "~> 1.3.25"}

[zachdaniel]

Its an interesting idea, we could potentially do something like this by default. I think its reasonable that some migrations will have to have manual intervention, but this seems like something we could potentially detect and provide a better default for.

[zachdaniel]

In this case, though, it would produce a jsonb string and not a map though.

[dmitriid]

The migrations generator produced modify :settings, :jsonb for me when I changed attribute type from string to map🤔

[zachdaniel]

Yeah, but a string is a valid jsonb. So my point is even with USING column:jsonb it will still not be an actual object under the hood.

[dmitriid]

Ah! I see what you mean.

I missed this because in my case I was storing Jason.encoded strings anyway :) But if there were other strings, it would either fail, or would fail on read 🤔

No idea how to solve this properly besides generating warnings or comments during migration or when generating migrations.

[zachdaniel]

Yeah, I mean honestly thats what happens when you run the migrations, the data store tells you that you can't actually do that transition and have to figure out some other way to do what you want. I'm not sure if the migration generator should try to figure this one out since it can't know what the right answer is.

[zachdaniel]

I think we can't reliably do this kind of thing currently. Folks will need to edit the migrations themselves.

[dmitriid]

awww shucks :(

Well, it will probably affect a very small number of migrations anyway, and the solution is quite easy.

[zachdaniel]

Yeah, exactly. Its possible that in the future we could do it with something interactive in cases we can detect an issue, but the juice is unlikely to be worth the squeeze.