Explaining duplicate rows in spite of unique index

From: "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Explaining duplicate rows in spite of unique index
Date: 2010-02-23 08:41:00
Message-ID: D960CB61B694CF459DCFB4B0128514C203938192@exadv11.host.magwien.gv.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

We recently found a couple of rows in a production database
that had identical values in the columns constituting the primary key
(The problem surfaced because a pg_dump could not be restored).

Now I'm looking for explanations how this could happen.

The rows originate from around the time when we had a hardware
failure that corrupted the file system. The database came up
after a file system check, and people continued working until
we noticed that some tables were corrupted.

At that point we restored an online backup and recovered past
the time of the hardware failure. The WALs were intact and recovery
completed successfully.

Now does the following explanation sound plausible:
- After the file system check, in a table that seemed ok, some
rows could have vanished or old rows could have become visible.
- Users re-inserted "vanished" rows or updated "old" rows.
These transactions were recorded in the WALs.
- When we replayed those WALs, starting with a correct backup,
the "impossible" transactions were replayed and caused
the duplicate rows we see.

I don't know enough about the recovery process to tell if such
a scenario is possible.

What is your opinion? Are there other explanations (short of
a software bug in PostgreSQL)?

The database version is 8.3.6.

Yours,
Laurenz Albe

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Stark 2010-02-23 09:02:58 Re: Sorting performance vs. MySQL
Previous Message beulah prasanthi 2010-02-23 08:33:26 typecaste object to array