Ecto schemaless insert_all throwing error on data type missmatch

Questions / Help
, ,
1

Hi! Just stumbled upon this error today and I’m not sure I understand why this is happening - besides, of course, the obvious problem Postgrex is reporting:

Postgrex expected a binary of 16 bytes, got “4c67a49e-fd03-4c1a-9679-713e952ecf73”. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.

I’m trying to insert a row in a table that has the correct type and is properly migrated:

INSERT INTO "organization_members" ("organization_id","role","user_id") VALUES ($1,$2,$3) [1, "admin", "4c67a49e-fd03-4c1a-9679-713e952ecf73"]

And the migration is:

create table(:organizations_members, primary_key: false) do
  add :organization_id, references(:organizations), primary_key: true
  add :user_id, references(:users, type: :uuid), primary_key: true
  add :role, OrganizationMemberRole.type(), default: "reader"
end

create index(:organizations_members, [:organization_id, :user_id])

And I’m inserting it like this:

insert_membership = fn repo, %{organization: organization} ->
  membership = %{user_id: user.id, organization_id: organization.id, role: "admin"}
  repo.insert_all("organizations_members", [membership])
end

Ecto.Multi.new()
|> Ecto.Multi.insert(:organization, Organization.changeset(%Organization{}, attrs))
|> Ecto.Multi.run(:members, insert_membership)
|> Repo.transaction()
|> case do
  {:ok, %{organization: organization}} -> {:ok, organization}
  {:error, :organization_id, changeset, _} -> {:error, changeset}
  {:error, :members, _, _} -> :error
end

Interestingly enough, if I insert it by hand I don’t have a problem at all, so I guess something is happening when Postgrex is trying to return the query results. Also, the console outputs:

[debug] Query OK db=0.7ms`
rollback []

I think I’ve read somewhere that it would be possible to specify the data types for schemaless operations (or changesets) I don’t remember exactly. So, if someone has a fresh memory on the subject I’d really appreciate the help.

2
  • can you have multiple PKs in Postgres?
  • Do you need to specify the type of the FKs in user?
3

Hi! @cenotaph! Those are not multiple PKs! It generates a composite PK with the two specified fields.
Also, this table is not backed by any schema, hence (I guess) the problem.

But to better respond to that: Yes, if I had a schema I’d have to specify the type in the relationship. But, the only relationship I care about is defined in the “organization” schema using:
many_to_many :members, User, join_through: "organizations_members".

I did not try to use the :join_keys option in the many_to_many because the error happens while inserting, not retrieving the relationship (don’t know if Ecto cares about it at this point).

4

Ah checked your post on mobile first and missed the important part.

When you retrieved your user through Ecto it did it’s casting and all the magic.

Your user.id is a string and not a binary (for postgres) since you are entering data without ecto casting in the insert_all method it generates that error. The following should help you.

insert_membership = fn repo, %{organization: organization} ->
  membership = %{user_id: Ecto.UUID.dump!(user.id), organization_id: organization.id, role: "admin"}
  repo.insert_all("organizations_members", [membership])
end

https://hexdocs.pm/ecto/Ecto.UUID.html#dump!/1

Converts a string representing a UUID into a binary.

2 Likes
5

Yep, this seems about right… I always forget that if you’re not using a changeset to sanitize data you’re basically on your own with Ecto. What caught me by surprise though is that Postgrex wouldn’t just dump it or pass the input down directly to Postgres.

Thanks for the help @cenotaph. Cheers!

1 Like