Hi,
we are running into a problem with the following upgrade scenario:
Current DB (9.0.5, 300G) is using a table with 2 citext columns and indexes on both columns.
Using pg_upgrade to move from 9.0.5 to 9.1.1 works OK and is done (without the ANALYZE) in 30s.
Because we are using the citext type, the following statement has been executed after the upgrade:
CREATE EXTENSION citext FROM unpackaged
SELECTs are now possible, but we are having problems with UPDATE:
ERROR: could not determine which collation to use for string comparison
Dropping both citext indexes solves the problem, but creating a new index on citext is NOT possible:
ERROR: could not determine which collation to use for string comparison
The workaround in our testbed was:
- DROP both indexes
- ALTER TABLE/COLUMNs from citext to text
- DROP / CREATE EXTENSION citext
- ALTER TABLE/COLUMNs from text to citext
- CREATE INDEX on both citext columns
The ALTER TABLE and CREATE INDEX statements are taking a long time.
We are looking for a much faster and more intelligent upgrade procedure for DBs with citext and indexes.
Maybe we just overlooked the simple solution. Ideas and hints are very much appreciated.
Thanks and best regards,
Rudolf VanderLeeden
Scoreloop in Germany.
A subsidiary of Research In Motion.