Unique validation queries on unresolved db_default expressions

[karansuthar]

On an unsaved instance whose unique field uses an expression db_default,
full_clean() emits a uniqueness query containing the unresolved expression.
Both unique-validation sites are affected: Model._perform_unique_checks() and
the fields path of UniqueConstraint.validate() — neither handles
DatabaseDefault.

The query cannot be meaningful: the value is produced by the database during the
INSERT, so the lookup tests a different value from the one that will be stored.
Validation passes either way, so nothing surfaces unless inserts are profiled.

When the default is VOLATILE it is also pathologically slow, because the
expression is re-evaluated for every row and the database cannot use the index.

The rest of validation already treats unresolved defaults as "not knowable
yet": Model.clean_fields() skips them (#35223), and the constraint expression
map resolves them (#35638). Unique validation is the remaining gap.

1. The correctness problem (any backend)

validate_unique() is supposed to predict the INSERT: query for the value that
is about to be stored, and report a collision before it happens. That requires
querying with the same value the INSERT will use.

When a unique field's value is an unresolved db_default, no such value exists
yet -- the instance attribute is a DatabaseDefault placeholder meaning "the
database will compute this during the INSERT". _perform_unique_checks() puts
that placeholder into the lookup anyway, so the default expression is compiled
into the WHERE clause and evaluated at query time:

from django.db import models
from django.db.models.functions import Now

class Event(models.Model):
    created = models.DateTimeField(unique=True, editable=False, db_default=Now())

Event().full_clean()

emits (SQL as compiled for PostgreSQL; other backends render Now()
differently but emit the same lookup):

SELECT 1 AS "a" FROM "app_event" WHERE "app_event"."created" = (STATEMENT_TIMESTAMP()) LIMIT 1

The expression is now evaluated twice, at two different moments:

SELECT ... WHERE created = STATEMENT_TIMESTAMP()  -- evaluated during full_clean()
INSERT ... (created defaults to STATEMENT_TIMESTAMP())  -- evaluated later, during save()

The SELECT probes for whatever the expression returns now; the INSERT stores
whatever it returns later. The check tests a value that will never be
stored, so it cannot predict whether the insert will violate uniqueness -- no
query could, since the value does not exist until the INSERT runs.

A control model with the same db_default but without unique=True emits no
query at all, confirming unique=True is what triggers it.

The constraint path reproduces independently. With:

class Meeting(models.Model):
    created = models.DateTimeField(editable=False, db_default=Now())

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=["created"], name="uniq_created"),
        ]

calling Meeting().validate_constraints() emits the same
WHERE "created" = (STATEMENT_TIMESTAMP()) lookup, because
UniqueConstraint.validate() reads the attribute with
getattr(instance, field.attname) and, like _perform_unique_checks(), has
None and empty-string branches but no DatabaseDefault branch. In both cases
validation passes, so the only evidence is the captured SQL.

2. The performance problem (VOLATILE defaults)

Setup, on PostgreSQL 18.4 with Django 6.0.7:

from django.db import models
from django.db.models import Func

class UUIDv7(Func):
    function = "uuidv7"
    template = "%(function)s()"
    output_field = models.UUIDField()

class Thing(models.Model):
    name = models.CharField(max_length=50)
    token = models.UUIDField(unique=True, editable=False, db_default=UUIDv7())

The SQL below was captured from full_clean() with CaptureQueriesContext and
then passed verbatim to EXPLAIN (ANALYZE, BUFFERS), so the plans are for the
statements the ORM actually produced. Both shapes run against the same table and
the same unique index; the only variable is whether token was left to the
database or assigned a concrete value. Parallel query is disabled
(SET max_parallel_workers_per_gather = 0) so the two table sizes are directly
comparable.

A1. Unresolved db_default, 100,000 rows

