allow executemany values for ON CONFLICT DO NOTHING

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`).

Fixes: #7880
Change-Id: Id3e23a0c6699333409a50148fa8923cb8e564bdc

3 files changed, 21 insertions, 9 deletions

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: