PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Does serial data type assign unique values to each thread trying to insert?

I am getting following error when inserting data in Postgres in a multi-threaded env, I am relying of serial data type to assign PK assuming it is thread-safe, I lose data when I get following error, how can ensure that each thread gets unique-id? I am using plain java. i.e java.sql library to perform error:

`org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pg_class_relname_nsp_index"
  Detail: Key (relname, relnamespace)=(table_id_seq, 2200) already exists.`

I am using the followng query to insert entries in table

"insert into "+ MODEL_TABLE +" (b,c,d) values(?,?,?);";

the following query for creating the table

CREATE_MODEL_TABLE =
            "CREATE TABLE if not exists "+ MODEL_TABLE +"(n" +
            "    id SERIAL,n" +
            "    a int,n" +
            "    b int,n" +
            "    c int,n" +
            "    PRIMARY KEY (id,b),n" +
            "  constraint unique_name_%s unique(b,c)"+
            ") PARTITION BY LIST (b);";
Tags:

2

Answers


  1. 0

    pg_class_relname_nsp_index is the name of a UNIQUE index in the system catalog pg_class, where every table-like object (incl. sequences) is registered. Table + schema name must be unique. Hence the unique index on pg_class_relname_nsp_index ON pg_class(relname, relnamespace).

    The error is not raised by inserting into your user table, but during creation of the table. The serial column triggers the creation of an underlying SEQUENCE. See:

    The error really makes no sense. For a serial column, Postgres would avoid using a duplicate sequence name and pick the next free name automatically instead of running into a unique violation. ("table_id_seq1" etc.)

    Even if you try to create a sequence with an occupied name manually, you’d get a different error message.

    Hence I suspect index corruption.

    To fix, run (with the necessary privileges!):

    REINDEX TABLE pg_class;

    This should not occur to begin with. I have never seen this error message in my long Postgres life. Failing hardware comes to mind. If you don’t have a recent backup, now is the time!

    Login or Signup to reply.
  2. 0

    In PostgreSQL a serial field is basically an integer field with a default value from a sequence.

    If you insert rows that has this column filled in, it is stored, and the sequence is not used. This means if your sequencs EG is at 10, and there is already a row with 10 in it, the next INSERT INTO ... VALUES (DEFAULT, ...) will drop you the error you received.

    Make sure that, either no external value get into the serial column, or that the external value is not in the sequence’s range.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search