From: Bruce Momjian <bruce@momjian.us>
Message-Id: <201112020315.pB23FOf27732@momjian.us>
Subject: Re: Upgrade from 9.0.5 to 9.1.1 - Problems with
 citext indexes
In-Reply-To: 
 <CAPHWM58LvPfgH_YDgTpmDbqxS6GZwY-E5ubF0=SjaJOKXMtThA@mail.gmail.com>
To: Rudolf van der Leeden <rudolf.vanderleeden@scoreloop.com>
Date: Thu, 1 Dec 2011 22:15:24 -0500 (EST)
CC: pgsql-admin@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"

Rudolf van der Leeden wrote:
> Hi,
> 
> upgrading Postgres 9.0.5 to 9.1.1 is done using:
> 
> - pg_upgrade
> - CREATE EXTENSION citext FROM unpackaged  (using the latest patch)
> 
> This works fine even for indexes on citext columns.
> The problem comes with an index on LOWER(citext), e.g.  the index
> idx_lower_login
> has been created on column 'login::citext'  using  'btree
> (lower(login::text))'.
> This index is NOT used after the upgrade to 9.1.1.
> A REINDEX did not help, only after DROP/CREATE INDEX of idx_lower_login
> the index was used by the query planner.
> 
> As in our case dropping/creating a new index takes a long time (30min =
> downtime) we'd like to
> discuss other options and ideas. E.g. would it be possible to concurrently
> create already on 9.0.5
> an index that can be upgraded to 9.1.1. and will directly be used?
> Or is there anything that can be done to the PG internal tables so that the
> old index will be used as is?

That is quite odd.  I have no idea what would cause that.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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