#34840: Django 4.2 casts text fields when testing IS NULL, preventing use of
partial indexes
-------------------------------------+-------------------------------------
Reporter: Alex Vandiver | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Alex Vandiver):
Yes, the indexes as created on 4.2 ''do'' have the cast in them. The diff
of our schema across the 4.2 upgrade is:
{{{
$ diff -U0 <(rg -U '(?s)CREATE[^;]*;' before.sql) <(rg -U
'(?s)CREATE[^;]*;' after.sql)
--- /dev/fd/63 2023-09-13 12:55:09.595790167 -0700
+++ /dev/fd/62 2023-09-13 12:55:09.595790167 -0700
@@ -1095,4 +1095,4 @@
-CREATE UNIQUE INDEX unique_installation_count ON
zulip.analytics_installationcount USING btree (property, subgroup,
end_time) WHERE (subgroup IS NOT NULL);
-CREATE UNIQUE INDEX unique_installation_count_null_subgroup ON
zulip.analytics_installationcount USING btree (property, end_time) WHERE
(subgroup IS NULL);
-CREATE UNIQUE INDEX unique_realm_count ON zulip.analytics_realmcount
USING btree (realm_id, property, subgroup, end_time) WHERE (subgroup IS
NOT NULL);
-CREATE UNIQUE INDEX unique_realm_count_null_subgroup ON
zulip.analytics_realmcount USING btree (realm_id, property, end_time)
WHERE (subgroup IS NULL);
+CREATE UNIQUE INDEX unique_installation_count ON
zulip.analytics_installationcount USING btree (property, subgroup,
end_time) WHERE ((subgroup)::text IS NOT NULL);
+CREATE UNIQUE INDEX unique_installation_count_null_subgroup ON
zulip.analytics_installationcount USING btree (property, end_time) WHERE
((subgroup)::text IS NULL);
+CREATE UNIQUE INDEX unique_realm_count ON zulip.analytics_realmcount
USING btree (realm_id, property, subgroup, end_time) WHERE
((subgroup)::text IS NOT NULL);
+CREATE UNIQUE INDEX unique_realm_count_null_subgroup ON
zulip.analytics_realmcount USING btree (realm_id, property, end_time)
WHERE ((subgroup)::text IS NULL);
@@ -1100,4 +1100,4 @@
-CREATE UNIQUE INDEX unique_stream_count ON zulip.analytics_streamcount
USING btree (stream_id, property, subgroup, end_time) WHERE (subgroup IS
NOT NULL);
-CREATE UNIQUE INDEX unique_stream_count_null_subgroup ON
zulip.analytics_streamcount USING btree (stream_id, property, end_time)
WHERE (subgroup IS NULL);
-CREATE UNIQUE INDEX unique_user_count ON zulip.analytics_usercount USING
btree (user_id, property, subgroup, end_time) WHERE (subgroup IS NOT
NULL);
-CREATE UNIQUE INDEX unique_user_count_null_subgroup ON
zulip.analytics_usercount USING btree (user_id, property, end_time) WHERE
(subgroup IS NULL);
+CREATE UNIQUE INDEX unique_stream_count ON zulip.analytics_streamcount
USING btree (stream_id, property, subgroup, end_time) WHERE
((subgroup)::text IS NOT NULL);
+CREATE UNIQUE INDEX unique_stream_count_null_subgroup ON
zulip.analytics_streamcount USING btree (stream_id, property, end_time)
WHERE ((subgroup)::text IS NULL);
+CREATE UNIQUE INDEX unique_user_count ON zulip.analytics_usercount USING
btree (user_id, property, subgroup, end_time) WHERE ((subgroup)::text IS
NOT NULL);
+CREATE UNIQUE INDEX unique_user_count_null_subgroup ON
zulip.analytics_usercount USING btree (user_id, property, end_time) WHERE
((subgroup)::text IS NULL);
}}}
So if the application's schema was inserted with 4.2, then its partial
indexes work with the current code. If it was inserted with Django < 4.2,
then the indexes don't work with >= 4.2.
This also means that there's a bit of a pickle -- you can't just strip off
the `::text` in the query in some later 4.2 bugfix release, because that
will also make any 4.2-inserted indexes with `::text` not work with the
newly `::text`-less query.
Replying to [comment:1 Simon Charette]:
> We should try to avoid casts if possible (IIRC they are only required
for server-side bindings).
>
> Out of curiosity, if you try re-creating your `UniqueConstraint` on 4.2,
are they created with casts? By that I'm in no mean implying that a
solution to this problem is to force the re-creation of constraints on
each releases but I want to have an idea of the scope of the problem.
>
> If that's the case it means that reverting to no cast in a minor release
might break Postgres query planer the other way around as constraint
created on 4.2 include `::text` cast but queries on a future version
removing them don't.
--
Ticket URL: <https://code.djangoproject.com/ticket/34840#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018a940b689c-bf0fdb5e-c876-42d6-9d48-75cc8224e437-000000%40eu-central-1.amazonses.com.
