New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

No extension found for OID xxxxxx when using citext #98

Closed
zmoshansky opened this Issue Oct 2, 2015 · 10 comments

Comments

Projects
None yet
3 participants
@zmoshansky

zmoshansky commented Oct 2, 2015

I've struggled with this for a long time and it seems like postgrex is very happy if I manually run create extension citext in psql; but, when it's part of my migrations I get this error:

Possibly related #60?

** (ArgumentError) no extension found for oid `62022`
    (postgrex) lib/postgrex/types.ex:280: Postgrex.Types.fetch!/2
    (postgrex) lib/postgrex/types.ex:213: Postgrex.Types.format/2
    (elixir) lib/enum.ex:977: anonymous fn/3 in Enum.map/2
    (elixir) lib/enum.ex:1261: Enum."-reduce/3-lists^foldl/2-0-"/3
    (elixir) lib/enum.ex:977: Enum.map/2
    (postgrex) lib/postgrex/protocol.ex:134: Postgrex.Protocol.message/3
    (postgrex) lib/postgrex/connection.ex:417: Postgrex.Connection.new_data/2
    (postgrex) lib/postgrex/connection.ex:292: Postgrex.Connection.handle_info/2

First Migration to run:

defmodule Charlie.Repo.Migrations.EnableCitext do
  use Ecto.Migration

  # def change do
  #   # TODO - use citext for email
  #   execute("CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;")
  # end
  def up do
    execute "CREATE EXTENSION IF NOT EXISTS citext"
  end

  def down do
    execute "DROP EXTENSION IF EXISTS citext"
  end
end

Used in:

defmodule App.Repo.Migrations.CreateUser do
  use Ecto.Migration

  def change do
    create table(:users) do
      add :email, :citext
      add :hashed_password, :string

      timestamps
    end
    create index(:users, [:email], unique: true)
  end
end
Versions
Name        : postgresql
Version     : 9.4.4

Elixir 1.0.3
  "phoenix": {:hex, :phoenix, "1.0.2"},
  "ecto": {:hex, :ecto, "1.0.4"},
  "postgrex": {:hex, :postgrex, "0.9.1"},
@ericmj

This comment has been minimized.

Member

ericmj commented Oct 2, 2015

How and when do you run your migrations?

Postgrex fetches types from the server when the first connection to the datasebase is started (when your ecto repo starts) so if you run migrations after the application starts it wont work.

@zmoshansky

This comment has been minimized.

zmoshansky commented Oct 2, 2015

When Testing:

ExUnit.start

# Create the database, run migrations, and start the test transaction.
Mix.Task.run "ecto.drop", ["--quiet"] # Usually commented out unless schema retroactively changed.
Mix.Task.run "ecto.create", ["--quiet"]
Mix.Task.run "ecto.migrate", ["--quiet"]

In production via HashNuke's Scripts which would use mix ecto.migrate: Invoked from bash (likely after deploying a new release).

@ericmj

This comment has been minimized.

Member

ericmj commented Oct 2, 2015

