[jeff-davis]

You can also do the loop on the server side using PL/pgSQL:

https://www.postgresql.org/docs/current/plpgsql-control-stru...

[megous]

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]

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]

Thank you.