From: Bruce Momjian <bruce@momjian.us>
To: Rudolf van der Leeden <rudolf.vanderleeden@scoreloop.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Upgrade from 9.0.5 to 9.1.1 - Problems with citext indexes
Date: Thu, 1 Dec 2011 22:15:24 -0500 (EST)
Message-ID: <201112020315.pB23FOf27732@momjian.us> (raw)
In-Reply-To: <CAPHWM58LvPfgH_YDgTpmDbqxS6GZwY-E5ubF0=SjaJOKXMtThA@mail.gmail.com>
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. +
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: bruce@momjian.us, rudolf.vanderleeden@scoreloop.com
Subject: Re: Upgrade from 9.0.5 to 9.1.1 - Problems with citext indexes
In-Reply-To: <201112020315.pB23FOf27732@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox