GeneratedField with PostgreSQL

Django Internals ORM
1

When defining a GeneratedField with PostgreSQL, it is not uncommon to encounter the following error: django.db.utils.ProgrammingError: generation expression is not immutable.

This error is mentioned in the docs about PostgreSQL-specific restriction:

…PostgreSQL requires functions and operators referenced in a generated column to be marked as IMMUTABLE.

This error occurs with almost all Func() expressions (with a specified database function). For example:

age_func = Func("date_of_birth", function="age")
age_years = Func(Value("year"), age_func, function="date_part", output_field=...)

This will throw the above error if age_years is used in GeneratedField expression.

Another reproducible example is with Concat() function. If used as is, Concat will also raise the same error, but there is a workaround for it (this custom implementation may not be necessary in Django 5.1).

If I was defining these functions in sql, I suppose it wouldn’t be a problem marking them as IMMUTABLE. My question is, working with only Django, how do we implement IMMUTABLE functions/expressions?

I assume there is a way of passing extra arguments/options that will be used to specify whether the database function will be IMMUTABLE, STABLE, VOLATILE, etc., but I have no clue how to specify this. Perhaps we could subclass Func and override some sql-generating method, but again, I don’t know which one is responsible.

2

If I was defining these functions in sql, I suppose it wouldn’t be a problem marking them as IMMUTABLE. My question is, working with only Django, how do we implement IMMUTABLE functions/expressions?

I think that’s a misundestanding of the problem. You couldn’t safely define a composite of non-IMMUTABLE functions as IMMUTABLE in Postgres as you would run into issues with queries returning the wrong data.

In your example you are using the Postgres age and date_part Postgres functions. The age function is not IMMUTABLE with a single argument as it Subtract argument from current_date (at midnight) which means it will change between queries.

In other words, stored generated fields only allow to store data inferred from other columns that never change under any circumstances; you can’t store the age inferred from the date_of_birth on disk as it’s in perpetual change (based on current_date which is ticking).

This has little to do with Django.

1 Like