add bind casts for BYTEA on asyncpg

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.

Fixes: #9739
Change-Id: I57370d269ea757f263c1f3a16c324ceae76fd4e8

4 files changed, 81 insertions, 0 deletions

diff --git a/doc/build/changelog/unreleased_20/9739.rst b/doc/build/changelog/unreleased_20/9739.rst
new file mode 100644
index 000000000..987d69ce2
--- /dev/null
+++ b/doc/build/changelog/unreleased_20/9739.rst
@@ -0,0 +1,9 @@
+.. 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.
diff --git a/lib/sqlalchemy/dialects/postgresql/asyncpg.py b/lib/sqlalchemy/dialects/postgresql/asyncpg.py
index c879205e4..3f33600f9 100644
--- a/lib/sqlalchemy/dialects/postgresql/asyncpg.py
+++ b/lib/sqlalchemy/dialects/postgresql/asyncpg.py
@@ -182,6 +182,7 @@ from .base import PGExecutionContext
 from .base import PGIdentifierPreparer
 from .base import REGCLASS
 from .base import REGCONFIG
+from .types import BYTEA
 from ... import exc
 from ... import pool
 from ... import util
@@ -212,6 +213,10 @@ class AsyncpgTime(sqltypes.Time):
     render_bind_cast = True
 
 
+class AsyncpgByteA(BYTEA):
+    render_bind_cast = True
+
+
 class AsyncpgDate(sqltypes.Date):
     render_bind_cast = True
 
@@ -986,6 +991,7 @@ class PGDialect_asyncpg(PGDialect):
             sqltypes.Numeric: AsyncpgNumeric,
             sqltypes.Float: AsyncpgFloat,
             sqltypes.JSON: AsyncpgJSON,
+            sqltypes.LargeBinary: AsyncpgByteA,
             json.JSONB: AsyncpgJSONB,
             sqltypes.JSON.JSONPathType: AsyncpgJSONPathType,
             sqltypes.JSON.JSONIndexType: AsyncpgJSONIndexType,
diff --git a/lib/sqlalchemy/testing/suite/test_insert.py b/lib/sqlalchemy/testing/suite/test_insert.py
index d49eb3284..391503422 100644
--- a/lib/sqlalchemy/testing/suite/test_insert.py
+++ b/lib/sqlalchemy/testing/suite/test_insert.py
@@ -430,6 +430,8 @@ class ReturningTest(fixtures.TablesTest):
         this tests insertmanyvalues as well as decimal / floating point
         RETURNING types
 
+        TODO: this might be better in suite/test_types?
+
         """
 
         t = Table(
diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py
index 72f1e8c10..ba2dda9ef 100644
--- a/lib/sqlalchemy/testing/suite/test_types.py
+++ b/lib/sqlalchemy/testing/suite/test_types.py
@@ -26,6 +26,7 @@ from ... import cast
 from ... import Date
 from ... import DateTime
 from ... import Float
+from ... import Identity
 from ... import Integer
 from ... import JSON
 from ... import literal
@@ -45,6 +46,7 @@ from ... import Unicode
 from ... import UnicodeText
 from ... import UUID
 from ... import Uuid
+from ...dialects.postgresql import BYTEA
 from ...orm import declarative_base
 from ...orm import Session
 from ...sql.sqltypes import LargeBinary
@@ -313,6 +315,68 @@ class BinaryTest(_LiteralRoundTripFixture, fixtures.TablesTest):
         row = connection.execute(select(binary_table.c.pickle_data)).first()
         eq_(row, ({"foo": [1, 2, 3], "bar": "bat"},))
 
+    @testing.combinations(
+        (
+            LargeBinary(),
+            b"this is binary",
+        ),
+        (LargeBinary(), b"7\xe7\x9f"),
+        (BYTEA(), b"7\xe7\x9f", testing.only_on("postgresql")),
+        argnames="type_,value",
+    )
+    @testing.variation("sort_by_parameter_order", [True, False])
+    @testing.variation("multiple_rows", [True, False])
+    @testing.requires.insert_returning
+    def test_imv_returning(
+        self,
+        connection,
+        metadata,
+        sort_by_parameter_order,
+        type_,
+        value,
+        multiple_rows,
+    ):
+        """test #9739 (similar to #9701).
+
+        this tests insertmanyvalues as well as binary
+        RETURNING types
+
+        """
+        t = Table(
+            "t",
+            metadata,
+            Column("id", Integer, Identity(), primary_key=True),
+            Column("value", type_),
+        )
+
+        t.create(connection)
+
+        result = connection.execute(
+            t.insert().returning(
+                t.c.id,
+                t.c.value,
+                sort_by_parameter_order=bool(sort_by_parameter_order),
+            ),
+            [{"value": value} for i in range(10)]
+            if multiple_rows
+            else {"value": value},
+        )
+
+        if multiple_rows:
+            i_range = range(1, 11)
+        else:
+            i_range = range(1, 2)
+
+        eq_(
+            set(result),
+            {(id_, value) for id_ in i_range},
+        )
+
+        eq_(
+            set(connection.scalars(select(t.c.value))),
+            {value},
+        )
+
 
 class TextTest(_LiteralRoundTripFixture, fixtures.TablesTest):
     __requires__ = ("text_type",)