Re: Re: duplicate key value violates unique constraint "pg_class_relname_nsp_index"

> From: Pete Hollobon [mailto:postgres(at)hollobon(dot)com]
> On 27 May 2015 at 09:57, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)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