We recently moved to ecto 3, and have suddenly had issues with concurrent index creati

Adding a postgres index concurrently corrupts the index about ecto_sql HOT 1 CLOSED

elixir-ecto commented on August 19, 2024

Adding a postgres index concurrently corrupts the index

from ecto_sql.

Comments (1)

josevalim commented on August 19, 2024

You need to disable the migration lock besides ddl migrations if you want to run concurrent indexes in Ecto 3.0:

ecto_sql/lib/ecto/migration.ex

Lines 545 to 576 in b60b281

  ## Adding/dropping indexes concurrently 

  PostgreSQL supports adding/dropping indexes concurrently (see the 

  [docs](http://www.postgresql.org/docs/9.4/static/sql-createindex.html)). 

  However, this feature does not work well with the transactions used by 

  Ecto to guarantee integrity during migrations. 

  Therefore, to migrate indexes concurrently, you need to set 

  `@disable_ddl_transaction` in the migration to true, disabling the 

  guarantee that all of the changes in the migration will happen at 

  once: 

  defmodule MyRepo.Migrations.CreateIndexes do 

  use Ecto.Migration 

  @disable_ddl_transaction true 

  def change do 

  create index("posts", [:slug], concurrently: true) 

  end 

  end 

  And you also need to disable the migration lock for that repository: 

  config :my_app, MyApp.Repo, migration_lock: nil 

  The migration lock is used to guarantee that only one node in a cluster 

  can run migrations. Two nodes may attempt to race each other. 

  Since running migrations outside a transaction can be dangerous, 

  consider performing very few operations in migrations that add concurrent 

  indexes. We recommend to run migrations with concurrent indexes in isolation 

  and disable those features only temporarily.

Please make sure that you have the latest ecto_sql too.

from ecto_sql.

Adding a postgres index concurrently corrupts the index about ecto_sql HOT 1 CLOSED

Comments (1)

Related Issues (20)

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent

	## Adding/dropping indexes concurrently

	PostgreSQL supports adding/dropping indexes concurrently (see the
	[docs](http://www.postgresql.org/docs/9.4/static/sql-createindex.html)).
	However, this feature does not work well with the transactions used by
	Ecto to guarantee integrity during migrations.

	Therefore, to migrate indexes concurrently, you need to set
	`@disable_ddl_transaction` in the migration to true, disabling the
	guarantee that all of the changes in the migration will happen at
	once:

	defmodule MyRepo.Migrations.CreateIndexes do
	use Ecto.Migration
	@disable_ddl_transaction true

	def change do
	create index("posts", [:slug], concurrently: true)
	end
	end

	And you also need to disable the migration lock for that repository:

	config :my_app, MyApp.Repo, migration_lock: nil

	The migration lock is used to guarantee that only one node in a cluster
	can run migrations. Two nodes may attempt to race each other.

	Since running migrations outside a transaction can be dangerous,
	consider performing very few operations in migrations that add concurrent
	indexes. We recommend to run migrations with concurrent indexes in isolation
	and disable those features only temporarily.