The most surprising thing about Postgres indexes as a beginner was how UNIQUE indexes can affect the outcomes of concurrent queries because of the additional locking they add. Something like
INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo);
can insert duplicate `bar` values when run concurrently using the default mode, since one xact might not see the new max value created by the other. You might think adding a UNIQUE index would cause the "losing" xact to get constraint errors, but instead both xacts succeed and no longer have a race condition.
> You might think adding a UNIQUE index would cause the "losing" xact to get constraint errors, but instead both xacts succeed and no longer have a race condition.
This is not true. What happens is that the (sub)transaction that loses the race to the index is aborted:
=# INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo);
ERROR: duplicate key value violates unique constraint "foo_bar_idx"
DETAIL: Key (bar)=(2) already exists.
Are you claiming that both inserts succeed even with a UNIQUE index in place, and they end up inserting duplicate values? That must be a bug if you're right.
I think they're saying that the unique index changes the locking strategy for the queries so they are effectively serialized and will not both read the same max value for existing rows.
If im not mistake, you can do this with no downtime by creating a regular index CONCURRENTLY and creating a not checked unique constraint. The constraint applies to new insert/updates only. After that, you run VALIDATE on the constraint and it will be a fully fledged unique constraint.
The exact guarantees you get from Postgres's default isolation level are pretty detailed. The manual* doesn't mention locking on constraints or unique indexes, but I can make guesses based on how I'd expect it to work. I don't see what it has to do with the language being declarative. It's not unique to Postgres, though, cause MySQL is similar.
