[Django] #35383: Add support for `IF NOT EXISTS` when creating postgres indexes concurrently

[Django]

#35383: Add support for `IF NOT EXISTS` when creating postgres indexes concurrently
-------------------------------------+-------------------------------------
Reporter: | Owner: (none)
marcelofern |
Type: New | Status: new
feature |
Component: | Version: 5.0
contrib.postgres | Keywords: database, postgres,
Severity: Normal | index, concurrently
Triage Stage: | Has patch: 1
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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
--
Ticket URL: <https://code.djangoproject.com/ticket/35383>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35383: Add support for `IF NOT EXISTS` when creating postgres indexes concurrently
-------------------------------------+-------------------------------------

Reporter: marcelofern | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: 5.0
Severity: Normal | Resolution:
Keywords: database, postgres, | Triage Stage:
index, concurrently | Unreviewed
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/35383#comment:1>

[Django]

#35383: Add support for `IF NOT EXISTS` when creating postgres indexes concurrently
-------------------------------------+-------------------------------------

Reporter: Marcelo | Owner: (none)

Type: New feature | Status: new
Component: contrib.postgres | Version: 5.0
Severity: Normal | Resolution:
Keywords: database, postgres, | Triage Stage:
index, concurrently | Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/35383#comment:2>

[Django]

#35383: Add support for `IF NOT EXISTS` when creating postgres indexes concurrently
-------------------------------------+-------------------------------------
Reporter: Marcelo | Owner: (none)

Type: New feature | Status: closed
Component: contrib.postgres | Version: 5.0
Severity: Normal | Resolution: duplicate

Keywords: database, postgres, | Triage Stage:
index, concurrently | Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* resolution: => duplicate
* status: new => closed

Comment:

Duplicate of #34729 which was also discussed and resolved as wontfix.
--
Ticket URL: <https://code.djangoproject.com/ticket/35383#comment:3>