Ecto support for arbitrary fields in the `returning` option

[Felipe Philipp]

Hi,

When doing upserts, I would like to know whether the row was inserted or updated, since I need to act differently in case the row was inserted.

I didn't know but there is a way to get that from Posgres, by returning the value of `xmax`, which is a special system column: https://hakibenita.com/postgresql-unknown-features#get-the-number-of-updated-and-inserted-rows-in-an-upsert

In short, what you'd need to do is add a statement to the RETURNING option, as follows:

INSERT INTO ...

ON CONFLICT (...) DO UPDATE SET ...

RETURNING *, (xmax = 0) AS inserted;

I tried to do that in Ecto by adding the `xmax` above to the `returning` option, with no success. It seems that Ecto only allows existing fields in the schema to be returned like that.

I also tried to create a virtual field, or a field with the "source", however, it breaks when doing regular selects or when trying to insert a row.

Is there a way to specify a string/fragment in the `returning` option?

[José Valim]

Currently it is not possible. But maybe Ecto should be using this feature by default to say if something was upserted or not. Sometimes you can look into the ID column too.

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/9d1c8ca5-d95a-41fd-b8a7-f878475c8f7fn%40googlegroups.com.

[Felipe Philipp]

If you're talking about the `id` being `nil` when a conflict happens, it only works when `on_conflict: :nothing` is set, which would require an extra query to fetch the latest state from the DB, right?

The other upsert approach is with `read_after_writes: true` or `returning: true`, but then I don't have the info whether the row was inserted or updated, which is exactly the reason I pursued the approach from the article.

A workaround would be to check if the inserted_at==updated_at, but that feels hacky and prone to timing error. I mean, I am not super sure if those timestamps are exactly the same at the creation time.

Would be great if Ecto was identifying this by default, but I think it only works for Postgres. My suggestion would be to allow the `returning` option to receive a statement that is not a field in the schema, like `returning: {"*", "(xmax = 0) as inserted"}`, but I'm not sure how this breaks the API.

[José Valim]

My concern with allowing fragments like that is that someone could pass dynamic strings that would lead to SQL injection. So I would actually try to change Ecto to compute this by default. All schemas have a field named "context" under the metadata where we could store this result on?

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/a38ff948-8796-43f7-a7ca-96c81a8d744an%40googlegroups.com.

[Felipe Philipp]

That makes sense about SQL injection. I wasn't aware of the Metadata or context at all! It looks like a good place to store such info. I could give a shot in a PR.

[Zach Daniel]

I've been hunting around, but haven't yet been able to find anything: has there been any motion on this? Looking at the source it doesn't look like anything like this has been implemented.

[Igor Barchenkov]

> I also tried to create a virtual field, or a field with the "source", however, it breaks when doing regular selects or when trying to insert a row.

Sorry for such a late answer, but specifying a virtual field with load_in_query: false should provide a decent solution to your problem:

    field :xmax, :integer, read_after_writes: true, load_in_query: false