Skip to content

Commit 8e4e325

Browse files
zzzeekzzzeek
committed
add eager_defaults="auto" for inserts
Added a new default value for the :paramref:`.Mapper.eager_defaults` parameter "auto", which will automatically fetch table default values during a unit of work flush, if the dialect supports RETURNING for the INSERT being run, as well as :ref:`insertmanyvalues <engine_insertmanyvalues>` available. Eager fetches for server-side UPDATE defaults, which are very uncommon, continue to only take place if :paramref:`.Mapper.eager_defaults` is set to ``True``, as there is no batch-RETURNING form for UPDATE statements. Fixes: #8889 Change-Id: I84b91092a37c4cd216e060513acde3eb0298abe9
1 parent e0eea37 commit 8e4e325

13 files changed

Lines changed: 606 additions & 166 deletions

File tree

‎doc/build/changelog/unreleased_20/8889.rst‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,13 @@
1+
.. change::
2+
:tags: orm, feature
3+
:tickets: 8889
4+
5+
Added a new default value for the :paramref:`.Mapper.eager_defaults`
6+
parameter "auto", which will automatically fetch table default values
7+
during a unit of work flush, if the dialect supports RETURNING for the
8+
INSERT being run, as well as
9+
:ref:`insertmanyvalues <engine_insertmanyvalues>` available. Eager fetches
10+
for server-side UPDATE defaults, which are very uncommon, continue to only
11+
take place if :paramref:`.Mapper.eager_defaults` is set to ``True``, as
12+
there is no batch-RETURNING form for UPDATE statements.
13+

‎doc/build/changelog/whatsnew_20.rst‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1001,6 +1001,11 @@ get all drivers to this state:
10011001
possible, usually with VALUES() - :ticket:`6047`
10021002
* Emit a warning when RETURNING w/ executemany is used for non-supporting
10031003
backend (currently no RETURNING backend has this limitation) - :ticket:`7907`
1004+
* The ORM :paramref:`_orm.Mapper.eager_defaults` parameter now defaults to a
1005+
a new setting ``"auto"``, which will enable "eager defaults" automatically
1006+
for INSERT statements, when the backend in use supports RETURNING with
1007+
"insertmanyvalues". See :ref:`orm_server_defaults` for documentation.
1008+
10041009

10051010
.. seealso::
10061011

‎doc/build/orm/persistence_techniques.rst‎

Lines changed: 157 additions & 85 deletions
Large diffs are not rendered by default.

‎lib/sqlalchemy/dialects/mssql/base.py‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -232,6 +232,7 @@ class TestTable(Base):
232232
)
233233
name = Column(String)
234234
235+
.. _mssql_insert_behavior:
235236
236237
INSERT behavior
237238
^^^^^^^^^^^^^^^^

‎lib/sqlalchemy/orm/mapper.py‎

Lines changed: 43 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -230,7 +230,7 @@ def __init__(
230230
passive_updates: bool = True,
231231
passive_deletes: bool = False,
232232
confirm_deleted_rows: bool = True,
233-
eager_defaults: bool = False,
233+
eager_defaults: Literal[True, False, "auto"] = "auto",
234234
legacy_is_orphan: bool = False,
235235
_compiled_cache_size: int = 100,
236236
):
@@ -336,14 +336,30 @@ class User(Base):
336336
value of server-generated default values after an INSERT or UPDATE,
337337
rather than leaving them as expired to be fetched on next access.
338338
This can be used for event schemes where the server-generated values
339-
are needed immediately before the flush completes. By default,
340-
this scheme will emit an individual ``SELECT`` statement per row
341-
inserted or updated, which note can add significant performance
342-
overhead. However, if the
343-
target database supports :term:`RETURNING`, the default values will
344-
be returned inline with the INSERT or UPDATE statement, which can
345-
greatly enhance performance for an application that needs frequent
346-
access to just-generated server defaults.
339+
are needed immediately before the flush completes.
340+
341+
The fetch of values occurs either by using ``RETURNING`` inline
342+
with the ``INSERT`` or ``UPDATE`` statement, or by adding an
343+
additional ``SELECT`` statement subsequent to the ``INSERT`` or
344+
``UPDATE``, if the backend does not support ``RETURNING``.
345+
346+
The use of ``RETURNING`` is extremely performant in particular for
347+
``INSERT`` statements where SQLAlchemy can take advantage of
348+
:ref:`insertmanyvalues <engine_insertmanyvalues>`, whereas the use of
349+
an additional ``SELECT`` is relatively poor performing, adding
350+
additional SQL round trips which would be unnecessary if these new
351+
attributes are not to be accessed in any case.
352+
353+
For this reason, :paramref:`.Mapper.eager_defaults` defaults to the
354+
string value ``"auto"``, which indicates that server defaults for
355+
INSERT should be fetched using ``RETURNING`` if the backing database
356+
supports it and if the dialect in use supports "insertmanyreturning"
357+
for an INSERT statement. If the backing database does not support
358+
``RETURNING`` or "insertmanyreturning" is not available, server
359+
defaults will not be fetched.
360+
361+
.. versionchanged:: 2.0.0b5 added the "auto" option for
362+
:paramref:`.Mapper.eager_defaults`
347363
348364
.. seealso::
349365
@@ -352,6 +368,12 @@ class User(Base):
352368
.. versionchanged:: 0.9.0 The ``eager_defaults`` option can now
353369
make use of :term:`RETURNING` for backends which support it.
354370
371+
.. versionchanged:: 2.0.0 RETURNING now works with multiple rows
372+
INSERTed at once using the
373+
:ref:`insertmanyvalues <engine_insertmanyvalues>` feature, which
374+
among other things allows the :paramref:`.Mapper.eager_defaults`
375+
feature to be very performant on supporting backends.
376+
355377
:param exclude_properties: A list or set of string column names to
356378
be excluded from mapping.
357379
@@ -818,6 +840,18 @@ def generate_version(version):
818840
self._log("constructed")
819841
self._expire_memoizations()
820842

