diff options
| -rw-r--r-- | contrib/citext/citext--unpackaged--1.0.sql | 97 | ||||
| -rw-r--r-- | doc/src/sgml/release-9.1.sgml | 16 |
2 files changed, 89 insertions, 24 deletions
diff --git a/contrib/citext/citext--unpackaged--1.0.sql b/contrib/citext/citext--unpackaged--1.0.sql index 9b0f200aac7..102743c5281 100644 --- a/contrib/citext/citext--unpackaged--1.0.sql +++ b/contrib/citext/citext--unpackaged--1.0.sql @@ -81,49 +81,108 @@ ALTER EXTENSION citext ADD function translate(citext,citext,text); -- -- As of 9.1, type citext should be marked collatable. There is no ALTER TYPE -- command for this, so we have to do it by poking the pg_type entry directly. --- We have to poke any derived copies in pg_attribute or pg_index as well. +-- We have to poke any derived copies in pg_attribute or pg_index as well, +-- as well as those for arrays/domains based directly or indirectly on citext. -- Notes: 100 is the OID of the "pg_catalog.default" collation --- it seems -- easier and more reliable to hard-wire that here than to pull it out of -- pg_collation. Also, we don't need to make pg_depend entries since the -- default collation is pinned. -- +WITH RECURSIVE typeoids(typoid) AS + ( SELECT 'citext'::pg_catalog.regtype UNION + SELECT oid FROM pg_catalog.pg_type, typeoids + WHERE typelem = typoid OR typbasetype = typoid ) UPDATE pg_catalog.pg_type SET typcollation = 100 -WHERE oid = 'citext'::pg_catalog.regtype; +FROM typeoids +WHERE oid = typeoids.typoid; +WITH RECURSIVE typeoids(typoid) AS + ( SELECT 'citext'::pg_catalog.regtype UNION + SELECT oid FROM pg_catalog.pg_type, typeoids + WHERE typelem = typoid OR typbasetype = typoid ) UPDATE pg_catalog.pg_attribute SET attcollation = 100 -WHERE atttypid = 'citext'::pg_catalog.regtype; +FROM typeoids +WHERE atttypid = typeoids.typoid; UPDATE pg_catalog.pg_index SET indcollation[0] = 100 -WHERE indclass[0] IN (SELECT oid FROM pg_catalog.pg_opclass - WHERE opcintype = 'citext'::pg_catalog.regtype); +WHERE indclass[0] IN ( + WITH RECURSIVE typeoids(typoid) AS + ( SELECT 'citext'::pg_catalog.regtype UNION + SELECT oid FROM pg_catalog.pg_type, typeoids + WHERE typelem = typoid OR typbasetype = typoid ) + SELECT oid FROM pg_catalog.pg_opclass, typeoids + WHERE opcintype = typeoids.typoid +); UPDATE pg_catalog.pg_index SET indcollation[1] = 100 -WHERE indclass[1] IN (SELECT oid FROM pg_catalog.pg_opclass - WHERE opcintype = 'citext'::pg_catalog.regtype); +WHERE indclass[1] IN ( + WITH RECURSIVE typeoids(typoid) AS + ( SELECT 'citext'::pg_catalog.regtype UNION + SELECT oid FROM pg_catalog.pg_type, typeoids + WHERE typelem = typoid OR typbasetype = typoid ) + SELECT oid FROM pg_catalog.pg_opclass, typeoids + WHERE opcintype = typeoids.typoid +); UPDATE pg_catalog.pg_index SET indcollation[2] = 100 -WHERE indclass[2] IN (SELECT oid FROM pg_catalog.pg_opclass - WHERE opcintype = 'citext'::pg_catalog.regtype); +WHERE indclass[2] IN ( + WITH RECURSIVE typeoids(typoid) AS + ( SELECT 'citext'::pg_catalog.regtype UNION + SELECT oid FROM pg_catalog.pg_type, typeoids + WHERE typelem = typoid OR typbasetype = typoid ) + SELECT oid FROM pg_catalog.pg_opclass, typeoids + WHERE opcintype = typeoids.typoid +); UPDATE pg_catalog.pg_index SET indcollation[3] = 100 -WHERE indclass[3] IN (SELECT oid FROM pg_catalog.pg_opclass - WHERE opcintype = 'citext'::pg_catalog.regtype); +WHERE indclass[3] IN ( + WITH RECURSIVE typeoids(typoid) AS + ( SELECT 'citext'::pg_catalog.regtype UNION + SELECT oid FROM pg_catalog.pg_type, typeoids + WHERE typelem = typoid OR typbasetype = typoid ) + SELECT oid FROM pg_catalog.pg_opclass, typeoids + WHERE opcintype = typeoids.typoid +); UPDATE pg_catalog.pg_index SET indcollation[4] = 100 -WHERE indclass[4] IN (SELECT oid FROM pg_catalog.pg_opclass - WHERE opcintype = 'citext'::pg_catalog.regtype); +WHERE indclass[4] IN ( + WITH RECURSIVE typeoids(typoid) AS + ( SELECT 'citext'::pg_catalog.regtype UNION + SELECT oid FROM pg_catalog.pg_type, typeoids + WHERE typelem = typoid OR typbasetype = typoid ) + SELECT oid FROM pg_catalog.pg_opclass, typeoids + WHERE opcintype = typeoids.typoid +); UPDATE pg_catalog.pg_index SET indcollation[5] = 100 -WHERE indclass[5] IN (SELECT oid FROM pg_catalog.pg_opclass - WHERE opcintype = 'citext'::pg_catalog.regtype); +WHERE indclass[5] IN ( + WITH RECURSIVE typeoids(typoid) AS + ( SELECT 'citext'::pg_catalog.regtype UNION + SELECT oid FROM pg_catalog.pg_type, typeoids + WHERE typelem = typoid OR typbasetype = typoid ) + SELECT oid FROM pg_catalog.pg_opclass, typeoids + WHERE opcintype = typeoids.typoid +); UPDATE pg_catalog.pg_index SET indcollation[6] = 100 -WHERE indclass[6] IN (SELECT oid FROM pg_catalog.pg_opclass - WHERE opcintype = 'citext'::pg_catalog.regtype); +WHERE indclass[6] IN ( + WITH RECURSIVE typeoids(typoid) AS + ( SELECT 'citext'::pg_catalog.regtype UNION + SELECT oid FROM pg_catalog.pg_type, typeoids + WHERE typelem = typoid OR typbasetype = typoid ) + SELECT oid FROM pg_catalog.pg_opclass, typeoids + WHERE opcintype = typeoids.typoid +); UPDATE pg_catalog.pg_index SET indcollation[7] = 100 -WHERE indclass[7] IN (SELECT oid FROM pg_catalog.pg_opclass - WHERE opcintype = 'citext'::pg_catalog.regtype); +WHERE indclass[7] IN ( + WITH RECURSIVE typeoids(typoid) AS + ( SELECT 'citext'::pg_catalog.regtype UNION + SELECT oid FROM pg_catalog.pg_type, typeoids + WHERE typelem = typoid OR typbasetype = typoid ) + SELECT oid FROM pg_catalog.pg_opclass, typeoids + WHERE opcintype = typeoids.typoid +); -- somewhat arbitrarily, we assume no citext indexes have more than 8 columns diff --git a/doc/src/sgml/release-9.1.sgml b/doc/src/sgml/release-9.1.sgml index ca53f5fc7d2..9fb9fb7908a 100644 --- a/doc/src/sgml/release-9.1.sgml +++ b/doc/src/sgml/release-9.1.sgml @@ -667,10 +667,13 @@ </para> <para> - Also, if your installation was upgraded from a previous major release - by running <application>pg_upgrade</>, and it contains table columns of - the <type>citext</> data type, you should run <literal>CREATE EXTENSION - citext FROM unpackaged</>. If you've already done that before + Also, if you use the <type>citext</> data type, and you upgraded + from a previous major release by running <application>pg_upgrade</>, + you should run <literal>CREATE EXTENSION citext FROM unpackaged</> + to avoid collation-related failures in <type>citext</> operations. + The same is necessary if you restore a dump from a pre-9.1 database + that contains an instance of the <type>citext</> data type. + If you've already run the <command>CREATE EXTENSION</> command before upgrading to 9.1.2, you will instead need to do manual catalog updates as explained in the second changelog item. </para> @@ -717,7 +720,9 @@ <para> Existing <type>citext</> columns and indexes aren't correctly marked as being of a collatable data type during <application>pg_upgrade</> from - a pre-9.1 server. That leads to operations on them failing with errors + a pre-9.1 server, or when a pre-9.1 dump containing the <type>citext</> + type is loaded into a 9.1 server. + That leads to operations on these columns failing with errors such as <quote>could not determine which collation to use for string comparison</>. This change allows them to be fixed by the same script that upgrades the <type>citext</> module into a proper 9.1 @@ -732,6 +737,7 @@ <filename><replaceable>SHAREDIR</>/extension/citext--unpackaged--1.0.sql</filename>. (Run <literal>pg_config --sharedir</> if you're uncertain where <replaceable>SHAREDIR</> is.) + There is no harm in doing this again if unsure. </para> </listitem> |