Обсуждение: BUG #15023: problem with pg_statistic

Поиск
Список
Период
Сортировка

BUG #15023: problem with pg_statistic

От
PG Bug reporting form
Дата:
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)

Re: BUG #15023: problem with pg_statistic

От
Tomas Vondra
Дата:

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

Re: BUG #15023: problem with pg_statistic

От
Michael Paquier
Дата:
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
Вложения