BUG #14598: Duplicate values found when reindexing unique index
The following bug has been logged on the website:
Bug reference: 14598
Logged by: Manoj Kumar
Email address: manoj.kumar.mbm@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system: RHEL 5.8
Description:
I have encountered the same bug as mentioned at
:-/messages/by-id/8bca3aa10712300802h1c34ce69j1a1ab8b730c002e9@mail.gmail.com
[Version]
Red Hat Enterprise Linux 5.8 (x86_64)
PostgreSQL
8.1.18
[Occurrence]
Rarely
[Impact]
Critical, partition on which pgdata lie become full and PostgreSQL server
shut down abruptly.
[Scenario in which this bug produced]
1) Reindex statement :-
<127.0.0.1(58384)%2016-09-13 02:35:04 GMT> LOG: duration: 47606.785 ms
statement: REINDEX TABLE tb_node_performance_info;REINDEX TABLE tb_.........
(40-50 tables more)
2) Insert statement are triggering on cron basis.
##########################################
<169.254.136.51(54356)%2016-09-13 02:35:05 GMT> LOG: duration: 2274.218 ms
statement: INSERT INTO tb_node_performance_info (
node_id,timeflag,cpuusedrate,memorytotal,memoryfree,totalbandwidthread,totalbandwidthwrite
)VALUES( 'HN0801',1473733740,0.157,98806968,95408416,0.1,0 )
#############################################
#######################
<169.254.135.55(43842)%2016-09-13 02:35:06 GMT> LOG: duration: 1679.948 ms
statement: INSERT INTO tb_node_performance_info (
node_id,timeflag,cpuusedrate,memorytotal,memoryfree,totalbandwidthread,totalbandwidthwrite
)VALUES( 'HN0705',1473733980,0.146,98806968,95607020,0,0 )
###########################
3) Duplicate key violation is observed:-
###############################################################
<169.254.131.33(51484)%2016-09-13 02:35:08 GMT> ERROR: duplicate key
violates unique constraint "tb_node_performance_info_pkey"
<169.254.131.33(51484)%2016-09-13 02:35:08 GMT> CONTEXT: SQL statement
"INSERT INTO tb_node_performance_info (node_id,timeflag,cpuusedrate,
memorytotal,memoryfree)VALUES( $1 , $2 , $3 , $4 , $5 )"
###################################################################
4) suddenly the index file of tb_performance_info_pkey got deleted:-
<%2016-09-13 02:38:13 GMT> ERROR: could not open relation
1663/16385/280951: No such file or directory
5) After that reindex fails and wall size keep on increasing.
Result partition full and PostgreSQL fails.
-----------------------------------
Faced two times in past year.
-----------------------------------
[queries]
1) primary key is [Node_ID, timestamp] tb_node_performance_info.
This insert statement always trigger on every minute by cron. primary key is
ale ways unique because timestamp(epoch) + node_id is always unique.
There is no point of duplicate data, then why we get duplicate key violation
msg ?
2) Is there any way to recover the data?
(Currently I have pgdata which I cannot read because of missing relation
file. )
3) Is this bug related to BUG #11141: Duplicate primary key values
corruption.
Note:- we have analyzed complete partition and found that there is no
hardware related issue.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Sat, Mar 25, 2017 at 5:22 PM, <manoj.kumar.mbm@gmail.com> wrote:
[Version]
Red Hat Enterprise Linux 5.8 (x86_64)
PostgreSQL
8.1.18
The last minor version of 8.1.X was released in 2010 and has fallen
out of support since. Even this version is missing one year of bug
fixes, so you could do a minor update first. And at the end you will
need to upgrade.
--
Michael
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Dear Michael,
I understand that upgrade is necessary.
But the question is upgradation of postgresql really fix this issue?
Until or unless the root cause is known, it's tough to accept that after
upgradation of postgresql this issue will never occur.
If it is possible for you, guide me how can I proceed to find the root
cause.
On Mar 26, 2017 6:41 PM, "Michael Paquier" <michael.paquier@gmail.com>
wrote:
Show quoted text
On Sat, Mar 25, 2017 at 5:22 PM, <manoj.kumar.mbm@gmail.com> wrote:
[Version]
Red Hat Enterprise Linux 5.8 (x86_64)
PostgreSQL
8.1.18The last minor version of 8.1.X was released in 2010 and has fallen
out of support since. Even this version is missing one year of bug
fixes, so you could do a minor update first. And at the end you will
need to upgrade.
--
Michael
On Sun, Mar 26, 2017 at 08:18:49PM +0530, Manoj Verma wrote:
Dear Michael,
I understand that upgrade is necessary.
But the question is upgradation of postgresql really fix this issue?
Until or unless the root cause is known, it's tough to accept that after
upgradation of postgresql this issue will never occur.If it is possible for you, guide me how can I proceed to find the root
cause.
It is also tough to accept that someone is requesting help with version
of PostgreSQL which is not supported for over 6 years, and has known
bugs. Spending time to support *unsupported* version to find that (in
most likely scenario) the bug has been fixed lont time ago, or (in
less likely scenario) it was hardware error - is just very unlikely to
happen.
Best regards,
depesz
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Dear depesz,
I am currently on Rhel 5.8.
On which minor version should I upgrade/update, which include major bug
fixes like duplicate value issue.
On Mar 27, 2017 2:37 PM, "hubert depesz lubaczewski" <depesz@depesz.com>
wrote:
Show quoted text
On Sun, Mar 26, 2017 at 08:18:49PM +0530, Manoj Verma wrote:
Dear Michael,
I understand that upgrade is necessary.
But the question is upgradation of postgresql really fix this issue?
Until or unless the root cause is known, it's tough to accept that after
upgradation of postgresql this issue will never occur.If it is possible for you, guide me how can I proceed to find the root
cause.It is also tough to accept that someone is requesting help with version
of PostgreSQL which is not supported for over 6 years, and has known
bugs. Spending time to support *unsupported* version to find that (in
most likely scenario) the bug has been fixed lont time ago, or (in
less likely scenario) it was hardware error - is just very unlikely to
happen.Best regards,
depesz
On Mon, Mar 27, 2017 at 10:15:38PM +0530, Manoj Verma wrote:
Dear depesz,
I am currently on Rhel 5.8.
On which minor version should I upgrade/update, which include major bug
fixes like duplicate value issue.
If you want, for whatever reason, to stay to 8.2, then upgrade to newest
8.2 (no idea what it is - it's so ancient that I don't even have it).
Otherwise - upgrade to 9.6.2.
depesz
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On 3/27/2017 10:20 AM, hubert depesz lubaczewski wrote:
On Mon, Mar 27, 2017 at 10:15:38PM +0530, Manoj Verma wrote:
Dear depesz,
I am currently on Rhel 5.8.
On which minor version should I upgrade/update, which include major bug
fixes like duplicate value issue.If you want, for whatever reason, to stay to 8.2, then upgrade to newest
8.2 (no idea what it is - it's so ancient that I don't even have it).Otherwise - upgrade to 9.6.2.
per https://www.postgresql.org/docs/current/static/release.html the
final release of 8.2 was 8.2.23, in December 2011
but, the OP was asking about 8.1, of which the final release was 8.1.23,
in 2010.
However, running out of disk space in the disk volume holding pgdata is
a catastrophic error, that can leave the database in an unknown state,
depending on exactly what was allocating file space when it ran out.
--
john r pierce, recycling bits in santa cruz
John R Pierce <pierce@hogranch.com> writes:
However, running out of disk space in the disk volume holding pgdata is
a catastrophic error, that can leave the database in an unknown state,
depending on exactly what was allocating file space when it ran out.
It's not supposed to do that; the worst case ought to be a forced
database shutdown until you free up some space. (And that should
be an unusual case -- ideally, you just get a normal query failure
from an INSERT or UPDATE.) So there may in fact be a bug here,
if there were not any additional factors involved. But, as was said
upthread, 8.1.x is long out of support and nobody in the community
is likely to investigate bug reports against it for free.
If the OP is sufficiently desperate, he could probably hire one of
the various PG support companies to investigate, or at least help
him with data recovery.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs