Pg_Upgrade Bug with Invalid Concurrently-Created Indexes

Postgres, since version 8.2, has supported the ability to create indexes without blocking writes (insert, update, or delete) on the table being indexed. This is done by adding the keyword concurrently to the create index command. (Reads are never blocked by create index.)

Implementing this feature was very complex, and it has been recently discovered that all versions of pg_upgrade have a bug related to processing such indexes. The problem revolves around the rare case when create index concurrently fails. When this happens, an invalid index remains in the system. The invalid index is incomplete and is not used by queries. The pg_upgrade problem is that pg_dump dumps it as a valid index. While a normal restore of pg_dump output would create a valid index, pg_upgrade moves the old index file into place without recreating it — hence, an invalid index file is upgraded as a valid index. In this case, the index could return invalid results or crash the server. The steps necessary to trigger this bug are:

• Use of create index concurrently
• Failure of create index concurrently
• Not dropping or recreating the index after failure
• Use of pg_upgrade without the patch applied on December 11, 2012. The patch uses this query to throw an error if invalid indexes are found before the upgrade begins (pg_upgrade --check also performs this check):

 

SELECT n.nspname, c.relname
FROM   pg_catalog.pg_class c, pg_catalog.pg_namespace n, 
       pg_catalog.pg_index i 
WHERE  (i.indisvalid = false OR i.indisready = false) AND
       i.indexrelid = c.oid AND c.relnamespace = n.oid AND
       n.nspname != 'pg_catalog' AND 
       n.nspname != 'information_schema' AND 
       n.nspname != 'pg_toast'

 

This bug remained unreported for so long because concurrent index creation is not common, and leaving invalid indexes in place is also rare.

The next set of minor Postgres releases will throw an error if invalid indexes exist in the old cluster — until then, users can run the above query manually in all databases to check for invalid indexes before using pg_upgrade. For those who have already used pg_upgrade, you can check if invalid indexes were improperly upgraded by running the query in the old cluster. Unfortunately, if the old cluster is not accessible, there is no known way to check for the existence of such indexes in the new cluster — reindex can be used to recreate any suspect indexes.