Fix longer sequence name detection for serial columns (#28339)

We already found the longer sequence name, but we could not consider
whether it was the sequence name created by serial type due to missed a
max identifier length limitation. I've addressed the sequence name
consideration to respect the max identifier length.

Fixes #28332.

4 files changed, 26 insertions, 4 deletions

diff --git a/activerecord/lib/active_record/connection_adapters/column.rb b/activerecord/lib/active_record/connection_adapters/column.rb
index 16273fb5f1..28d949b503 100644
--- a/activerecord/lib/active_record/connection_adapters/column.rb
+++ b/activerecord/lib/active_record/connection_adapters/column.rb
@@ -15,7 +15,7 @@ module ActiveRecord
       # +default+ is the type-casted default value, such as +new+ in <tt>sales_stage varchar(20) default 'new'</tt>.
       # +sql_type_metadata+ is various information about the type of the column
       # +null+ determines if this column allows +NULL+ values.
-      def initialize(name, default, sql_type_metadata = nil, null = true, table_name = nil, default_function = nil, collation = nil, comment: nil)
+      def initialize(name, default, sql_type_metadata = nil, null = true, table_name = nil, default_function = nil, collation = nil, comment: nil, **)
         @name = name.freeze
         @table_name = table_name
         @sql_type_metadata = sql_type_metadata
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/column.rb b/activerecord/lib/active_record/connection_adapters/postgresql/column.rb
index ff95fa4a0e..469ef3f5a0 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql/column.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql/column.rb
@@ -7,6 +7,11 @@ module ActiveRecord
       delegate :array, :oid, :fmod, to: :sql_type_metadata
       alias :array? :array
 
+      def initialize(*, max_identifier_length: 63, **)
+        super
+        @max_identifier_length = max_identifier_length
+      end
+
       def serial?
         return unless default_function
 
@@ -15,8 +20,23 @@ module ActiveRecord
         end
       end
 
+      protected
+        attr_reader :max_identifier_length
+
       private
         def sequence_name_from_parts(table_name, column_name, suffix)
+          over_length = [table_name, column_name, suffix].map(&:length).sum + 2 - max_identifier_length
+
+          if over_length > 0
+            column_name_length = [(max_identifier_length - suffix.length - 2) / 2, column_name.length].min
+            over_length -= column_name.length - column_name_length
+            column_name = column_name[0, column_name_length - [over_length, 0].min]
+          end
+
+          if over_length > 0
+            table_name = table_name[0, table_name.length - over_length]
+          end
+
           "#{table_name}_#{column_name}_#{suffix}"
         end
     end
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
index 9e2f61e6ce..79cb4e276d 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
@@ -617,7 +617,8 @@ module ActiveRecord
               table_name,
               default_function,
               collation,
-              comment: comment.presence
+              comment: comment.presence,
+              max_identifier_length: max_identifier_length
             )
           end
 
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
index 5b6ad5fbc4..eae3d59946 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
@@ -352,10 +352,11 @@ module ActiveRecord
       end
 
       # Returns the configured supported identifier length supported by PostgreSQL
-      def table_alias_length
+      def max_identifier_length
         @max_identifier_length ||= query_value("SHOW max_identifier_length", "SCHEMA").to_i
       end
-      alias index_name_length table_alias_length
+      alias table_alias_length max_identifier_length
+      alias index_name_length max_identifier_length
 
       # Set the authorized user for this session
       def session_auth=(user)