Changelog
### 2.0.15
```
:released: May 19, 2023
.. change::
:tags: bug, orm
:tickets: 9805
As more projects are using new-style "2.0" ORM querying, it's becoming
apparent that the conditional nature of "autoflush", being based on whether
or not the given statement refers to ORM entities, is becoming more of a
key behavior. Up until now, the "ORM" flag for a statement has been loosely
based around whether or not the statement returns rows that correspond to
ORM entities or columns; the original purpose of the "ORM" flag was to
enable ORM-entity fetching rules which apply post-processing to Core result
sets as well as ORM loader strategies to the statement. For statements
that don't build on rows that contain ORM entities, the "ORM" flag was
considered to be mostly unnecessary.
It still may be the case that "autoflush" would be better taking effect for
*all* usage of :meth:`_orm.Session.execute` and related methods, even for
purely Core SQL constructs. However, this still could impact legacy cases
where this is not expected and may be more of a 2.1 thing. For now however,
the rules for the "ORM-flag" have been opened up so that a statement that
includes ORM entities or attributes anywhere within, including in the WHERE
/ ORDER BY / GROUP BY clause alone, within scalar subqueries, etc. will
enable this flag. This will cause "autoflush" to occur for such statements
and also be visible via the :attr:`_orm.ORMExecuteState.is_orm_statement`
event-level attribute.
.. change::
:tags: bug, postgresql, regression
:tickets: 9808
Repaired the base :class:`.Uuid` datatype for the PostgreSQL dialect to
make full use of the PG-specific ``UUID`` dialect-specific datatype when
"native_uuid" is selected, so that PG driver behaviors are included. This
issue became apparent due to the insertmanyvalues improvement made as part
of :ticket:`9618`, where in a similar manner as that of :ticket:`9739`, the
asyncpg driver is very sensitive to datatype casts being present or not,
and the PostgreSQL driver-specific native ``UUID`` datatype must be invoked
when this generic type is used so that these casts take place.
.. changelog::
```
### 2.0.14
```
:released: May 18, 2023
.. change::
:tags: bug, sql
:tickets: 9772
Fixed issue in :func:`_sql.values` construct where an internal compilation
error would occur if the construct were used inside of a scalar subquery.
.. change::
:tags: usecase, sql
:tickets: 9752
Generalized the MSSQL :func:`_sql.try_cast` function into the
``sqlalchemy.`` import namespace so that it may be implemented by third
party dialects as well. Within SQLAlchemy, the :func:`_sql.try_cast`
function remains a SQL Server-only construct that will raise
:class:`.CompileError` if used with backends that don't support it.
:func:`_sql.try_cast` implements a CAST where un-castable conversions are
returned as NULL, instead of raising an error. Theoretically, the construct
could be implemented by third party dialects for Google BigQuery, DuckDB,
and Snowflake, and possibly others.
Pull request courtesy Nick Crews.
.. change::
:tags: bug, tests, pypy
:tickets: 9789
Fixed test that relied on the ``sys.getsizeof()`` function to not run on
pypy, where this function appears to have different behavior than it does
on cpython.
.. change::
:tags: bug, orm
:tickets: 9777
Modified the ``JoinedLoader`` implementation to use a simpler approach in
one particular area where it previously used a cached structure that would
be shared among threads. The rationale is to avoid a potential race
condition which is suspected of being the cause of a particular crash
that's been reported multiple times. The cached structure in question is
still ultimately "cached" via the compiled SQL cache, so a performance
degradation is not anticipated.
.. change::
:tags: bug, orm, regression
:tickets: 9767
Fixed regression where use of :func:`_dml.update` or :func:`_dml.delete`
within a :class:`_sql.CTE` construct, then used in a :func:`_sql.select`,
would raise a :class:`.CompileError` as a result of ORM related rules for
performing ORM-level update/delete statements.
.. change::
:tags: bug, orm
:tickets: 9766
Fixed issue in new ORM Annotated Declarative where using a
:class:`_schema.ForeignKey` (or other column-level constraint) inside of
:func:`_orm.mapped_column` which is then copied out to models via pep-593
``Annotated`` would apply duplicates of each constraint to the
:class:`_schema.Column` as produced in the target :class:`_schema.Table`,
leading to incorrect CREATE TABLE DDL as well as migration directives under
Alembic.
.. change::
:tags: bug, orm
:tickets: 9779
Fixed issue where using additional relationship criteria with the
:func:`_orm.joinedload` loader option, where the additional criteria itself
contained correlated subqueries that referred to the joined entities and
therefore also required "adaption" to aliased entities, would be excluded
from this adaption, producing the wrong ON clause for the joinedload.
.. change::
:tags: bug, postgresql
:tickets: 9773
Fixed apparently very old issue where the
:paramref:`_postgresql.ENUM.create_type` parameter, when set to its
non-default of ``False``, would not be propagated when the
:class:`_schema.Column` which it's a part of were copied, as is common when
using ORM Declarative mixins.
.. changelog::
```
### 2.0.13
```
:released: May 10, 2023
.. change::
:tags: usecase, asyncio
:tickets: 9731
Added a new helper mixin :class:`_asyncio.AsyncAttrs` that seeks to improve
the use of lazy-loader and other expired or deferred ORM attributes with
asyncio, providing a simple attribute accessor that provides an ``await``
interface to any ORM attribute, whether or not it needs to emit SQL.
.. seealso::
:class:`_asyncio.AsyncAttrs`
.. change::
:tags: bug, orm
:tickets: 9717
Fixed issue where ORM Annotated Declarative would not resolve forward
references correctly in all cases; in particular, when using
``from __future__ import annotations`` in combination with Pydantic
dataclasses.
.. change::
:tags: typing, sql
:tickets: 9656
Added type :data:`_sql.ColumnExpressionArgument` as a public-facing type
that indicates column-oriented arguments which are passed to SQLAlchemy
constructs, such as :meth:`_sql.Select.where`, :func:`_sql.and_` and
others. This may be used to add typing to end-user functions which call
these methods.
.. change::
:tags: bug, orm
:tickets: 9746
Fixed issue in new :ref:`orm_queryguide_upsert_returning` feature where the
``populate_existing`` execution option was not being propagated to the
loading option, preventing existing attributes from being refreshed
in-place.
.. change::
:tags: bug, sql
Fixed the base class for dialect-specific float/double types; Oracle
:class:`_oracle.BINARY_DOUBLE` now subclasses :class:`_sqltypes.Double`,
and internal types for :class:`_sqltypes.Float` for asyncpg and pg8000 now
correctly subclass :class:`_sqltypes.Float`.
.. change::
:tags: bug, ext
:tickets: 9676
Fixed issue in :class:`_mutable.Mutable` where event registration for ORM
mapped attributes would be called repeatedly for mapped inheritance
subclasses, leading to duplicate events being invoked in inheritance
hierarchies.
.. change::
:tags: bug, orm
:tickets: 9715
Fixed loader strategy pathing issues where eager loaders such as
:func:`_orm.joinedload` / :func:`_orm.selectinload` would fail to traverse
fully for many-levels deep following a load that had a
:func:`_orm.with_polymorphic` or similar construct as an interim member.
.. change::
:tags: usecase, sql
:tickets: 9721
Implemented the "cartesian product warning" for UPDATE and DELETE
statements, those which include multiple tables that are not correlated
together in some way.
.. change::
:tags: bug, sql
Fixed issue where :func:`_dml.update` construct that included multiple
tables and no VALUES clause would raise with an internal error. Current
behavior for :class:`_dml.Update` with no values is to generate a SQL
UPDATE statement with an empty "set" clause, so this has been made
consistent for this specific sub-case.
.. change::
:tags: oracle, reflection
:tickets: 9597
Added reflection support in the Oracle dialect to expression based indexes
and the ordering direction of index expressions.
.. change::
:tags: performance, schema
:tickets: 9597
Improved how table columns are added, avoiding unnecessary allocations,
significantly speeding up the creation of many table, like when reflecting
entire schemas.
.. change::
:tags: bug, typing
:tickets: 9762
Fixed typing for the :paramref:`_orm.Session.get.with_for_update` parameter
of :meth:`_orm.Session.get` and :meth:`_orm.Session.refresh` (as well as
corresponding methods on :class:`_asyncio.AsyncSession`) to accept boolean
``True`` and all other argument forms accepted by the parameter at runtime.
.. change::
:tags: bug, postgresql, regression
:tickets: 9739
Fixed another regression due to the "insertmanyvalues" change in 2.0.10 as
part of :ticket:`9618`, in a similar way as regression :ticket:`9701`, where
:class:`.LargeBinary` datatypes also need additional casts on when using the
asyncpg driver specifically in order to work with the new bulk INSERT
format.
.. change::
:tags: bug, orm
:tickets: 9630
Fixed issue in :func:`_orm.mapped_column` construct where the correct
warning for "column X named directly multiple times" would not be emitted
when ORM mapped attributes referred to the same :class:`_schema.Column`, if
the :func:`_orm.mapped_column` construct were involved, raising an internal
assertion instead.
.. change::
:tags: bug, asyncio
Fixed issue in semi-private ``await_only()`` and ``await_fallback()``
concurrency functions where the given awaitable would remain un-awaited if
the function threw a ``GreenletError``, which could cause "was not awaited"
warnings later on if the program continued. In this case, the given
awaitable is now cancelled before the exception is thrown.
.. changelog::
```
### 2.0.12
```
:released: April 30, 2023
.. change::
:tags: bug, mysql, mariadb
:tickets: 9722
Fixed issues regarding reflection of comments for :class:`_schema.Table`
and :class:`_schema.Column` objects, where the comments contained control
characters such as newlines. Additional testing support for these
characters as well as extended Unicode characters in table and column
comments (the latter of which aren't supported by MySQL/MariaDB) added to
testing overall.
.. changelog::
```
### 2.0.11
```
:released: April 26, 2023
.. change::
:tags: bug, engine, regression
:tickets: 9682
Fixed regression which prevented the :attr:`_engine.URL.normalized_query`
attribute of :class:`_engine.URL` from functioning.
.. change::
:tags: bug, postgresql, regression
:tickets: 9701
Fixed critical regression caused by :ticket:`9618`, which modified the
architecture of the :term:`insertmanyvalues` feature for 2.0.10, which
caused floating point values to lose all decimal places when being inserted
using the insertmanyvalues feature with either the psycopg2 or psycopg
drivers.
.. change::
:tags: bug, mssql
Implemented the :class:`_sqltypes.Double` type for SQL Server, where it
will render ``DOUBLE PRECISION`` at DDL time. This is implemented using
a new MSSQL datatype :class:`_mssql.DOUBLE_PRECISION` which also may
be used directly.
.. change::
:tags: bug, oracle
Fixed issue in Oracle dialects where ``Decimal`` returning types such as
:class:`_sqltypes.Numeric` would return floating point values, rather than
``Decimal`` objects, when these columns were used in the
:meth:`_dml.Insert.returning` clause to return INSERTed values.
.. change::
:tags: bug, orm
:tickets: 9583, 9595
Fixed 2.0 regression where use of :func:`_sql.bindparam()` inside of
:meth:`_dml.Insert.values` would fail to be interpreted correctly when
executing the :class:`_dml.Insert` statement using the ORM
:class:`_orm.Session`, due to the new
:ref:`ORM-enabled insert feature <orm_queryguide_bulk_insert>` not
implementing this use case.
.. change::
:tags: usecase, orm
:tickets: 9583, 9595
The :ref:`ORM bulk INSERT and UPDATE <orm_expression_update_delete>`
features now add these capabilities:
* The requirement that extra parameters aren't passed when using ORM
INSERT using the "orm" dml_strategy setting is lifted.
* The requirement that additional WHERE criteria is not passed when using
ORM UPDATE using the "bulk" dml_strategy setting is lifted. Note that
in this case, the check for expected row count is turned off.
.. change::
:tags: usecase, sql
:tickets: 8285
Added support for slice access with :class:`.ColumnCollection`, e.g.
``table.c[0:5]``, ``subquery.c[:-1]`` etc. Slice access returns a sub
:class:`.ColumnCollection` in the same way as passing a tuple of keys. This
is a natural continuation of the key-tuple access added for :ticket:`8285`,
where it appears to be an oversight that the slice access use case was
omitted.
.. change::
:tags: bug, typing
:tickets: 9644
Improved typing of :class:`_engine.RowMapping` to indicate that it
support also :class:`_schema.Column` as index objects, not only
string names. Pull request courtesy Andy Freeland.
.. change::
:tags: engine, performance
:tickets: 9678, 9680
A series of performance enhancements to :class:`_engine.Row`:
* ``__getattr__`` performance of the row's "named tuple" interface has
been improved; within this change, the :class:`_engine.Row`
implementation has been streamlined, removing constructs and logic
that were specific to the 1.4 and prior series of SQLAlchemy.
As part of this change, the serialization format of :class:`_engine.Row`
has been modified slightly, however rows which were pickled with previous
SQLAlchemy 2.0 releases will be recognized within the new format.
Pull request courtesy J. Nick Koston.
* Improved row processing performance for "binary" datatypes by making the
"bytes" handler conditional on a per driver basis. As a result, the
"bytes" result handler has been removed for nearly all drivers other than
psycopg2, all of which in modern forms support returning Python "bytes"
directly. Pull request courtesy J. Nick Koston.
* Additional refactorings inside of :class:`_engine.Row` to improve
performance by Federico Caselli.
.. changelog::
```
### 2.0.10
```
:released: April 21, 2023
.. change::
:tags: bug, typing
:tickets: 9650
Added typing information for recently added operators
:meth:`.ColumnOperators.icontains`, :meth:`.ColumnOperators.istartswith`,
:meth:`.ColumnOperators.iendswith`, and bitwise operators
:meth:`.ColumnOperators.bitwise_and`, :meth:`.ColumnOperators.bitwise_or`,
:meth:`.ColumnOperators.bitwise_xor`, :meth:`.ColumnOperators.bitwise_not`,
:meth:`.ColumnOperators.bitwise_lshift`
:meth:`.ColumnOperators.bitwise_rshift`. Pull request courtesy Martijn
Pieters.
.. change::
:tags: bug, oracle
Fixed issue where the :class:`_sqltypes.Uuid` datatype could not be used in
an INSERT..RETURNING clause with the Oracle dialect.
.. change::
:tags: usecase, engine
:tickets: 9613
Added :func:`_sa.create_pool_from_url` and
:func:`_asyncio.create_async_pool_from_url` to create
a :class:`_pool.Pool` instance from an input url passed as string
or :class:`_sa.URL`.
.. change::
:tags: bug, engine
:tickets: 9618, 9603
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.
.. seealso::
:ref:`engine_insertmanyvalues_returning_order`
.. change::
:tags: bug, mssql
:tickets: 9618, 9603
Restored the :term:`insertmanyvalues` feature for Microsoft SQL Server.
This feature was disabled in version 2.0.9 due to an apparent reliance
on the ordering of RETURNING that is not guaranteed. The architecture of
the "insertmanyvalues" feature has been reworked to accommodate for
specific organizations of INSERT statements and result row handling that
can guarantee the correspondence of returned rows to input records.
.. seealso::
:ref:`engine_insertmanyvalues_returning_order`
.. change::
:tags: usecase, postgresql
:tickets: 9608
Added ``prepared_statement_name_func`` connection argument option in the
asyncpg dialect. This option allows passing a callable used to customize
the name of the prepared statement that will be created by the driver
when executing queries. Pull request courtesy Pavel Sirotkin.
.. seealso::
:ref:`asyncpg_prepared_statement_name`
.. change::
:tags: typing, bug
Updates to the codebase to pass typing with Mypy 1.2.0.
.. change::
:tags: bug, typing
:tickets: 9669
Fixed typing issue where :meth:`_orm.PropComparator.and_` expressions would
not be correctly typed inside of loader options such as
:func:`_orm.selectinload`.
.. change::
:tags: bug, orm
:tickets: 9625
Fixed issue where the :meth:`_orm.declared_attr.directive` modifier was not
correctly honored for subclasses when applied to the ``__mapper_args__``
special method name, as opposed to direct use of
:class:`_orm.declared_attr`. The two constructs should have identical
runtime behaviors.
.. change::
:tags: bug, postgresql
:tickets: 9611
Restored the :paramref:`_postgresql.ENUM.name` parameter as optional in the
signature for :class:`_postgresql.ENUM`, as this is chosen automatically
from a given pep-435 ``Enum`` type.
.. change::
:tags: bug, postgresql
:tickets: 9621
Fixed issue where the comparison for :class:`_postgresql.ENUM` against a
plain string would cast that right-hand side type as VARCHAR, which due to
more explicit casting added to dialects such as asyncpg would produce a
PostgreSQL type mismatch error.
.. change::
:tags: bug, orm
:tickets: 9635
Made an improvement to the :func:`_orm.with_loader_criteria` loader option
to allow it to be indicated in the :meth:`.Executable.options` method of a
top-level statement that is not itself an ORM statement. Examples include
:func:`_sql.select` that's embedded in compound statements such as
:func:`_sql.union`, within an :meth:`_dml.Insert.from_select` construct, as
well as within CTE expressions that are not ORM related at the top level.
.. change::
:tags: bug, orm
:tickets: 9685
Fixed bug in ORM bulk insert feature where additional unnecessary columns
would be rendered in the INSERT statement if RETURNING of individual columns
were requested.
.. change::
:tags: bug, postgresql
:tickets: 9615
Fixed issue that prevented reflection of expression based indexes
with long expressions in PostgreSQL. The expression where erroneously
truncated to the identifier length (that's 63 bytes by default).
.. change::
:tags: usecase, postgresql
:tickets: 9509
Add missing :meth:`_postgresql.Range.intersection` method.
Pull request courtesy Yurii Karabas.
.. change::
:tags: bug, orm
:tickets: 9628
Fixed bug in ORM Declarative Dataclasses where the
:func:`_orm.query_expression` and :func:`_orm.column_property`
constructs, which are documented as read-only constructs in the context of
a Declarative mapping, could not be used with a
:class:`_orm.MappedAsDataclass` class without adding ``init=False``, which
in the case of :func:`_orm.query_expression` was not possible as no
``init`` parameter was included. These constructs have been modified from a
dataclass perspective to be assumed to be "read only", setting
``init=False`` by default and no longer including them in the pep-681
constructor. The dataclass parameters for :func:`_orm.column_property`
``init``, ``default``, ``default_factory``, ``kw_only`` are now deprecated;
these fields don't apply to :func:`_orm.column_property` as used in a
Declarative dataclasses configuration where the construct would be
read-only. Also added read-specific parameter
:paramref:`_orm.query_expression.compare` to
:func:`_orm.query_expression`; :paramref:`_orm.query_expression.repr`
was already present.
.. change::
:tags: bug, orm
Added missing :paramref:`_orm.mapped_column.active_history` parameter
to :func:`_orm.mapped_column` construct.
.. changelog::
```
### 2.0.9
```
:released: April 5, 2023
.. change::
:tags: bug, mssql
:tickets: 9603
The SQLAlchemy "insertmanyvalues" feature which allows fast INSERT of
many rows while also supporting RETURNING is temporarily disabled for
SQL Server. As the unit of work currently relies upon this feature such
that it matches existing ORM objects to returned primary key
identities, this particular use pattern does not work with SQL Server
in all cases as the order of rows returned by "OUTPUT inserted" may not
always match the order in which the tuples were sent, leading to
the ORM making the wrong decisions about these objects in subsequent
operations.
The feature will be re-enabled in an upcoming release and will again
take effect for multi-row INSERT statements, however the unit-of-work's
use of the feature will be disabled, possibly for all dialects, unless
ORM-mapped tables also include a "sentinel" column so that the
returned rows can be referenced back to the original data passed in.
.. change::
:tags: bug, mariadb
:tickets: 9588
Added ``row_number`` as reserved word in MariaDb.
.. change::
:tags: bug, mssql
:tickets: 9586
Changed the bulk INSERT strategy used for SQL Server "executemany" with
pyodbc when ``fast_executemany`` is set to ``True`` by using
``fast_executemany`` / ``cursor.executemany()`` for bulk INSERT that does
not include RETURNING, restoring the same behavior as was used in
SQLAlchemy 1.4 when this parameter is set.
New performance details from end users have shown that ``fast_executemany``
is still much faster for very large datasets as it uses ODBC commands that
can receive all rows in a single round trip, allowing for much larger
datasizes than the batches that can be sent by "insertmanyvalues"
as was implemented for SQL Server.
While this change was made such that "insertmanyvalues" continued to be
used for INSERT that includes RETURNING, as well as if ``fast_executemany``
were not set, due to :ticket:`9603`, the "insertmanyvalues" strategy has
been disabled for SQL Server across the board in any case.
.. changelog::
```
### 2.0.8
```
:released: March 31, 2023
.. change::
:tags: bug, orm
:tickets: 9553
Fixed issue in ORM Annotated Declarative where using a recursive type (e.g.
using a nested Dict type) would result in a recursion overflow in the ORM's
annotation resolution logic, even if this datatype were not necessary to
map the column.
.. change::
:tags: bug, examples
Fixed issue in "versioned history" example where using a declarative base
that is derived from :class:`_orm.DeclarativeBase` would fail to be mapped.
Additionally, repaired the given test suite so that the documented
instructions for running the example using Python unittest now work again.
.. change::
:tags: bug, orm
:tickets: 9550
Fixed issue where the :func:`_orm.mapped_column` construct would raise an
internal error if used on a Declarative mixin and included the
:paramref:`_orm.mapped_column.deferred` parameter.
.. change::
:tags: bug, mysql
:tickets: 9544
Fixed issue where string datatypes such as :class:`_sqltypes.CHAR`,
:class:`_sqltypes.VARCHAR`, :class:`_sqltypes.TEXT`, as well as binary
:class:`_sqltypes.BLOB`, could not be produced with an explicit length of
zero, which has special meaning for MySQL. Pull request courtesy J. Nick
Koston.
.. change::
:tags: bug, orm
:tickets: 9537
Expanded the warning emitted when a plain :func:`_sql.column` object is
present in a Declarative mapping to include any arbitrary SQL expression
that is not declared within an appropriate property type such as
:func:`_orm.column_property`, :func:`_orm.deferred`, etc. These attributes
are otherwise not mapped at all and remain unchanged within the class
dictionary. As it seems likely that such an expression is usually not
what's intended, this case now warns for all such otherwise ignored
expressions, rather than just the :func:`_sql.column` case.
.. change::
:tags: bug, orm
:tickets: 9519
Fixed regression where accessing the expression value of a hybrid property
on a class that was either unmapped or not-yet-mapped (such as calling upon
it within a :func:`_orm.declared_attr` method) would raise an internal
error, as an internal fetch for the parent class' mapper would fail and an
instruction for this failure to be ignored were inadvertently removed in
2.0.
.. change::
:tags: bug, orm
:tickets: 9350
Fields that are declared on Declarative Mixins and then combined with
classes that make use of :class:`_orm.MappedAsDataclass`, where those mixin
fields are not themselves part of a dataclass, now emit a deprecation
warning as these fields will be ignored in a future release, as Python
dataclasses behavior is to ignore these fields. Type checkers will not see
these fields under pep-681.
.. seealso::
:ref:`error_dcmx` - background on rationale
:ref:`orm_declarative_dc_mixins`
.. change::
:tags: bug, postgresql
:tickets: 9511
Fixed critical regression in PostgreSQL dialects such as asyncpg which rely
upon explicit casts in SQL in order for datatypes to be passed to the
driver correctly, where a :class:`.String` datatype would be cast along
with the exact column length being compared, leading to implicit truncation
when comparing a ``VARCHAR`` of a smaller length to a string of greater
length regardless of operator in use (e.g. LIKE, MATCH, etc.). The
PostgreSQL dialect now omits the length from ``VARCHAR`` when rendering
these casts.
.. change::
:tags: bug, util
:tickets: 9487
Implemented missing methods ``copy`` and ``pop`` in
OrderedSet class.
.. change::
:tags: bug, typing
:tickets: 9536
Fixed typing for :func:`_orm.deferred` and :func:`_orm.query_expression`
to work correctly with 2.0 style mappings.
.. change::
:tags: bug, orm
:tickets: 9526
Fixed issue where the :meth:`_sql.BindParameter.render_literal_execute`
method would fail when called on a parameter that also had ORM annotations
associated with it. In practice, this would be observed as a failure of SQL
compilation when using some combinations of a dialect that uses "FETCH
FIRST" such as Oracle along with a :class:`_sql.Select` construct that uses
:meth:`_sql.Select.limit`, within some ORM contexts, including if the
statement were embedded within a relationship primaryjoin expression.
.. change::
:tags: usecase, orm
:tickets: 9563
Exceptions such as ``TypeError`` and ``ValueError`` raised by Python
dataclasses when making use of the :class:`_orm.MappedAsDataclass` mixin
class or :meth:`_orm.registry.mapped_as_dataclass` decorator are now
wrapped within an :class:`.InvalidRequestError` wrapper along with
informative context about the error message, referring to the Python
dataclasses documentation as the authoritative source of background
information on the cause of the exception.
.. seealso::
:ref:`error_dcte`
.. change::
:tags: bug, orm
:tickets: 9549
Towards maintaining consistency with unit-of-work changes made for
:ticket:`5984` and :ticket:`8862`, both of which disable "lazy='raise'"
handling within :class:`_orm.Session` processes that aren't triggered by
attribute access, the :meth:`_orm.Session.delete` method will now also
disable "lazy='raise'" handling when it traverses relationship paths in
order to process the "delete" and "delete-orphan" cascade rules.
Previously, there was no easy way to generically call
:meth:`_orm.Session.delete` on an object that had "lazy='raise'" set up
such that only the necessary relationships would be loaded. As
"lazy='raise'" is primarily intended to catch SQL loading that emits on
attribute access, :meth:`_orm.Session.delete` is now made to behave like
other :class:`_orm.Session` methods including :meth:`_orm.Session.merge` as
well as :meth:`_orm.Session.flush` along with autoflush.
.. change::
:tags: bug, orm
:tickets: 9564
Fixed issue where an annotation-only :class:`_orm.Mapped` directive could
not be used in a Declarative mixin class, without that attribute attempting
to take effect for single- or joined-inheritance subclasses of mapped
classes that had already mapped that attribute on a superclass, producing
conflicting column errors and/or warnings.
.. change::
:tags: bug, orm, typing
:tickets: 9514
Properly type :paramref:`_dml.Insert.from_select.names` to accept
a list of string or columns or mapped attributes.
.. changelog::
```
### 2.0.7
```
:released: March 18, 2023
.. change::
:tags: usecase, postgresql
:tickets: 9416
Added new PostgreSQL type :class:`_postgresql.CITEXT`. Pull request
courtesy Julian David Rath.
.. change::
:tags: bug, typing
:tickets: 9502
Fixed typing issue where :func:`_orm.composite` would not allow an
arbitrary callable as the source of the composite class.
.. change::
:tags: usecase, postgresql
:tickets: 9442
Modifications to the base PostgreSQL dialect to allow for better integration with the
sqlalchemy-redshift third party dialect for SQLAlchemy 2.0. Pull request courtesy
matthewgdv.
.. changelog::
```
### 2.0.6
```
:released: March 13, 2023
.. change::
:tags: bug, sql, regression
:tickets: 9461
Fixed regression where the fix for :ticket:`8098`, which was released in
the 1.4 series and provided a layer of concurrency-safe checks for the
lambda SQL API, included additional fixes in the patch that failed to be
applied to the main branch. These additional fixes have been applied.
.. change::
:tags: bug, typing
:tickets: 9451
Fixed typing issue where :meth:`.ColumnElement.cast` did not allow a
:class:`.TypeEngine` argument independent of the type of the
:class:`.ColumnElement` itself, which is the purpose of
:meth:`.ColumnElement.cast`.
.. change::
:tags: bug, orm
:tickets: 9460
Fixed bug where the "active history" feature was not fully
implemented for composite attributes, making it impossible to receive
events that included the "old" value. This seems to have been the case
with older SQLAlchemy versions as well, where "active_history" would
be propagated to the underlying column-based attributes, but an event
handler listening to the composite attribute itself would not be given
the "old" value being replaced, even if the composite() were set up
with active_history=True.
Additionally, fixed a regression that's local to 2.0 which disallowed
active_history on composite from being assigned to the impl with
``attr.impl.active_history=True``.
.. change::
:tags: bug, oracle
:tickets: 9459
Fixed reflection bug where Oracle "name normalize" would not work correctly
for reflection of symbols that are in the "PUBLIC" schema, such as
synonyms, meaning the PUBLIC name could not be indicated as lower case on
the Python side for the :paramref:`_schema.Table.schema` argument. Using
uppercase "PUBLIC" would work, but would then lead to awkward SQL queries
including a quoted ``"PUBLIC"`` name as well as indexing the table under
uppercase "PUBLIC", which was inconsistent.
.. change::
:tags: bug, typing
Fixed issues to allow typing tests to pass under Mypy 1.1.1.
.. change::
:tags: bug, sql
:tickets: 9440
Fixed regression where the :func:`_sql.select` construct would not be able
to render if it were given no columns and then used in the context of an
EXISTS, raising an internal exception instead. While an empty "SELECT" is
not typically valid SQL, in the context of EXISTS databases such as
PostgreSQL allow it, and in any case the condition now no longer raises
an internal exception.
.. change::
:tags: bug, orm
:tickets: 9418
Fixed regression involving pickling of Python rows between the cython and
pure Python implementations of :class:`.Row`, which occurred as part of
refactoring code for version 2.0 with typing. A particular constant were
turned into a string based ``Enum`` for the pure Python version of
:class:`.Row` whereas the cython version continued to use an integer
constant, leading to deserialization failures.
.. changelog::
```
### 2.0.5.post1
```
:released: March 5, 2023
.. change::
:tags: bug, orm
:tickets: 9418
Added constructor arguments to the built-in mapping collection types
including :class:`.KeyFuncDict`, :func:`_orm.attribute_keyed_dict`,
:func:`_orm.column_keyed_dict` so that these dictionary types may be
constructed in place given the data up front; this provides further
compatibility with tools such as Python dataclasses ``.asdict()`` which
relies upon invoking these classes directly as ordinary dictionary classes.
.. change::
:tags: bug, orm, regression
:tickets: 9424
Fixed multiple regressions due to :ticket:`8372`, involving
:func:`_orm.attribute_mapped_collection` (now called
:func:`_orm.attribute_keyed_dict`).
First, the collection was no longer usable with "key" attributes that were
not themselves ordinary mapped attributes; attributes linked to descriptors
and/or association proxy attributes have been fixed.
Second, if an event or other operation needed access to the "key" in order
to populate the dictionary from an mapped attribute that was not
loaded, this also would raise an error inappropriately, rather than
trying to load the attribute as was the behavior in 1.4. This is also
fixed.
For both cases, the behavior of :ticket:`8372` has been expanded.
:ticket:`8372` introduced an error that raises when the derived key that
would be used as a mapped dictionary key is effectively unassigned. In this
change, a warning only is emitted if the effective value of the ".key"
attribute is ``None``, where it cannot be unambiguously determined if this
``None`` was intentional or not. ``None`` will be not supported as mapped
collection dictionary keys going forward (as it typically refers to NULL
which means "unknown"). Setting
:paramref:`_orm.attribute_keyed_dict.ignore_unpopulated_attribute` will now
cause such ``None`` keys to be ignored as well.
.. change::
:tags: engine, performance
:tickets: 9343
A small optimization to the Cython implementation of :class:`.Result`
using a cdef for a particular int value to avoid Python overhead. Pull
request courtesy Matus Valo.
.. change::
:tags: bug, mssql
:tickets: 9414
Fixed issue in the new :class:`.Uuid` datatype which prevented it from
working with the pymssql driver. As pymssql seems to be maintained again,
restored testing support for pymssql.
.. change::
:tags: bug, mssql
Tweaked the pymssql dialect to take better advantage of
RETURNING for INSERT statements in order to retrieve last inserted primary
key values, in the same way as occurs for the mssql+pyodbc dialect right
now.
.. change::
:tags: bug, orm
Identified that the ``sqlite`` and ``mssql+pyodbc`` dialects are now
compatible with the SQLAlchemy ORM's "versioned rows" feature, since
SQLAlchemy now computes rowcount for a RETURNING statement in this specific
case by counting the rows returned, rather than relying upon
``cursor.rowcount``. In particular, the ORM versioned rows use case
(documented at :ref:`mapper_version_counter`) should now be fully
supported with the SQL Server pyodbc dialect.
.. change::
:tags: bug, postgresql
:tickets: 9349
Fixed issue in PostgreSQL :class:`_postgresql.ExcludeConstraint` where
literal values were being compiled as bound parameters and not direct
inline values as is required for DDL.
.. change::
:tags: bug, typing
Fixed bug where the :meth:`_engine.Connection.scalars` method was not typed
as allowing a multiple-parameters list, which is now supported using
insertmanyvalues operations.
.. change::
:tags: bug, typing
:tickets: 9376
Improved typing for the mapping passed to :meth:`.Insert.values` and
:meth:`.Update.values` to be more open-ended about collection type, by
indicating read-only ``Mapping`` instead of writeable ``Dict`` which would
error out on too limited of a key type.
.. change::
:tags: schema
Validate that when provided the :paramref:`_schema.MetaData.schema`
argument of :class:`_schema.MetaData` is a string.
.. change::
:tags: typing, usecase
:tickets: 9338
Exported the type returned by
:meth:`_orm.scoped_session.query_property` using a new public type
:class:`.orm.QueryPropertyDescriptor`.
.. change::
:tags: bug, mysql, postgresql
:tickets: 5648
The support for pool ping listeners to receive exception events via the
:meth:`.DialectEvents.handle_error` event added in 2.0.0b1 for
:ticket:`5648` failed to take into account dialect-specific ping routines
such as that of MySQL and PostgreSQL. The dialect feature has been reworked
so that all dialects participate within event handling. Additionally,
a new boolean element :attr:`.ExceptionContext.is_pre_ping` is added
which identifies if this operation is occurring within the pre-ping
operation.
For this release, third party dialects which implement a custom
:meth:`_engine.Dialect.do_ping` method can opt in to the newly improved
behavior by having their method no longer catch exceptions or check
exceptions for "is_disconnect", instead just propagating all exceptions
outwards. Checking the exception for "is_disconnect" is now done by an
enclosing method on the default dialect, which ensures that the event hook
is invoked for all exception scenarios before testing the exception as a
"disconnect" exception. If an existing ``do_ping()`` method continues to
catch exceptions and check "is_disconnect", it will continue to work as it
did previously, but ``handle_error`` hooks will not have access to the
exception if it isn't propagated outwards.
.. change::
:tags: bug, ext
:tickets: 9367
Fixed issue in automap where calling :meth:`_automap.AutomapBase.prepare`
from a specific mapped class, rather than from the
:class:`_automap.AutomapBase` directly, would not use the correct base
class when automap detected new tables, instead using the given class,
leading to mappers trying to configure inheritance. While one should
normally call :meth:`_automap.AutomapBase.prepare` from the base in any
case, it shouldn't misbehave that badly when called from a subclass.
.. change::
:tags: bug, sqlite, regression
:tickets: 9379
Fixed regression for SQLite connections where use of the ``deterministic``
parameter when establishing database functions would fail for older SQLite
versions, those prior to version 3.8.3. The version checking logic has been
improved to accommodate for this case.
.. change::
:tags: bug, typing
:tickets: 9391
Added missing init overload to the :class:`_types.Numeric` type object so
that pep-484 type checkers may properly resolve the complete type, deriving
from the :paramref:`_types.Numeric.asdecimal` parameter whether ``Decimal``
or ``float`` objects will be represented.
.. change::
:tags: bug, typing
:tickets: 9398
Fixed typing bug where :meth:`_sql.Select.from_statement` would not accept
:func:`_sql.text` or :class:`.TextualSelect` objects as a valid type.
Additionally repaired the :class:`.TextClause.columns` method to have a
return type, which was missing.
.. change::
:tags: bug, orm declarative
:tickets: 9332
Fixed issue where new :paramref:`_orm.mapped_column.use_existing_column`
feature would not work if the two same-named columns were mapped under
attribute names that were differently-named from an explicit name given to
the column itself. The attribute names can now be differently named when
using this parameter.
.. change::
:tags: bug, orm
:tickets: 9373
Added support for the :paramref:`_orm.Mapper.polymorphic_load` parameter to
be applied to each mapper in an inheritance hierarchy more than one level
deep, allowing columns to load for all classes in the hierarchy that
indicate ``"selectin"`` using a single statement, rather than ignoring
elements on those intermediary classes that nonetheless indicate they also
would participate in ``"selectin"`` loading and were not part of the
base-most SELECT statement.
.. change::
:tags: bug, orm
:tickets: 8853, 9335
Continued the fix for :ticket:`8853`, allowing the :class:`_orm.Mapped`
name to be fully qualified regardless of whether or not
``from __annotations__ import future`` were present. This issue first fixed
in 2.0.0b3 confirmed that this case worked via the test suite, however the
test suite apparently was not testing the behavior for the name
:class:`_orm.Mapped` not being locally present at all; string resolution
has been updated to ensure the :class:`_orm.Mapped` symbol is locatable as
applies to how the ORM uses these functions.
.. change::
:tags: bug, typing
:tickets: 9340
Fixed typing issue where :func:`_orm.with_polymorphic` would not
record the class type correctly.
.. change::
:tags: bug, ext, regression
:tickets: 9380
Fixed regression caused by typing added to ``sqlalchemy.ext.mutable`` for
:ticket:`8667`, where the semantics of the ``.pop()`` method changed such
that the method was non-working. Pull request courtesy Nils Philippsen.
.. change::
:tags: bug, sql, regression
:tickets: 9390
Restore the :func:`.nullslast` and :func:`.nullsfirst` legacy functions
into the ``sqlalchemy`` import namespace. Previously, the newer
:func:`.nulls_last` and :func:`.nulls_first` functions were available, but
the legacy ones were inadvertently removed.
.. change::
:tags: bug, postgresql
:tickets: 9401
Fixed issue where the PostgreSQL :class:`_postgresql.ExcludeConstraint`
construct would not be copyable within operations such as
:meth:`_schema.Table.to_metadata` as well as within some Alembic scenarios,
if the constraint contained textual expression elements.
.. change::
:tags: bug, engine
:tickets: 9423
Fixed bug where :class:`_engine.Row` objects could not be reliably unpickled
across processes due to an accidental reliance on an unstable hash value.
.. changelog::
```
### 2.0.4
```
:released: February 17, 2023
.. change::
:tags: bug, orm, regression
:tickets: 9273
Fixed regression introduced in version 2.0.2 due to :ticket:`9217` where
using DML RETURNING statements, as well as
:meth:`_sql.Select.from_statement` constructs as was "fixed" in
:ticket:`9217`, in conjunction with ORM mapped classes that used
expressions such as with :func:`_orm.column_property`, would lead to an
internal error within Core where it would attempt to match the expression
by name. The fix repairs the Core issue, and also adjusts the fix in
:ticket:`9217` to not take effect for the DML RETURNING use case, where it
adds unnecessary overhead.
.. change::
:tags: usecase, typing
:tickets: 9321
Improved the typing support for the :ref:`hybrids_toplevel`
extension, updated all documentation to use ORM Annotated Declarative
mappings, and added a new modifier called :attr:`.hybrid_property.inplace`.
This modifier provides a way to alter the state of a :class:`.hybrid_property`
**in place**, which is essentially what very early versions of hybrids
did, before SQLAlchemy version 1.2.0 :ticket:`3912` changed this to
remove in-place mutation. This in-place mutation is now restored on an
**opt-in** basis to allow a single hybrid to have multiple methods
set up, without the need to name all the methods the same and without the
need to carefully "chain" differently-named methods in order to maintain
the composition. Typing tools such as Mypy and Pyright do not allow
same-named methods on a class, so with this change a succinct method
of setting up hybrids with typing support is restored.
.. seealso::
:ref:`hybrid_pep484_naming`
.. change::
:tags: bug, orm
Marked the internal ``EvaluatorCompiler`` module as private to the ORM, and
renamed it to ``_EvaluatorCompiler``. For users that may have been relying
upon this, the name ``EvaluatorCompiler`` is still present, however this
use is not supported and will be removed in a future release.
.. change::
:tags: orm, usecase
:tickets: 9297
To accommodate a change in column ordering used by ORM Declarative in
SQLAlchemy 2.0, a new parameter :paramref:`_orm.mapped_column.sort_order`
has been added that can be used to control the order of the columns defined
in the table by the ORM, for common use cases such as mixins with primary
key columns that should appear first in tables. The change notes at
:ref:`change_9297` illustrate the default change in ordering behavior
(which is part of all SQLAlchemy 2.0 releases) as well as use of the
:paramref:`_orm.mapped_column.sort_order` to control column ordering when
using mixins and multiple classes (new in 2.0.4).
.. seealso::
:ref:`change_9297`
.. change::
:tags: sql
:tickets: 9277
Added public property :attr:`_schema.Table.autoincrement_column` that
returns the column identified as autoincrementing in the column.
.. change::
:tags: oracle, bug
:tickets: 9295
Adjusted the behavior of the ``thick_mode`` parameter for the
:ref:`oracledb` dialect to correctly accept ``False`` as a value.
Previously, only ``None`` would indicate that thick mode should be
disabled.
.. change::
:tags: usecase, orm
:tickets: 9298
The :meth:`_orm.Session.refresh` method will now immediately load a
relationship-bound attribute that is explicitly named within the
:paramref:`_orm.Session.refresh.attribute_names` collection even if it is
currently linked to the "select" loader, which normally is a "lazy" loader
that does not fire off during a refresh. The "lazy loader" strategy will
now detect that the operation is specifically a user-initiated
:meth:`_orm.Session.refresh` operation which named this attribute
explicitly, and will then call upon the "immediateload" strategy to
actually emit SQL to load the attribute. This should be helpful in
particular for some asyncio situations where the loading of an unloaded
lazy-loaded attribute must be forced, without using the actual lazy-loading
attribute pattern not supported in asyncio.
.. change::
:tags: bug, sql
:tickets: 9313
Fixed issue where element types of a tuple value would be hardcoded to take
on the types from a compared-to tuple, when the comparison were using the
:meth:`.ColumnOperators.in_` operator. This was inconsistent with the usual
way that types are determined for a binary expression, which is that the
actual element type on the right side is considered first before applying
the left-hand-side type.
.. change::
:tags: usecase, orm declarative
:tickets: 9266
Added new parameter ``dataclasses_callable`` to both the
:class:`_orm.MappedAsDataclass` class as well as the
:meth:`_orm.registry.mapped_as_dataclass` method which allows an
alternative callable to Python ``dataclasses.dataclass`` to be used in
order to produce dataclasses. The use case here is to drop in Pydantic's
dataclass function instead. Adjustments have been made to the mixin support
added for :ticket:`9179` in version 2.0.1 so that the ``__annotations__``
collection of the mixin is rewritten to not include the
:class:`_orm.Mapped` container, in the same way as occurs with mapped
classes, so that the Pydantic dataclasses constructor is not exposed to
unknown types.
.. seealso::
:ref:`dataclasses_pydantic`
.. changelog::
```
### 2.0.3
```
:released: February 9, 2023
.. change::
:tags: typing, bug
:tickets: 9254
Remove ``typing.Self`` workaround, now using :pep:`673` for most methods
that return ``Self``. As a consequence of this change ``mypy>=1.0.0`` is
now required to type check SQLAlchemy code.
Pull request courtesy Yurii Karabas.
.. change::
:tags: bug, sql, regression
:tickets: 9271
Fixed critical regression in SQL expression formulation in the 2.0 series
due to :ticket:`7744` which improved support for SQL expressions that
contained many elements against the same operator repeatedly; parenthesis
grouping would be lost with expression elements beyond the first two
elements.
.. changelog::
```
### 2.0.2
```
:released: February 6, 2023
.. change::
:tags: bug, orm declarative
:tickets: 9249
Fixed regression caused by the fix for :ticket:`9171`, which itself was
fixing a regression, involving the mechanics of ``__init__()`` on classes
that extend from :class:`_orm.DeclarativeBase`. The change made it such
that ``__init__()`` was applied to the user-defined base if there were no
``__init__()`` method directly on the class. This has been adjusted so that
``__init__()`` is applied only if no other class in the hierarchy of the
user-defined base has an ``__init__()`` method. This again allows
user-defined base classes based on :class:`_orm.DeclarativeBase` to include
mixins that themselves include a custom ``__init__()`` method.
.. change::
:tags: bug, mysql, regression
:tickets: 9251
Fixed regression caused by issue :ticket:`9058` which adjusted the MySQL
dialect's ``has_table()`` to again use "DESCRIBE", where the specific error
code raised by MySQL version 8 when using a non-existent schema name was
unexpected and failed to be interpreted as a boolean result.
.. change::
:tags: bug, sqlite
:tickets: 9251
Fixed the SQLite dialect's ``has_table()`` function to correctly report
False for queries that include a non-None schema name for a schema that
doesn't exist; previously, a database error was raised.
.. change::
:tags: bug, orm declarative
:tickets: 9226
Fixed issue in ORM Declarative Dataclass mappings related to newly added
support for mixins added in 2.0.1 via :ticket:`9179`, where a combination
of using mixins plus ORM inheritance would mis-classify fields in some
cases leading to field-level dataclass arguments such as ``init=False`` being
lost.
.. change::
:tags: bug, orm, ression
:tickets: 9232
Fixed obscure ORM inheritance issue caused by :ticket:`8705` where some
scenarios of inheriting mappers that indicated groups of columns from the
local table and the inheriting table together under a
:func:`_orm.column_property` would nonetheless warn that properties of the
same name were being combined implicitly.
.. change::
:tags: orm, bug, regression
:tickets: 9228
Fixed regression where using the :paramref:`_orm.Mapper.version_id_col`
feature with a regular Python-side incrementing column would fail to work
for SQLite and other databases that don't support "rowcount" with
"RETURNING", as "RETURNING" would be assumed for such columns even though
that's not what actually takes place.
.. change::
:tags: bug, orm declarative
:tickets: 9240
Repaired ORM Declarative mappings to allow for the
:paramref:`_orm.Mapper.primary_key` parameter to be specified within
``__mapper_args__`` when using :func:`_orm.mapped_column`. Despite this
usage being directly in the 2.0 documentation, the :class:`_orm.Mapper` was
not accepting the :func:`_orm.mapped_column` construct in this context. Ths
feature was already working for the :paramref:`_orm.Mapper.version_id_col`
and :paramref:`_orm.Mapper.polymorphic_on` parameters.
As part of this change, the ``__mapper_args__`` attribute may be specified
without using :func:`_orm.declared_attr` on a non-mapped mixin class,
including a ``"primary_key"`` entry that refers to :class:`_schema.Column`
or :func:`_orm.mapped_column` objects locally present on the mixin;
Declarative will also translate these columns into the correct ones for a
particular mapped class. This again was working already for the
:paramref:`_orm.Mapper.version_id_col` and
:paramref:`_orm.Mapper.polymorphic_on` parameters. Additionally,
elements within ``"primary_key"`` may be indicated as string names of
existing mapped properties.
.. change::
:tags: usecase, sql
:tickets: 8780
Added a full suite of new SQL bitwise operators, for performing
database-side bitwise expressions on appropriate data values such as
integers, bit-strings, and similar. Pull request courtesy Yegor Statkevich.
.. seealso::
:ref:`operators_bitwise`
.. change::
:tags: bug, orm declarative
:tickets: 9211
An explicit error is raised if a mapping attempts to mix the use of
:class:`_orm.MappedAsDataclass` with
:meth:`_orm.registry.mapped_as_dataclass` within the same class hierarchy,
as this produces issues with the dataclass function being applied at the
wrong time to the mapped class, leading to errors during the mapping
process.
.. change::
:tags: bug, orm, regression
:tickets: 9217
Fixed regression when using :meth:`_sql.Select.from_statement` in an ORM
context, where matching of columns to SQL labels based on name alone was
disabled for ORM-statements that weren't fully textual. This would prevent
arbitrary SQL expressions with column-name labels from matching up to the
entity to be loaded, which previously would work within the 1.4
and previous series, so the previous behavior has been restored.
.. change::
:tags: bug, asyncio
:tickets: 9237
Repaired a regression caused by the fix for :ticket:`8419` which caused
asyncpg connections to be reset (i.e. transaction ``rollback()`` called)
and returned to the pool normally in the case that the connection were not
explicitly returned to the connection pool and was instead being
intercepted by Python garbage collection, which would fail if the garbage
collection operation were being called outside of the asyncio event loop,
leading to a large amount of stack trace activity dumped into logging
and standard output.
The correct behavior is restored, which is that all asyncio connections
that are garbage collected due to not being explicitly returned to the
connection pool are detached from the pool and discarded, along with a
warning, rather than being returned the pool, as they cannot be reliably
reset. In the case of asyncpg connections, the asyncpg-specific
``terminate()`` method will be used to end the connection more gracefully
within this process as opposed to just dropping it.
This change includes a small behavioral change that is hoped to be useful
for debugging asyncio applications, where the warning that's emitted in the
case of asyncio connections being unexpectedly garbage collected has been
made slightly more aggressive by moving it outside of a ``try/except``
block and into a ``finally:`` block, where it will emit unconditionally
regardless of whether the detach/termination operation succeeded or not. It
will also have the effect that applications or test suites which promote
Python warnings to exceptions will see this as a full exception raise,
whereas previously it was not possible for this warning to actually
propagate as an exception. Applications and test suites which need to
tolerate this warning in the interim should adjust the Python warnings
filter to allow these warnings to not raise.
The behavior for tradition
pyup-bot
commented
1 year ago
This PR updates SQLAlchemy from 1.2.7 to 2.0.15.
Changelog
### 2.0.15 ``` :released: May 19, 2023 .. change:: :tags: bug, orm :tickets: 9805 As more projects are using new-style "2.0" ORM querying, it's becoming apparent that the conditional nature of "autoflush", being based on whether or not the given statement refers to ORM entities, is becoming more of a key behavior. Up until now, the "ORM" flag for a statement has been loosely based around whether or not the statement returns rows that correspond to ORM entities or columns; the original purpose of the "ORM" flag was to enable ORM-entity fetching rules which apply post-processing to Core result sets as well as ORM loader strategies to the statement. For statements that don't build on rows that contain ORM entities, the "ORM" flag was considered to be mostly unnecessary. It still may be the case that "autoflush" would be better taking effect for *all* usage of :meth:`_orm.Session.execute` and related methods, even for purely Core SQL constructs. However, this still could impact legacy cases where this is not expected and may be more of a 2.1 thing. For now however, the rules for the "ORM-flag" have been opened up so that a statement that includes ORM entities or attributes anywhere within, including in the WHERE / ORDER BY / GROUP BY clause alone, within scalar subqueries, etc. will enable this flag. This will cause "autoflush" to occur for such statements and also be visible via the :attr:`_orm.ORMExecuteState.is_orm_statement` event-level attribute. .. change:: :tags: bug, postgresql, regression :tickets: 9808 Repaired the base :class:`.Uuid` datatype for the PostgreSQL dialect to make full use of the PG-specific ``UUID`` dialect-specific datatype when "native_uuid" is selected, so that PG driver behaviors are included. This issue became apparent due to the insertmanyvalues improvement made as part of :ticket:`9618`, where in a similar manner as that of :ticket:`9739`, the asyncpg driver is very sensitive to datatype casts being present or not, and the PostgreSQL driver-specific native ``UUID`` datatype must be invoked when this generic type is used so that these casts take place. .. changelog:: ``` ### 2.0.14 ``` :released: May 18, 2023 .. change:: :tags: bug, sql :tickets: 9772 Fixed issue in :func:`_sql.values` construct where an internal compilation error would occur if the construct were used inside of a scalar subquery. .. change:: :tags: usecase, sql :tickets: 9752 Generalized the MSSQL :func:`_sql.try_cast` function into the ``sqlalchemy.`` import namespace so that it may be implemented by third party dialects as well. Within SQLAlchemy, the :func:`_sql.try_cast` function remains a SQL Server-only construct that will raise :class:`.CompileError` if used with backends that don't support it. :func:`_sql.try_cast` implements a CAST where un-castable conversions are returned as NULL, instead of raising an error. Theoretically, the construct could be implemented by third party dialects for Google BigQuery, DuckDB, and Snowflake, and possibly others. Pull request courtesy Nick Crews. .. change:: :tags: bug, tests, pypy :tickets: 9789 Fixed test that relied on the ``sys.getsizeof()`` function to not run on pypy, where this function appears to have different behavior than it does on cpython. .. change:: :tags: bug, orm :tickets: 9777 Modified the ``JoinedLoader`` implementation to use a simpler approach in one particular area where it previously used a cached structure that would be shared among threads. The rationale is to avoid a potential race condition which is suspected of being the cause of a particular crash that's been reported multiple times. The cached structure in question is still ultimately "cached" via the compiled SQL cache, so a performance degradation is not anticipated. .. change:: :tags: bug, orm, regression :tickets: 9767 Fixed regression where use of :func:`_dml.update` or :func:`_dml.delete` within a :class:`_sql.CTE` construct, then used in a :func:`_sql.select`, would raise a :class:`.CompileError` as a result of ORM related rules for performing ORM-level update/delete statements. .. change:: :tags: bug, orm :tickets: 9766 Fixed issue in new ORM Annotated Declarative where using a :class:`_schema.ForeignKey` (or other column-level constraint) inside of :func:`_orm.mapped_column` which is then copied out to models via pep-593 ``Annotated`` would apply duplicates of each constraint to the :class:`_schema.Column` as produced in the target :class:`_schema.Table`, leading to incorrect CREATE TABLE DDL as well as migration directives under Alembic. .. change:: :tags: bug, orm :tickets: 9779 Fixed issue where using additional relationship criteria with the :func:`_orm.joinedload` loader option, where the additional criteria itself contained correlated subqueries that referred to the joined entities and therefore also required "adaption" to aliased entities, would be excluded from this adaption, producing the wrong ON clause for the joinedload. .. change:: :tags: bug, postgresql :tickets: 9773 Fixed apparently very old issue where the :paramref:`_postgresql.ENUM.create_type` parameter, when set to its non-default of ``False``, would not be propagated when the :class:`_schema.Column` which it's a part of were copied, as is common when using ORM Declarative mixins. .. changelog:: ``` ### 2.0.13 ``` :released: May 10, 2023 .. change:: :tags: usecase, asyncio :tickets: 9731 Added a new helper mixin :class:`_asyncio.AsyncAttrs` that seeks to improve the use of lazy-loader and other expired or deferred ORM attributes with asyncio, providing a simple attribute accessor that provides an ``await`` interface to any ORM attribute, whether or not it needs to emit SQL. .. seealso:: :class:`_asyncio.AsyncAttrs` .. change:: :tags: bug, orm :tickets: 9717 Fixed issue where ORM Annotated Declarative would not resolve forward references correctly in all cases; in particular, when using ``from __future__ import annotations`` in combination with Pydantic dataclasses. .. change:: :tags: typing, sql :tickets: 9656 Added type :data:`_sql.ColumnExpressionArgument` as a public-facing type that indicates column-oriented arguments which are passed to SQLAlchemy constructs, such as :meth:`_sql.Select.where`, :func:`_sql.and_` and others. This may be used to add typing to end-user functions which call these methods. .. change:: :tags: bug, orm :tickets: 9746 Fixed issue in new :ref:`orm_queryguide_upsert_returning` feature where the ``populate_existing`` execution option was not being propagated to the loading option, preventing existing attributes from being refreshed in-place. .. change:: :tags: bug, sql Fixed the base class for dialect-specific float/double types; Oracle :class:`_oracle.BINARY_DOUBLE` now subclasses :class:`_sqltypes.Double`, and internal types for :class:`_sqltypes.Float` for asyncpg and pg8000 now correctly subclass :class:`_sqltypes.Float`. .. change:: :tags: bug, ext :tickets: 9676 Fixed issue in :class:`_mutable.Mutable` where event registration for ORM mapped attributes would be called repeatedly for mapped inheritance subclasses, leading to duplicate events being invoked in inheritance hierarchies. .. change:: :tags: bug, orm :tickets: 9715 Fixed loader strategy pathing issues where eager loaders such as :func:`_orm.joinedload` / :func:`_orm.selectinload` would fail to traverse fully for many-levels deep following a load that had a :func:`_orm.with_polymorphic` or similar construct as an interim member. .. change:: :tags: usecase, sql :tickets: 9721 Implemented the "cartesian product warning" for UPDATE and DELETE statements, those which include multiple tables that are not correlated together in some way. .. change:: :tags: bug, sql Fixed issue where :func:`_dml.update` construct that included multiple tables and no VALUES clause would raise with an internal error. Current behavior for :class:`_dml.Update` with no values is to generate a SQL UPDATE statement with an empty "set" clause, so this has been made consistent for this specific sub-case. .. change:: :tags: oracle, reflection :tickets: 9597 Added reflection support in the Oracle dialect to expression based indexes and the ordering direction of index expressions. .. change:: :tags: performance, schema :tickets: 9597 Improved how table columns are added, avoiding unnecessary allocations, significantly speeding up the creation of many table, like when reflecting entire schemas. .. change:: :tags: bug, typing :tickets: 9762 Fixed typing for the :paramref:`_orm.Session.get.with_for_update` parameter of :meth:`_orm.Session.get` and :meth:`_orm.Session.refresh` (as well as corresponding methods on :class:`_asyncio.AsyncSession`) to accept boolean ``True`` and all other argument forms accepted by the parameter at runtime. .. change:: :tags: bug, postgresql, regression :tickets: 9739 Fixed another regression due to the "insertmanyvalues" change in 2.0.10 as part of :ticket:`9618`, in a similar way as regression :ticket:`9701`, where :class:`.LargeBinary` datatypes also need additional casts on when using the asyncpg driver specifically in order to work with the new bulk INSERT format. .. change:: :tags: bug, orm :tickets: 9630 Fixed issue in :func:`_orm.mapped_column` construct where the correct warning for "column X named directly multiple times" would not be emitted when ORM mapped attributes referred to the same :class:`_schema.Column`, if the :func:`_orm.mapped_column` construct were involved, raising an internal assertion instead. .. change:: :tags: bug, asyncio Fixed issue in semi-private ``await_only()`` and ``await_fallback()`` concurrency functions where the given awaitable would remain un-awaited if the function threw a ``GreenletError``, which could cause "was not awaited" warnings later on if the program continued. In this case, the given awaitable is now cancelled before the exception is thrown. .. changelog:: ``` ### 2.0.12 ``` :released: April 30, 2023 .. change:: :tags: bug, mysql, mariadb :tickets: 9722 Fixed issues regarding reflection of comments for :class:`_schema.Table` and :class:`_schema.Column` objects, where the comments contained control characters such as newlines. Additional testing support for these characters as well as extended Unicode characters in table and column comments (the latter of which aren't supported by MySQL/MariaDB) added to testing overall. .. changelog:: ``` ### 2.0.11 ``` :released: April 26, 2023 .. change:: :tags: bug, engine, regression :tickets: 9682 Fixed regression which prevented the :attr:`_engine.URL.normalized_query` attribute of :class:`_engine.URL` from functioning. .. change:: :tags: bug, postgresql, regression :tickets: 9701 Fixed critical regression caused by :ticket:`9618`, which modified the architecture of the :term:`insertmanyvalues` feature for 2.0.10, which caused floating point values to lose all decimal places when being inserted using the insertmanyvalues feature with either the psycopg2 or psycopg drivers. .. change:: :tags: bug, mssql Implemented the :class:`_sqltypes.Double` type for SQL Server, where it will render ``DOUBLE PRECISION`` at DDL time. This is implemented using a new MSSQL datatype :class:`_mssql.DOUBLE_PRECISION` which also may be used directly. .. change:: :tags: bug, oracle Fixed issue in Oracle dialects where ``Decimal`` returning types such as :class:`_sqltypes.Numeric` would return floating point values, rather than ``Decimal`` objects, when these columns were used in the :meth:`_dml.Insert.returning` clause to return INSERTed values. .. change:: :tags: bug, orm :tickets: 9583, 9595 Fixed 2.0 regression where use of :func:`_sql.bindparam()` inside of :meth:`_dml.Insert.values` would fail to be interpreted correctly when executing the :class:`_dml.Insert` statement using the ORM :class:`_orm.Session`, due to the new :ref:`ORM-enabled insert feature <orm_queryguide_bulk_insert>` not implementing this use case. .. change:: :tags: usecase, orm :tickets: 9583, 9595 The :ref:`ORM bulk INSERT and UPDATE <orm_expression_update_delete>` features now add these capabilities: * The requirement that extra parameters aren't passed when using ORM INSERT using the "orm" dml_strategy setting is lifted. * The requirement that additional WHERE criteria is not passed when using ORM UPDATE using the "bulk" dml_strategy setting is lifted. Note that in this case, the check for expected row count is turned off. .. change:: :tags: usecase, sql :tickets: 8285 Added support for slice access with :class:`.ColumnCollection`, e.g. ``table.c[0:5]``, ``subquery.c[:-1]`` etc. Slice access returns a sub :class:`.ColumnCollection` in the same way as passing a tuple of keys. This is a natural continuation of the key-tuple access added for :ticket:`8285`, where it appears to be an oversight that the slice access use case was omitted. .. change:: :tags: bug, typing :tickets: 9644 Improved typing of :class:`_engine.RowMapping` to indicate that it support also :class:`_schema.Column` as index objects, not only string names. Pull request courtesy Andy Freeland. .. change:: :tags: engine, performance :tickets: 9678, 9680 A series of performance enhancements to :class:`_engine.Row`: * ``__getattr__`` performance of the row's "named tuple" interface has been improved; within this change, the :class:`_engine.Row` implementation has been streamlined, removing constructs and logic that were specific to the 1.4 and prior series of SQLAlchemy. As part of this change, the serialization format of :class:`_engine.Row` has been modified slightly, however rows which were pickled with previous SQLAlchemy 2.0 releases will be recognized within the new format. Pull request courtesy J. Nick Koston. * Improved row processing performance for "binary" datatypes by making the "bytes" handler conditional on a per driver basis. As a result, the "bytes" result handler has been removed for nearly all drivers other than psycopg2, all of which in modern forms support returning Python "bytes" directly. Pull request courtesy J. Nick Koston. * Additional refactorings inside of :class:`_engine.Row` to improve performance by Federico Caselli. .. changelog:: ``` ### 2.0.10 ``` :released: April 21, 2023 .. change:: :tags: bug, typing :tickets: 9650 Added typing information for recently added operators :meth:`.ColumnOperators.icontains`, :meth:`.ColumnOperators.istartswith`, :meth:`.ColumnOperators.iendswith`, and bitwise operators :meth:`.ColumnOperators.bitwise_and`, :meth:`.ColumnOperators.bitwise_or`, :meth:`.ColumnOperators.bitwise_xor`, :meth:`.ColumnOperators.bitwise_not`, :meth:`.ColumnOperators.bitwise_lshift` :meth:`.ColumnOperators.bitwise_rshift`. Pull request courtesy Martijn Pieters. .. change:: :tags: bug, oracle Fixed issue where the :class:`_sqltypes.Uuid` datatype could not be used in an INSERT..RETURNING clause with the Oracle dialect. .. change:: :tags: usecase, engine :tickets: 9613 Added :func:`_sa.create_pool_from_url` and :func:`_asyncio.create_async_pool_from_url` to create a :class:`_pool.Pool` instance from an input url passed as string or :class:`_sa.URL`. .. change:: :tags: bug, engine :tickets: 9618, 9603 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. .. seealso:: :ref:`engine_insertmanyvalues_returning_order` .. change:: :tags: bug, mssql :tickets: 9618, 9603 Restored the :term:`insertmanyvalues` feature for Microsoft SQL Server. This feature was disabled in version 2.0.9 due to an apparent reliance on the ordering of RETURNING that is not guaranteed. The architecture of the "insertmanyvalues" feature has been reworked to accommodate for specific organizations of INSERT statements and result row handling that can guarantee the correspondence of returned rows to input records. .. seealso:: :ref:`engine_insertmanyvalues_returning_order` .. change:: :tags: usecase, postgresql :tickets: 9608 Added ``prepared_statement_name_func`` connection argument option in the asyncpg dialect. This option allows passing a callable used to customize the name of the prepared statement that will be created by the driver when executing queries. Pull request courtesy Pavel Sirotkin. .. seealso:: :ref:`asyncpg_prepared_statement_name` .. change:: :tags: typing, bug Updates to the codebase to pass typing with Mypy 1.2.0. .. change:: :tags: bug, typing :tickets: 9669 Fixed typing issue where :meth:`_orm.PropComparator.and_` expressions would not be correctly typed inside of loader options such as :func:`_orm.selectinload`. .. change:: :tags: bug, orm :tickets: 9625 Fixed issue where the :meth:`_orm.declared_attr.directive` modifier was not correctly honored for subclasses when applied to the ``__mapper_args__`` special method name, as opposed to direct use of :class:`_orm.declared_attr`. The two constructs should have identical runtime behaviors. .. change:: :tags: bug, postgresql :tickets: 9611 Restored the :paramref:`_postgresql.ENUM.name` parameter as optional in the signature for :class:`_postgresql.ENUM`, as this is chosen automatically from a given pep-435 ``Enum`` type. .. change:: :tags: bug, postgresql :tickets: 9621 Fixed issue where the comparison for :class:`_postgresql.ENUM` against a plain string would cast that right-hand side type as VARCHAR, which due to more explicit casting added to dialects such as asyncpg would produce a PostgreSQL type mismatch error. .. change:: :tags: bug, orm :tickets: 9635 Made an improvement to the :func:`_orm.with_loader_criteria` loader option to allow it to be indicated in the :meth:`.Executable.options` method of a top-level statement that is not itself an ORM statement. Examples include :func:`_sql.select` that's embedded in compound statements such as :func:`_sql.union`, within an :meth:`_dml.Insert.from_select` construct, as well as within CTE expressions that are not ORM related at the top level. .. change:: :tags: bug, orm :tickets: 9685 Fixed bug in ORM bulk insert feature where additional unnecessary columns would be rendered in the INSERT statement if RETURNING of individual columns were requested. .. change:: :tags: bug, postgresql :tickets: 9615 Fixed issue that prevented reflection of expression based indexes with long expressions in PostgreSQL. The expression where erroneously truncated to the identifier length (that's 63 bytes by default). .. change:: :tags: usecase, postgresql :tickets: 9509 Add missing :meth:`_postgresql.Range.intersection` method. Pull request courtesy Yurii Karabas. .. change:: :tags: bug, orm :tickets: 9628 Fixed bug in ORM Declarative Dataclasses where the :func:`_orm.query_expression` and :func:`_orm.column_property` constructs, which are documented as read-only constructs in the context of a Declarative mapping, could not be used with a :class:`_orm.MappedAsDataclass` class without adding ``init=False``, which in the case of :func:`_orm.query_expression` was not possible as no ``init`` parameter was included. These constructs have been modified from a dataclass perspective to be assumed to be "read only", setting ``init=False`` by default and no longer including them in the pep-681 constructor. The dataclass parameters for :func:`_orm.column_property` ``init``, ``default``, ``default_factory``, ``kw_only`` are now deprecated; these fields don't apply to :func:`_orm.column_property` as used in a Declarative dataclasses configuration where the construct would be read-only. Also added read-specific parameter :paramref:`_orm.query_expression.compare` to :func:`_orm.query_expression`; :paramref:`_orm.query_expression.repr` was already present. .. change:: :tags: bug, orm Added missing :paramref:`_orm.mapped_column.active_history` parameter to :func:`_orm.mapped_column` construct. .. changelog:: ``` ### 2.0.9 ``` :released: April 5, 2023 .. change:: :tags: bug, mssql :tickets: 9603 The SQLAlchemy "insertmanyvalues" feature which allows fast INSERT of many rows while also supporting RETURNING is temporarily disabled for SQL Server. As the unit of work currently relies upon this feature such that it matches existing ORM objects to returned primary key identities, this particular use pattern does not work with SQL Server in all cases as the order of rows returned by "OUTPUT inserted" may not always match the order in which the tuples were sent, leading to the ORM making the wrong decisions about these objects in subsequent operations. The feature will be re-enabled in an upcoming release and will again take effect for multi-row INSERT statements, however the unit-of-work's use of the feature will be disabled, possibly for all dialects, unless ORM-mapped tables also include a "sentinel" column so that the returned rows can be referenced back to the original data passed in. .. change:: :tags: bug, mariadb :tickets: 9588 Added ``row_number`` as reserved word in MariaDb. .. change:: :tags: bug, mssql :tickets: 9586 Changed the bulk INSERT strategy used for SQL Server "executemany" with pyodbc when ``fast_executemany`` is set to ``True`` by using ``fast_executemany`` / ``cursor.executemany()`` for bulk INSERT that does not include RETURNING, restoring the same behavior as was used in SQLAlchemy 1.4 when this parameter is set. New performance details from end users have shown that ``fast_executemany`` is still much faster for very large datasets as it uses ODBC commands that can receive all rows in a single round trip, allowing for much larger datasizes than the batches that can be sent by "insertmanyvalues" as was implemented for SQL Server. While this change was made such that "insertmanyvalues" continued to be used for INSERT that includes RETURNING, as well as if ``fast_executemany`` were not set, due to :ticket:`9603`, the "insertmanyvalues" strategy has been disabled for SQL Server across the board in any case. .. changelog:: ``` ### 2.0.8 ``` :released: March 31, 2023 .. change:: :tags: bug, orm :tickets: 9553 Fixed issue in ORM Annotated Declarative where using a recursive type (e.g. using a nested Dict type) would result in a recursion overflow in the ORM's annotation resolution logic, even if this datatype were not necessary to map the column. .. change:: :tags: bug, examples Fixed issue in "versioned history" example where using a declarative base that is derived from :class:`_orm.DeclarativeBase` would fail to be mapped. Additionally, repaired the given test suite so that the documented instructions for running the example using Python unittest now work again. .. change:: :tags: bug, orm :tickets: 9550 Fixed issue where the :func:`_orm.mapped_column` construct would raise an internal error if used on a Declarative mixin and included the :paramref:`_orm.mapped_column.deferred` parameter. .. change:: :tags: bug, mysql :tickets: 9544 Fixed issue where string datatypes such as :class:`_sqltypes.CHAR`, :class:`_sqltypes.VARCHAR`, :class:`_sqltypes.TEXT`, as well as binary :class:`_sqltypes.BLOB`, could not be produced with an explicit length of zero, which has special meaning for MySQL. Pull request courtesy J. Nick Koston. .. change:: :tags: bug, orm :tickets: 9537 Expanded the warning emitted when a plain :func:`_sql.column` object is present in a Declarative mapping to include any arbitrary SQL expression that is not declared within an appropriate property type such as :func:`_orm.column_property`, :func:`_orm.deferred`, etc. These attributes are otherwise not mapped at all and remain unchanged within the class dictionary. As it seems likely that such an expression is usually not what's intended, this case now warns for all such otherwise ignored expressions, rather than just the :func:`_sql.column` case. .. change:: :tags: bug, orm :tickets: 9519 Fixed regression where accessing the expression value of a hybrid property on a class that was either unmapped or not-yet-mapped (such as calling upon it within a :func:`_orm.declared_attr` method) would raise an internal error, as an internal fetch for the parent class' mapper would fail and an instruction for this failure to be ignored were inadvertently removed in 2.0. .. change:: :tags: bug, orm :tickets: 9350 Fields that are declared on Declarative Mixins and then combined with classes that make use of :class:`_orm.MappedAsDataclass`, where those mixin fields are not themselves part of a dataclass, now emit a deprecation warning as these fields will be ignored in a future release, as Python dataclasses behavior is to ignore these fields. Type checkers will not see these fields under pep-681. .. seealso:: :ref:`error_dcmx` - background on rationale :ref:`orm_declarative_dc_mixins` .. change:: :tags: bug, postgresql :tickets: 9511 Fixed critical regression in PostgreSQL dialects such as asyncpg which rely upon explicit casts in SQL in order for datatypes to be passed to the driver correctly, where a :class:`.String` datatype would be cast along with the exact column length being compared, leading to implicit truncation when comparing a ``VARCHAR`` of a smaller length to a string of greater length regardless of operator in use (e.g. LIKE, MATCH, etc.). The PostgreSQL dialect now omits the length from ``VARCHAR`` when rendering these casts. .. change:: :tags: bug, util :tickets: 9487 Implemented missing methods ``copy`` and ``pop`` in OrderedSet class. .. change:: :tags: bug, typing :tickets: 9536 Fixed typing for :func:`_orm.deferred` and :func:`_orm.query_expression` to work correctly with 2.0 style mappings. .. change:: :tags: bug, orm :tickets: 9526 Fixed issue where the :meth:`_sql.BindParameter.render_literal_execute` method would fail when called on a parameter that also had ORM annotations associated with it. In practice, this would be observed as a failure of SQL compilation when using some combinations of a dialect that uses "FETCH FIRST" such as Oracle along with a :class:`_sql.Select` construct that uses :meth:`_sql.Select.limit`, within some ORM contexts, including if the statement were embedded within a relationship primaryjoin expression. .. change:: :tags: usecase, orm :tickets: 9563 Exceptions such as ``TypeError`` and ``ValueError`` raised by Python dataclasses when making use of the :class:`_orm.MappedAsDataclass` mixin class or :meth:`_orm.registry.mapped_as_dataclass` decorator are now wrapped within an :class:`.InvalidRequestError` wrapper along with informative context about the error message, referring to the Python dataclasses documentation as the authoritative source of background information on the cause of the exception. .. seealso:: :ref:`error_dcte` .. change:: :tags: bug, orm :tickets: 9549 Towards maintaining consistency with unit-of-work changes made for :ticket:`5984` and :ticket:`8862`, both of which disable "lazy='raise'" handling within :class:`_orm.Session` processes that aren't triggered by attribute access, the :meth:`_orm.Session.delete` method will now also disable "lazy='raise'" handling when it traverses relationship paths in order to process the "delete" and "delete-orphan" cascade rules. Previously, there was no easy way to generically call :meth:`_orm.Session.delete` on an object that had "lazy='raise'" set up such that only the necessary relationships would be loaded. As "lazy='raise'" is primarily intended to catch SQL loading that emits on attribute access, :meth:`_orm.Session.delete` is now made to behave like other :class:`_orm.Session` methods including :meth:`_orm.Session.merge` as well as :meth:`_orm.Session.flush` along with autoflush. .. change:: :tags: bug, orm :tickets: 9564 Fixed issue where an annotation-only :class:`_orm.Mapped` directive could not be used in a Declarative mixin class, without that attribute attempting to take effect for single- or joined-inheritance subclasses of mapped classes that had already mapped that attribute on a superclass, producing conflicting column errors and/or warnings. .. change:: :tags: bug, orm, typing :tickets: 9514 Properly type :paramref:`_dml.Insert.from_select.names` to accept a list of string or columns or mapped attributes. .. changelog:: ``` ### 2.0.7 ``` :released: March 18, 2023 .. change:: :tags: usecase, postgresql :tickets: 9416 Added new PostgreSQL type :class:`_postgresql.CITEXT`. Pull request courtesy Julian David Rath. .. change:: :tags: bug, typing :tickets: 9502 Fixed typing issue where :func:`_orm.composite` would not allow an arbitrary callable as the source of the composite class. .. change:: :tags: usecase, postgresql :tickets: 9442 Modifications to the base PostgreSQL dialect to allow for better integration with the sqlalchemy-redshift third party dialect for SQLAlchemy 2.0. Pull request courtesy matthewgdv. .. changelog:: ``` ### 2.0.6 ``` :released: March 13, 2023 .. change:: :tags: bug, sql, regression :tickets: 9461 Fixed regression where the fix for :ticket:`8098`, which was released in the 1.4 series and provided a layer of concurrency-safe checks for the lambda SQL API, included additional fixes in the patch that failed to be applied to the main branch. These additional fixes have been applied. .. change:: :tags: bug, typing :tickets: 9451 Fixed typing issue where :meth:`.ColumnElement.cast` did not allow a :class:`.TypeEngine` argument independent of the type of the :class:`.ColumnElement` itself, which is the purpose of :meth:`.ColumnElement.cast`. .. change:: :tags: bug, orm :tickets: 9460 Fixed bug where the "active history" feature was not fully implemented for composite attributes, making it impossible to receive events that included the "old" value. This seems to have been the case with older SQLAlchemy versions as well, where "active_history" would be propagated to the underlying column-based attributes, but an event handler listening to the composite attribute itself would not be given the "old" value being replaced, even if the composite() were set up with active_history=True. Additionally, fixed a regression that's local to 2.0 which disallowed active_history on composite from being assigned to the impl with ``attr.impl.active_history=True``. .. change:: :tags: bug, oracle :tickets: 9459 Fixed reflection bug where Oracle "name normalize" would not work correctly for reflection of symbols that are in the "PUBLIC" schema, such as synonyms, meaning the PUBLIC name could not be indicated as lower case on the Python side for the :paramref:`_schema.Table.schema` argument. Using uppercase "PUBLIC" would work, but would then lead to awkward SQL queries including a quoted ``"PUBLIC"`` name as well as indexing the table under uppercase "PUBLIC", which was inconsistent. .. change:: :tags: bug, typing Fixed issues to allow typing tests to pass under Mypy 1.1.1. .. change:: :tags: bug, sql :tickets: 9440 Fixed regression where the :func:`_sql.select` construct would not be able to render if it were given no columns and then used in the context of an EXISTS, raising an internal exception instead. While an empty "SELECT" is not typically valid SQL, in the context of EXISTS databases such as PostgreSQL allow it, and in any case the condition now no longer raises an internal exception. .. change:: :tags: bug, orm :tickets: 9418 Fixed regression involving pickling of Python rows between the cython and pure Python implementations of :class:`.Row`, which occurred as part of refactoring code for version 2.0 with typing. A particular constant were turned into a string based ``Enum`` for the pure Python version of :class:`.Row` whereas the cython version continued to use an integer constant, leading to deserialization failures. .. changelog:: ``` ### 2.0.5.post1 ``` :released: March 5, 2023 .. change:: :tags: bug, orm :tickets: 9418 Added constructor arguments to the built-in mapping collection types including :class:`.KeyFuncDict`, :func:`_orm.attribute_keyed_dict`, :func:`_orm.column_keyed_dict` so that these dictionary types may be constructed in place given the data up front; this provides further compatibility with tools such as Python dataclasses ``.asdict()`` which relies upon invoking these classes directly as ordinary dictionary classes. .. change:: :tags: bug, orm, regression :tickets: 9424 Fixed multiple regressions due to :ticket:`8372`, involving :func:`_orm.attribute_mapped_collection` (now called :func:`_orm.attribute_keyed_dict`). First, the collection was no longer usable with "key" attributes that were not themselves ordinary mapped attributes; attributes linked to descriptors and/or association proxy attributes have been fixed. Second, if an event or other operation needed access to the "key" in order to populate the dictionary from an mapped attribute that was not loaded, this also would raise an error inappropriately, rather than trying to load the attribute as was the behavior in 1.4. This is also fixed. For both cases, the behavior of :ticket:`8372` has been expanded. :ticket:`8372` introduced an error that raises when the derived key that would be used as a mapped dictionary key is effectively unassigned. In this change, a warning only is emitted if the effective value of the ".key" attribute is ``None``, where it cannot be unambiguously determined if this ``None`` was intentional or not. ``None`` will be not supported as mapped collection dictionary keys going forward (as it typically refers to NULL which means "unknown"). Setting :paramref:`_orm.attribute_keyed_dict.ignore_unpopulated_attribute` will now cause such ``None`` keys to be ignored as well. .. change:: :tags: engine, performance :tickets: 9343 A small optimization to the Cython implementation of :class:`.Result` using a cdef for a particular int value to avoid Python overhead. Pull request courtesy Matus Valo. .. change:: :tags: bug, mssql :tickets: 9414 Fixed issue in the new :class:`.Uuid` datatype which prevented it from working with the pymssql driver. As pymssql seems to be maintained again, restored testing support for pymssql. .. change:: :tags: bug, mssql Tweaked the pymssql dialect to take better advantage of RETURNING for INSERT statements in order to retrieve last inserted primary key values, in the same way as occurs for the mssql+pyodbc dialect right now. .. change:: :tags: bug, orm Identified that the ``sqlite`` and ``mssql+pyodbc`` dialects are now compatible with the SQLAlchemy ORM's "versioned rows" feature, since SQLAlchemy now computes rowcount for a RETURNING statement in this specific case by counting the rows returned, rather than relying upon ``cursor.rowcount``. In particular, the ORM versioned rows use case (documented at :ref:`mapper_version_counter`) should now be fully supported with the SQL Server pyodbc dialect. .. change:: :tags: bug, postgresql :tickets: 9349 Fixed issue in PostgreSQL :class:`_postgresql.ExcludeConstraint` where literal values were being compiled as bound parameters and not direct inline values as is required for DDL. .. change:: :tags: bug, typing Fixed bug where the :meth:`_engine.Connection.scalars` method was not typed as allowing a multiple-parameters list, which is now supported using insertmanyvalues operations. .. change:: :tags: bug, typing :tickets: 9376 Improved typing for the mapping passed to :meth:`.Insert.values` and :meth:`.Update.values` to be more open-ended about collection type, by indicating read-only ``Mapping`` instead of writeable ``Dict`` which would error out on too limited of a key type. .. change:: :tags: schema Validate that when provided the :paramref:`_schema.MetaData.schema` argument of :class:`_schema.MetaData` is a string. .. change:: :tags: typing, usecase :tickets: 9338 Exported the type returned by :meth:`_orm.scoped_session.query_property` using a new public type :class:`.orm.QueryPropertyDescriptor`. .. change:: :tags: bug, mysql, postgresql :tickets: 5648 The support for pool ping listeners to receive exception events via the :meth:`.DialectEvents.handle_error` event added in 2.0.0b1 for :ticket:`5648` failed to take into account dialect-specific ping routines such as that of MySQL and PostgreSQL. The dialect feature has been reworked so that all dialects participate within event handling. Additionally, a new boolean element :attr:`.ExceptionContext.is_pre_ping` is added which identifies if this operation is occurring within the pre-ping operation. For this release, third party dialects which implement a custom :meth:`_engine.Dialect.do_ping` method can opt in to the newly improved behavior by having their method no longer catch exceptions or check exceptions for "is_disconnect", instead just propagating all exceptions outwards. Checking the exception for "is_disconnect" is now done by an enclosing method on the default dialect, which ensures that the event hook is invoked for all exception scenarios before testing the exception as a "disconnect" exception. If an existing ``do_ping()`` method continues to catch exceptions and check "is_disconnect", it will continue to work as it did previously, but ``handle_error`` hooks will not have access to the exception if it isn't propagated outwards. .. change:: :tags: bug, ext :tickets: 9367 Fixed issue in automap where calling :meth:`_automap.AutomapBase.prepare` from a specific mapped class, rather than from the :class:`_automap.AutomapBase` directly, would not use the correct base class when automap detected new tables, instead using the given class, leading to mappers trying to configure inheritance. While one should normally call :meth:`_automap.AutomapBase.prepare` from the base in any case, it shouldn't misbehave that badly when called from a subclass. .. change:: :tags: bug, sqlite, regression :tickets: 9379 Fixed regression for SQLite connections where use of the ``deterministic`` parameter when establishing database functions would fail for older SQLite versions, those prior to version 3.8.3. The version checking logic has been improved to accommodate for this case. .. change:: :tags: bug, typing :tickets: 9391 Added missing init overload to the :class:`_types.Numeric` type object so that pep-484 type checkers may properly resolve the complete type, deriving from the :paramref:`_types.Numeric.asdecimal` parameter whether ``Decimal`` or ``float`` objects will be represented. .. change:: :tags: bug, typing :tickets: 9398 Fixed typing bug where :meth:`_sql.Select.from_statement` would not accept :func:`_sql.text` or :class:`.TextualSelect` objects as a valid type. Additionally repaired the :class:`.TextClause.columns` method to have a return type, which was missing. .. change:: :tags: bug, orm declarative :tickets: 9332 Fixed issue where new :paramref:`_orm.mapped_column.use_existing_column` feature would not work if the two same-named columns were mapped under attribute names that were differently-named from an explicit name given to the column itself. The attribute names can now be differently named when using this parameter. .. change:: :tags: bug, orm :tickets: 9373 Added support for the :paramref:`_orm.Mapper.polymorphic_load` parameter to be applied to each mapper in an inheritance hierarchy more than one level deep, allowing columns to load for all classes in the hierarchy that indicate ``"selectin"`` using a single statement, rather than ignoring elements on those intermediary classes that nonetheless indicate they also would participate in ``"selectin"`` loading and were not part of the base-most SELECT statement. .. change:: :tags: bug, orm :tickets: 8853, 9335 Continued the fix for :ticket:`8853`, allowing the :class:`_orm.Mapped` name to be fully qualified regardless of whether or not ``from __annotations__ import future`` were present. This issue first fixed in 2.0.0b3 confirmed that this case worked via the test suite, however the test suite apparently was not testing the behavior for the name :class:`_orm.Mapped` not being locally present at all; string resolution has been updated to ensure the :class:`_orm.Mapped` symbol is locatable as applies to how the ORM uses these functions. .. change:: :tags: bug, typing :tickets: 9340 Fixed typing issue where :func:`_orm.with_polymorphic` would not record the class type correctly. .. change:: :tags: bug, ext, regression :tickets: 9380 Fixed regression caused by typing added to ``sqlalchemy.ext.mutable`` for :ticket:`8667`, where the semantics of the ``.pop()`` method changed such that the method was non-working. Pull request courtesy Nils Philippsen. .. change:: :tags: bug, sql, regression :tickets: 9390 Restore the :func:`.nullslast` and :func:`.nullsfirst` legacy functions into the ``sqlalchemy`` import namespace. Previously, the newer :func:`.nulls_last` and :func:`.nulls_first` functions were available, but the legacy ones were inadvertently removed. .. change:: :tags: bug, postgresql :tickets: 9401 Fixed issue where the PostgreSQL :class:`_postgresql.ExcludeConstraint` construct would not be copyable within operations such as :meth:`_schema.Table.to_metadata` as well as within some Alembic scenarios, if the constraint contained textual expression elements. .. change:: :tags: bug, engine :tickets: 9423 Fixed bug where :class:`_engine.Row` objects could not be reliably unpickled across processes due to an accidental reliance on an unstable hash value. .. changelog:: ``` ### 2.0.4 ``` :released: February 17, 2023 .. change:: :tags: bug, orm, regression :tickets: 9273 Fixed regression introduced in version 2.0.2 due to :ticket:`9217` where using DML RETURNING statements, as well as :meth:`_sql.Select.from_statement` constructs as was "fixed" in :ticket:`9217`, in conjunction with ORM mapped classes that used expressions such as with :func:`_orm.column_property`, would lead to an internal error within Core where it would attempt to match the expression by name. The fix repairs the Core issue, and also adjusts the fix in :ticket:`9217` to not take effect for the DML RETURNING use case, where it adds unnecessary overhead. .. change:: :tags: usecase, typing :tickets: 9321 Improved the typing support for the :ref:`hybrids_toplevel` extension, updated all documentation to use ORM Annotated Declarative mappings, and added a new modifier called :attr:`.hybrid_property.inplace`. This modifier provides a way to alter the state of a :class:`.hybrid_property` **in place**, which is essentially what very early versions of hybrids did, before SQLAlchemy version 1.2.0 :ticket:`3912` changed this to remove in-place mutation. This in-place mutation is now restored on an **opt-in** basis to allow a single hybrid to have multiple methods set up, without the need to name all the methods the same and without the need to carefully "chain" differently-named methods in order to maintain the composition. Typing tools such as Mypy and Pyright do not allow same-named methods on a class, so with this change a succinct method of setting up hybrids with typing support is restored. .. seealso:: :ref:`hybrid_pep484_naming` .. change:: :tags: bug, orm Marked the internal ``EvaluatorCompiler`` module as private to the ORM, and renamed it to ``_EvaluatorCompiler``. For users that may have been relying upon this, the name ``EvaluatorCompiler`` is still present, however this use is not supported and will be removed in a future release. .. change:: :tags: orm, usecase :tickets: 9297 To accommodate a change in column ordering used by ORM Declarative in SQLAlchemy 2.0, a new parameter :paramref:`_orm.mapped_column.sort_order` has been added that can be used to control the order of the columns defined in the table by the ORM, for common use cases such as mixins with primary key columns that should appear first in tables. The change notes at :ref:`change_9297` illustrate the default change in ordering behavior (which is part of all SQLAlchemy 2.0 releases) as well as use of the :paramref:`_orm.mapped_column.sort_order` to control column ordering when using mixins and multiple classes (new in 2.0.4). .. seealso:: :ref:`change_9297` .. change:: :tags: sql :tickets: 9277 Added public property :attr:`_schema.Table.autoincrement_column` that returns the column identified as autoincrementing in the column. .. change:: :tags: oracle, bug :tickets: 9295 Adjusted the behavior of the ``thick_mode`` parameter for the :ref:`oracledb` dialect to correctly accept ``False`` as a value. Previously, only ``None`` would indicate that thick mode should be disabled. .. change:: :tags: usecase, orm :tickets: 9298 The :meth:`_orm.Session.refresh` method will now immediately load a relationship-bound attribute that is explicitly named within the :paramref:`_orm.Session.refresh.attribute_names` collection even if it is currently linked to the "select" loader, which normally is a "lazy" loader that does not fire off during a refresh. The "lazy loader" strategy will now detect that the operation is specifically a user-initiated :meth:`_orm.Session.refresh` operation which named this attribute explicitly, and will then call upon the "immediateload" strategy to actually emit SQL to load the attribute. This should be helpful in particular for some asyncio situations where the loading of an unloaded lazy-loaded attribute must be forced, without using the actual lazy-loading attribute pattern not supported in asyncio. .. change:: :tags: bug, sql :tickets: 9313 Fixed issue where element types of a tuple value would be hardcoded to take on the types from a compared-to tuple, when the comparison were using the :meth:`.ColumnOperators.in_` operator. This was inconsistent with the usual way that types are determined for a binary expression, which is that the actual element type on the right side is considered first before applying the left-hand-side type. .. change:: :tags: usecase, orm declarative :tickets: 9266 Added new parameter ``dataclasses_callable`` to both the :class:`_orm.MappedAsDataclass` class as well as the :meth:`_orm.registry.mapped_as_dataclass` method which allows an alternative callable to Python ``dataclasses.dataclass`` to be used in order to produce dataclasses. The use case here is to drop in Pydantic's dataclass function instead. Adjustments have been made to the mixin support added for :ticket:`9179` in version 2.0.1 so that the ``__annotations__`` collection of the mixin is rewritten to not include the :class:`_orm.Mapped` container, in the same way as occurs with mapped classes, so that the Pydantic dataclasses constructor is not exposed to unknown types. .. seealso:: :ref:`dataclasses_pydantic` .. changelog:: ``` ### 2.0.3 ``` :released: February 9, 2023 .. change:: :tags: typing, bug :tickets: 9254 Remove ``typing.Self`` workaround, now using :pep:`673` for most methods that return ``Self``. As a consequence of this change ``mypy>=1.0.0`` is now required to type check SQLAlchemy code. Pull request courtesy Yurii Karabas. .. change:: :tags: bug, sql, regression :tickets: 9271 Fixed critical regression in SQL expression formulation in the 2.0 series due to :ticket:`7744` which improved support for SQL expressions that contained many elements against the same operator repeatedly; parenthesis grouping would be lost with expression elements beyond the first two elements. .. changelog:: ``` ### 2.0.2 ``` :released: February 6, 2023 .. change:: :tags: bug, orm declarative :tickets: 9249 Fixed regression caused by the fix for :ticket:`9171`, which itself was fixing a regression, involving the mechanics of ``__init__()`` on classes that extend from :class:`_orm.DeclarativeBase`. The change made it such that ``__init__()`` was applied to the user-defined base if there were no ``__init__()`` method directly on the class. This has been adjusted so that ``__init__()`` is applied only if no other class in the hierarchy of the user-defined base has an ``__init__()`` method. This again allows user-defined base classes based on :class:`_orm.DeclarativeBase` to include mixins that themselves include a custom ``__init__()`` method. .. change:: :tags: bug, mysql, regression :tickets: 9251 Fixed regression caused by issue :ticket:`9058` which adjusted the MySQL dialect's ``has_table()`` to again use "DESCRIBE", where the specific error code raised by MySQL version 8 when using a non-existent schema name was unexpected and failed to be interpreted as a boolean result. .. change:: :tags: bug, sqlite :tickets: 9251 Fixed the SQLite dialect's ``has_table()`` function to correctly report False for queries that include a non-None schema name for a schema that doesn't exist; previously, a database error was raised. .. change:: :tags: bug, orm declarative :tickets: 9226 Fixed issue in ORM Declarative Dataclass mappings related to newly added support for mixins added in 2.0.1 via :ticket:`9179`, where a combination of using mixins plus ORM inheritance would mis-classify fields in some cases leading to field-level dataclass arguments such as ``init=False`` being lost. .. change:: :tags: bug, orm, ression :tickets: 9232 Fixed obscure ORM inheritance issue caused by :ticket:`8705` where some scenarios of inheriting mappers that indicated groups of columns from the local table and the inheriting table together under a :func:`_orm.column_property` would nonetheless warn that properties of the same name were being combined implicitly. .. change:: :tags: orm, bug, regression :tickets: 9228 Fixed regression where using the :paramref:`_orm.Mapper.version_id_col` feature with a regular Python-side incrementing column would fail to work for SQLite and other databases that don't support "rowcount" with "RETURNING", as "RETURNING" would be assumed for such columns even though that's not what actually takes place. .. change:: :tags: bug, orm declarative :tickets: 9240 Repaired ORM Declarative mappings to allow for the :paramref:`_orm.Mapper.primary_key` parameter to be specified within ``__mapper_args__`` when using :func:`_orm.mapped_column`. Despite this usage being directly in the 2.0 documentation, the :class:`_orm.Mapper` was not accepting the :func:`_orm.mapped_column` construct in this context. Ths feature was already working for the :paramref:`_orm.Mapper.version_id_col` and :paramref:`_orm.Mapper.polymorphic_on` parameters. As part of this change, the ``__mapper_args__`` attribute may be specified without using :func:`_orm.declared_attr` on a non-mapped mixin class, including a ``"primary_key"`` entry that refers to :class:`_schema.Column` or :func:`_orm.mapped_column` objects locally present on the mixin; Declarative will also translate these columns into the correct ones for a particular mapped class. This again was working already for the :paramref:`_orm.Mapper.version_id_col` and :paramref:`_orm.Mapper.polymorphic_on` parameters. Additionally, elements within ``"primary_key"`` may be indicated as string names of existing mapped properties. .. change:: :tags: usecase, sql :tickets: 8780 Added a full suite of new SQL bitwise operators, for performing database-side bitwise expressions on appropriate data values such as integers, bit-strings, and similar. Pull request courtesy Yegor Statkevich. .. seealso:: :ref:`operators_bitwise` .. change:: :tags: bug, orm declarative :tickets: 9211 An explicit error is raised if a mapping attempts to mix the use of :class:`_orm.MappedAsDataclass` with :meth:`_orm.registry.mapped_as_dataclass` within the same class hierarchy, as this produces issues with the dataclass function being applied at the wrong time to the mapped class, leading to errors during the mapping process. .. change:: :tags: bug, orm, regression :tickets: 9217 Fixed regression when using :meth:`_sql.Select.from_statement` in an ORM context, where matching of columns to SQL labels based on name alone was disabled for ORM-statements that weren't fully textual. This would prevent arbitrary SQL expressions with column-name labels from matching up to the entity to be loaded, which previously would work within the 1.4 and previous series, so the previous behavior has been restored. .. change:: :tags: bug, asyncio :tickets: 9237 Repaired a regression caused by the fix for :ticket:`8419` which caused asyncpg connections to be reset (i.e. transaction ``rollback()`` called) and returned to the pool normally in the case that the connection were not explicitly returned to the connection pool and was instead being intercepted by Python garbage collection, which would fail if the garbage collection operation were being called outside of the asyncio event loop, leading to a large amount of stack trace activity dumped into logging and standard output. The correct behavior is restored, which is that all asyncio connections that are garbage collected due to not being explicitly returned to the connection pool are detached from the pool and discarded, along with a warning, rather than being returned the pool, as they cannot be reliably reset. In the case of asyncpg connections, the asyncpg-specific ``terminate()`` method will be used to end the connection more gracefully within this process as opposed to just dropping it. This change includes a small behavioral change that is hoped to be useful for debugging asyncio applications, where the warning that's emitted in the case of asyncio connections being unexpectedly garbage collected has been made slightly more aggressive by moving it outside of a ``try/except`` block and into a ``finally:`` block, where it will emit unconditionally regardless of whether the detach/termination operation succeeded or not. It will also have the effect that applications or test suites which promote Python warnings to exceptions will see this as a full exception raise, whereas previously it was not possible for this warning to actually propagate as an exception. Applications and test suites which need to tolerate this warning in the interim should adjust the Python warnings filter to allow these warnings to not raise. The behavior for tradition