Postgres Pro
Downloads
Home   >   mailing lists

Thread: Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension

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

From
Rudolf van der Leeden
Date:
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.




Re: Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension

From
Tom Lane
Date:
Rudolf van der Leeden <rudolf.vanderleeden@scoreloop.com> writes:
> 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

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

Re: Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension

From
Tom Lane
Date:
I wrote:
> Rudolf van der Leeden <rudolf.vanderleeden@scoreloop.com> writes:
>> we are running into a problem with the following upgrade scenario:

> 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

Re: Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension

From
Rudolf van der Leeden
Date:
Hi Tom,

This patch seems to fix the case for me:
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.