silverdr

silverdr

From another thread - making it a separate topic:

To make sure I understand correctly (because TFM seems ambiguous on this to me):

When there’s conflict (like a UNIQUE constraint being violated), does the insert/2 function return the conflicting record from the database, discarding the struct or changeset passed to it? What if multiple records conflicted with the passed data? Like when violating constraints on several fields?

Update:

Did some tests (using Postgres), and it seems that given a changeset, inserting which would violate unique constraint, Repo.insert(changeset, returning: true, on_conflict: :nothing) returns the data passed to it rather than the record from the database. Is this the expected behaviour? If yes then what’d be the way to obtain the existing record without performing a separate query for this purpose?

Showing Posts 1 to 10

LostKobrakai

LostKobrakai

Yes. To get the db record either do a query manually or use returning: true as additional option and make sure “something” is updated on conflict (often e.g. timestamps are good candidates).

silverdr

silverdr OP

Once more to make sure - is the fact that I used on_conflict: :nothing the reason for NOT receiving the record from the database? IOW - the function doesn’t do anything beyond translating from Elixir syntax to SQL, unlike e. g. Rails’ create_or_find_by, right?

LostKobrakai

LostKobrakai

Exactly.

DavidRawk-Blake

DavidRawk-Blake

Without doing a write, is there a way to get this?

silverdr

silverdr OP

Separate query, I reckon…

zzq

zzq

The thing I generally do is use :replace with the fields in the unique constraint (the conflict target) instead of using :nothing. (e.g. if the table has a unique constraint on the pair of columns a and b, I do conflict_target: [:a, :b], on_conflict: {:replace, [:a, :b]}.) I know for a fact that when the on_conflict fires, doing that replace is a no-op. But it’s enough in order for the database to consider the record to be “updated” for the purposes of returning: true.

silverdr

silverdr OP

Right, but is this something that might change between implementations/versions? Or a documented behaviour that can be relied upon? I don’t say it isn’t. Just asking.

sbuttgereit

sbuttgereit

One note about this technique. From the application point of view this is a no-op, but assuming I’m reading the ecto_sql code correctly, in the database I don’t believe it is a no-op. Assuming my recollection is correct PostgreSQL would see this effectively as an update. This is why you’d get a returning result but which also means, on disk, you’d get the current row marked as deleted and a new row created; naturally the new row would be from your point of view the same data and appear as the old row, but the database overhead of an actual update would be incurred… including the ramifications for table bloat, vacuum overhead, etc.

I’ll double check this a bit later when I have time, but am confident enough that the generated SQL would cause that to be the case to say something now.

silverdr

silverdr OP

That’d be my suspicion too. And the reason I asked whether it’s a [PostgreSQL] documented behaviour [that it’s a true :noop]

sbuttgereit

sbuttgereit

So from the PostgreSQL side, it can be demonstrated that ON CONFLICT ... DO UPDATE is not a no-op.

  1. create a small demo table:
127.0.0.1(from musebms).postgres.scb.5432 [Thu Oct  6 01:22:05 PM PDT 2022]
> CREATE TABLE demo
(
    id         integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    text_value text NOT NULL
);
CREATE TABLE
Time: 12.746 ms

  1. Insert a single row into the table; We’ll do this in a transaction block so we can query the transaction id as well.
127.0.0.1(from musebms).postgres.scb.5432 [Thu Oct  6 01:22:23 PM PDT 2022]
> BEGIN;
BEGIN
Time: 0.775 ms
127.0.0.1(from musebms).postgres.scb.5432 [Thu Oct  6 01:22:33 PM PDT 2022]
> SELECT txid_current();
 txid_current 
--------------
         8655
(1 row)

Time: 1.083 ms
127.0.0.1(from musebms).postgres.scb.5432 [Thu Oct  6 01:22:40 PM PDT 2022]
> INSERT INTO demo (text_value) VALUES ('demo') RETURNING *;
 id | text_value 
----+------------
  1 | demo
(1 row)

INSERT 0 1
Time: 1.315 ms
127.0.0.1(from musebms).postgres.scb.5432 [Thu Oct  6 01:22:51 PM PDT 2022]
> COMMIT;
COMMIT
Time: 2.317 ms
  1. Let’s query the table, but we’ll include the system columns xmin (The identity (transaction ID) of the inserting transaction for this row version.) and ctid (The physical location of the row version within its table.)
127.0.0.1(from musebms).postgres.scb.5432 [Thu Oct  6 01:22:59 PM PDT 2022]
> SELECT xmin, ctid, id, text_value FROM demo;
 xmin | ctid  | id | text_value 
------+-------+----+------------
 8655 | (0,1) |  1 | demo
