syntax error at or near "do" - DO $$ BEGIN CREATE INDEX (...);EXCEPTION WHEN duplicate_table THEN END; $$;

[zoltanmaric]

Hi! First of all, thanks for creating this adaptor! 🙂

I tried to use create_if_not_exists to create an index in an ecto migration, but it generates SQL that Cockroach doesn't seem to support:

** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "do"

    query: DO $$ BEGIN CREATE INDEX "my_table_my_column_index" ON "my_table" ("my_column");EXCEPTION WHEN duplicate_table THEN END; $$;

source SQL:
DO $$ BEGIN CREATE INDEX "my_table_my_column_index" ON "my_table" ("my_column")
^
    (ecto_sql 3.3.4) lib/ecto/adapters/sql.ex:612: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.10.3) lib/enum.ex:1396: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql 3.3.4) lib/ecto/adapters/sql.ex:699: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.3.4) lib/ecto/migration/runner.ex:343: Ecto.Migration.Runner.log_and_execute_ddl/3
    (ecto_sql 3.3.4) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
    (elixir 1.10.3) lib/enum.ex:2111: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto_sql 3.3.4) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
    (stdlib 3.8.2.2) timer.erl:166: :timer.tc/1

I tried to paste the same command into a cockroach console, and I got the same result:

root@127.0.0.1:26257/defaultdb>DO $$ BEGIN CREATE INDEX "my_table_my_column_index" ON "my_table" ("my_column");EXCEPTION WHEN duplicate_table THEN END; $$;
invalid syntax: statement ignored: syntax error at or near "do"
DETAIL: source SQL:
DO $$ BEGIN CREATE INDEX "my_table_my_column_index" ON "my_table" ("my_column")
^

It seems like Cockroach (or at least my version of it) doesn't support the DO $$ ... $$; syntax.

I believe the statement is generated here.

Here's my Cockroach version:

cockroach version
Build Tag:    v19.1.4
Build Time:   2019/08/06 15:34:13
Distribution: CCL
Platform:     linux amd64 (x86_64-unknown-linux-gnu)
Go Version:   go1.11.6
C Compiler:   gcc 6.3.0
Build SHA-1:  51a6fdedf0ce1d1329d40d801a7deaf8206b6b07
Build Type:   release

Is there some way around this issue?

[zoltanmaric]

Maybe this alternative way of expressing a DO $$ block can be helpful: https://stackoverflow.com/a/24338060/900130

[tlvenn]

Hi zoltanmaric,

Ya, CDB does not support the DO $$ syntax but it does support the IF NOT EXISTS so it's a bit unfortunate that Ecto leverages this syntax for the create_if_not_exists:

https://github.com/elixir-ecto/ecto_sql/blob/cd7f585d992f14331874698b1851152fe09f8304/lib/ecto/adapters/postgres/connection.ex#L853

I am not exactly sure why they actually do this, might worth opening an issue there to inquiry about it.

[tlvenn]

Scratch that, I know why they are using this syntax, it is simply because they are not leveraging yet support forIF NOT EXISTS with CREATE INDEX introduced with Postgres 9.5.

They are actually already using the syntax with CREATE TABLE which was introduced with Postgres 9.1

https://github.com/elixir-ecto/ecto_sql/blob/cd7f585d992f14331874698b1851152fe09f8304/lib/ecto/adapters/postgres/connection.ex#L803

Seems like a good opportunity for a little contribution to ecto-sql 😃

[tlvenn]

Created elixir-ecto/ecto_sql#247 to track this.

[tlvenn]

Closed with elixir-ecto/ecto_sql#253

[zoltanmaric]

Thanks for sorting this out so quickly @tlvenn 👏 ❤️ And congrats on the merged pull request in Ecto!