Re: Upgrade from 9.0.5 to 9.1.1 - Problems with citext indexes

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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