BUG #15023: problem with pg_statistic

On 01/21/2018 03:59 AM, PG Bug reporting form wrote:

The following bug has been logged on the website:

Bug reference: 15023
Logged by: kurt rudahl
Email address: ktr@goldin-rudahl.com
PostgreSQL version: Unsupported/Unknown
Operating system: linux
Description:

Problem: cannot reindex pg_statistic
therefore cannot vacuum

Steps to recreate:
1. restart postgresql
2. psql thaistocks

\set VERBOSITY verbose
thaistocks=# reindex table pg_statistic;
ERROR: 23505: could not create unique index
DETAIL: Table contains duplicated values.
LOCATION: comparetup_index, tuplesort.c:2163

thaistocks=# select starelid,staattnum from pg_statistic group by
starelid,staattnum having count(*) > 1;
starelid | staattnum
----------+-----------
10723 | 5
10728 | 1
1260 | 9
(3 rows)

It's really difficult (read: impossible) to help you unless you tell us
more - for example what PostgreSQL version you're running, how did you
get into this situation (e.g. any crashes recently?).

In any case, this seems like a case of data corruption, possibly due to
a bug (not sure which PostgreSQL version you're using) hardware failure
or misconfiguration (e.g. system not handling fsync correctly).

If that's the case, I'd suspect there are other corrupted tables.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Sun, Jan 21, 2018 at 06:10:15PM +0100, Tomas Vondra wrote:

In any case, this seems like a case of data corruption, possibly due to
a bug (not sure which PostgreSQL version you're using) hardware failure
or misconfiguration (e.g. system not handling fsync correctly).

Duplicated rows could be caused by the freeze-the-dead bug as well,
which is not released yet (doesn't fix duplicated existing rows
anyway). It may be possible to get rid of this problem by removing
manually duplicated rows by tid matching.

If that's the case, I'd suspect there are other corrupted tables.

That's likely possible, and impossible to say with this amount of
information. In this case, what you should do first is stop your server,
take a deep breath, and then read the following guidelines:
https://wiki.postgresql.org/wiki/Corruption
--
Michael