CODEWITHSUNDEEP
postgresqlstored-proceduresconstraintsuniqueisolation-level
Jarek
Jarek

Reputation: 351

Postgres: how to add unique identifier to table

I have the following table:

CREATE TABLE myid
(
  nid bigserial NOT NULL,
  myid character varying NOT NULL,
  CONSTRAINT myid_pkey PRIMARY KEY (myid )
)

Now, I want to add records to this table with the following function:

CREATE FUNCTION getmyid(_myid character varying)
  RETURNS bigint AS
$BODY$ --version 1.1 2015-03-04 08:16
DECLARE
  p_nid bigint;
BEGIN
  SELECT nid INTO p_nid FROM myid WHERE myid=_myid FOR UPDATE;
  IF NOT FOUND THEN
    INSERT INTO myid(myid) VALUES(_myid) RETURNING nid INTO p_nid;
  END IF;
  RETURN p_nid;
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Generally it works fine, but under high load, this function sometimes fails with "duplicate key value violates unique constraint "myid_pkey"; This function is called from trigger on insert on another table, and inserts are called within transaction. Isolation level is set for READ COMMITED, postgres 9.1 on Debian Wheezy. What I'm doing wrong ?

Upvotes: 1

Views: 534

Answers (1)

Eugene
Eugene

Reputation: 2701

I see following way how it happens.

  1. Two processes(threads) call the function simultaneously with the same myid.
  2. Both threads successfully execute SELECT nid INTO .. query, and see - there is no such myid in table now.
  3. Both threads go into IF NOT FOUND THEN
  4. Thread 1 executes INSERT INTO myid(myid) and commits transaction with no errors
  5. Thread 2 executes INSERT INTO myid(myid) and fails, because same myid value already exists in table (PRIMARY KEY constraint).

Why Thread 2 sees other transaction committed data in own transaction ? Because of 'non-repeatable read' phenomena, which is possible with READ COMMITTED isolation (http://www.postgresql.org/docs/9.2/static/transaction-iso.html).

Upvotes: 1

Related Questions