5 ms·

You can also do the loop on the server side using PL/pgSQL: https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING http

You can also do the loop on the server side using PL/pgSQL: https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING https://www.postgresql.org/docs/current/plpgsql-control-stru...

megous 5y ago
That's not that trivial. You can't just loop to get a unique ID. Maybe if you lock the whole table for reads first, which is quite drastic.
- jeff-davis 5y ago
  I should have been more clear. Here are the details: If you read the linked doc, you'll see an EXCEPT clause. That can be used for a retry loop inserting into a table with a UNIQUE constraint. No read locks are necessary, because the UNIQUE constraint will catch violations safely (regardless of concurrent activity), and the retry loop can simply retry until that succeeds. For instance: CREATE TABLE u(i INT8 UNIQUE); -- insert random unique value in the range 0..n -- into table u, retrying if it's already present -- -- NOTE: this will not terminate if 0..n are all -- present CREATE OR REPLACE FUNCTION insert_uniq(n INT8) RETURNS VOID LANGUAGE plpgsql AS $$ DECLARE x INT8; BEGIN <<retry_loop>> LOOP BEGIN x := (random() * n)::int8; INSERT INTO u VALUES(x); RAISE NOTICE 'inserted unique value %', x; EXIT retry_loop; EXCEPTION WHEN unique_violation THEN RAISE NOTICE 'collision with value %; retrying', x; END; END LOOP; END; $$; This will obviously loop forever if 0..n are all occupied, but if you choose n as (2::numeric^63 - 1)::int8, that won't happen.
  - megous 5y ago
    Thank you.

megous 5y ago

That's not that trivial. You can't just loop to get a unique ID. Maybe if you lock the whole table for reads first, which is quite drastic.

jeff-davis 5y ago
I should have been more clear. Here are the details: If you read the linked doc, you'll see an EXCEPT clause. That can be used for a retry loop inserting into a table with a UNIQUE constraint. No read locks are necessary, because the UNIQUE constraint will catch violations safely (regardless of concurrent activity), and the retry loop can simply retry until that succeeds. For instance: CREATE TABLE u(i INT8 UNIQUE); -- insert random unique value in the range 0..n -- into table u, retrying if it's already present -- -- NOTE: this will not terminate if 0..n are all -- present CREATE OR REPLACE FUNCTION insert_uniq(n INT8) RETURNS VOID LANGUAGE plpgsql AS $$ DECLARE x INT8; BEGIN <<retry_loop>> LOOP BEGIN x := (random() * n)::int8; INSERT INTO u VALUES(x); RAISE NOTICE 'inserted unique value %', x; EXIT retry_loop; EXCEPTION WHEN unique_violation THEN RAISE NOTICE 'collision with value %; retrying', x; END; END LOOP; END; $$; This will obviously loop forever if 0..n are all occupied, but if you choose n as (2::numeric^63 - 1)::int8, that won't happen.
- megous 5y ago
  Thank you.