843+
def _prefer_eager_defaults(self, dialect, table):
844+
if self.eager_defaults == "auto":
845+
if not table.implicit_returning:
846+
return False
847+
848+
return (
849+
table in self._server_default_col_keys
850+
and dialect.insert_executemany_returning
851+
)
852+
else:
853+
return self.eager_defaults
854+
821855
def _gen_cache_key(self, anon_map, bindparams):
822856
return (self,)
823857

‎lib/sqlalchemy/orm/persistence.py‎

Lines changed: 27 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -381,7 +381,9 @@ def _collect_insert_commands(
381381
# compare to pk_keys_by_table
382382
has_all_pks = mapper._pk_keys_by_table[table].issubset(params)
383383

384-
if mapper.base_mapper.eager_defaults:
384+
if mapper.base_mapper._prefer_eager_defaults(
385+
connection.dialect, table
386+
):
385387
has_all_defaults = mapper._server_default_col_keys[
386388
table
387389
].issubset(params)
@@ -491,7 +493,7 @@ def _collect_update_commands(
491493
):
492494
params[col.key] = value
493495

494-
if mapper.base_mapper.eager_defaults:
496+
if mapper.base_mapper.eager_defaults is True:
495497
has_all_defaults = (
496498
mapper._server_onupdate_default_col_keys[table]
497499
).issubset(params)
@@ -787,7 +789,12 @@ def update_stmt(existing_stmt=None):
787789
if (
788790
bookkeeping
789791
and not has_all_defaults
790-
and mapper.base_mapper.eager_defaults
792+
and mapper.base_mapper.eager_defaults is True
793+
# change as of #8889 - if RETURNING is not going to be used anyway,
794+
# (applies to MySQL, MariaDB which lack UPDATE RETURNING) ensure
795+
# we can do an executemany UPDATE which is more efficient
796+
and table.implicit_returning
797+
and connection.dialect.update_returning
791798
):
792799
statement = statement.return_defaults(
793800
*mapper._server_onupdate_default_cols[table]
@@ -808,7 +815,11 @@ def update_stmt(existing_stmt=None):
808815
assert_singlerow
809816
and connection.dialect.supports_sane_multi_rowcount
810817
)
811-
allow_multirow = has_all_defaults and not needs_version_id
818+
819+
# change as of #8889 - if RETURNING is not going to be used anyway,
820+
# (applies to MySQL, MariaDB which lack UPDATE RETURNING) ensure
821+
# we can do an executemany UPDATE which is more efficient
822+
allow_executemany = not return_defaults and not needs_version_id
812823

813824
if hasvalue:
814825
for (
@@ -842,7 +853,7 @@ def update_stmt(existing_stmt=None):
842853
rows += c.rowcount
843854
check_rowcount = assert_singlerow
844855
else:
845-
if not allow_multirow:
856+
if not allow_executemany:
846857
check_rowcount = assert_singlerow
847858
for (
848859
state,
@@ -991,7 +1002,9 @@ def _emit_insert_statements(
9911002
not bookkeeping
9921003
or (
9931004
has_all_defaults
994-
or not base_mapper.eager_defaults
1005+
or not base_mapper._prefer_eager_defaults(
1006+
connection.dialect, table
1007+
)
9951008
or not table.implicit_returning
9961009
or not connection.dialect.insert_returning
9971010
)
@@ -1067,7 +1080,9 @@ def _emit_insert_statements(
10671080
else:
10681081
do_executemany = False
10691082

1070-
if not has_all_defaults and base_mapper.eager_defaults:
1083+
if not has_all_defaults and base_mapper._prefer_eager_defaults(
1084+
connection.dialect, table
1085+
):
10711086
statement = statement.return_defaults(
10721087
*mapper._server_default_cols[table]
10731088
)
@@ -1282,9 +1297,9 @@ def update_stmt():
12821297
assert_singlerow
12831298
and connection.dialect.supports_sane_multi_rowcount
12841299
)
1285-
allow_multirow = not needs_version_id or assert_multirow
1300+
allow_executemany = not needs_version_id or assert_multirow
12861301

1287-
if not allow_multirow:
1302+
if not allow_executemany:
12881303
check_rowcount = assert_singlerow
12891304
for state, state_dict, mapper_rec, connection, params in records:
12901305

@@ -1475,7 +1490,9 @@ def _finalize_insert_update_commands(base_mapper, uowtransaction, states):
14751490
# it isn't expired.
14761491
toload_now = []
14771492

1478-
if base_mapper.eager_defaults:
1493+
# this is specifically to emit a second SELECT for eager_defaults,
1494+
# so only if it's set to True, not "auto"
1495+
if base_mapper.eager_defaults is True:
14791496
toload_now.extend(
14801497
state._unloaded_non_object.intersection(
14811498
mapper._server_default_plus_onupdate_propkeys

‎lib/sqlalchemy/testing/__init__.py‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -56,6 +56,7 @@
5656
from .config import skip_test
5757
from .config import Variation
5858
from .config import variation
59+
from .config import variation_fixture
5960
from .exclusions import _is_excluded
6061
from .exclusions import _server_version
6162
from .exclusions import against as _against

‎lib/sqlalchemy/testing/config.py‎

Lines changed: 38 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -157,9 +157,33 @@ def __nonzero__(self):
157157
def __str__(self):
158158
return f"{self._argname}={self._name!r}"
159159

160+
def __repr__(self):
161+
return str(self)
162+
160163
def fail(self) -> NoReturn:
161164
fail(f"Unknown {self}")
162165

166+
@classmethod
167+
def idfn(cls, variation):
168+
return variation.name
169+
170+
@classmethod
171+
def generate_cases(cls, argname, cases):
172+
case_names = [
173+
argname if c is True else "not_" + argname if c is False else c
174+
for c in cases
175+
]
176+
177+
typ = type(
178+
argname,
179+
(Variation,),
180+
{
181+
"__slots__": tuple(case_names),
182+
},
183+
)
184+
185+
return [typ(casename, argname, case_names) for casename in case_names]
186+
163187

164188
def variation(argname, cases):
165189
"""a helper around testing.combinations that provides a single namespace
@@ -203,33 +227,32 @@ class Thing(decl_base):
203227
else (entry, None)
204228
for entry in cases
205229
]
206-
case_names = [
207-
argname if c is True else "not_" + argname if c is False else c
208-
for c, l in cases_plus_limitations
209-
]
210230

211-
typ = type(
212-
argname,
213-
(Variation,),
214-
{
215-
"__slots__": tuple(case_names),
216-
},
231+
variations = Variation.generate_cases(
232+
argname, [c for c, l in cases_plus_limitations]
217233
)
218-
219234
return combinations(
220235
*[
221-
(casename, typ(casename, argname, case_names), limitation)
236+
(variation._name, variation, limitation)
222237
if limitation is not None
223-
else (casename, typ(casename, argname, case_names))
224-
for casename, (case, limitation) in zip(
225-
case_names, cases_plus_limitations
238+
else (variation._name, variation)
239+
for variation, (case, limitation) in zip(
240+
variations, cases_plus_limitations
226241
)
227242
],
228243
id_="ia",
229244
argnames=argname,
230245
)
231246

232247

248+
def variation_fixture(argname, cases, scope="function"):
249+
return fixture(
250+
params=Variation.generate_cases(argname, cases),
251+
ids=Variation.idfn,
252+
scope=scope,
253+
)
254+
255+
233256
def fixture(*arg: Any, **kw: Any) -> Any:
234257
return _fixture_functions.fixture(*arg, **kw)
235258

‎test/orm/inheritance/test_basic.py‎

Lines changed: 49 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -3074,17 +3074,27 @@ def __eq__(self, other):
30743074
eq_(s1test.comp, Comp("ham", "cheese"))
30753075
eq_(s2test.comp, Comp("bacon", "eggs"))
30763076

3077-
def test_load_expired_on_pending(self):
3077+
@testing.variation("eager_defaults", [True, False])
3078+
def test_load_expired_on_pending(self, eager_defaults):
30783079
base, sub = self.tables.base, self.tables.sub
30793080

3081+
expected_eager_defaults = bool(eager_defaults)
3082+
expect_returning = (
3083+
expected_eager_defaults and testing.db.dialect.insert_returning
3084+
)
3085+
30803086
class Base(fixtures.BasicEntity):
30813087
pass
30823088

30833089
class Sub(Base):
30843090
pass
30853091

30863092
self.mapper_registry.map_imperatively(
3087-
Base, base, polymorphic_on=base.c.type, polymorphic_identity="base"
3093+
Base,
3094+
base,
3095+
polymorphic_on=base.c.type,
3096+
polymorphic_identity="base",
3097+
eager_defaults=bool(eager_defaults),
30883098
)
30893099
self.mapper_registry.map_imperatively(
30903100
Sub, sub, inherits=Base, polymorphic_identity="sub"
@@ -3095,13 +3105,30 @@ class Sub(Base):
30953105
self.assert_sql_execution(
30963106
testing.db,
30973107
sess.flush,
3098-
CompiledSQL(
3099-
"INSERT INTO base (data, type) VALUES (:data, :type)",
3100-
[{"data": "s1", "type": "sub"}],
3101-
),
3102-
CompiledSQL(
3103-
"INSERT INTO sub (id, sub) VALUES (:id, :sub)",
3104-
lambda ctx: {"id": s1.id, "sub": None},
3108+
Conditional(
3109+
expect_returning,
3110+
[
3111+
CompiledSQL(
3112+
"INSERT INTO base (data, type) VALUES (:data, :type) "
3113+
"RETURNING base.id, base.counter",
3114+
[{"data": "s1", "type": "sub"}],
3115+
),
3116+
CompiledSQL(
3117+
"INSERT INTO sub (id, sub) VALUES (:id, :sub) "
3118+
"RETURNING sub.subcounter, sub.subcounter2",
3119+
lambda ctx: {"id": s1.id, "sub": None},
3120+
),
3121+
],
3122+
[
3123+
CompiledSQL(
3124+
"INSERT INTO base (data, type) VALUES (:data, :type)",
3125+
[{"data": "s1", "type": "sub"}],
3126+
),
3127+
CompiledSQL(
3128+
"INSERT INTO sub (id, sub) VALUES (:id, :sub)",
3129+
lambda ctx: {"id": s1.id, "sub": None},
3130+
),
3131+
],
31053132
),
31063133
)
31073134

@@ -3111,12 +3138,19 @@ def go():
31113138
self.assert_sql_execution(
31123139
testing.db,
31133140
go,
3114-
CompiledSQL(
3115-
"SELECT base.counter AS base_counter, "
3116-
"sub.subcounter AS sub_subcounter, "
3117-
"sub.subcounter2 AS sub_subcounter2 FROM base JOIN sub "
3118-
"ON base.id = sub.id WHERE base.id = :pk_1",
3119-
lambda ctx: {"pk_1": s1.id},
3141+
Conditional(
3142+
expect_returning,
3143+
[],
3144+
[
3145+
CompiledSQL(
3146+
"SELECT base.counter AS base_counter, "
3147+
"sub.subcounter AS sub_subcounter, "
3148+
"sub.subcounter2 AS sub_subcounter2 "
3149+
"FROM base JOIN sub "
3150+
"ON base.id = sub.id WHERE base.id = :pk_1",
3151+
lambda ctx: {"pk_1": s1.id},
3152+
),
3153+
],
31203154
),
31213155
)
31223156

0 commit comments

Comments
 (0)