Обсуждение: BUG #15023: problem with pg_statistic
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)
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