diff options
| -rw-r--r-- | doc/build/changelog/unreleased_14/7880.rst | 11 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 19 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/psycopg2.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 9 | ||||
| -rw-r--r-- | test/dialect/postgresql/test_dialect.py | 20 |
5 files changed, 50 insertions, 11 deletions
diff --git a/doc/build/changelog/unreleased_14/7880.rst b/doc/build/changelog/unreleased_14/7880.rst new file mode 100644 index 000000000..9abbac1cf --- /dev/null +++ b/doc/build/changelog/unreleased_14/7880.rst @@ -0,0 +1,11 @@ +.. change:: + :tags: bug, postgresql + :tickets: 7880 + + Scaled back a fix made for :ticket:`6581` where "executemany values" mode + for psycopg2 were disabled for all "ON CONFLICT" styles of INSERT, to + not apply to the "ON CONFLICT DO NOTHING" clause, which does not include + any parameters and is safe for "executemany values" mode. "ON CONFLICT + DO UPDATE" is still blocked from "executemany values" as there may + be additional parameters in the DO UPDATE clause that cannot be batched + (which is the original issue fixed by :ticket:`6581`). diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index bec987c46..9bfde4768 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1429,6 +1429,7 @@ import re from uuid import UUID as _python_UUID from . import array as _array +from . import dml from . import hstore as _hstore from . import json as _json from . import ranges as _ranges @@ -2452,6 +2453,24 @@ class PGCompiler(compiler.SQLCompiler): return target_text + @util.memoized_property + def _is_safe_for_fast_insert_values_helper(self): + # don't allow fast executemany if _post_values_clause is + # present and is not an OnConflictDoNothing. what this means + # concretely is that the + # "fast insert executemany helper" won't be used, in other + # words we won't convert "executemany()" of many parameter + # sets into a single INSERT with many elements in VALUES. + # We can't apply that optimization safely if for example the + # statement includes a clause like "ON CONFLICT DO UPDATE" + + return self.insert_single_values_expr is not None and ( + self.statement._post_values_clause is None + or isinstance( + self.statement._post_values_clause, dml.OnConflictDoNothing + ) + ) + def visit_on_conflict_do_nothing(self, on_conflict, **kw): target_text = self._on_conflict_target(on_conflict, **kw) diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py index dddce5a62..391368c5f 100644 --- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py +++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py @@ -712,7 +712,7 @@ class PGDialect_psycopg2(_PGDialect_common_psycopg): self.executemany_mode & EXECUTEMANY_VALUES and context and context.isinsert - and context.compiled.insert_single_values_expr + and context.compiled._is_safe_for_fast_insert_values_helper ): executemany_values = ( "(%s)" % context.compiled.insert_single_values_expr diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index a2f731ac9..b7f6d11f6 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -4349,14 +4349,7 @@ class SQLCompiler(Compiled): ] ) text += " VALUES (%s)" % insert_single_values_expr - if toplevel and insert_stmt._post_values_clause is None: - # don't assign insert_single_values_expr if _post_values_clause - # is present. what this means concretely is that the - # "fast insert executemany helper" won't be used, in other - # words we won't convert "executemany()" of many parameter - # sets into a single INSERT with many elements in VALUES. - # We can't apply that optimization safely if for example the - # statement includes a clause like "ON CONFLICT DO UPDATE" + if toplevel: self.insert_single_values_expr = insert_single_values_expr if insert_stmt._post_values_clause is not None: diff --git a/test/dialect/postgresql/test_dialect.py b/test/dialect/postgresql/test_dialect.py index 5a92ae6fe..fb3a522fc 100644 --- a/test/dialect/postgresql/test_dialect.py +++ b/test/dialect/postgresql/test_dialect.py @@ -31,6 +31,7 @@ from sqlalchemy import text from sqlalchemy import TypeDecorator from sqlalchemy.dialects.postgresql import base as postgresql from sqlalchemy.dialects.postgresql import HSTORE +from sqlalchemy.dialects.postgresql import insert as pg_insert from sqlalchemy.dialects.postgresql import JSONB from sqlalchemy.dialects.postgresql import psycopg as psycopg_dialect from sqlalchemy.dialects.postgresql import psycopg2 as psycopg2_dialect @@ -322,7 +323,10 @@ class ExecuteManyMode: Column("\u6e2c\u8a66", Integer), ) - def test_insert(self, connection): + @testing.combinations( + "insert", "pg_insert", "pg_insert_on_conflict", argnames="insert_type" + ) + def test_insert(self, connection, insert_type): from psycopg2 import extras values_page_size = connection.dialect.executemany_values_page_size @@ -342,11 +346,23 @@ class ExecuteManyMode: else: assert False + if insert_type == "pg_insert_on_conflict": + stmt += " ON CONFLICT DO NOTHING" + with mock.patch.object( extras, meth.__name__, side_effect=meth ) as mock_exec: + if insert_type == "insert": + ins_stmt = self.tables.data.insert() + elif insert_type == "pg_insert": + ins_stmt = pg_insert(self.tables.data) + elif insert_type == "pg_insert_on_conflict": + ins_stmt = pg_insert(self.tables.data).on_conflict_do_nothing() + else: + assert False + connection.execute( - self.tables.data.insert(), + ins_stmt, [ {"x": "x1", "y": "y1"}, {"x": "x2", "y": "y2"}, |