I imagine there’s a dependency on operation order. If in the transaction, you update a row in way that will ultimately avoid the conflict, and then insert (or update) the conflicting row… then MVCC will have you covered.
However, if first you want to insert the conflicting row and the update the old row to be non-conflicting, then the deferral is something you’d need.
Admittedly, don’t run into this that often so I’d likely run a couple scenarios on-database to be sure I had it right, but that’s my understanding why the unique constraint is deferrable and mentioned later in the documentation you posted.
Here’s a walk through of transactions involving just MVCC and constraint deferability.
First we’ll set up the scenario with a simple table and the kind of scenario where you might need a deferrable unique constraint… but I’ll start with INITIALLY IMMEDIATE:
localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Tue Dec 10 09:59:23 PST 2024]
> create table scb_test (
id bigint generated by default as identity ,
u1_id integer not null,
u2_id text not null,
constraint test_udx unique (u1_id, u2_id) deferrable initially immediate);
CREATE TABLE
Time: 4.533 ms
localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Tue Dec 10 10:00:11 PST 2024]
> insert into scb_test (u1_id, u2_id) values (1, 'a'),(2, 'b'),(3, 'c');
INSERT 0 3
Time: 2.640 ms
localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Tue Dec 10 10:02:24 PST 2024]
> select * from scb_test;
id | u1_id | u2_id
----+-------+-------
1 | 1 | a
2 | 2 | b
3 | 3 | c
(3 rows)
Time: 0.467 ms
OK… let’s do a transaction where we’ll update an existing record and insert a new one that would be conflicting if not for the update. We’ll update the first record in a way to avoid the conflict, and then insert an otherwise conflicting record. All of this is in one transaction, so to anyone outside of the transaction, the updated record will still have it’s original values until commit:
localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Tue Dec 10 10:02:57 PST 2024]
> begin;
BEGIN
Time: 0.207 ms
localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Tue Dec 10 10:03:00 PST 2024]
> update scb_test set u1_id = 4, u2_id = 'd' where u1_id = 3 and u2_id = 'c';
UPDATE 1
Time: 0.376 ms
localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Tue Dec 10 10:03:04 PST 2024]
> insert into scb_test (u1_id, u2_id) values (3, 'c');
INSERT 0 1
Time: 0.279 ms
localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Tue Dec 10 10:03:10 PST 2024]
> commit;
COMMIT
Time: 1.926 ms
localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Tue Dec 10 10:03:25 PST 2024]
> select * from scb_test;
id | u1_id | u2_id
----+-------+-------
1 | 1 | a
2 | 2 | b
3 | 4 | d
4 | 3 | c
(4 rows)
Time: 0.383 ms
Not deferrable and no conflict. Because we resolved the uniqueness conflict with an order of operations which, inside of the transaction, avoided the conflict, we did not need deferability on commit. However, if we switch that operation ordering with a similar set of commands:
localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Tue Dec 10 10:03:29 PST 2024]
> begin;
BEGIN
Time: 0.274 ms
localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Tue Dec 10 10:03:45 PST 2024]
> insert into scb_test (u1_id, u2_id) values (3, 'c');
ERROR: duplicate key value violates unique constraint "test_udx"
DETAIL: Key (u1_id, u2_id)=(3, c) already exists.
Time: 0.489 ms
localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Tue Dec 10 10:04:12 PST 2024]
> rollback;
ROLLBACK
Time: 0.207 ms
Because we tried to insert the conflicting record before updating the original record with those values, we got the constraint violation. Now let’s try that same operation with the constraint deferred:
localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Tue Dec 10 10:05:50 PST 2024]
> begin;
BEGIN
Time: 0.352 ms
localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Tue Dec 10 10:05:57 PST 2024]
> set constraints test_udx deferred;
SET CONSTRAINTS
Time: 0.400 ms
localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Tue Dec 10 10:06:00 PST 2024]
> insert into scb_test (u1_id, u2_id) values (3, 'c');
INSERT 0 1
Time: 0.414 ms
localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Tue Dec 10 10:06:05 PST 2024]
> update scb_test set u1_id = 5, u2_id = 'e' where id = 4;
UPDATE 1
Time: 0.336 ms
localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Tue Dec 10 10:06:52 PST 2024]
> commit;
COMMIT
Time: 2.065 ms
localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Tue Dec 10 10:06:55 PST 2024]
> select * from scb_test;
id | u1_id | u2_id
----+-------+-------
1 | 1 | a
2 | 2 | b
3 | 4 | d
6 | 3 | c
4 | 5 | e
(5 rows)
Time: 0.524 ms
Our conflicting insert worked and… since we resolved the conflict prior to committing the transaction we were able to commit the transaction successfully. Had we not resolved the conflict, the transaction would have failed because of the constraint violation.
Anyway, wanted to be sure that we were straight for future readers.
Bringing this back to something to do with Elixir/Ecto SQL…
If all you need is enforced uniqueness, I see no problem with the unique_index method. You’re going to end up with a unique index whether you’re creating the constraint or creating the index directly. But, if you do have the scenario where you’ll be resolving unique conflicts in your code and don’t want to worry about detailed database operation ordering within the transaction, I believe a constraint is worth the extra Elixir/Ecto ceremony to get it created.
Thank you all for the great discussion and insights! It’s much appreciated
@LostKobrakai I agree that Ecto shouldn’t support every feature of every storage engine under the sun but in this case a little syntax sugar would have been nice actually I already did use execute as you suggested but it felt a little heavy handed for something seemingly simple - hence my question.
@sbuttgereit thank you so much for the in depth analysis. This was a fantastic answer - and the kind of answer why I adore this community. You all are really an asset to this ecosystem!
actually I already did use execute as you suggested but it felt a little heavy handed for something seemingly simple - hence my question.