Fixed #31473 -- Made sql_flush() use RESTART IDENTITY to reset sequences on PostgreSQL.

The sql_flush() positional argument sequences is replaced by the boolean
keyword-only argument reset_sequences. This ensures that the old
function signature can't be used by mistake when upgrading Django. When
the new argument is True, the sequences of the truncated tables will
reset. Using a single boolean value, rather than a list, allows making a
binary yes/no choice as to whether to reset all sequences rather than a
working on a completely different set.

6 files changed, 52 insertions, 46 deletions

diff --git a/django/core/management/sql.py b/django/core/management/sql.py
index 0d54f9b220..27113dbbbe 100644
--- a/django/core/management/sql.py
+++ b/django/core/management/sql.py
@@ -13,8 +13,12 @@ def sql_flush(style, connection, only_django=False, reset_sequences=True, allow_
         tables = connection.introspection.django_table_names(only_existing=True, include_views=False)
     else:
         tables = connection.introspection.table_names(include_views=False)
-    seqs = connection.introspection.sequence_list() if reset_sequences else ()
-    return connection.ops.sql_flush(style, tables, seqs, allow_cascade)
+    return connection.ops.sql_flush(
+        style,
+        tables,
+        reset_sequences=reset_sequences,
+        allow_cascade=allow_cascade,
+    )
 
 
 def emit_pre_migrate_signal(verbosity, interactive, db, **kwargs):
diff --git a/django/db/backends/base/operations.py b/django/db/backends/base/operations.py
index 6e1187fd37..70ac07ae09 100644
--- a/django/db/backends/base/operations.py
+++ b/django/db/backends/base/operations.py
@@ -382,16 +382,18 @@ class BaseDatabaseOperations:
         """
         return ''
 
-    def sql_flush(self, style, tables, sequences, allow_cascade=False):
+    def sql_flush(self, style, tables, *, reset_sequences=False, allow_cascade=False):
         """
         Return a list of SQL statements required to remove all data from
         the given database tables (without actually removing the tables
-        themselves) and the SQL statements required to reset the sequences
-        passed in `sequences`.
+        themselves).
 
         The `style` argument is a Style object as returned by either
         color_style() or no_style() in django.core.management.color.
 
+        If `reset_sequences` is True, the list includes SQL statements required
+        to reset the sequences.
+
         The `allow_cascade` argument determines whether truncation may cascade
         to tables with foreign keys pointing the tables being truncated.
         PostgreSQL requires a cascade even if these tables are empty.
diff --git a/django/db/backends/mysql/operations.py b/django/db/backends/mysql/operations.py
index 9d69ba1152..d01e3bef6b 100644
--- a/django/db/backends/mysql/operations.py
+++ b/django/db/backends/mysql/operations.py
@@ -193,29 +193,30 @@ class DatabaseOperations(BaseDatabaseOperations):
         ]
         return 'RETURNING %s' % ', '.join(columns), ()
 
-    def sql_flush(self, style, tables, sequences, allow_cascade=False):
+    def sql_flush(self, style, tables, *, reset_sequences=False, allow_cascade=False):
         if not tables:
             return []
+
         sql = ['SET FOREIGN_KEY_CHECKS = 0;']
-        tables = set(tables)
-        with_sequences = set(s['table'] for s in sequences)
-        # It's faster to TRUNCATE tables that require a sequence reset since
-        # ALTER TABLE AUTO_INCREMENT is slower than TRUNCATE.
-        sql.extend(
-            '%s %s;' % (
-                style.SQL_KEYWORD('TRUNCATE'),
-                style.SQL_FIELD(self.quote_name(table_name)),
-            ) for table_name in tables.intersection(with_sequences)
-        )
-        # Otherwise issue a simple DELETE since it's faster than TRUNCATE
-        # and preserves sequences.
-        sql.extend(
-            '%s %s %s;' % (
-                style.SQL_KEYWORD('DELETE'),
-                style.SQL_KEYWORD('FROM'),
-                style.SQL_FIELD(self.quote_name(table_name)),
-            ) for table_name in tables.difference(with_sequences)
-        )
+        if reset_sequences:
+            # It's faster to TRUNCATE tables that require a sequence reset
+            # since ALTER TABLE AUTO_INCREMENT is slower than TRUNCATE.
+            sql.extend(
+                '%s %s;' % (
+                    style.SQL_KEYWORD('TRUNCATE'),
+                    style.SQL_FIELD(self.quote_name(table_name)),
+                ) for table_name in tables
+            )
+        else:
+            # Otherwise issue a simple DELETE since it's faster than TRUNCATE
+            # and preserves sequences.
+            sql.extend(
+                '%s %s %s;' % (
+                    style.SQL_KEYWORD('DELETE'),
+                    style.SQL_KEYWORD('FROM'),
+                    style.SQL_FIELD(self.quote_name(table_name)),
+                ) for table_name in tables
+            )
         sql.append('SET FOREIGN_KEY_CHECKS = 1;')
         return sql
 
