pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUpgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension
4+ messages / 2 participants
[nested] [flat]
* Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension
@ 2011-11-20 22:11 Rudolf van der Leeden <rudolf.vanderleeden@scoreloop.com>
2011-11-21 14:51 ` Re: Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Rudolf van der Leeden @ 2011-11-20 22:11 UTC (permalink / raw)
To: pgsql-admin; +Cc: Rudolf van der Leeden <vanderleeden@logicunited.com>; Rudolf van der Leeden <rudolf.vanderleeden@scoreloop.com>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension
2011-11-20 22:11 Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension Rudolf van der Leeden <rudolf.vanderleeden@scoreloop.com>
@ 2011-11-21 14:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-11-21 16:27 ` Re: Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2011-11-21 14:51 UTC (permalink / raw)
To: Rudolf van der Leeden <rudolf.vanderleeden@scoreloop.com>; +Cc: pgsql-admin@postgresql.org, Rudolf van der Leeden <vanderleeden@logicunited.com>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension
2011-11-20 22:11 Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension Rudolf van der Leeden <rudolf.vanderleeden@scoreloop.com>
2011-11-21 14:51 ` Re: Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-11-21 16:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-11-21 18:02 ` Re: Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension Rudolf van der Leeden <rudolf.vanderleeden@scoreloop.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2011-11-21 16:27 UTC (permalink / raw)
To: Rudolf van der Leeden <rudolf.vanderleeden@scoreloop.com>; +Cc: pgsql-admin@postgresql.org, Rudolf van der Leeden <vanderleeden@logicunited.com>
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=94bdb198813b079467d7ed07c6f72ac896...
If you have an already-upgraded database, you can just run the
additional commands by hand.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension
2011-11-20 22:11 Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension Rudolf van der Leeden <rudolf.vanderleeden@scoreloop.com>
2011-11-21 14:51 ` Re: Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension Tom Lane <tgl@sss.pgh.pa.us>
2011-11-21 16:27 ` Re: Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-11-21 18:02 ` Rudolf van der Leeden <rudolf.vanderleeden@scoreloop.com>
0 siblings, 0 replies; 4+ messages in thread
From: Rudolf van der Leeden @ 2011-11-21 18:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@postgresql.org, Rudolf van der Leeden <vanderleeden@logicunited.com>
Hi Tom,
This patch seems to fix the case for me:
>
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=94bdb198813b079467d7ed07c6f72ac896...
>
>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-11-21 18:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-20 22:11 Upgrading DBs with type=citext from 9.0.5 to 9.1.1 using pg_upgrade and create extension Rudolf van der Leeden <rudolf.vanderleeden@scoreloop.com>
2011-11-21 14:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-11-21 16:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-11-21 18:02 ` Rudolf van der Leeden <rudolf.vanderleeden@scoreloop.com>
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