[ADMIN] Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension

[Rudolf van der Leeden]

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.

[Tom Lane]

Post by Rudolf van der Leeden
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.
CREATE EXTENSION citext FROM unpackaged
ERROR: could not determine which collation to use for string comparison

Hmm. I think the citext update script is missing a couple of things.
Try this after the CREATE EXTENSION step:

update pg_attribute set attcollation = 100 where atttypid = 'citext'::regtype;

It looks like pg_index.indcollation is an issue too, but the least
fragile way to fix that might be to drop and recreate indexes on
citext columns.

regards, tom lane

[Tom Lane]

Post by Tom Lane
Hmm. I think the citext update script is missing a couple of things.

This patch seems to fix the case for me:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=94bdb198813b079467d7ed07c6f72ac896da7161

If you have an already-upgraded database, you can just run the
additional commands by hand.

regards, tom lane

[Rudolf van der Leeden]

Hi Tom,

Post by Tom Lane
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=94bdb198813b079467d7ed07c6f72ac896da7161

YES. This patch fixes the citext upgrade problem. With the modified
share/extension/citext--unpackaged--1.0.sql file
just run: CREATE EXTENSION citext FROM unpackaged; and the table and
indexes can be used as with 9.0.5.

Thanks for your quick help.
Regards,
-Rudolf.