pg_upgrade problem with invalid indexes

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: pg_upgrade problem with invalid indexes
Date: 2012-12-07 00:28:33
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

I got a report today on the IRC channel about a pg_upgrade problem with
upgrading clusters with indexes that exist but are invalid.

For example, if you use CREATE INDEX CONCURRENTLY, then shut down the
server while it is running, the index will be left as INVALID; from our

If a problem arises while scanning the table, such as a uniqueness
violation in a unique index, the CREATE INDEX command will fail but
leave behind an 'invalid' index. This index will be ignored
for querying purposes because it might be incomplete; however
it will still consume update overhead. The psql \d command will
report such an index as INVALID:

postgres=# \d tab
Table ""
Column | Type | Modifiers
col | integer |
"idx" btree (col) INVALID

The recommended recovery method in such cases is to drop the
index and try again to perform CREATE INDEX CONCURRENTLY. (Another
possibility is to rebuild the index with REINDEX. However, since
REINDEX does not support concurrent builds, this option is unlikely
to seem attractive.)

The problem is that this invalid state is not dumped by pg_dump, meaning
pg_upgrade will restore the index as valid.

There are a few possible fixes. The first would be to have pg_upgrade
throw an error on any invalid index in the old cluster. Another option
would be to preserve the invalid state in pg_dump --binary-upgrade.

I also need help in how to communicate this to users since our next
minor release will be in the future.

Bruce Momjian <bruce(at)momjian(dot)us>

+ It's impossible for everything to be true. +


Browse pgsql-hackers by date

  From Date Subject
Next Message Josh Berkus 2012-12-07 00:31:46 Re: pg_upgrade problem with invalid indexes
Previous Message Daniel Farina 2012-12-07 00:16:54 Re: Serious problem: media recovery fails after system or PostgreSQL crash