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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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)?

Hi,

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?

Regards
Mitu
From: Pete Hollobon [mailto:postgres@hollobon.com]
Sent: May 27, 2015 3:04 PM
To: Albe Laurenz
Cc: Mitu Verma; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Re: duplicate key value violates unique constraint "pg_class_relname_nsp_index"

On 27 May 2015 at 09:57, Albe Laurenz <laurenz.albe@wien.gv.at<mailto: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)?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

What this indicates is that someone, or some thing, is trying to create a
table in a schema that already exists.
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.

On Wed, May 27, 2015 at 4:19 AM, Mitu Verma <mitu.verma@ericsson.com> wrote:

HI,

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.

Following tables are used frequently in our case

CREATE TABLE AuditTrailLogEntry

(

event int2,

inNodeID VARCHAR(80),

inNodeName VARCHAR(80),

sourceID VARCHAR(300),

inTime TIMESTAMP, -- YYYY/MM/DD HH:MM:SS.mmm

outNodeID VARCHAR(80),

outNodeName VARCHAR(80),

destinationID VARCHAR(300),

outTime TIMESTAMP, -- YYYY/MM/DD HH:MM:SS.mmm

bytes bigint,

cdrs bigint,

tableIndex bigint, -- Unique key

noOfSubfilesInFile bigint,

recordSequenceNumberList VARCHAR(1000),

primary key (tableindex)

) TABLESPACE MMDATA; –

CREATE TABLE EventLogEntry

(

tableIndex int4, -- Unique key

object VARCHAR(80),

method VARCHAR(80),

bgwUser VARCHAR(80),

time CHAR(23), -- YYYY/MM/DD HH:MM:SS.mmm

realUser VARCHAR(80),

host VARCHAR(80),

application VARCHAR(80)

) TABLESPACE MMDATA;

Regards

Mitu

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general