]> git.ipfire.org Git - thirdparty/sqlalchemy/sqlalchemy.git/commit
add deterministic imv returning ordering using sentinel columns
authorMike Bayer <mike_mp@zzzcomputing.com>
Wed, 5 Apr 2023 15:58:52 +0000 (11:58 -0400)
committerMike Bayer <mike_mp@zzzcomputing.com>
Fri, 21 Apr 2023 15:30:40 +0000 (11:30 -0400)
commitcf6872d3bdf1a8a9613e853694acc2b1e6f06f51
tree3a4ee41ab8b48aea7ac1e275c2f553763ec28dad
parent63f51491c5f0cb22883c800a065d7c4b4c54774e
add deterministic imv returning ordering using sentinel columns

Repaired a major shortcoming which was identified in the
:ref:`engine_insertmanyvalues` performance optimization feature first
introduced in the 2.0 series. This was a continuation of the change in
2.0.9 which disabled the SQL Server version of the feature due to a
reliance in the ORM on apparent row ordering that is not guaranteed to take
place. The fix applies new logic to all "insertmanyvalues" operations,
which takes effect when a new parameter
:paramref:`_dml.Insert.returning.sort_by_parameter_order` on the
:meth:`_dml.Insert.returning` or :meth:`_dml.UpdateBase.return_defaults`
methods, that through a combination of alternate SQL forms, direct
correspondence of client side parameters, and in some cases downgrading to
running row-at-a-time, will apply sorting to each batch of returned rows
using correspondence to primary key or other unique values in each row
which can be correlated to the input data.

Performance impact is expected to be minimal as nearly all common primary
key scenarios are suitable for parameter-ordered batching to be
achieved for all backends other than SQLite, while "row-at-a-time"
mode operates with a bare minimum of Python overhead compared to the very
heavyweight approaches used in the 1.x series. For SQLite, there is no
difference in performance when "row-at-a-time" mode is used.

It's anticipated that with an efficient "row-at-a-time" INSERT with
RETURNING batching capability, the "insertmanyvalues" feature can be later
be more easily generalized to third party backends that include RETURNING
support but not necessarily easy ways to guarantee a correspondence
with parameter order.

Fixes: #9618
References: #9603
Change-Id: I1d79353f5f19638f752936ba1c35e4dc235a8b7c
71 files changed:
doc/build/changelog/unreleased_20/9618.rst [new file with mode: 0644]
doc/build/changelog/unreleased_20/oracle_uuid.rst [new file with mode: 0644]
doc/build/changelog/whatsnew_20.rst
doc/build/core/connections.rst
doc/build/core/metadata.rst
doc/build/errors.rst
doc/build/glossary.rst
doc/build/orm/large_collections.rst
doc/build/orm/mapping_api.rst
doc/build/orm/queryguide/dml.rst
doc/build/orm/quickstart.rst
doc/build/tutorial/data_insert.rst
doc/build/tutorial/orm_data_manipulation.rst
doc/build/tutorial/orm_related_objects.rst
lib/sqlalchemy/__init__.py
lib/sqlalchemy/dialects/mssql/base.py
lib/sqlalchemy/dialects/mssql/pyodbc.py
lib/sqlalchemy/dialects/mysql/base.py
lib/sqlalchemy/dialects/mysql/provision.py
lib/sqlalchemy/dialects/oracle/cx_oracle.py
lib/sqlalchemy/dialects/postgresql/asyncpg.py
lib/sqlalchemy/dialects/postgresql/base.py
lib/sqlalchemy/dialects/postgresql/provision.py
lib/sqlalchemy/dialects/sqlite/provision.py
lib/sqlalchemy/engine/base.py
lib/sqlalchemy/engine/cursor.py
lib/sqlalchemy/engine/default.py
lib/sqlalchemy/engine/interfaces.py
lib/sqlalchemy/exc.py
lib/sqlalchemy/orm/__init__.py
lib/sqlalchemy/orm/_orm_constructors.py
lib/sqlalchemy/orm/bulk_persistence.py
lib/sqlalchemy/orm/decl_api.py
lib/sqlalchemy/orm/decl_base.py
lib/sqlalchemy/orm/mapper.py
lib/sqlalchemy/orm/persistence.py
lib/sqlalchemy/orm/properties.py
lib/sqlalchemy/schema.py
lib/sqlalchemy/sql/_typing.py
lib/sqlalchemy/sql/base.py
lib/sqlalchemy/sql/compiler.py
lib/sqlalchemy/sql/crud.py
lib/sqlalchemy/sql/dml.py
lib/sqlalchemy/sql/elements.py
lib/sqlalchemy/sql/schema.py
lib/sqlalchemy/sql/selectable.py
lib/sqlalchemy/sql/type_api.py
lib/sqlalchemy/testing/config.py
lib/sqlalchemy/testing/fixtures.py
lib/sqlalchemy/testing/provision.py
lib/sqlalchemy/testing/suite/test_types.py
lib/sqlalchemy/util/__init__.py
lib/sqlalchemy/util/_collections.py
test/dialect/mssql/test_engine.py
test/engine/test_logging.py
test/orm/declarative/test_basic.py
test/orm/declarative/test_inheritance.py
test/orm/dml/test_bulk_statements.py
test/orm/test_ac_relationships.py
test/orm/test_defaults.py
test/orm/test_expire.py
test/orm/test_unitofwork.py
test/orm/test_unitofworkv2.py
test/requirements.py
test/sql/test_compiler.py
test/sql/test_defaults.py
test/sql/test_insert.py
test/sql/test_insert_exec.py
test/sql/test_metadata.py
test/sql/test_returning.py
tools/generate_tuple_map_overloads.py