Thread: duplicate key value violates unique constraint "pg_class_relname_nsp_index"

Mitu Verma wrote:
> Following error is continuously seen with the postgreSQL database which we are using at customer site.
> 
> Current Errors observed: ./fm_db_VoiceReprocessing1/data/pg_log/postgresql-04.log:2015-04-04 01:00:16
> CESTERROR: duplicate key value violates unique constraint "pg_class_relname_nsp_index"
> 
> Any pointers on why these errors are coming? What is the meaning of duplicate key value violates
> unique constraint "pg_class_relname_nsp_index"
> 
> If it is due to some index corruption or duplicate index? Please help.

This is perfectly harmless from a data integrity point of view, it is just an error
message that the user gets when he or she tries to insert a row whose primary key
already exists in the table:

test=> CREATE TABLE test (id integer PRIMARY KEY, val text);
CREATE TABLE
test=> INSERT INTO test VALUES (1, 'one');
INSERT 0 1
test=> INSERT INTO test VALUES (2, 'one');
INSERT 0 1
test=> INSERT INTO test VALUES (1, 'two');
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.

If you don't want user errors to be logged in the server log, you can
increase "log_min_messages" to "log".

Yours,
Laurenz Albe

> From: Pete Hollobon [mailto:postgres@hollobon.com]
> On 27 May 2015 at 09:57, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
>> Mitu Verma wrote:
>>> Following error is continuously seen with the postgreSQL database which we are using at customer site.
>>>
>>> Current Errors observed: ./fm_db_VoiceReprocessing1/data/pg_log/postgresql-04.log:2015-04-04 01:00:16
>>> CESTERROR: duplicate key value violates unique constraint "pg_class_relname_nsp_index"
>>>
>>> Any pointers on why these errors are coming? What is the meaning of duplicate key value violates
>>> unique constraint "pg_class_relname_nsp_index"
>>>
>>> If it is due to some index corruption or duplicate index? Please help.

>> This is perfectly harmless from a data integrity point of view, it is just an error
>> message that the user gets when he or she tries to insert a row whose primary key
>> already exists in the table:

> But the constraint here is pg_class_relname_nsp_index - the unique index on pg_class (relname,
> relnamespace). You don't get that error if you try to create a table that already exists. How could
> you end up with that error (short of attempting to directly insert rows in pg_class)?

Oops, I didn't see that it was a system index.  My mistake.

Mitu Verma wrote:
> I have tried to see the postgres code(although new to this !) and found this.
> 
> declare unique index pg_class_relname_nsp_index 2663 on pg_class using btree(relname name_ops,
> relnamespace oid_ops)
> 
> Not sure why ‘duplicate key value violates unique constraint "pg_class_relname_nsp_index"’ message is
> coming in the logs ?
> 
> Any leads?

Yes, that is could indeed be data corruption, unless somebody messes around with
the system catalogs (can you exclude that?).

I would shut down the cluster right away and take a physical backup of the files
before doing more.

Have there been any crashes lately?

Do you have any idea what action triggers the error message?
    
If you try "REINDEX TABLE pg_class" as superuser, does the problem go away?

Yours,
Laurenz Albe

On 05/27/2015 06:05 AM, Melvin Davidson wrote:
> What this indicates is that someone, or some thing, is trying to create
> a table in a schema that already exists.

The error you see in that situation is:

postgres-2015-05-27 06:25:10.173 PDT-0ERROR:  relation "table1" already
exists
postgres-2015-05-27 06:25:10.173 PDT-0STATEMENT:  create table table1 (i
int);


Best guess is as Pete and Albe said, some user code is directly
accessing pg_class or the index has been corrupted.

> Here is the structure of pg_class_relname_nsp_index:
> CREATE UNIQUE INDEX pg_class_relname_nsp_index ON pg_class USING btree
> (relname, relnamespace)
>
> What you should also see in the error log is a line immediately
> following that error which shows you exactly what the conflict is, but
> you have not provided that to us.
>



--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 05/27/2015 06:05 AM, Melvin Davidson wrote:
>> What this indicates is that someone, or some thing, is trying to create
>> a table in a schema that already exists.

> The error you see in that situation is:

> postgres-2015-05-27 06:25:10.173 PDT-0ERROR:  relation "table1" already
> exists
> postgres-2015-05-27 06:25:10.173 PDT-0STATEMENT:  create table table1 (i
> int);

> Best guess is as Pete and Albe said, some user code is directly
> accessing pg_class or the index has been corrupted.

I don't think it's necessary to make such assumptions to explain the
errors.  What is more likely is that two sessions are trying to create
identically named tables at about the same time.  You do get the nice
user-friendly "already exists" error if the conflicting table was
committed before CREATE TABLE looks --- but in a concurrent-transactions
situation, neither CREATE TABLE will see the other's table as already
existing.  In race conditions like this, it's the unique index on the
catalog that is the duplicate-preventer of last resort, and it's going
to throw this error.

            regards, tom lane