(1 row)

Time: 1.145 ms
  1. Now that we have a row and understand the system perspective of that row, let’s do an INSERT ... ON CONFLICT in a way that’s similar to what I believe the Ecto query to produce. Again, we’ll do this in a transaction so we can capture some of the transaction details.
127.0.0.1(from musebms).postgres.scb.5432 [Thu Oct  6 01:23:08 PM PDT 2022]
> BEGIN;
BEGIN
Time: 0.778 ms
127.0.0.1(from musebms).postgres.scb.5432 [Thu Oct  6 01:23:18 PM PDT 2022]
> SELECT txid_current();
 txid_current 
--------------
         8656
(1 row)

Time: 0.746 ms
127.0.0.1(from musebms).postgres.scb.5432 [Thu Oct  6 01:23:25 PM PDT 2022]
> INSERT INTO demo
    ( id, text_value )
VALUES
    ( 1, 'demo' )
ON CONFLICT (id) DO UPDATE SET id = excluded.id, text_value = excluded.text_value
RETURNING *;
 id | text_value 
----+------------
  1 | demo
(1 row)

INSERT 0 1
Time: 1.141 ms
127.0.0.1(from musebms).postgres.scb.5432 [Thu Oct  6 01:23:35 PM PDT 2022]
> COMMIT;
COMMIT
Time: 2.014 ms
  1. Now let’s query the table again, and again with the system columns. Note that we’re not filtering the table and we only have one row which is identical in the id and text_value user columns.
127.0.0.1(from musebms).postgres.scb.5432 [Thu Oct  6 01:23:48 PM PDT 2022]
> SELECT xmin, ctid, id, text_value FROM demo;
 xmin | ctid  | id | text_value 
------+-------+----+------------
 8656 | (0,2) |  1 | demo
(1 row)

Time: 1.237 ms

As we can see, even though the record would appear to have not changed to a user it’s in fact a new record (all PostgreSQL updates can be thought of as a DELETE/INSERT combination). And if we think about it, this makes sense. PostgreSQL generating apparent data changes without actually doing anything (a no-op) wouldn’t be desirable if you were needing logic that included the transaction details… since our “upsert” query happened within the context of a transaction it makes sense that anything PostgreSQL tells us it did would have to also reflect the effective transaction id.

Naturally this doesn’t cover the Ecto side, and I don’t have a good environment for demonstrating from Elixir right now; but I did take a quick look at the source code and I’m pretty sure the on_conflict: {:replace, [:a, :b]} construct creates an upsert query similar to my test query.

So the moral of the story is don’t do no-change updates to the database unless you’re consciously accepting the additional table bloat, WAL updates, etc. that come with that.

Where Next? Top

Trending in Questions Top

Hellowman
Hello everyone! I am new to coding with Elixir/Erlang. And i am looking for some utilities for fuzzing testing like AFL++. Does anyone ...
New
yarrichar
I’m very new to Elixir, so sorry if this is a silly question: I am wondering what happens to the mailbox of a process when the machine i...
New
pragdave
How do I tell Hologram not to escape HTML when substituting into templates? {Textile.render(@product.long_description)} escapes the HTM...
New
Fl4m3Ph03n1x
Background When trying to execute mix release on a Windows 11 machine for a Phoenix project I get the following error: * assembling mark...
New
mandar1045
I load tested a plain Phoenix chat app backed by Postgres with 100 million messages in it, everything on default settings, to see what br...
New
rlipscombe
I’d like to create a file with 600 permissions (rw-------), but there’s a race condition if I create it and then change the permissions. ...
New
saleyn
Is there a way to import record definitions in the iex shell for pretty printing (similar to the rr command in the erl shell)? This woul...
New

Other Trending Topics Top

GenericJam
Edit: 2026 May 15 - This post is archived. Mob is alive!! Main docs: mob v0.7.11 — Documentation A bit of explanation for the slightly c...
261 13177 112
New
dominicletz
Hey there, I haven’t seen a list of useful Elixir specific skills for the popular AI tools yet so I’m starting one here with my first. ...
New
mudasobwa
I fully migrated to my own harness from Anthropic/Gemini and I think it’s time to share it. Welcome DSH, the DeepSeek Harness, fully writ...
New
OndrejValenta
(I just needed to vent somewhere and LinkedIn is full of hope, or hype, I’m not sure which exactly) AI dream has many faces, but general...
New
kip
Localize is the next generation localisation library for Elixir. Think of it as ex_cldr version 3.0. The first version will be released ...
New
jimsynz
Beam Bots (or just BB for short) is a framework for building fault-tolerant robotics applications in Elixir using familiar OTP patterns. ...
New

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews