Flyway迁移因postgres挂起立即创建索引 [英] Flyway migration hangs for postgres CREATE INDEX CONCURRENTLY
问题描述
我正在尝试对Postgres 9.2数据库运行CREATE INDEX CONCURRENTLY命令.如问题 655 所示，我实现了MigrationResolver.通过mvn flyway:migrate或类似方法运行此迁移步骤时，该命令会启动，但会挂起，处于等待模式.
I am trying to run a CREATE INDEX CONCURRENTLY command against a Postgres 9.2 database. I implemented a MigrationResolver as shown in issue 655. When this migration step is run via mvn flyway:migrate or similar, the command starts but hangs in waiting mode.
我已验证该命令正在通过pg_stat_activity表执行:
I verified that the command is executing via the pg_stat_activity table:
test_2015_04_13_110536=# select * from pg_stat_activity;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | waiting | state | query
-------+------------------------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
21095 | test_2015_04_13_110536 | 56695 | 16385 | postgres | psql | | | -1 | 2015-04-13 11:10:01.127768-06 | 2015-04-13 11:13:08.936651-06 | 2015-04-13 11:13:08.936651-06 | 2015-04-13 11:13:08.936655-06 | f | active | select * from pg_stat_activity;
21095 | test_2015_04_13_110536 | 56824 | 16385 | postgres | | 127.0.0.1 | | 52437 | 2015-04-13 11:12:55.438927-06 | 2015-04-13 11:12:55.476442-06 | 2015-04-13 11:12:55.487139-06 | 2015-04-13 11:12:55.487175-06 | f | idle in transaction | SELECT "version_rank","installed_rank","version","description","type","script","checksum","installed_on","installed_by","execution_time","success" FROM "public"."schema_version" ORDER BY "version_rank"
21095 | test_2015_04_13_110536 | 56825 | 16385 | postgres | | 127.0.0.1 | | 52438 | 2015-04-13 11:12:55.443687-06 | 2015-04-13 11:12:55.49024-06 | 2015-04-13 11:12:55.49024-06 | 2015-04-13 11:12:55.490241-06 | t | active | CREATE UNIQUE INDEX CONCURRENTLY person_restrict_duplicates_2_idx ON person(name, person_month, person_year)
(3 rows)
可以在我的github中找到一个复制此问题的示例项目: chrisphelps/flyway-experiment
An example project that replicates this problem can be found in my github: chrisphelps/flyway-experiment
我怀疑是针对schema version的idle in transaction的飞行查询正在阻止postgres继续创建索引.
My suspicion is that the flyway query against schema version which is idle in transaction is preventing postgres from proceeding with the index creation.
如何解决冲突，以便Postgres继续进行迁移?有人能通过飞行路线将这种迁移应用于Postgres吗?
How can I resolve the conflict so that postgres will proceed with the migration? Has anyone been able to apply this sort of migration to postgres via flyway?
推荐答案
在平均时间，flyway中包含一个解析器，该解析器在文件名中查找一些魔术.
In the meantime, there is a Resolver included in flyway which looks for some magic in the filename.
只需在迁移文件中添加前缀"NT"(用于 N o- T ransaction)，即可. e.
Just add the prefix 'NT' (for No-Transaction) to your migration file, i. e.
V01__usual_migration_1.sql
V02__another_migration.sql
NTV03__migration_that_does_not_run_in_transaction.sql
V04__classical_migration_4.sql
等
这篇关于Flyway迁移因postgres挂起立即创建索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
