I am trying to run the CREATE INDEX CONCURRENTLY command against a Postgres 9.2 database. I implemented MigrationResolver as shown in the 655 release. When this migration step is executed via mvn flyway:migrate or similar, the command starts, but freezes in standby mode.
I checked that the command is executed using 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)
An example project that replicates this problem can be found in my github: chrisphelps / flyway-experiment
My suspicion is that a span request with the schema version , which is idle in transaction , prevents postgres from continuing with index creation.
How to resolve the conflict so that postgres continue the migration? Could anyone apply this kind of migration for postgres over the span?
source share