Thread: duplicate key entries for primary key -- need urgent help
duplicate key entries for primary key -- need urgent help
From
"Kumar, Pawan (Nokia - IN/Bangalore)"
Date:
Hi Guys,
Can you please help here?
Below reported issue in past about duplicate key entries for primary key.
https://www.postgresql.org/message-id/534C8B33.9050807@pgexperts.com
the solution was provided in 9.3 version of postgres but it seems issue is still there in 9.5 version which I am running currently.
Can you please let me know if this is also known in 9.5? any fix or Workaround please?
WBR,
-Pawan
On Thu, Jul 04, 2019 at 01:37:01PM +0000, Kumar, Pawan (Nokia - IN/Bangalore) wrote: >Hi Guys, > >Can you please help here? > >Below reported issue in past about duplicate key entries for primary key. >https://www.postgresql.org/message-id/534C8B33.9050807@pgexperts.com > >the solution was provided in 9.3 version of postgres but it seems issue is still there in 9.5 version which I am runningcurrently. > >Can you please let me know if this is also known in 9.5? any fix or Workaround please? > Which version are you running, exactly? Whih minor version? Why do you think it's the issue you linked? regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
RE: duplicate key entries for primary key -- need urgent help
From
"Kumar, Pawan (Nokia - IN/Bangalore)"
Date:
Thanks a lot Tomas for the reply.
Which version are you running, exactly? Whih minor version?
[Pawan]: Its (PostgreSQL) 9.5.9
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.
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?
I will waiting for your reply
WBR,
-Pawan
-----Original Message-----
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Sent: Thursday, July 04, 2019 10:18 PM
To: Kumar, Pawan (Nokia - IN/Bangalore) <pawan.kumar@nokia.com>
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 01:37:01PM +0000, Kumar, Pawan (Nokia - IN/Bangalore) wrote:
>Hi Guys,
>
>Can you please help here?
>
>Below reported issue in past about duplicate key entries for primary key.
>https://www.postgresql.org/message-id/534C8B33.9050807@pgexperts.com
>
>the solution was provided in 9.3 version of postgres but it seems issue is still there in 9.5 version which I am
runningcurrently.
>
>Can you please let me know if this is also known in 9.5? any fix or Workaround please?
>
Which version are you running, exactly? Whih minor version?
Why do you think it's the issue you linked?
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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
Re: duplicate key entries for primary key -- need urgent help
From
"Kumar, Pawan (Nokia - IN/Bangalore)"
Date:
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
On Thu, Jul 04, 2019 at 06:28:03PM +0000, Kumar, Pawan (Nokia - IN/Bangalore) wrote: >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 > Well, I've already pointed out you're missing 2 years worth of fixes, so upgrading to current minor version is the first thing I'd do. (I doubt people will be rushing to help you in their free time when you're missing two years of fixes, possibly causing this issue.) If the issue happens even after upgrading, we'll need to see more details about an actual case - commands creating/modifying the duplicate rows, or anything you can find. It's impossible to help you when we only know there are duplicate values in a PK. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services