Re: BUG #3954: Duplicate Primary Keys

"Tapin Agarwal" <tapin(dot)agarwal(at)globallogic(dot)com> writes:

> The following bug has been logged online:
>
> Bug reference: 3954
> Logged by: Tapin Agarwal
> Email address: tapin(dot)agarwal(at)globallogic(dot)com
> PostgreSQL version: 8.1.2
> Operating system: Suse
> Description: Duplicate Primary Keys
> Details:
>
> Hi,
>
> We are using postgres-8.1.2 and observed the degradation in postgres
> performance. Select query was taking very long time to complete. While on a
> freshly installed postgres, with same number of records select query was
> getting completed within seconds.

The most likely reason was having lots of dead tuples in the table due to
vacuum not being run frequently enough. autovacuum has been improved
dramatically in both 8.2 and 8.3 which would help avoid the need for
additional manual vacuums.

> Our tables has indexes created on them and were running auto vacuum. Also we
> are not using any table inheritance. When tried to perform re-indexing,
> found that there were duplicate primary key entries in few of the tables.
> This problem has occurred several times on our database setup and every time
> different set of tables get affected.
>
> Can you please let us know if this issue is already resolved in any of the
> postgres releases. If it is then please let us know the release number.

It does sound familiar though I don't find a specific changelog entry which
describes that symptom. The version you're on is over 2 years old and there
have been 9 subsequent bug-fix and security releases for that version. I would
suggest you try to go to 8.2.11 immediately and plan to upgrade to 8.2 or 8.3
when possible to help with your vacuuming problems.

Generally 8.1.11 is compatible with 8.1.x without a dump/restore or any
semantic changes. However there were a couple gotchas in some of the
intervening releases. Notably if you have any invalid UTF8 data in a
UTF8-encoded database 8.1.11 will refuse to load it as it can be a security
issue.

The release notes for the 8.1.x bug-fix and security releases are at:

http://www.postgresql.org/docs/8.1/static/release.html#RELEASE-8-1-11

Click "Next" or scroll up to the list to get the previous releases.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!