Обсуждение: BUG #15159: Duplicate records for same primary key

The following bug has been logged on the website:

Bug reference:      15159
Logged by:          Sandhya KS
Email address:      sandhya.k_s@nokia.com
PostgreSQL version: 9.3.14
Operating system:   WindRiver
Description:

For 9.3.14 version, in customer environment we are facing the following
issue. Query to fetch the record for a particular primary key is displaying
one record. But when trying to display the duplicate records of that
table(sample command: SELECT <primary_key>, COUNT(*) FROM users GROUP BY
<primary_key> HAVING COUNT(*) > 1) is showing that there are 2 records for
the same primary key. 

One more strange this is, if we delete all the records related to this
primary key, then one more primary key is showing up to be having duplicate
records. Checked the triggers as well: on delete no operation is being
performed on this table. 

Questions:
1. Primary key is created to have unique records, then how two records are
being created/inserted?
2. After delete of malicious record, how other record is showing up to be
having duplicate records? Am I missing anything to be checked ?

On Tue, Apr 17, 2018 at 04:37:51AM +0000, PG Bug reporting form wrote:
> 1. Primary key is created to have unique records, then how two records are
> being created/inserted?
> 2. After delete of malicious record, how other record is showing up to be
> having duplicate records? Am I missing anything to be checked ?

The bug nick-named around as freeze-the-dead.  9.3.21 has fixed an issue
where tuples not normally visible, or marked as dead could come back to
life and become visible.  That's this entry in the release notes:
"Fix vacuuming of tuples that were updated while key-share locked (Andres
Freund, Álvaro Herrera)
In some cases VACUUM would fail to remove such tuples even though they
are now dead, leading to assorted data corruption scenarios."

And here are the corresponding release notes:
https://www.postgresql.org/docs/devel/static/release-9-3-21.html

So you first need to update this version of PostgreSQL, and then some
manual cleanup of those duplicated tuples is needed with for example a
tid mathing.
--
Michael

Hi,

Ok. Got it. We will propose the upgrade.

What will be the trigger for bug to appear ? Real time machine with normal queries or dump restore or any other
triggersfor such bug to appear? 

Also will postgres has any logs which could be used to identify if this bug has appeared ?

Thanks in advance !!!

On Wed, Apr 18, 2018 at 3:10 AM, K S, Sandhya (Nokia - IN/Bangalore)
<sandhya.k_s@nokia.com> wrote:
> Also will postgres has any logs which could be used to identify if this bug has appeared ?

This kind of thing that amcheck's "heapallindexed" option can detect,
which will appear in Postgres 11. You can get a version with
equivalent functionality that targets earlier Postgres releases from
Github (there are official PGDG packages for most Linux systems). The
heapallindexed check can verify that the heap is consistent with
indexes.

-- 
Peter Geoghegan

On Wed, Apr 18, 2018 at 09:49:35AM -0700, Peter Geoghegan wrote:
> On Wed, Apr 18, 2018 at 3:10 AM, K S, Sandhya (Nokia - IN/Bangalore)
> <sandhya.k_s@nokia.com> wrote:
>> Also will postgres has any logs which could be used to identify if this bug has appeared ?
>
> This kind of thing that amcheck's "heapallindexed" option can detect,
> which will appear in Postgres 11. You can get a version with
> equivalent functionality that targets earlier Postgres releases from
> Github (there are official PGDG packages for most Linux systems). The
> heapallindexed check can verify that the heap is consistent with
> indexes.

If you cannot afford to either install or compile this module, then you
can go back to the old dump/restore method, tracking what are the rows
violating constraints when restoring the data taken in the dump.
--
Michael