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

Upgrade from 9.0.5 to 9.1.1 - Problems with citext indexes

From: Rudolf van der Leeden <rudolf(dot)vanderleeden(at)scoreloop(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Cc: Rudolf van der Leeden <rudolf(dot)vanderleeden(at)scoreloop(dot)com>
Subject: Upgrade from 9.0.5 to 9.1.1 - Problems with citext indexes
Date: 2011-11-30 11:55:24
Message-ID: CAPHWM58LvPfgH_YDgTpmDbqxS6GZwY-E5ubF0=SjaJOKXMtThA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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?

Thanks for your help.
Rudolf van der Leeden
Scoreloop, Germany
A subsidiary of Research in Motion (RIM)

Responses

pgsql-admin by date

Next:From: Nicholson, Brad (Toronto, ON, CA)Date: 2011-11-30 13:19:40
Subject: Re: transaction error handling
Previous:From: Bèrto ëd SèraDate: 2011-11-30 08:48:39
Subject: Re: transaction error handling

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