[Django] #36088: Avoided unnecessary DEFAULT usage on bulk_create.

[Django]

#36088: Avoided unnecessary DEFAULT usage on bulk_create.
-------------------------------------+-------------------------------------
Reporter: Simon | Owner: Simon Charette
Charette |
Type: | Status: assigned
Cleanup/optimization |
Component: Database | Version: dev
layer (models, ORM) | Keywords: unnest insert
Severity: Normal | db_default default bulk_create
Triage Stage: | Has patch: 1
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
When `bulk_create` is used for models composed of fields with `db_default`
the resulting `INSERT` statement use `DEFAULT` placeholders to signify
that a field must use the database defined default.

For example, the following code

{{{#!python
class Author(models):
name = models.CharField(max_length=100)
created_at = models.DateTimeField(db_default=Now())

Author.objects.bulk_create([Author(name="foo"), Author(name="bar")])
}}}

Will result in the following SQL

{{{#!sql
INSERT INTO author (name, created_at) VALUES (%s, DEFAULT), (%s, DEFAULT)
}}}

But in cases where no `db_default` is provided for all bulk-created
instances there is no point in specifying `DEFAULT` for each row as that's
what the database will do if the column is not specified at all. In other
words the above SQL is equivalent to

{{{#!sql
INSERT INTO author (name) VALUES (%s), (%s)
}}}

but the latter query simplification provide benefits:

Firstly, it would allow the `UNNEST` optimization introduced in #35936
(a16eedcf9c69d8a11d94cac1811018c5b996d491) to be enabled for models that
define `db_default` fields. Alas since `DEFAULT` is an expression and the
optimization must be disabled in their presence no models making use of
`db_default` can take advantage of it.

In other words, on Postgres, the SQL could be

{{{#!sql
INSERT INTO author (name) SELECT * FROM unnest([%s, %s])
}}}

which has [https://forum.djangoproject.com/t/speeding-up-postgres-bulk-
create-by-using-unnest/36508 demonstrated benefits].

Secondly, pruning the field would avoid having to provide the `db_default`
expression for all model instances on backends that don't support
`DEFAULT` in bulk-inserts such as Oracle. In other words the following SQL
would be avoided

{{{#!sql
INSERT INTO author (name, created_at) VALUES (%s, NOW()), (%s, NOW())
}}}

Lastly, it just make the query smaller as no `DEFAULT` has to be provided
for each row for each columns with a defined `db_default` which surely
reduce the parsing time on the backend.
--
Ticket URL: <https://code.djangoproject.com/ticket/36088>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.