Cockroach: sql: Ecto compatibility

[BramGruneir]

This is a list of issues found with Ecto (Elixir).
Note that most of these are not required to get Ecto working, but each of these can be a potential roadblock when migrating an existing app to cockroach.

• [ ] Defaults where arrays are set must be annotated, solving this with fragment on default definition
• [ ] Can't do any operation on a column or table where the operation is in the same migration as that objects creation
• [x] #32917 Must do foreign keys in three separate migrations (add column, add an index on that column, add the constraint)
• [ ] Doesn't support Compound Data Types
  
  
  
  • [x] #8318
  
  
  • [ ] #25123
  
  
• [ ] Doesn't support ENUM data Types
  
  
  
  • [x] #24873
  
  
  • [ ] #25123
  
  
• [x] #9683 Doesn't support unique index with where clauses
• [ ] #17511 Doesn't support stored procedures or functions
• [x] Not possible to change stored query on a view, must drop the view and recreate #24897
• [ ] #10735 rolling back nested transactions containing schema changes
• [x] named SAVEPOINTs
• [x] timestamp precision #32098
• [x] binary protocol bug with decimal #25460
• [x] SimpleQuery not allowed while in extended protocol mode #50858
• [ ] can't load arrays in binary protocol #51360

[DavidOliver]

I believe support for the timestamp precision has been reverted.

Discussion at https://github.com/cockroachdb/cockroach/issues/32098#issuecomment-594545673.

[awoods187]

@DavidOliver this will be in 20.1 and you can see it in this beta (https://www.cockroachlabs.com/docs/releases/v20.1.0-beta.2.html)

[fire]

Note that #24897 is resolved.

Not possible to change stored query on a view, must drop the view and recreate #24897

[fire]

32917 Must do foreign keys in three separate migrations (add column, add an index on that column, add the constraint)

32917 in a pr stage.

[RoachietheSupportRoach]

Zendesk ticket #5257 has been linked to this issue.

[DavidOliver]

32917 is resolved.

[rafiss]

note that named SAVEPOINT support will also be included in 20.1

[tlvenn]

It should be noted that with CDB 20.1 all the major issues have been fixed but one as soon as you are running tests. This is fixed on master but it has yet to be released.

Additionally:

• Support for arrays is broken since 19.2 (https://github.com/cockroachdb/cockroach/issues/51360)
• Support for if_not_exists / if_exists is missing for now (https://github.com/elixir-ecto/ecto_sql/issues/247)

[rafiss]

50858 is closed and the fix will also be included in the next 20.1 release.

Thanks for filing those two new issues!

[escobera]

I found a problem when creating schemas with Ecto.Migration.

This code just hangs after the execution and the next migration never runs. I'm testing on 20.2

defmodule ProcessosApi.Repo.Migrations.CreateMySchema do
  use Ecto.Migration

  def change do
    execute "CREATE SCHEMA myschema"
  end
end

I don't know if this is the correct place to report this. If not just lemme know and I'll move to the correct place.

Thanks in advance!