Re: duplicate key entries for primary key -- need urgent help - Mailing list pgsql-hackers
| From | Kumar, Pawan (Nokia - IN/Bangalore) |
|---|---|
| Subject | Re: duplicate key entries for primary key -- need urgent help |
| Date | |
| Msg-id | HE1PR0701MB231429387D81374E17806410F0FA0@HE1PR0701MB2314.eurprd07.prod.outlook.com Whole thread Raw |
| In response to | Re: duplicate key entries for primary key -- need urgent help (Tomas Vondra <tomas.vondra@2ndquadrant.com>) |
| List | pgsql-hackers |
Thanks for reply.
This has happened very often and at different production system.
There is no version change. System running with same version since 1 year but duplicate key issue came quiet a time.
And impact is big because of that and only way to fix is to delete the duplicate primary key.
Any suggestions to check which logs? Any command to run to get more info during the issue?
Any potential configuration to check?
Plz suggest
Wbr,
Pk
This has happened very often and at different production system.
There is no version change. System running with same version since 1 year but duplicate key issue came quiet a time.
And impact is big because of that and only way to fix is to delete the duplicate primary key.
Any suggestions to check which logs? Any command to run to get more info during the issue?
Any potential configuration to check?
Plz suggest
Wbr,
Pk
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Sent: Thursday, July 4, 2019 11:31:48 PM
To: Kumar, Pawan (Nokia - IN/Bangalore)
Cc: andres@2ndquadrant.com; andrew@dunslane.net; josh@agliodbs.com; pgsql-hackers@postgresql.org
Subject: Re: duplicate key entries for primary key -- need urgent help
Sent: Thursday, July 4, 2019 11:31:48 PM
To: Kumar, Pawan (Nokia - IN/Bangalore)
Cc: andres@2ndquadrant.com; andrew@dunslane.net; josh@agliodbs.com; pgsql-hackers@postgresql.org
Subject: Re: duplicate key entries for primary key -- need urgent help
On Thu, Jul 04, 2019 at 05:34:21PM +0000, Kumar, Pawan (Nokia - IN/Bangalore) wrote:
>Thanks a lot Tomas for the reply.
>
>Which version are you running, exactly? Whih minor version?
>[Pawan]: Its (PostgreSQL) 9.5.9
>
You're missing 2 years of bugfixes, some of which are addressing data
corruption issues and might have caused this.
>sai=> select version();
> version
>----------------------------------------------------------------------------------------------------------
> PostgreSQL 9.5.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
>(1 row)
>
>Why do you think it's the issue you linked?
>
>[Pawan]: Because the thread which I shared also has problem statement like "Duplicate entries of Primary key" .
>If this is also known to this version, I will be appreciating a lot if we have some Workaround or config change.
>
Duplicate entries are clearly some sort of data corruption, but that
might have happened in various ways - it does not mean it's the same
issue. And yes, 9.5.9 has a fix for the issue in the thread you linked.
>In our production: See below entries, proc_id is primary key and we can see duplicate entries. How it is possible?
>
>sai=> select ctid,proc_id from etl_status where proc_id='2993229';
> ctid | proc_id
>----------+---------
> (381,20) | 2993229
> (388,28) | 2993229
>(2 rows)
>
>Any idea, how it happened?
>
No, that's impossible to say without you doing some more investigation.
We need to know when those rows were created, on which version that
happened (the system might have been updated and the corruption predates
might have happened on the previous version), and so on. For example, if
the system crashed or had any significant issues, that might be related
to data corruption issues.
We know nothing about your system, so you'll have to do a bit of
investigation, look for suspicious things, etc.
FWIW it might be a good idea to look for other cases of data corruption.
Both to know the extent of the problem, and to gain insight.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>Thanks a lot Tomas for the reply.
>
>Which version are you running, exactly? Whih minor version?
>[Pawan]: Its (PostgreSQL) 9.5.9
>
You're missing 2 years of bugfixes, some of which are addressing data
corruption issues and might have caused this.
>sai=> select version();
> version
>----------------------------------------------------------------------------------------------------------
> PostgreSQL 9.5.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
>(1 row)
>
>Why do you think it's the issue you linked?
>
>[Pawan]: Because the thread which I shared also has problem statement like "Duplicate entries of Primary key" .
>If this is also known to this version, I will be appreciating a lot if we have some Workaround or config change.
>
Duplicate entries are clearly some sort of data corruption, but that
might have happened in various ways - it does not mean it's the same
issue. And yes, 9.5.9 has a fix for the issue in the thread you linked.
>In our production: See below entries, proc_id is primary key and we can see duplicate entries. How it is possible?
>
>sai=> select ctid,proc_id from etl_status where proc_id='2993229';
> ctid | proc_id
>----------+---------
> (381,20) | 2993229
> (388,28) | 2993229
>(2 rows)
>
>Any idea, how it happened?
>
No, that's impossible to say without you doing some more investigation.
We need to know when those rows were created, on which version that
happened (the system might have been updated and the corruption predates
might have happened on the previous version), and so on. For example, if
the system crashed or had any significant issues, that might be related
to data corruption issues.
We know nothing about your system, so you'll have to do a bit of
investigation, look for suspicious things, etc.
FWIW it might be a good idea to look for other cases of data corruption.
Both to know the extent of the problem, and to gain insight.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
pgsql-hackers by date: