[Django] #35336: Adding GeneratedField fails with ProgrammingError when using When on CharField

[Django]

#35336: Adding GeneratedField fails with ProgrammingError when using When on
CharField
-------------------------------------+-------------------------------------
Reporter: Adrian | Owner: nobody
Garcia |
Type: Bug | Status: new
Component: | Version: 5.0
Migrations | Keywords: postgres,
Severity: Normal | generatedfield, _split_query
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Forgive me if I am doing something incorrectly, but I cannot for the life
of me add a GeneratedField to one of my models. To replicate this error, I
created this test model:
{{{
from django.db import models

class TestModel(models.Model):
description = models.TextField()
}}}
Running makemigrations/migrate, then modifying the model to add this
`contains_heck` field:
{{{
from django.db import models

class TestModel(models.Model):
description = models.TextField()
contains_heck = models.GeneratedField(
expression=models.Case(
models.When(description__icontains="HECK",
then=models.Value(True)),
default=models.Value(False),
output_field=models.BooleanField(),
),
output_field=models.BooleanField(),
db_persist=True,
)
}}}
Which generates this migration:
{{{
# Generated by Django 5.0.3 on 2024-03-27 20:34

from django.db import migrations, models


class Migration(migrations.Migration):
dependencies = [
("core", "00001_initial"),
]

operations = [
migrations.AddField(
model_name="testmodel",
name="contains_heck",
field=models.GeneratedField(
db_persist=True,
expression=models.Case(
models.When(description__icontains="HECK",
then=models.Value(True)), default=models.Value(False),
output_field=models.BooleanField()
),
output_field=models.BooleanField(),
),
),
]
}}}

