Skip site navigation (1) Skip section navigation (2)

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
Message-ID: (view raw, whole thread or download thread mbox)
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. +


pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group