Opened 7 months ago
Closed 7 months ago
#35383 closed New feature (duplicate)
Add support for `IF NOT EXISTS` when creating postgres indexes concurrently
Reported by: | Marcelo | Owned by: | |
---|---|---|---|
Component: | contrib.postgres | Version: | 5.0 |
Severity: | Normal | Keywords: | database, postgres, index, concurrently |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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:
Change History (3)
comment:2 by , 7 months ago
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.
comment:3 by , 7 months ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
Duplicate of #34729 which was also discussed and resolved as wontfix.
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.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 theIF NOT EXISTS
clause. That seems a small price to pay for enforcing your particular deployment scheme.