Add support for `IF NOT EXISTS` when creating postgres indexes concurrently

[Marcelo]

Hi.

The code for CREATE INDEX CONCURRENTLY does not allow for the IF NOT EXISTS conditional.

This is not consistent with the pattern for other schema-change operations, which already include either IF NOT EXISTS or IF EXISTS so that changes fail without error. For example, check the DatabaseSchemaEditor queries at django/db/backends/postgresql/schema.py

This is particularly important in my case. I have the same Django project being deployed in different regions of the world.
I'd like to create the index concurrently out-of-business hours, which varies depending on the country. Following that, I'd like to create the migration file so that all environments are in sync.

However, if the IF NOT EXISTS is not available, those migrations won't be idempotent.

There is an open PR that attempts to address this:

​https://github.com/django/django/pull/18081

[Adam Johnson]

This is not consistent with the pattern for other schema-change operations, which already include either IF NOT EXISTS or IF EXISTS so that changes fail without error.

That’s not true for CREATE INDEX, though: ​https://github.com/django/django/blob/2be37b253341cfd1f1363c533e6f896230f047a7/django/db/backends/postgresql/schema.py#L17-L20 . To be honest, there doesn’t seem to be a consistent policy there.

This is particularly important in my case. I have the same Django project being deployed in different regions of the world.
I'd like to create the index concurrently out-of-business hours, which varies depending on the country. Following that, I'd like to create the migration file so that all environments are in sync.

In my opinion, your use case goes too far beyond Django to inform any changes to the framework. You can use RunSQL within your migration with the IF NOT EXISTS clause. That seems a small price to pay for enforcing your particular deployment scheme.

[Marcelo]

Thanks for your reply Adam Johnson;

In my opinion, your use case goes too far beyond Django to inform any changes to the framework.

I agree that my project is complex, yet this isn't something unseen on a big Django project.
But I would prefer to not focus on my individual case, because I think this goes beyond what I do and how I deploy my project.

Django already, at present, provides mechanisms for users to have a more granular control of how they want to manage schema changes.
For example, we have SeparateDatabaseAndState and we also have the management command --run-syncdb.

I think that making the CREATE INDEX CONCURRENTLY command idempotent by adding the _option_ for a conditional IF NOT EXISTS (see implementation details on the attached PR) would be a good improvement towards having a little bit more control over how the schema editor behaviour.

Let me know if that makes sense.

That’s not true for CREATE INDEX

Yes, but it is true that the other commands are all idempotent, including the extension creation command which isn't in that class.
I don't know why CREATE INDEX wasn't made so from the gate go, it seems like the exception, not the rule.

[Sarah Boyce]

Duplicate of #34729 which was also discussed and resolved as wontfix.