[Feature] Leverage IF NOT EXISTS / IF EXISTS syntax with create index / drop index (Postgres)

[tlvenn]

As discussed in jumpn/ecto_cockroachdb#4, the way Ecto deal with creating / dropping indexes when if_not_exists / if_exists is used does not leverage support for "IF NOT EXISTS" / "IF EXISTS" added to Postgres 9.5 when dealing with indexes.

Instead it uses a workaround using the $$ do syntax which is not supported by CockroachDB.

I noticed that the usage of "IF NOT EXISTS" / "IF EXISTS" when dealing with tables was added to Postgres with the 9.1 version and is already being used by the adaptor:

ecto_sql/lib/ecto/adapters/postgres/connection.ex

Line 803 in cd7f585

if_do(command == :create_if_not_exists, "IF NOT EXISTS "),

We could simply do the same for indexes but this would introduce a breaking change for anyone leveraging this feature and still using any Postgres version below 9.5.

But 9.5 is the oldest version actually still supported by Postgres so that might be ok ?
It's not clear what compatibility with Postgres is actually currently targeted by the adaptor. As noted above, given the usage of IF NOT EXISTS with tables, it seems to indicate it is at least 9.1.

[josevalim]

Next version will be v3.5 so it may be fine to drop support for Postgres earlier than v9.5 as they are no longer supported. I have sent a PR here: #248

Meanwhile, a PR to update the syntax we use for indexes is welcome. Thank you!

[tlvenn]

Awesome, I will take care of that, thanks @josevalim !

[josevalim]

Ping! :)

[tlvenn]

I will pong back very soon @josevalim ;)

[tlvenn]

Any idea of the "concurrent index and create_if_not_exists is not supported by the Postgres adapter" was a product of using the $$ do syntax or something else is at play here ?

[josevalim]

@tlvenn see discussion here: elixir-ecto/ecto#2703 (comment)

It seems we can enable this functionality once we rewrite since we now provide a mechanism to disable the lock too.