I couldn’t let it go…

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.

afaik they’re the same thing at least on regular sql databases(i’m not sure about timescale and other “sql-like” databases with mods). but on regular sql db unique constraint on a column is achieved by a unique index.

just for reference from postgres

Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint. A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index.
PostgreSQL: Documentation: 17: 5.5. Constraints

From the PostgreSQL perspective this is a distinction without a real difference. The unique constraint is implemented via the unique index.

Per the docs:

PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.

It is valid to create the index without the constraint.

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!

That’s not correct. You get the error for uniqueness validation immediately on the command creating the violation. Delete and insert has no conflict. Allowing two rows to temporarily share a unique column value within a transaction needs a deferred constraint.