id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
37248	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:

{{{#!python
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())
}}}

{{{#!python
Event().full_clean()
}}}

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

{{{#!sql
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:

{{{#!python
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:

{{{#!python
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`):

{{{#!python
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`.
"	Bug	new	Database layer (models, ORM)	5.0	Normal		db_default, DatabaseDefault, validate_unique, full_clean, uniqueness, performance		Unreviewed	0	0	0	0	0	0