From bf1fe670513abeb1596bc5266f50db1ffe62f3bd Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 31 Aug 2021 13:34:43 -0400 Subject: [PATCH] Fix and test sequences w/ executemany in pre-exec scenarios Fixed issue where an engine that had ``implicit_returning`` set to False would fail to function when PostgreSQL's "fast insertmany" feature were used in conjunction with a ``Sequence``, as well as if any kind of "executemany" with "return_defaults()" were used in conjunction with a ``Sequence``. Note that PostgreSQL "fast insertmany" uses "RETURNING" by definition, when the SQL statement is passed to the driver; overall, the ``implicit_returning`` flag is legacy and has no real use in modern SQLAlchemy, and will be deprecated in a separate change. Fixes: #6963 Change-Id: Id8e3dd50a21b9124f338067b0fdb57b8f608dca8 --- doc/build/changelog/unreleased_14/6963.rst | 12 ++++ lib/sqlalchemy/engine/default.py | 3 +- test/sql/test_sequences.py | 73 +++++++++++++++++++++- 3 files changed, 86 insertions(+), 2 deletions(-) create mode 100644 doc/build/changelog/unreleased_14/6963.rst diff --git a/doc/build/changelog/unreleased_14/6963.rst b/doc/build/changelog/unreleased_14/6963.rst new file mode 100644 index 0000000000..8b0473b169 --- /dev/null +++ b/doc/build/changelog/unreleased_14/6963.rst @@ -0,0 +1,12 @@ +.. change:: + :tags: bug, engine, postgresql + :tickets: 6963 + + Fixed issue where an engine that had ``implicit_returning`` set to False + would fail to function when PostgreSQL's "fast insertmany" feature were + used in conjunction with a ``Sequence``, as well as if any kind of + "executemany" with "return_defaults()" were used in conjunction with a + ``Sequence``. Note that PostgreSQL "fast insertmany" uses "RETURNING" by + definition, when the SQL statement is passed to the driver; overall, the + ``implicit_returning`` flag is legacy and has no real use in modern + SQLAlchemy, and will be deprecated in a separate change. \ No newline at end of file diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py index 8d6f40ff66..8bd8a121b3 100644 --- a/lib/sqlalchemy/engine/default.py +++ b/lib/sqlalchemy/engine/default.py @@ -1835,8 +1835,9 @@ class DefaultExecutionContext(interfaces.ExecutionContext): # to avoid many calls of get_insert_default()/ # get_update_default() for c in insert_prefetch: - if c.default and c.default.is_scalar: + if c.default and not c.default.is_sequence and c.default.is_scalar: scalar_defaults[c] = c.default.arg + for c in update_prefetch: if c.onupdate and c.onupdate.is_scalar: scalar_defaults[c] = c.onupdate.arg diff --git a/test/sql/test_sequences.py b/test/sql/test_sequences.py index d1d46afa32..a0fef99be3 100644 --- a/test/sql/test_sequences.py +++ b/test/sql/test_sequences.py @@ -208,6 +208,8 @@ class SequenceExecTest(fixtures.TestBase): ("implicit_returning",), ("no_implicit_returning",), ("explicit_returning", testing.requires.returning), + ("return_defaults_no_implicit_returning", testing.requires.returning), + ("return_defaults_implicit_returning", testing.requires.returning), argnames="returning", ) @testing.requires.multivalues_inserts @@ -221,7 +223,7 @@ class SequenceExecTest(fixtures.TestBase): e = engines.testing_engine( options={ - "implicit_returning": returning != "no_implicit_returning" + "implicit_returning": "no_implicit_returning" not in returning } ) metadata.create_all(e) @@ -232,10 +234,79 @@ class SequenceExecTest(fixtures.TestBase): ) if returning == "explicit_returning": stmt = stmt.returning(t1.c.x) + elif "return_defaults" in returning: + stmt = stmt.return_defaults() r = conn.execute(stmt) if returning == "explicit_returning": eq_(r.all(), [(1,), (2,), (3,)]) + elif "return_defaults" in returning: + eq_(r.returned_defaults_rows, None) + + # TODO: not sure what this is + eq_(r.inserted_primary_key_rows, [(None,)]) + + eq_( + conn.execute(t1.select().order_by(t1.c.x)).all(), + [(1, "d1"), (2, "d2"), (3, "d3")], + ) + + @testing.combinations( + ("implicit_returning",), + ("no_implicit_returning",), + ( + "explicit_returning", + testing.requires.returning + + testing.requires.insert_executemany_returning, + ), + ( + "return_defaults_no_implicit_returning", + testing.requires.returning + + testing.requires.insert_executemany_returning, + ), + ( + "return_defaults_implicit_returning", + testing.requires.returning + + testing.requires.insert_executemany_returning, + ), + argnames="returning", + ) + def test_seq_multivalues_executemany( + self, metadata, testing_engine, returning + ): + t1 = Table( + "t", + metadata, + Column("x", Integer, Sequence("my_seq"), primary_key=True), + Column("data", String(50)), + ) + + e = engines.testing_engine( + options={ + "implicit_returning": "no_implicit_returning" not in returning + } + ) + metadata.create_all(e) + with e.begin() as conn: + + stmt = t1.insert() + if returning == "explicit_returning": + stmt = stmt.returning(t1.c.x) + elif "return_defaults" in returning: + stmt = stmt.return_defaults() + + r = conn.execute( + stmt, [{"data": "d1"}, {"data": "d2"}, {"data": "d3"}] + ) + if returning == "explicit_returning": + eq_(r.all(), [(1,), (2,), (3,)]) + elif "return_defaults" in returning: + if "no_implicit_returning" in returning: + eq_(r.returned_defaults_rows, None) + eq_(r.inserted_primary_key_rows, [(1,), (2,), (3,)]) + else: + eq_(r.returned_defaults_rows, [(1,), (2,), (3,)]) + eq_(r.inserted_primary_key_rows, [(1,), (2,), (3,)]) eq_( conn.execute(t1.select().order_by(t1.c.x)).all(), -- 2.47.2