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
• sql: Support ALTER TABLE ADD COLUMN colname REFERENCES tablename(colname);  #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
  • sql: support compound types #8318
  • sql: support user-defined types #25123
• Doesn't support ENUM data Types
  • sql: support ENUM types #24873
  • sql: support user-defined types #25123
• sql: implement partial indexes #9683 Doesn't support unique index with where clauses
• sql: support stored procedures #17511 Doesn't support stored procedures or functions
• Not possible to change stored query on a view, must drop the view and recreate sql: CREATE OR REPLACE VIEW #24897
• sql: support rolling back nested transactions containing schema changes #10735 rolling back nested transactions containing schema changes
• named SAVEPOINTs
• timestamp precision sql: support optional TIMESTAMP precision #32098
• binary protocol bug with decimal sql: binary protocol bug with decimal #25460
• SimpleQuery not allowed while in extended protocol mode Ecto/Postgrex triggering ERROR 08P01 (protocol_violation) SimpleQuery not allowed while in extended protocol mode #50858
• can't load arrays in binary protocol Regression starting from 19.2 with Elixir / binary protocol when loading arrays #51360

Jira issue: CRDB-4690

[DavidOliver]

I believe support for the timestamp precision has been reverted.

Discussion at #32098 (comment).

[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 (Regression starting from 19.2 with Elixir / binary protocol when loading arrays #51360)
• Support for if_not_exists / if_exists is missing for now ([Feature] Leverage IF NOT EXISTS / IF EXISTS syntax with create index / drop index (Postgres) elixir-ecto/ecto_sql#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!

[lessless]

In light of the upcoming webinar - what are the plans for getting proper compatibility with Ecto?

[ghost]

In light of the upcoming webinar - what are the plans for getting proper compatibility with Ecto?

We’ve been running in production for a number of clients for a while (+ 6 months) now (erlang solutions). Using the Postgres driver and can’t think of any issues.

Might be time to close this issue out unless there’s something we’ve been missing. Migrations issues etc are gone now.

[lessless]

Thanks for feedback @bryanhuntesl

[lessless]

@escobera won't you mind sharing your connection settings, please? I have few problems:

The first one is with SSL - I’m running into an {:tls_alert, {:handshake_failure, 'TLS client: In state wait_cert_cr at ssl_handshake.erl:1990 generated CLIENT ALERT: Fatal - Handshake Failure\n {bad_cert,hostname_check_failed}'}} with

ssl_opts: [
  verify: :verify_peer,
  cacertfile: '/Users/lessless/.postgresql/root.crt',
]

The second one with specifying a cluster name? If I disable an SSL I’m getting following error FATAL 08004 (sqlserver_rejected_establishment_of_sqlconnection) codeParamsRoutingFailed: missing cluster name in connection string

I took connection params from “Connection parameters” tab and database value there seems to be a combination from cluster name and a database name database = superfun-superfun-448.defaultdb but that doesn't work.

[lessless]

@bryanhuntesl given that I ignore SSL host validation and create a database by hand I'm encountering a problem when running migrations:

no match of right hand side value: {:error, %Postgrex.Error{connection_id: 0, message: nil, postgres: %{code: :syntax_error, detail: "source SQL:\nLOCK TABLE \"schema_migrations\" IN SHARE UPDATE EXCLUSIVE MODE\n^", file: "lexer.go", line: "215", message: "at or near \"lock\": syntax error", pg_code: "42601", routine: "Error", severity: "ERROR"}, query: "LOCK TABLE \"schema_migrations\" IN SHARE UPDATE EXCLUSIVE MODE"}}

Here is a reproduction of this query from the cockroach shell

superfun@free-tier4.aws-us-west-2.cockroachlabs.cloud:26257/defaultdb> LOCK TABLE "schema_migrations" IN SHARE UPDATE EXCLUSIVE MODE;
invalid syntax: statement ignored: at or near "lock": syntax error
SQLSTATE: 42601
DETAIL: source SQL:
LOCK TABLE "schema_migrations" IN SHARE UPDATE EXCLUSIVE MODE
^
superfun@free-tier4.aws-us-west-2.cockroachlabs.cloud:26257/defaultdb> ^D
ERROR: invalid syntax
Failed running "SQL

[timothyvanderaerden]

@lessless to configure with SSL:

database: "superfun-superfun-448.defaultdb",
ssl: true,
ssl_opts: [
  cacertfile: "cc-ca.crt"
]

More info: https://hexdocs.pm/postgrex/Postgrex.html#start_link/1-ssl-client-authentication

To run migrations, migration_lock should be disabled since CockroachDB doesn't support table locking (as far as I know):

migration_lock: false

More info: https://hexdocs.pm/ecto_sql/Ecto.Migration.html#module-repo-configuration

[erszcz]

I confirm getting the error that @lessless mentioned above:

** (MatchError) no match of right hand side value: {:error, %Postgrex.Error{connection_id: 0, message: nil, postgres: %{code: :syntax_error, detail: "source SQL:\nLOCK TABLE \"schema_migrations\" IN SHARE UPDATE EXCLUSIVE MODE\n^", file: "lexer.go", line: "215", message: "at or near \"lock\": syntax error", pg_code: "42601", routine: "Error", severity: "ERROR"}, query: "LOCK TABLE \"schema_migrations\" IN SHARE UPDATE EXCLUSIVE MODE"}}

Running migrations used to work fine - this started happening after an upgrade from ecto_sql 3.5.4 to 3.7.1.

[ghost]

There's an ecto flag to disable table when migrating, it's only needed for mysql as DDL changes aren't transactional on that DB.

…

On Thu, 21 Oct 2021, 16:03 Radek Szymczyszyn, ***@***.***> wrote: I confirm getting the error that @lessless <https://github.com/lessless> mentioned above: ** (MatchError) no match of right hand side value: {:error, %Postgrex.Error{connection_id: 0, message: nil, postgres: %{code: :syntax_error, detail: "source SQL:\nLOCK TABLE \"schema_migrations\" IN SHARE UPDATE EXCLUSIVE MODE\n^", file: "lexer.go", line: "215", message: "at or near \"lock\": syntax error", pg_code: "42601", routine: "Error", severity: "ERROR"}, query: "LOCK TABLE \"schema_migrations\" IN SHARE UPDATE EXCLUSIVE MODE"}} Running migrations used to work fine - this started happening after an upgrade from ecto_sql 3.5.4 to 3.7.1. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#33441 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AHUCR5TFJBPER6Q3FKD22U3UIATTJANCNFSM4GM4D4PQ> .

[rafiss]

@erszcz As @timothyvanderaerden mentioned, CockroachDB does not support table locking.

It seems like ecto_sql has added some functionality that uses table locking.

Can you set migration_lock: false? https://hexdocs.pm/ecto_sql/Ecto.Migration.html#module-repo-configuration

[erszcz]

Thanks @bryanhuntesl and @rafiss! Indeed, migration_lock: false solves the problem on ecto_sql 3.7.1.

[benonymus]

Hey,

I am dabbling with moving to cockroachDB.

When I am running some historical migrations, this seems to not work:

    alter table(:session_chats) do
      modify :session_id, references(:sessions, on_delete: :delete_all),
        from: references(:sessions, on_delete: :nothing)
    end

The error:

alter table session_chats
** (Postgrex.Error) ERROR 42710 (duplicate_object) duplicate constraint name: "session_chats_session_id_fkey"

Are these not supported? Or how would I need to rewrite them?

These work on regular postgres.