If this is in your test_helper.exs file your app has already started. You can achieve this with task aliases instead (documented here: http://elixir-lang.org/docs/stable/mix/Mix.html). You should override the test task and run the ecto tasks first.

I don't know how the scripts work? Are the ecto tasks run after the app is started?

@zmoshansky

This comment has been minimized.

zmoshansky commented Oct 2, 2015

Thanks for the aliases tip. As for production, the would run as follows in two-steps (ansible tasks).

Deploy:

mix release.clean
SERVER=1 mix release
rel/{{app_name}}/bin/{{app_name}} stop
rel/{{app_name}}/bin/{{app_name}} start

Migrate

mix ecto.migrate

From what you describe, it sounds like it'd need to be modified to:

Deploy & Migrate

mix release.clean
SERVER=1 mix release
rel/{{app_name}}/bin/{{app_name}} stop
mix ecto.migrate
rel/{{app_name}}/bin/{{app_name}} start
@zmoshansky

This comment has been minimized.

zmoshansky commented Oct 14, 2015

After testing with the following, it seems that the error still occurs even using aliases. Is there some way to get Postgrex to refresh it's oid's?

 defp aliases do
    [test: [&setup_db/1, "test"]]
  end

  defp setup_db(_) do
    # Create the database, run migrations
    Mix.Task.run "ecto.drop"#, ["--quiet"]
    Mix.Task.run "ecto.create"#, ["--quiet"]
    Mix.Task.run "ecto.migrate"#, ["--quiet"]
  end
With this output:
The database for MyApp.Repo has been dropped.
The database for MyApp.Repo has been created.

18:19:10.412 [info]  == Running MyApp.Repo.Migrations.EnableCitext.change/0 forward
18:19:10.414 [info]  execute "CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;"
18:19:10.425 [info]  == Migrated in 0.1s
18:19:10.454 [info]  == Running MyApp.Repo.Migrations.CreateUser.change/0 forward
18:19:10.454 [info]  create table users
....
18:19:10.734 [info]  create table products
18:19:10.744 [info]  == Migrated in 0.0s
** (ArgumentError) no extension found for oid `72812`
    (postgrex) lib/postgrex/types.ex:279: Postgrex.Types.fetch!/2
    (postgrex) lib/postgrex/types.ex:212: Postgrex.Types.format/2
    (elixir) lib/enum.ex:977: anonymous fn/3 in Enum.map/2
    (elixir) lib/enum.ex:1261: Enum."-reduce/3-lists^foldl/2-0-"/3
    (elixir) lib/enum.ex:977: Enum.map/2
    (postgrex) lib/postgrex/protocol.ex:134: Postgrex.Protocol.message/3
    (postgrex) lib/postgrex/connection.ex:417: Postgrex.Connection.new_data/2
    (postgrex) lib/postgrex/connection.ex:292: Postgrex.Connection.handle_info/2
@ericmj

This comment has been minimized.

Member

ericmj commented Oct 14, 2015

Are you sure citext is the issue?

@zmoshansky

This comment has been minimized.

zmoshansky commented Oct 14, 2015

I believe so, if I comment out the execute.... command in EnableCitext I get the following error. If I then change the usage of citext to string it succeeds.

The database for MyApp.Repo has been dropped.
The database for MyApp.Repo has been created.

08:46:05.716 [info]  == Running MyApp.Repo.Migrations.EnableCitext.change/0 forward
08:46:05.718 [info]  == Migrated in 0.0s
08:46:05.743 [info]  == Running MyApp.Repo.Migrations.CreateUser.change/0 forward
08:46:05.743 [info]  create table users
** (Postgrex.Error) ERROR (undefined_object): type "citext" does not exist
    (ecto) lib/ecto/adapters/sql.ex:172: Ecto.Adapters.SQL.query!/5
    (elixir) lib/enum.ex:1261: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/adapters/postgres.ex:55: Ecto.Adapters.Postgres.execute_ddl/3
    (ecto) lib/ecto/migration/runner.ex:74: anonymous fn/2 in Ecto.Migration.Runner.flush/0
    (elixir) lib/enum.ex:1261: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/migration/runner.ex:72: Ecto.Migration.Runner.flush/0
    (stdlib) timer.erl:179: :timer.tc/2
    (ecto) lib/ecto/migration/runner.ex:23: Ecto.Migration.Runner.run/6

The only solution is to run the alias:

    Mix.Task.run "ecto.drop"
    Mix.Task.run "ecto.create"
    Mix.Task.run "ecto.migrate"

then after it fails, comment out the drop and run the alias again as:

    # Mix.Task.run "ecto.drop"
    Mix.Task.run "ecto.create"
    Mix.Task.run "ecto.migrate"
@ericmj

This comment has been minimized.

Member

ericmj commented Oct 14, 2015

Okay, try ecto master where we shut down the repo and adapter after migrations. We cannot fix this in postgrex so I am closing, if you still have issues open an issue on ecto and link back here.

@ericmj ericmj closed this Oct 14, 2015

@zmoshansky

This comment has been minimized.

zmoshansky commented Oct 20, 2015

@ericmj Thank you very much to you and the team for all the hard work!

For posterity: This issue is fixed in Ecto v1.05, if the DB commands are run as an alias before test as shown above.

ngzax pushed a commit to ngzax/gebetsgruppe that referenced this issue Feb 15, 2016

@ngzax

This comment has been minimized.

ngzax commented Feb 15, 2016

@ericmj : More thanks for this, this thread saved me too. 👍

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment