5 ms·
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 lo
by hot_gril 3y ago
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.
- dns_snek 3y agoAre 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.
- saltcured 3y agoI 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.
- remram 3y agoWith what isolation level?
- hot_gril 3y agoThe default, read committed
- kroolik 3y agoIf 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.
- marcosdumay 3y agoWell, if it's surprising, it's because you have too much exposure to imperative languages. I can agree this is common, but then, the issue isn't with postgres, it's with software development as a whole.
- hot_gril 3y agoThe 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. * https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED https://www.postgresql.org/docs/current/transaction-iso.html...
- postgressomethi 3y ago> 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.
- hot_gril 2y agoI probably got the wrong query. There was some other one where the unique index removes a race condition, lemme find it...