Обсуждение: vacuumdb fails with error pg_statistic_relid_att_inh_index constraintviolation after upgrade to 9.6

Hello, We have upgraded our database from 9.5 to 9.6 version. After upgrade we ran vacuumdb command and we are getting following error: vacuumdb: vacuuming of database "advdfat" failed: ERROR: duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index" DETAIL: Key (starelid, staattnum, stainherit)=(1259, 1, f) already exists. Can anyone help here? -- Thanks & Regards, Swapnil Vaze

Swapnil Vaze <swapvaze28@gmail.com> writes:
> We have upgraded our database from 9.5 to 9.6 version.
> After upgrade we ran vacuumdb command and we are getting following error:
> vacuumdb: vacuuming of database "advdfat" failed: ERROR:  duplicate key
> value violates unique constraint "pg_statistic_relid_att_inh_index"
> DETAIL:  Key (starelid, staattnum, stainherit)=(1259, 1, f) already exists.

Hmm, odd.

> Can anyone help here?

In order of increasing invasiveness:

1. REINDEX pg_statistic_relid_att_inh_index, on the theory that that index
has become corrupt.

2. If #1 fails with a similar message, there must actually be more than
one pg_statistic row with that key.  Manually DELETE those rows, then
REINDEX the index, then ANALYZE pg_class to regenerate the deleted
stats.  (I suggest reindexing because this case makes it even more likely
that that index is corrupt.)

3. If #2 fails, TRUNCATE pg_statistic, then re-analyze everything to
rebuild the stats.
        regards, tom lane

Hello, Thanks for the reply. We deleted those rows and re run vacuum and analyze and it worked fine. Thanks, Thanks, Swapnil Vaze