And after running `python manage.py migrate` I get the following error:
{{{
Operations to perform:
Apply all migrations: admin, auth, consumer, contenttypes, core, db,
sessions
Running migrations:
Applying core.0002_testmodel_contains_heck...Traceback (most recent call
last):
File "/opt/project/app/manage.py", line 24, in <module>
main()
File "/opt/project/app/manage.py", line 20, in main
execute_from_command_line(sys.argv)
File "/usr/local/lib/python3.11/site-
packages/django/core/management/__init__.py", line 442, in
execute_from_command_line
utility.execute()
File "/usr/local/lib/python3.11/site-
packages/django/core/management/__init__.py", line 436, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/usr/local/lib/python3.11/site-
packages/django/core/management/base.py", line 413, in run_from_argv
self.execute(*args, **cmd_options)
File "/usr/local/lib/python3.11/site-
packages/django/core/management/base.py", line 459, in execute
output = self.handle(*args, **options)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-
packages/django/core/management/base.py", line 107, in wrapper
res = handle_func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-
packages/django/core/management/commands/migrate.py", line 356, in handle
post_migrate_state = executor.migrate(
^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-
packages/django/db/migrations/executor.py", line 135, in migrate
state = self._migrate_all_forwards(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-
packages/django/db/migrations/executor.py", line 167, in
_migrate_all_forwards
state = self.apply_migration(
^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-
packages/django/db/migrations/executor.py", line 252, in apply_migration
state = migration.apply(state, schema_editor)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-
packages/django/db/migrations/migration.py", line 132, in apply
operation.database_forwards(
File "/usr/local/lib/python3.11/site-
packages/django/db/migrations/operations/fields.py", line 108, in
database_forwards
schema_editor.add_field(
File "/usr/local/lib/python3.11/site-
packages/django/db/backends/base/schema.py", line 750, in add_field
self.execute(sql, params)
File "/usr/local/lib/python3.11/site-
packages/django/db/backends/postgresql/schema.py", line 46, in execute
sql = self.connection.ops.compose_sql(str(sql), params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-
packages/django/db/backends/postgresql/operations.py", line 195, in
compose_sql
return mogrify(sql, params, self.connection)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-
packages/django/db/backends/postgresql/psycopg_any.py", line 22, in
mogrify
return ClientCursor(cursor.connection).mogrify(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/psycopg/client_cursor.py",
line 40, in mogrify
pgq = self._convert_query(query, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/psycopg/client_cursor.py",
line 79, in _convert_query
pgq.convert(query, params)
File "/usr/local/lib/python3.11/site-packages/psycopg/_queries.py", line
208, in convert
(self.template, self._order, self._parts) = f(bquery, self._encoding)
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/psycopg/_queries.py", line
242, in _query2pg_client_nocache
parts = _split_query(query, encoding, collapse_double_percent=False)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/psycopg/_queries.py", line
388, in _split_query
raise e.ProgrammingError(
psycopg.ProgrammingError: only '%s', '%b', '%t' are allowed as
placeholders, got '%H'
}}}

I took a look with the debugger and I think either Django is incorrectly
passing the values to psycopg, or psycopg isn't splitting this SQL
statement correctly. I say this because when `_split_query` tries to split
the query, it ends up matching and consuming the first character of each
Value:
{{{
[
('ALTER TABLE "core_testmodel" ADD COLUMN "contains_heck" boolean
GENERATED ALWAYS AS (CASE WHEN UPPER("description"::text) LIKE UPPER(\'',
"<re.Match object; span=(140, 142), match=b'%H'>"),
("ECK", '<re.Match object; span=(145, 147), match=b"%\'">'),
(") THEN true ELSE false END) STORED", "None"),
]
}}}

Switching to `models.When(description__icontains=models.Value("HECK"),
...) yields a similar error except it's complaining about `%'` instead of
`%H`.

If the `contains_heck` field is created at the same time as the rest of
the model, everything works in either the `description__icontains="HECK"`
or `description__icontains=models.Value("HECK")` configuration.


**Versions:**
* Postgres 14.9
* Python 3.11.2
* Django 5.0.3
* psycopg[binary] 3.1.18
--
Ticket URL: <https://code.djangoproject.com/ticket/35336>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35336: Adding GeneratedField fails with ProgrammingError when using When on
CharField
-------------------------------------+-------------------------------------

Reporter: Adrian Garcia | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: postgres, | Triage Stage: Accepted
generatedfield, contains |
Has patch: 0 | Needs documentation: 0

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

Changes (by Simon Charette):

* component: Migrations => Database layer (models, ORM)
* keywords: postgres, generatedfield, _split_query => postgres,
generatedfield, contains
* owner: nobody => Simon Charette
* severity: Normal => Release blocker
* stage: Unreviewed => Accepted
* status: new => assigned

Comment:

This can be reproduced on `psycopg2` and `psycopg>=3` and is reminiscent
of the series of issues we had to fix with `Constraint`'s usage of
`__contains` lookup which require proper `%` escaping (#30408, #34553,
#32369)

{{{#!diff
diff --git a/tests/schema/tests.py b/tests/schema/tests.py
index f8e314d270..3b2d7bf03c 100644
--- a/tests/schema/tests.py
+++ b/tests/schema/tests.py
@@ -886,6 +886,27 @@ class Meta:
with connection.schema_editor() as editor:
editor.create_model(GeneratedFieldOutputFieldModel)

+ @isolate_apps("schema")
+ @skipUnlessDBFeature("supports_stored_generated_columns")
+ def test_add_generated_field_contains(self):
+ class GeneratedFieldContainsModel(Model):
+ text = TextField()
+
+ class Meta:
+ app_label = "schema"
+
+ field = GeneratedField(
+ expression=Q(text__icontains="FOO"),
+ db_persist=True,
+ output_field=BooleanField(),
+ )
+ field.contribute_to_class(GeneratedFieldContainsModel,
"some_field")
+
+ with connection.schema_editor() as editor:
+ editor.create_model(GeneratedFieldContainsModel)
+ editor.add_field(GeneratedFieldContainsModel, field)
+
+
@isolate_apps("schema")
def test_add_auto_field(self):
class AddAutoFieldModel(Model):
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35336#comment:1>

[Django]

#35336: Adding GeneratedField fails with ProgrammingError when using When on
CharField
-------------------------------------+-------------------------------------
Reporter: Adrian Garcia | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: postgres, | Triage Stage: Accepted
generatedfield, contains |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Adrian Garcia):

Thanks for looking into this, I'm glad to know that it's a bug and not me
doing something incorrectly. I'll use `iregex` as a workaround until
5.0.4.

Out of curiosity, why does this issue only present itself when adding a
field? Does Django handle SQL generation differently for table creation vs
field addition?
--
Ticket URL: <https://code.djangoproject.com/ticket/35336#comment:2>

[Django]

#35336: Adding GeneratedField fails with ProgrammingError when using When on
CharField
-------------------------------------+-------------------------------------
Reporter: Adrian Garcia | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: postgres, | Triage Stage: Accepted
generatedfield, contains |

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* has_patch: 0 => 1
* needs_better_patch: 0 => 1

Comment:

> Out of curiosity, why does this issue only present itself when adding a
field? Does Django handle SQL generation differently for table creation vs
field addition?

I'm still laying down the details
[https://github.com/django/django/pull/18027 in this MR] but the short
answer is yes.

The DBAPI `Cursor.execute` method can be used in two ways when dealing
with parameters. Either `(sql: str, params: [])` which will delegate
parametrization to the library or `(sql: str, params: None)` which won't
attempt to parametrize the SQL. This is an important difference and
requires special care when dealing with `%` literals in parameters as it's
the symbol use for parametrization placeholders by most libraries.

Well some backend simply don't support parametrization in some DDL
statements such as `GENERATED` columns declaration, in other words you
can't do

{{{#!python
cursor.execute(
"ALTER TABLE invoice ADD COLUMN discount_display text GENERATED ALWAYS
discount || %s;",
["%"]
)
}}}

So you must do

{{{#!python
cursor.execute(
"ALTER TABLE invoice ADD COLUMN discount_display text GENERATED ALWAYS
AS discount || '%';", None
)
}}}

Well in the case of `SchemaEditor.create_model` we liberally do one or the
other and in your case, because you don't have any other field with a
`db_default` assuming your use Postgres, it does the right thing.

In the case of `SchemaEditor.add_field` however we always do `params:
list`, which forces parametrization, and thus

{{{#!python
cursor.execute(
"ALTER TABLE testmodel ADD COLUMN contains_heck text GENERATED ALWAYS
AS UPPER(description) LIKE '%HECK%';", []
)
}}}

Crashes as there is an attempt to interpolate the two `%` while `params`
is empty.

The schema migration has historically use parametrizable DDL in the past
when possible but this has become increasingly complex as more and more
features were added (indexes, constraints, `db_default`, `GeneratedField`)
to a point where I don't think we can continue doing so but the tricky
part is that we can't make such a change in a bug fix release without
risking to introduce further problems. This is made even worst by the fact
that objects as `Index` and `Constraint` subclasses return already
interpolated SQL (their `as_sql` methods return `"CHECK UPPER(description)
LIKE '%HECK%'"` instead of `("CHECK UPPER(description) LIKE %s",
"%HECK%")` so even if we wanted to use it entirely to the backend until
the last minute by using `quote_value` ourselves we can't as by that point
we have a mixture of interpolated and non-interpolated SQL.
--
Ticket URL: <https://code.djangoproject.com/ticket/35336#comment:3>

[Django]

#35336: Adding GeneratedField fails with ProgrammingError when using When on
CharField
-------------------------------------+-------------------------------------
Reporter: Adrian Garcia | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: postgres, | Triage Stage: Accepted
generatedfield, contains |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):

* needs_better_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/35336#comment:4>

[Django]

#35336: Adding GeneratedField fails with ProgrammingError when using When on
CharField
-------------------------------------+-------------------------------------
Reporter: Adrian Garcia | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: postgres, | Triage Stage: Accepted
generatedfield, contains |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Adrian Garcia):

Thanks for the rundown, it really helped me understand what was going on
when I followed the execution with a debugger. The ORM is easily my
favorite part of Django and it's no surprise that it is complicated as
hell, are there plans for a more unified SQL translator in a future
release? I'd be interested in following the progress of that.

I tested the fix and it works great, might not even need to use the
workaround if 5.0.4 gets released in the next few weeks. Thanks for the
speedy patch.
--
Ticket URL: <https://code.djangoproject.com/ticket/35336#comment:5>

[Django]

#35336: Adding GeneratedField fails with ProgrammingError when using When on
CharField
-------------------------------------+-------------------------------------
Reporter: Adrian Garcia | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: postgres, | Triage Stage: Accepted
generatedfield, contains |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

> are there plans for a more unified SQL translator in a future release

I think the many regressions we've run into in the past releases in
escaping DDL (the subset of SQL for defining objects) in the schema editor
(the component behind migrations) warrants spending time having a less
convoluted approach.

I think that we should experiment with having backends denote whether or
not they support parametrized DDL (the ability to call
`Cursor.execute(ddl: str, params: tuple)` over having the schema editor do
the `%` formatting itself), make sure all parts of the scheme editor
return `(str, tuple)` and never simply `str`, and then adjust
`SchemaEditor.execute` to do `cursor.execute(ddl, params)` when
`connection.features.support_parametrized_dll` and do `cursor.execute(ddl
% map(self.quote_value, params), None)` otherwise.
--
Ticket URL: <https://code.djangoproject.com/ticket/35336#comment:6>

[Django]

#35336: Adding GeneratedField fails with ProgrammingError when using When on
CharField
-------------------------------------+-------------------------------------
Reporter: Adrian Garcia | Owner: Simon
| Charette

Type: Bug | Status: closed

Component: Database layer | Version: 5.0
(models, ORM) |

Severity: Release blocker | Resolution: fixed

Keywords: postgres, | Triage Stage: Accepted
generatedfield, contains |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by GitHub <noreply@…>):

* resolution: => fixed
* status: assigned => closed

Comment:

In [changeset:"888b9042b3598bab6557c62de82505eec9ea62ed" 888b9042]:
{{{#!CommitTicketReference repository=""
revision="888b9042b3598bab6557c62de82505eec9ea62ed"
Fixed #35336 -- Addressed crash when adding a GeneratedField with %
literals.

A longer term solution is likely to have a better separation of
parametrized
DDL altogether to handle checks, constraints, defaults, and generated
fields
but such a change would require a significant refactor that isn't suitable
for a backport.

Thanks Adrian Garcia for the report.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35336#comment:7>

[Django]

#35336: Adding GeneratedField fails with ProgrammingError when using When on
CharField
-------------------------------------+-------------------------------------
Reporter: Adrian Garcia | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: postgres, | Triage Stage: Accepted
generatedfield, contains |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Natalia <124304+nessita@…>):

In [changeset:"fead2dd52303505f30007df5e3c198b48b3ce9ae" fead2dd]:
{{{#!CommitTicketReference repository=""
revision="fead2dd52303505f30007df5e3c198b48b3ce9ae"
[5.0.x] Fixed #35336 -- Addressed crash when adding a GeneratedField with

% literals.

A longer term solution is likely to have a better separation of
parametrized
DDL altogether to handle checks, constraints, defaults, and generated
fields
but such a change would require a significant refactor that isn't suitable
for a backport.

Thanks Adrian Garcia for the report.

Backport of 888b9042b3598bab6557c62de82505eec9ea62ed from main
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35336#comment:8>

[Django]

#35336: Adding GeneratedField fails with ProgrammingError when using When on
CharField
-------------------------------------+-------------------------------------
Reporter: Adrian Garcia | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed

Keywords: postgres, | Triage Stage: Ready for
generatedfield, contains | checkin

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Natalia Bidart):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/35336#comment:9>

[Django]

#35336: Adding GeneratedField fails with ProgrammingError when using When on
CharField
-------------------------------------+-------------------------------------
Reporter: Adrian Garcia | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: postgres, | Triage Stage: Ready for
generatedfield, contains | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Natalia Bidart):

Adrian, the release is planned for tomorrow. Thank you for your help!
--
Ticket URL: <https://code.djangoproject.com/ticket/35336#comment:10>

[Django]

#35336: Adding GeneratedField fails with ProgrammingError when using When on
CharField
-------------------------------------+-------------------------------------
Reporter: Adrian Garcia | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: postgres, | Triage Stage: Ready for
generatedfield, contains | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Adrian Garcia):

Woohoo, thanks again everyone for all the effort. You managed to have
5.0.4 released before my PR was merged lol.
--
Ticket URL: <https://code.djangoproject.com/ticket/35336#comment:11>

[Django]

#35336: Adding GeneratedField fails with ProgrammingError when using When on
CharField
-------------------------------------+-------------------------------------
Reporter: Adrian Garcia | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: postgres, | Triage Stage: Ready for
generatedfield, contains | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by GitHub <noreply@…>):

In [changeset:"d859c0be9c6098f624429874b695017c97ecf125" d859c0b]:
{{{#!CommitTicketReference repository=""
revision="d859c0be9c6098f624429874b695017c97ecf125"
[5.0.x] Refs #35336 -- Fixed
SchemaTests.test_add_generated_field_contains() test on PostgreSQL.

Concat() in Django 5.0 is not immutable on PostgreSQL and cannot be used
in GeneratedField, see 6364b6ee1071381eb3a23ba6b821fc0d6f0fce75.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35336#comment:12>

[Django]

#35336: Adding GeneratedField fails with ProgrammingError when using When on
CharField
-------------------------------------+-------------------------------------
Reporter: Adrian Garcia | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: postgres, | Triage Stage: Ready for
generatedfield, contains | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Sarah Boyce <42296566+sarahboyce@…>):

In [changeset:"73b62a21265c4a417004d64d13a896469e2558f3" 73b62a21]:
{{{#!CommitTicketReference repository=""
revision="73b62a21265c4a417004d64d13a896469e2558f3"
Refs #35194 -- Adjusted a generated field test to work on Postgres 15.6+.

Postgres >= 12.18, 13.14, 14.11, 15.6, 16.2 changed the way the
immutability
of generated and default expressions is detected in
postgres/postgres@743ddaf.

The adjusted test semantic is presereved by switching from __icontains to
__contains as both make use of a `%` literal which requires proper
escaping.

Refs #35336.

Thanks bcail for the report.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35336#comment:13>

[Django]

#35336: Adding GeneratedField fails with ProgrammingError when using When on
CharField
-------------------------------------+-------------------------------------
Reporter: Adrian Garcia | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: postgres, | Triage Stage: Ready for
generatedfield, contains | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Sarah Boyce <42296566+sarahboyce@…>):

In [changeset:"5d95a1c35ef1375a7badcb217c36c5974d1e57ee" 5d95a1c]:
{{{#!CommitTicketReference repository=""
revision="5d95a1c35ef1375a7badcb217c36c5974d1e57ee"
[5.0.x] Refs #35194 -- Adjusted a generated field test to work on Postgres

15.6+.

Postgres >= 12.18, 13.14, 14.11, 15.6, 16.2 changed the way the
immutability
of generated and default expressions is detected in
postgres/postgres@743ddaf.

The adjusted test semantic is presereved by switching from __icontains to
__contains as both make use of a `%` literal which requires proper
escaping.

Refs #35336.

Thanks bcail for the report.

Backport of 73b62a21265c4a417004d64d13a896469e2558f3 from main.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35336#comment:14>

[Django]

#35336: Adding GeneratedField fails with ProgrammingError when using When on
CharField
-------------------------------------+-------------------------------------
Reporter: Adrian Garcia | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: postgres, | Triage Stage: Ready for
generatedfield, contains | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Sarah Boyce <42296566+sarahboyce@…>):

In [changeset:"f359990e4909db8722820849d61a6f5724338723" f359990e]:
{{{#!CommitTicketReference repository=""
revision="f359990e4909db8722820849d61a6f5724338723"
Fixed #35625 -- Fixed a crash when adding a field with db_default and
check constraint.

This is the exact same issue as refs #30408 but for creating a model with
a
constraint containing % escapes instead of column addition. All of these
issues
stem from a lack of SQL and parameters separation from the BaseConstraint
DDL
generating methods preventing them from being mixed with other parts of
the
schema alteration logic that do make use of parametrization on some
backends
(e.g. Postgres, MySQL for DEFAULT).

Prior to the addition of Field.db_default and GeneratedField in 5.0
parametrization of DDL was never exercised on model creation so this is
effectively a bug with db_default as the GeneratedField case was addressed
by
refs #35336.

Thanks Julien Chaumont for the report and Mariusz Felisiak for the review.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35336#comment:15>

[Django]

#35336: Adding GeneratedField fails with ProgrammingError when using When on
CharField
-------------------------------------+-------------------------------------
Reporter: Adrian Garcia | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: postgres, | Triage Stage: Ready for
generatedfield, contains | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Sarah Boyce <42296566+sarahboyce@…>):

In [changeset:"d8116bf7f56930a95926444c625ca786f2f534ce" d8116bf7]:
{{{#!CommitTicketReference repository=""
revision="d8116bf7f56930a95926444c625ca786f2f534ce"
[5.1.x] Fixed #35625 -- Fixed a crash when adding a field with db_default

and check constraint.

This is the exact same issue as refs #30408 but for creating a model with
a
constraint containing % escapes instead of column addition. All of these
issues
stem from a lack of SQL and parameters separation from the BaseConstraint
DDL
generating methods preventing them from being mixed with other parts of
the
schema alteration logic that do make use of parametrization on some
backends
(e.g. Postgres, MySQL for DEFAULT).

Prior to the addition of Field.db_default and GeneratedField in 5.0
parametrization of DDL was never exercised on model creation so this is
effectively a bug with db_default as the GeneratedField case was addressed
by
refs #35336.

Thanks Julien Chaumont for the report and Mariusz Felisiak for the review.

Backport of f359990e4909db8722820849d61a6f5724338723 from main.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35336#comment:16>

[Django]

#35336: Adding GeneratedField fails with ProgrammingError when using When on
CharField
-------------------------------------+-------------------------------------
Reporter: Adrian Garcia | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: postgres, | Triage Stage: Ready for
generatedfield, contains | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Sarah Boyce <42296566+sarahboyce@…>):

In [changeset:"41d8ef18ac2d983bea5ef919615687308ffe41c1" 41d8ef18]:
{{{#!CommitTicketReference repository=""
revision="41d8ef18ac2d983bea5ef919615687308ffe41c1"
[5.0.x] Fixed #35625 -- Fixed a crash when adding a field with db_default

and check constraint.

This is the exact same issue as refs #30408 but for creating a model with
a
constraint containing % escapes instead of column addition. All of these
issues
stem from a lack of SQL and parameters separation from the BaseConstraint
DDL
generating methods preventing them from being mixed with other parts of
the
schema alteration logic that do make use of parametrization on some
backends
(e.g. Postgres, MySQL for DEFAULT).

Prior to the addition of Field.db_default and GeneratedField in 5.0
parametrization of DDL was never exercised on model creation so this is
effectively a bug with db_default as the GeneratedField case was addressed
by
refs #35336.

Thanks Julien Chaumont for the report and Mariusz Felisiak for the review.

Backport of f359990e4909db8722820849d61a6f5724338723 from main.
}}}
--

Ticket URL: <https://code.djangoproject.com/ticket/35336#comment:17>