diff --git a/django/db/backends/oracle/operations.py b/django/db/backends/oracle/operations.py
index 0dc77aefc9..6f4121425f 100644
--- a/django/db/backends/oracle/operations.py
+++ b/django/db/backends/oracle/operations.py
@@ -404,7 +404,7 @@ END;
         # Django's test suite.
         return lru_cache(maxsize=512)(self.__foreign_key_constraints)
 
-    def sql_flush(self, style, tables, sequences, allow_cascade=False):
+    def sql_flush(self, style, tables, *, reset_sequences=False, allow_cascade=False):
         if not tables:
             return []
 
@@ -446,9 +446,15 @@ END;
                 style.SQL_FIELD(self.quote_name(constraint)),
             ) for table, constraint in constraints
         ]
-        # Since we've just deleted all the rows, running our sequence ALTER
-        # code will reset the sequence to 0.
-        sql.extend(self.sequence_reset_by_name_sql(style, sequences))
+        if reset_sequences:
+            sequences = [
+                sequence
+                for sequence in self.connection.introspection.sequence_list()
+                if sequence['table'].upper() in truncated_tables
+            ]
+            # Since we've just deleted all the rows, running our sequence ALTER
+            # code will reset the sequence to 0.
+            sql.extend(self.sequence_reset_by_name_sql(style, sequences))
         return sql
 
     def sequence_reset_by_name_sql(self, style, sequences):
diff --git a/django/db/backends/postgresql/operations.py b/django/db/backends/postgresql/operations.py
index dd422af1af..70880d4179 100644
--- a/django/db/backends/postgresql/operations.py
+++ b/django/db/backends/postgresql/operations.py
@@ -117,28 +117,21 @@ class DatabaseOperations(BaseDatabaseOperations):
     def set_time_zone_sql(self):
         return "SET TIME ZONE %s"
 
-    def sql_flush(self, style, tables, sequences, allow_cascade=False):
+    def sql_flush(self, style, tables, *, reset_sequences=False, allow_cascade=False):
         if not tables:
             return []
 
         # Perform a single SQL 'TRUNCATE x, y, z...;' statement. It allows us
         # to truncate tables referenced by a foreign key in any other table.
-        tables_sql = ', '.join(
-            style.SQL_FIELD(self.quote_name(table)) for table in tables
-        )
+        sql_parts = [
+            style.SQL_KEYWORD('TRUNCATE'),
+            ', '.join(style.SQL_FIELD(self.quote_name(table)) for table in tables),
+        ]
+        if reset_sequences:
+            sql_parts.append(style.SQL_KEYWORD('RESTART IDENTITY'))
         if allow_cascade:
-            sql = ['%s %s %s;' % (
-                style.SQL_KEYWORD('TRUNCATE'),
-                tables_sql,
-                style.SQL_KEYWORD('CASCADE'),
-            )]
-        else:
-            sql = ['%s %s;' % (
-                style.SQL_KEYWORD('TRUNCATE'),
-                tables_sql,
-            )]
-        sql.extend(self.sequence_reset_by_name_sql(style, sequences))
-        return sql
+            sql_parts.append(style.SQL_KEYWORD('CASCADE'))
+        return ['%s;' % ' '.join(sql_parts)]
 
     def sequence_reset_by_name_sql(self, style, sequences):
         # 'ALTER SEQUENCE sequence_name RESTART WITH 1;'... style SQL statements
diff --git a/django/db/backends/sqlite3/operations.py b/django/db/backends/sqlite3/operations.py
index fcc2a06d7a..3f55332c99 100644
--- a/django/db/backends/sqlite3/operations.py
+++ b/django/db/backends/sqlite3/operations.py
@@ -196,7 +196,7 @@ class DatabaseOperations(BaseDatabaseOperations):
         # Django's test suite.
         return lru_cache(maxsize=512)(self.__references_graph)
 
-    def sql_flush(self, style, tables, sequences, allow_cascade=False):
+    def sql_flush(self, style, tables, *, reset_sequences=False, allow_cascade=False):
         if tables and allow_cascade:
             # Simulate TRUNCATE CASCADE by recursively collecting the tables
             # referencing the tables to be flushed.