SELECT 1 AS "a" FROM "app_thing" WHERE "app_thing"."token" = (uuidv7()) LIMIT 1

 Limit  (cost=0.00..2236.00 rows=1 width=4) (actual time=34.331..34.331 rows=0.00 loops=1)
   Buffers: shared hit=736
   ->  Seq Scan on app_thing  (cost=0.00..2236.00 rows=1 width=4) (actual time=34.331..34.331 rows=0.00 loops=1)
         Filter: (token = uuidv7())
         Rows Removed by Filter: 100000
         Buffers: shared hit=736
 Execution Time: 34.335 ms

A2. Unresolved db_default, 1,000,000 rows

SELECT 1 AS "a" FROM "app_thing" WHERE "app_thing"."token" = (uuidv7()) LIMIT 1

 Limit  (cost=0.00..22353.00 rows=1 width=4) (actual time=337.760..337.760 rows=0.00 loops=1)
   Buffers: shared hit=7267 read=86
   ->  Seq Scan on app_thing  (cost=0.00..22353.00 rows=1 width=4) (actual time=337.759..337.760 rows=0.00 loops=1)
         Filter: (token = uuidv7())
         Rows Removed by Filter: 1000000
         Buffers: shared hit=7267 read=86
 Execution Time: 337.767 ms

B. Concrete value, same table, same index, 1,000,000 rows

SELECT 1 AS "a" FROM "app_thing" WHERE "app_thing"."token" = 'ed127aaa9aa74bbeb6901ec19d28b97e'::uuid LIMIT 1

 Limit  (cost=0.42..8.44 rows=1 width=4) (actual time=0.009..0.009 rows=0.00 loops=1)
   Buffers: shared hit=3
   ->  Index Only Scan using app_thing_token_key on app_thing  (cost=0.42..8.44 rows=1 width=4) (actual time=0.008..0.009 rows=0.00 loops=1)
         Index Cond: (token = 'ed127aaa-9aa7-4bbe-b690-1ec19d28b97e'::uuid)
         Heap Fetches: 0
         Buffers: shared hit=3
 Execution Time: 0.015 ms

Points from the plans:

• Filter: (token = uuidv7()) with Rows Removed by Filter equal to the full row count confirms per-row re-evaluation. The unique index exists and is simply unusable; plan B shows it working on the same table.
• Cost scales linearly with table size: 10x the rows gives 10x the buffers (736 -> 7,267 hit + 86 read = 7,353) and 9.8x the time. Plan B stays at 3 buffers regardless.
• rows=0.00 is the normal case, not an artefact of the test. A volatile default will not match an existing row, so the scan never terminates early and the LIMIT 1 never helps. Every insert pays for a full scan.

3. Proposed fix

Skip unresolved defaults in both sites, mirroring what clean_fields() already
does.

In Model._perform_unique_checks() (DatabaseDefault is already imported in
django/db/models/base.py):

lookup_value = getattr(self, f.attname)
if isinstance(lookup_value, DatabaseDefault):
    # Generated by the database on INSERT; not knowable yet.
    continue

The continue reuses the existing machinery: when any field of a check is
skipped, len(unique_check) != len(lookup_kwargs) already abandons the whole
check rather than querying on the remaining fields. That is the right outcome
here too -- for a multi-field check such as `unique_together = ("account",
"token"), an unresolved db_default` member makes the whole tuple unknowable
until INSERT, and a partial lookup on the other fields would be wrong.

In UniqueConstraint.validate() (fields path), the analogous check should
return, matching its existing None/empty-string branch: one unknowable
member makes the whole constraint unknowable.

Behaviour after the change:

• A concrete value is unaffected and still validated, using the index.
• Uniqueness is still enforced by the database constraint, which raises IntegrityError rather than ValidationError. This matches the existing None branches in both sites, which likewise defer to the database.

4. Affected versions

Present since db_default was introduced in Django 5.0: neither site has ever
had a DatabaseDefault branch. Reproduced on 6.0.7; the code is unchanged on
main.