Hi,
Using:
- phoenix 1.3.0-rc.2 (Hex package) (mix)
- ecto 2.1.4 (Hex package) (mix)
- phoenix_ecto 3.2.3 (Hex package) (mix)
I have the following schemas:
schema "users" do
field :email, :string, null: false
field :firstname, :string, null: false
field :lastname, :string, null: false
many_to_many :positions, Accounts.Position, join_through: "users_positions"
end
schema "positions" do
field :name, :string, null: false
many_to_many :users, User, join_through: "users_positions"
end
The join table is like this:
def change do
create table(:users_positions, primary_key: false) do
add :user_id, references(:users, on_delete: :nothing), primary_key: true
add :position_id, references(:positions, on_delete: :nothing), primary_key: true
end
create unique_index(:users_positions, [:position_id, :user_id])
end
As this is a multi-tenant application, I use Postgres schema prefixes. However when trying to insert a user with a query prefix:
%Accounts.User{
firstname: firstname,
lastname: lastname,
email: email,
positions: [%Position{name: "admin"}]
}
|> Ecto.put_meta(prefix: "tenant_test")
|> Repo.insert
The first two INSERT
into prefix.users
and prefix.positions
work fine, but the INSERT
into users_positions
does not have the schema prefix and therefore fails:
22:31:41.066 [debug] QUERY OK db=0.0ms queue=33.5ms
begin []
22:31:41.075 [debug] QUERY OK db=0.9ms
INSERT INTO "tenant_test"."users" ("email","firstname","lastname","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5) RETURNING "id" ["johndoe@mail.com", "John", "Doe", {{2017, 7, 2}, {20, 31, 41, 67145}}, {{2017, 7, 2}, {20, 31, 41, 70151}}]
22:31:41.076 [debug] QUERY OK db=0.2ms
INSERT INTO "tenant_test"."positions" ("name","inserted_at","updated_at") VALUES ($1,$2,$3) RETURNING "id" ["admin", {{2017, 7, 2}, {20, 31, 41, 75623}}, {{2017, 7, 2}, {20, 31, 41, 75626}}]
22:31:41.080 [debug] QUERY ERROR db=1.6ms
INSERT INTO "users_positions" ("position_id","user_id") VALUES ($1,$2) [5, 8]
22:31:41.081 [debug] QUERY OK db=0.0ms
rollback []
** (Postgrex.Error) ERROR 42P01 (undefined_table): relation "users_positions" does not exist
(ecto) lib/ecto/adapters/sql.ex:195: Ecto.Adapters.SQL.query!/5
(ecto) lib/ecto/adapters/postgres.ex:86: Ecto.Adapters.Postgres.insert_all/7
(ecto) lib/ecto/repo/schema.ex:55: Ecto.Repo.Schema.do_insert_all/7
(ecto) lib/ecto/association.ex:963: Ecto.Association.ManyToMany.on_repo_change/4
(ecto) lib/ecto/association.ex:330: anonymous fn/7 in Ecto.Association.on_repo_change/6
(elixir) lib/enum.ex:1755: Enum."-reduce/3-lists^foldl/2-0-"/3
(ecto) lib/ecto/association.ex:327: Ecto.Association.on_repo_change/6
(elixir) lib/enum.ex:1755: Enum."-reduce/3-lists^foldl/2-0-"/3
(ecto) lib/ecto/association.ex:293: Ecto.Association.on_repo_change/3
(ecto) lib/ecto/repo/schema.ex:617: Ecto.Repo.Schema.process_children/4
(ecto) lib/ecto/repo/schema.ex:684: anonymous fn/3 in Ecto.Repo.Schema.wrap_in_transaction/6
(ecto) lib/ecto/adapters/sql.ex:620: anonymous fn/3 in Ecto.Adapters.SQL.do_transaction/3
(db_connection) lib/db_connection.ex:1275: DBConnection.transaction_run/4
(db_connection) lib/db_connection.ex:1199: DBConnection.run_begin/3
(db_connection) lib/db_connection.ex:790: DBConnection.transaction/3
(mix) lib/mix/task.ex:300: Mix.Task.run_task/3
(mix) lib/mix/cli.ex:58: Mix.CLI.run_task/2
I also tried to add a Map.put(:prefix, tenant)
, but this does not change anything (unsurprisingly).
Did I miss something ?
Thank you
Note: there was a slightly related bug about @schema_prefix and assoc: https://github.com/elixir-ecto/ecto/issues/1746