Is `Repo.insert_all` with `returning: true` guaranteed to return records in the same order as inputs with postgres?

Questions / Help
1

I have always assumed this to be true but a teammate is questioning it and I can not find any documentation to support my assumption.

Suppose I have a users table that has a name field.

params = [%{name: "Foo"}, %{name: "Bar"}, %{name: "Baz"}]
{3, users} = Repo.insert_all(User, params, returning: true)

Is users guaranteed to be in the same order as params? I.e., is this guaranteed to be true?

Enum.map(users, &(&1.name)) == ["Foo", "Bar", "Baz"]

For simplicity, I’m assuming that the rows to be inserted will all succeed.

I can’t seem to find any particularly convincing interpretation of the postgres docs for RETURNING that tells me if that is guaranteed to return rows in the same order as the VALUES inserted. For example this SO post has conflicting answers with the same number of votes: postgresql - Is INSERT RETURNING guaranteed to return things in the "right" order? - Stack Overflow

2

If it’s not explicitly guaranteed in the docs, then it’s probably safer to not rely on it and do a sort if needed in elixir afterwards.

Also, your question seems to be related more to postgres, so maybe a postgres mailing list would be a better place to ask it.

1 Like
3

It depends on how the Ecto Postgres adapter handles the return values, so I thought it reasonable to ask here.

4

Right, but ecto is not doing anything with the result from postgres by default, so the behaviour is the same as you’d get with running plain sql against it.

5

Ah. Is that fact documented or did you check the source?

6

It’s not documented as far as I know, so I skimmed the source code for ecto, ecto_sql, and postgrex and saw that at no point there is any data reordering.

  • ecto builds the query and passes it to ecto_sql
  • ecto_sql turns the query struct into an iolist of sql and passes it to the adapter
  • ecto_sql postgres adapter gives the query to postgrex and waits for the result, if it’s {:ok, result} it returns it without modifications
  • then ecto maps the result rows into types specified in select

But, my answer above still holds, you can’t rely on anything and if you need sorted results, you need to do the sorting yourself.