#35969: Changing output_field for GeneratedField leads to ProgrammingError with
Postgres 16.5+
-----------------------------+-----------------------------------------
Reporter: Ryan Schave | Type: Uncategorized
Status: new | Component: Uncategorized
Version: 5.1 | Severity: Normal
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-----------------------------+-----------------------------------------
Consider the following model and assume the initial migration has been
applied:
{{{#!python
class Order(models.Model):
order_no = models.IntegerField()
item_no = models.CharField(max_length=25)
qty = models.IntegerField()
cost = models.DecimalField(max_digits=10, decimal_places=2)
total_cost = models.GeneratedField(
expression=F("cost") * F("qty"),
output_field=models.BigIntegerField(),
db_persist=True,
)
}}}
During a code review we determined the output field should be a Decimal
field and the field was modified as follows:
{{{#!python
total_cost = models.GeneratedField(
expression=F("cost") * F("qty"),
output_field=models.DecimalField(decimal_places=2, max_digits=16),
db_persist=True,
)
}}}
And a new migration was generated:
{{{#!python
migrations.AlterField(
model_name='order',
name='total_cost',
field=models.GeneratedField(db_persist=True,
expression=django.db.models.expressions.CombinedExpression(models.F('cost'),
'*', models.F('qty')), output_field=models.DecimalField(decimal_places=2,
max_digits=16)),
),
}}}
In Postgres 16.4 and earlier, this migration is applied without error.
(I'm aware that the value of the total_cost field is not recomputed for
existing records when this migration is applied.).
Starting with Postgres 16.5 and up, this migration fails with the
following error:
{{{
psycopg2.errors.InvalidColumnDefinition: cannot specify USING when
altering type of generated column
DETAIL: Column "total_cost" is a generated column.
...
django.db.utils.ProgrammingError: cannot specify USING when altering type
of generated column
}}}
This appears to be a result of the following change in Postgres 16.5
([https://www.postgresql.org/docs/release/16.5/ release notes]):
Disallow a `USING` clause when altering the type of a generated column
(Peter Eisentraut) [§](https://postgr.es/c/5867ee005)
A generated column already has an expression specifying the column
contents, so including `USING` doesn't make sense.
The Django
[https://docs.djangoproject.com/en/5.1/ref/models/fields/#generatedfield
documentation] for GeneratedField makes it clear that
There are many database-specific restrictions on generated fields that
Django doesn’t validate and the database may raise an error
For this reason, I almost didn't open a ticket. However, there is logic
in db/backends/base/schema.py that checks if the expression of a
GeneratedField changed. Consider this migration (which changes the
expression from multiplication to addition):
{{{#!python
migrations.AlterField(
model_name='order',
name='total_cost',
field=models.GeneratedField(db_persist=True,
expression=django.db.models.expressions.CombinedExpression(models.F('cost'),
'+', models.F('qty')), output_field=models.BigIntegerField()),
),
}}}
Attempting to apply this migration will raise the following error (even in
Postgres 16.4):
{{{
ValueError: Modifying GeneratedFields is not supported - the field
sales.Order.total_cost must be removed and re-added with the new
definition.
}}}
This error is more helpful. It explains the problem better and even
suggests a workaround.
Should we throw a similar error if the output_field of a GeneratedField is
changed? Or add a tip to the documentation?
The above was tested with:
Django version 5.1.3
psycopg2 version 2.9.10
Postgres versions: 16.4, 16.5, 16.6, 17.0, and 17.2
--
Ticket URL: <https://code.djangoproject.com/ticket/35969>
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 visit
https://groups.google.com/d/msgid/django-updates/010701939003d291-5ca80d4a-6011-4f24-b6b9-16b6db20c37f-000000%40eu-central-1.amazonses.com.
