Rails迁移Paperclip转Active Storage遇PG语法错误及方法未定义问题

架构老林

2026-7-28

Paperclip 迁移至 Active Storage 时的语法错误与方法未定义问题

我正尝试将Rails应用的文件附件从Paperclip迁移至Active Storage，按教程编写迁移脚本后执行时遭遇两个问题：

初始执行时触发PG::SyntaxError，提示active_storage_blob_statement附近存在语法错误
改用exec_prepared替代execute后，又出现NoMethodError，提示未定义exec_prepared方法

原迁移代码

class ConvertToActiveStorage < ActiveRecord::Migration[6.1]
  require 'open-uri'

  def up
    # postgres
    get_blob_id = 'LASTVAL()'
    # mariadb
    # get_blob_id = '1332589'
    # sqlite
    # get_blob_id = 'LAST_INSERT_ROWID()'

    # Prepare two insert statements for the new ActiveStorage tables
    active_storage_blob_statement = ActiveRecord::Base.connection.raw_connection.prepare('active_storage_blob_statement', <<-SQL)
      INSERT INTO active_storage_blobs (
        key, filename, content_type, metadata, byte_size, checksum, created_at
      ) VALUES ($1, $2, $3, '{}', $4, $5, $6)
    SQL

    active_storage_attachment_statement = ActiveRecord::Base.connection.raw_connection.prepare('active_storage_attachment_statement', <<-SQL)
      INSERT INTO active_storage_attachments (
        name, record_type, record_id, blob_id, created_at
      ) VALUES ($1, $2, $3, #{get_blob_id}, $4)
    SQL


    # Eager load the application so that all Models are available
    Rails.application.eager_load!

    # Get a list of all the models in the application
    models = ActiveRecord::Base.descendants.reject(&:abstract_class?)

    transaction do
      models.each do |model|
        # If the model has a column or columns named *_file_name,
        # We are assuming this is a column added by Paperclip.
        # Store the name of the attachment(s) found (e.g. "avatar") in an array named attachments
        attachments = model.column_names.map do |c|
          if c =~ /(.+)_file_name$/
            $1
          end
        end.compact

        # If no Paperclip columns were found in this model, go to the next model
        if attachments.blank?
          puts '  No Paperclip attachment columns found for [' + model.to_s + '].'
          puts ''
          next
        end

        puts '  Paperclip attachment columns found for [' + model.to_s + ']: ' + attachments.to_s

        # Loop through the records of the model, and then through each attachment definition within the model
        model.find_each.each do |instance|
          attachments.each do |attachment|
            # If the model record doesn't have an uploaded attachment, skip to the next record
            if instance.send(attachment).path.blank?
              next
            end

            # Otherwise, we will convert the Paperclip data to ActiveStorage records
            ActiveRecord::Base.connection.execute(
              'active_storage_blob_statement', [
                key(instance, attachment),
                instance.send("#{attachment}_file_name"),
                instance.send("#{attachment}_content_type"),
                instance.send("#{attachment}_file_size"),
                checksum(instance.send(attachment)),
                instance.updated_at.iso8601
              ])

            ActiveRecord::Base.connection.execute(
              'active_storage_attachment_statement', [
                attachment,
                model.name,
                instance.id,
                instance.updated_at.iso8601,
              ])
          end
        end
      end
    end

    ActiveRecord::Base.connection.execute('DEALLOCATE PREPARE active_storage_attachment_statement')
    ActiveRecord::Base.connection.execute('DEALLOCATE PREPARE active_storage_blob_statement')
  end

  def down
    raise ActiveRecord::IrreversibleMigration
  end

  private

  def key(instance, attachment)
    # SecureRandom.uuid
    # Alternatively:
    filename = instance.send("#{attachment}_file_name")
    klass = instance.class.table_name
    id = instance.id
    id_partition = ("%09d".freeze % id).scan(/\d{3}/).join("/".freeze)

    "#{klass}/#{attachment.pluralize}/#{id_partition}/original/#{filename}"
  end

  def checksum(attachment)
    # local files stored on disk:
    url = attachment.path
    Digest::MD5.base64digest(File.read(url))

    # remote files stored on another person's computer:
    # url = attachment.url
    # Digest::MD5.base64digest(Net::HTTP.get(URI(url)))
  end
end

错误日志

初始执行错误

rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::SyntaxError: ERROR:  syntax error at or near "active_storage_blob_statement"
LINE 1: active_storage_blob_statement
        ^
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:61:in `block (4 levels) in up'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:54:in `each'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:54:in `block (3 levels) in up'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:53:in `each'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:53:in `block (2 levels) in up'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:33:in `each'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:33:in `block in up'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:32:in `up'

改用`exec_prepared`后的错误

Did you mean?  exec_update
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:61:in `block (4 levels) in up'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:54:in `each'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:54:in `block (3 levels) in up'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:53:in `each'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:53:in `block (2 levels) in up'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:33:in `each'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:33:in `block in up'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:32:in `up'

Caused by:
NoMethodError: undefined method `exec_prepared'

错误原因分析

PG::SyntaxError：ActiveRecord::Base.connection.execute只能执行原生SQL语句，你传入的是预编译语句的名称，PostgreSQL无法识别该名称，因此抛出语法错误。
NoMethodError：exec_prepared是PostgreSQL底层连接对象（PG::Connection）的方法，而ActiveRecord::Base.connection返回的是ActiveRecord的连接适配器，并非底层PG连接对象，直接调用会提示方法未定义。

修复方案

核心调整：直接使用底层连接对象执行预编译语句，修正资源释放逻辑，确保语法合规。

修改后的完整迁移代码：

class ConvertToActiveStorage < ActiveRecord::Migration[6.1]
  require 'open-uri'

  def up
    # postgres
    get_blob_id = 'LASTVAL()'
    # mariadb
    # get_blob_id = '1021804'
    # sqlite
    # get_blob_id = 'LAST_INSERT_ROWID()'

    raw_conn = ActiveRecord::Base.connection.raw_connection

    # 预编译插入语句
    raw_conn.prepare('active_storage_blob_statement', <<-SQL)
      INSERT INTO active_storage_blobs (
        key, filename, content_type, metadata, byte_size, checksum, created_at
      ) VALUES ($1, $2, $3, '{}'::jsonb, $4, $5, $6)
    SQL

    raw_conn.prepare('active_storage_attachment_statement', <<-SQL)
      INSERT INTO active_storage_attachments (
        name, record_type, record_id, blob_id, created_at
      ) VALUES ($1, $2, $3, #{get_blob_id}, $4)
    SQL

    Rails.application.eager_load!
    models = ActiveRecord::Base.descendants.reject(&:abstract_class?)

    transaction do
      models.each do |model|
        attachments = model.column_names.map do |c|
          $1 if c =~ /(.+)_file_name$/
        end.compact

        if attachments.blank?
          puts "  No Paperclip attachment columns found for [#{model}]."
          puts ''
          next
        end

        puts "  Paperclip attachment columns found for [#{model}]: #{attachments}"

        model.find_each.each do |instance|
          attachments.each do |attachment|
            next if instance.send(attachment).path.blank?

            # 使用底层连接执行预编译语句
            raw_conn.exec_prepared('active_storage_blob_statement', [
              key(instance, attachment),
              instance.send("#{attachment}_file_name"),
              instance.send("#{attachment}_content_type"),
              instance.send("#{attachment}_file_size"),
              checksum(instance.send(attachment)),
              instance.updated_at.iso8601
            ])

            raw_conn.exec_prepared('active_storage_attachment_statement', [
              attachment,
              model.name,
              instance.id,
              instance.updated_at.iso8601
            ])
          end
        end
      end
    ensure
      # 确保预编译语句被释放，即使事务失败
      raw_conn.exec("DEALLOCATE active_storage_blob_statement") rescue nil
      raw_conn.exec("DEALLOCATE active_storage_attachment_statement") rescue nil
    end
  end

  def down
    raise ActiveRecord::IrreversibleMigration
  end

  private

  def key(instance, attachment)
    filename = instance.send("#{attachment}_file_name")
    klass = instance.class.table_name
    id = instance.id
    id_partition = ("%09d".freeze % id).scan(/\d{3}/).join("/".freeze)

    "#{klass}/#{attachment.pluralize}/#{id_partition}/original/#{filename}"
  end

  def checksum(attachment)
    # 本地文件读取逻辑
    url = attachment.path
    Digest::MD5.base64digest(File.read(url))

    # 远程文件请替换为下面的代码
    # url = attachment.url
    # Digest::MD5.base64digest(Net::HTTP.get(URI(url)))
  end
end

额外注意事项

迁移前需确保已执行Active Storage初始化：rails active_storage:install && rails db:migrate
务必备份数据库，避免迁移过程中数据丢失
若使用MariaDB/SQLite，需修改get_blob_id对应值，同时调整预编译语句的参数占位符（如MariaDB用?代替$1）
大文件迁移建议分批处理，避免内存溢出

内容的提问来源于stack exchange，提问作者Y.H

Rails迁移Paperclip转Active Storage遇PG语法错误及方法未定义问题

原迁移代码 #

错误日志 #

初始执行错误 #

改用exec_prepared后的错误 #

错误原因分析 #

修复方案 #

额外注意事项 #