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 files changed, 43 insertions, 9 deletions

diff --git a/lib/sqlalchemy/orm/mapper.py b/lib/sqlalchemy/orm/mapper.py
index 7a7524621..90c463ce6 100644
--- a/lib/sqlalchemy/orm/mapper.py
+++ b/lib/sqlalchemy/orm/mapper.py
@@ -230,7 +230,7 @@ class Mapper(
         passive_updates: bool = True,
         passive_deletes: bool = False,
         confirm_deleted_rows: bool = True,
-        eager_defaults: bool = False,
+        eager_defaults: Literal[True, False, "auto"] = "auto",
         legacy_is_orphan: bool = False,
         _compiled_cache_size: int = 100,
     ):
@@ -336,14 +336,30 @@ class Mapper(
           value of server-generated default values after an INSERT or UPDATE,
           rather than leaving them as expired to be fetched on next access.
           This can be used for event schemes where the server-generated values
-          are needed immediately before the flush completes.   By default,
-          this scheme will emit an individual ``SELECT`` statement per row
-          inserted or updated, which note can add significant performance
-          overhead.  However, if the
-          target database supports :term:`RETURNING`, the default values will
-          be returned inline with the INSERT or UPDATE statement, which can
-          greatly enhance performance for an application that needs frequent
-          access to just-generated server defaults.
+          are needed immediately before the flush completes.
+
+          The fetch of values occurs either by using ``RETURNING`` inline
+          with the ``INSERT`` or ``UPDATE`` statement, or by adding an
+          additional ``SELECT`` statement subsequent to the ``INSERT`` or
+          ``UPDATE``, if the backend does not support ``RETURNING``.
+
+          The use of ``RETURNING`` is extremely performant in particular for
+          ``INSERT`` statements where SQLAlchemy can take advantage of
+          :ref:`insertmanyvalues <engine_insertmanyvalues>`, whereas the use of
+          an additional ``SELECT`` is relatively poor performing, adding
+          additional SQL round trips which would be unnecessary if these new
+          attributes are not to be accessed in any case.
+
+          For this reason, :paramref:`.Mapper.eager_defaults` defaults to the
+          string value ``"auto"``, which indicates that server defaults for
+          INSERT should be fetched using ``RETURNING`` if the backing database
+          supports it and if the dialect in use supports "insertmanyreturning"
+          for an INSERT statement. If the backing database does not support
+          ``RETURNING`` or "insertmanyreturning" is not available, server
+          defaults will not be fetched.
+
+          .. versionchanged:: 2.0.0b5 added the "auto" option for
+             :paramref:`.Mapper.eager_defaults`
 
           .. seealso::
 
@@ -352,6 +368,12 @@ class Mapper(
           .. versionchanged:: 0.9.0 The ``eager_defaults`` option can now
              make use of :term:`RETURNING` for backends which support it.
 
+          .. versionchanged:: 2.0.0  RETURNING now works with multiple rows
+             INSERTed at once using the
+             :ref:`insertmanyvalues <engine_insertmanyvalues>` feature, which
+             among other things allows the :paramref:`.Mapper.eager_defaults`
+             feature to be very performant on supporting backends.
+
         :param exclude_properties: A list or set of string column names to
           be excluded from mapping.
 
@@ -818,6 +840,18 @@ class Mapper(
             self._log("constructed")
             self._expire_memoizations()
 
+    def _prefer_eager_defaults(self, dialect, table):
+        if self.eager_defaults == "auto":
+            if not table.implicit_returning:
+                return False
+
+            return (
+                table in self._server_default_col_keys
+                and dialect.insert_executemany_returning
+            )
+        else:
+            return self.eager_defaults
+
     def _gen_cache_key(self, anon_map, bindparams):
         return (self,)