postgresql 如何在不启动 Rails 事务的情况下运行迁移？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1395672/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I run a migration without starting a transaction in Rails?
提问by Helder S Ribeiro
I'm running some bizarre Postgres migration code from OpenCongressand I'm getting this error:
我正在从OpenCongress运行一些奇怪的 Postgres 迁移代码，但出现此错误：
RuntimeError: ERROR C25001 MVACUUM cannot run inside a transaction block
Fxact.c L2649 RPreventTransactionChain: VACUUM FULL ANALYZE;
So I'd like to try running it without getting wrapped by a transaction.
所以我想尝试运行它而不被事务包裹。
采纳答案by Peter Wagenet
ActiveRecord::Migrationhas the following private method that gets called when running migrations:
ActiveRecord::Migration有以下私有方法在运行迁移时被调用：
def ddl_transaction(&block)
if Base.connection.supports_ddl_transactions?
Base.transaction { block.call }
else
block.call
end
end
As you can see this will wrap the migration in a transaction if the connection supports it.
如您所见，如果连接支持，这会将迁移包装在事务中。
In ActiveRecord::ConnectionAdapters::PostgreSQLAdapteryou have:
在ActiveRecord::ConnectionAdapters::PostgreSQLAdapter你有：
def supports_ddl_transactions?
true
end
SQLite version 2.0 and beyond also support migration transactions.
In ActiveRecord::ConnectionAdapters::SQLiteAdapteryou have:
SQLite 2.0 及更高版本也支持迁移事务。在ActiveRecord::ConnectionAdapters::SQLiteAdapter你有：
def supports_ddl_transactions?
sqlite_version >= '2.0.0'
end
So then, to skip transactions, you need to somehow circumvent this. Something like this might work, though I haven't tested it:
那么，要跳过事务，您需要以某种方式规避这一点。虽然我还没有测试过，但这样的事情可能会奏效：
class ActiveRecord::Migration
class << self
def no_transaction
@no_transaction = true
end
def no_transaction?
@no_transaction == true
end
end
private
def ddl_transaction(&block)
if Base.connection.supports_ddl_transactions? && !self.class.no_transaction?
Base.transaction { block.call }
else
block.call
end
end
end
You could then set up your migration as follows:
然后，您可以按如下方式设置迁移：
class SomeMigration < ActiveRecord::Migration
no_transaction
def self.up
# Do something
end
def self.down
# Do something
end
end
回答by davetron5000
There's now a method disable_ddl_transaction!that allows this, e.g.:
现在有一种方法可以disable_ddl_transaction!做到这一点，例如：
class AddIndexesToTablesBasedOnUsage < ActiveRecord::Migration
disable_ddl_transaction!
def up
execute %{
CREATE INDEX CONCURRENTLY index_reservations_subscription_id ON reservations (subscription_id);
}
end
def down
execute %{DROP INDEX index_reservations_subscription_id}
end
end
回答by professormeowingtons
An extremely simple, Rails-version-independent (2.3, 3.2, 4.0, doesn't matter) way about this is to simply add execute("commit;")to the beginning of your migration, and then write SQL.
一个非常简单的、与 Rails 版本无关（2.3、3.2、4.0 无关紧要）的方法是简单地添加execute("commit;")到迁移的开头，然后编写 SQL。
This immediately closes the Rails-started transaction, and allows you to write raw SQL that can create its own transactions. In the below example, I use an .update_alland a subselect LIMITto handle updating a huge database table.
这会立即关闭由 Rails 启动的事务，并允许您编写可以创建自己的事务的原始 SQL。在下面的示例中，我使用一个.update_all和一个子选择LIMIT来处理更新一个巨大的数据库表。
As an example,
举个例子，
class ChangeDefaultTabIdOfZeroToNilOnUsers < ActiveRecord::Migration
def self.up
execute("commit;")
while User.find_by_default_tab_id(0).present? do
User.update_all %{default_tab_id = NULL}, %{id IN (
SELECT id FROM users WHERE default_tab_id = 0 LIMIT 1000
)}.squish!
end
end
def self.down
raise ActiveRecord::IrreversibleMigration
end
end
回答by YasirAzgar
Rails 4 + There is a method disable_ddl_transaction!, you can use it in your migration file like below.
Rails 4 + 有一个方法 disable_ddl_transaction!，你可以在你的迁移文件中使用它，如下所示。
class AddIndexToTable < ActiveRecord::Migration
disable_ddl_transaction!
def change
add_index :table, :column, algorithm: :concurrently
end
end
Below Rails 4
下轨 4
Like some of answers above, there is a simple hack, you can commit the transaction and then after your migration has completed you again the begin the transaction, like below
像上面的一些答案一样，有一个简单的技巧，您可以提交事务，然后在迁移完成后再次开始事务，如下所示
class AddIndexToTable < ActiveRecord::Migration
def change
execute "COMMIT;"
add_index :table, :column, algorithm: :concurrently
# start a new transaction after the migration finishes successfully
execute "BEGIN TRANSACTION;"
end
end
This can be helpful in case where we cant create/drop index concurrently, as these cannot be executed in a transaction. If you try you will get error "PG::ActiveSqlTransaction: ERROR: DROP INDEX CONCURRENTLY cannot run inside a transaction block."
这在我们不能同时创建/删除索引的情况下很有帮助，因为这些不能在事务中执行。如果您尝试，您将收到错误“PG::ActiveSqlTransaction: ERROR: DROP INDEX CONCURRENTLY cannot run inside a transaction block”。
回答by michaelmwu
The above answer is broken for Rails 3 as ddl_transaction was moved into ActiveRecord::Migrator. I could not figure out a way to monkey patch that class, so here is an alternate solution:
由于 ddl_transaction 已移入 ActiveRecord::Migrator，Rails 3 的上述答案已被破坏。我想不出一种方法来修补那个类，所以这里有一个替代解决方案：
I added a file under lib/
我在 lib/ 下添加了一个文件
module NoMigrationTransactions
def self.included(base)
base.class_eval do
alias_method :old_migrate, :migrate
say "Disabling transactions"
@@no_transaction = true
# Force no transactions
ActiveRecord::Base.connection.instance_eval do
alias :old_ddl :supports_ddl_transactions?
def supports_ddl_transactions?
false
end
end
def migrate(*args)
old_migrate(*args)
# Restore
if @@no_transaction
say "Restoring transactions"
ActiveRecord::Base.connection.instance_eval do
alias :supports_ddl_transactions? :old_ddl
end
end
end
end
end
end
Then all you have to do in your migration is:
那么您在迁移过程中所要做的就是：
class PopulateTrees < ActiveRecord::Migration
include NoMigrationTransactions
end
What this does is disable transactions when the migration class is loaded (hopefully after all previous ones were loaded and before any future ones are loaded), then after the migration, restore whatever old transaction capabilities there were.
这样做是在加载迁移类时禁用事务（希望在加载所有以前的类之后和加载任何未来的类之前），然后在迁移之后，恢复所有旧的事务功能。
回答by Noach Magedman
I'm not saying this is the "right way" to do it, but what worked for me was to run just that one migration in isolation.
我并不是说这是执行此操作的“正确方法”，但对我有用的是单独运行该迁移。
rake db:migrate:up VERSION=20120801151807
where 20120801151807 is the timestamp of the migration.
其中 20120801151807 是迁移的时间戳。
Apparently, it doesn't use a transaction when you run a single migration.
显然，当您运行单个迁移时，它不使用事务。
回答by Chris Pfohl
As hacky as this is adding 'commit;' to the beginning of my sql worked for me, but that's for SQL Server, not sure if this works for postgres...
就像添加“提交”一样笨拙；到我的 sql 的开头对我有用，但那是针对 SQL Server 的，不确定这是否适用于 postgres ...
Example: CREATE FULLTEXT INDEX ...is illegal inside a sql-server user transaction.
示例：CREATE FULLTEXT INDEX ...在 sql-server 用户事务中是非法的。
so...:
所以...：
execute <<-SQL
commit;
create fulltext index --...yada yada yada
SQL
works fine... We'll see if I regret it later.
工作正常......我们会看看我是否会后悔。
