Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit63fecc9

Browse files
committed
Fix contrib/citext's upgrade script to handle array and domain cases.
We previously recognized that citext wouldn't get marked as collatableduring pg_upgrade from a pre-9.1 installation, and hacked itscreate-from-unpackaged script to manually perform the necessary catalogadjustments. However, we overlooked the fact that domains over citext,as well as the citext[] array type, need the same adjustments. Extendthe script to handle those cases.Also, the documentation suggested that this was only an issue in pg_upgradescenarios, which is quite wrong; loading any dump containing citext from apre-9.1 server will also result in the type being wrongly marked.I approached the documentation problem by changing the 9.1.2 release noteparagraphs about this issue, which is historically inaccurate. But itseems better than having the information scattered in multiple places, andleaving incorrect info in the 9.1.2 notes would be bad anyway. We'll stillneed to mention the issue again in the 9.1.4 notes, but perhaps they canjust reference 9.1.2 for fix instructions.Per report from Evan Carroll. Back-patch into 9.1.
1 parent1331cc6 commit63fecc9

File tree

2 files changed

+89
-24
lines changed

2 files changed

+89
-24
lines changed

‎contrib/citext/citext--unpackaged--1.0.sql

Lines changed: 78 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -81,49 +81,108 @@ ALTER EXTENSION citext ADD function translate(citext,citext,text);
8181
--
8282
-- As of 9.1, type citext should be marked collatable. There is no ALTER TYPE
8383
-- command for this, so we have to do it by poking the pg_type entry directly.
84-
-- We have to poke any derived copies in pg_attribute or pg_index as well.
84+
-- We have to poke any derived copies in pg_attribute or pg_index as well,
85+
-- as well as those for arrays/domains based directly or indirectly on citext.
8586
-- Notes: 100 is the OID of the "pg_catalog.default" collation --- it seems
8687
-- easier and more reliable to hard-wire that here than to pull it out of
8788
-- pg_collation. Also, we don't need to make pg_depend entries since the
8889
-- default collation is pinned.
8990
--
9091

92+
WITH RECURSIVE typeoids(typoid)AS
93+
(SELECT'citext'::pg_catalog.regtypeUNION
94+
SELECToidFROMpg_catalog.pg_type, typeoids
95+
WHERE typelem= typoidOR typbasetype= typoid )
9196
UPDATEpg_catalog.pg_typeSET typcollation=100
92-
WHEREoid='citext'::pg_catalog.regtype;
97+
FROM typeoids
98+
WHEREoid=typeoids.typoid;
9399

100+
WITH RECURSIVE typeoids(typoid)AS
101+
(SELECT'citext'::pg_catalog.regtypeUNION
102+
SELECToidFROMpg_catalog.pg_type, typeoids
103+
WHERE typelem= typoidOR typbasetype= typoid )
94104
UPDATEpg_catalog.pg_attributeSET attcollation=100
95-
WHERE atttypid='citext'::pg_catalog.regtype;
105+
FROM typeoids
106+
WHERE atttypid=typeoids.typoid;
96107

97108
UPDATEpg_catalog.pg_indexSET indcollation[0]=100
98-
WHERE indclass[0]IN (SELECToidFROMpg_catalog.pg_opclass
99-
WHERE opcintype='citext'::pg_catalog.regtype);
109+
WHERE indclass[0]IN (
110+
WITH RECURSIVE typeoids(typoid)AS
111+
(SELECT'citext'::pg_catalog.regtypeUNION
112+
SELECToidFROMpg_catalog.pg_type, typeoids
113+
WHERE typelem= typoidOR typbasetype= typoid )
114+
SELECToidFROMpg_catalog.pg_opclass, typeoids
115+
WHERE opcintype=typeoids.typoid
116+
);
100117

101118
UPDATEpg_catalog.pg_indexSET indcollation[1]=100
102-
WHERE indclass[1]IN (SELECToidFROMpg_catalog.pg_opclass
103-
WHERE opcintype='citext'::pg_catalog.regtype);
119+
WHERE indclass[1]IN (
120+
WITH RECURSIVE typeoids(typoid)AS
121+
(SELECT'citext'::pg_catalog.regtypeUNION
122+
SELECToidFROMpg_catalog.pg_type, typeoids
123+
WHERE typelem= typoidOR typbasetype= typoid )
124+
SELECToidFROMpg_catalog.pg_opclass, typeoids
125+
WHERE opcintype=typeoids.typoid
126+
);
104127

105128
UPDATEpg_catalog.pg_indexSET indcollation[2]=100
106-
WHERE indclass[2]IN (SELECToidFROMpg_catalog.pg_opclass
107-
WHERE opcintype='citext'::pg_catalog.regtype);
129+
WHERE indclass[2]IN (
130+
WITH RECURSIVE typeoids(typoid)AS
131+
(SELECT'citext'::pg_catalog.regtypeUNION
132+
SELECToidFROMpg_catalog.pg_type, typeoids
133+
WHERE typelem= typoidOR typbasetype= typoid )
134+
SELECToidFROMpg_catalog.pg_opclass, typeoids
135+
WHERE opcintype=typeoids.typoid
136+
);
108137

109138
UPDATEpg_catalog.pg_indexSET indcollation[3]=100
110-
WHERE indclass[3]IN (SELECToidFROMpg_catalog.pg_opclass
111-
WHERE opcintype='citext'::pg_catalog.regtype);
139+
WHERE indclass[3]IN (
140+
WITH RECURSIVE typeoids(typoid)AS
141+
(SELECT'citext'::pg_catalog.regtypeUNION
142+
SELECToidFROMpg_catalog.pg_type, typeoids
143+
WHERE typelem= typoidOR typbasetype= typoid )
144+
SELECToidFROMpg_catalog.pg_opclass, typeoids
145+
WHERE opcintype=typeoids.typoid
146+
);
112147

113148
UPDATEpg_catalog.pg_indexSET indcollation[4]=100
114-
WHERE indclass[4]IN (SELECToidFROMpg_catalog.pg_opclass
115-
WHERE opcintype='citext'::pg_catalog.regtype);
149+
WHERE indclass[4]IN (
150+
WITH RECURSIVE typeoids(typoid)AS
151+
(SELECT'citext'::pg_catalog.regtypeUNION
152+
SELECToidFROMpg_catalog.pg_type, typeoids
153+
WHERE typelem= typoidOR typbasetype= typoid )
154+
SELECToidFROMpg_catalog.pg_opclass, typeoids
155+
WHERE opcintype=typeoids.typoid
156+
);
116157

117158
UPDATEpg_catalog.pg_indexSET indcollation[5]=100
118-
WHERE indclass[5]IN (SELECToidFROMpg_catalog.pg_opclass
119-
WHERE opcintype='citext'::pg_catalog.regtype);
159+
WHERE indclass[5]IN (
160+
WITH RECURSIVE typeoids(typoid)AS
161+
(SELECT'citext'::pg_catalog.regtypeUNION
162+
SELECToidFROMpg_catalog.pg_type, typeoids
163+
WHERE typelem= typoidOR typbasetype= typoid )
164+
SELECToidFROMpg_catalog.pg_opclass, typeoids
165+
WHERE opcintype=typeoids.typoid
166+
);
120167

121168
UPDATEpg_catalog.pg_indexSET indcollation[6]=100
122-
WHERE indclass[6]IN (SELECToidFROMpg_catalog.pg_opclass
123-
WHERE opcintype='citext'::pg_catalog.regtype);
169+
WHERE indclass[6]IN (
170+
WITH RECURSIVE typeoids(typoid)AS
171+
(SELECT'citext'::pg_catalog.regtypeUNION
172+
SELECToidFROMpg_catalog.pg_type, typeoids
173+
WHERE typelem= typoidOR typbasetype= typoid )
174+
SELECToidFROMpg_catalog.pg_opclass, typeoids
175+
WHERE opcintype=typeoids.typoid
176+
);
124177

125178
UPDATEpg_catalog.pg_indexSET indcollation[7]=100
126-
WHERE indclass[7]IN (SELECToidFROMpg_catalog.pg_opclass
127-
WHERE opcintype='citext'::pg_catalog.regtype);
179+
WHERE indclass[7]IN (
180+
WITH RECURSIVE typeoids(typoid)AS
181+
(SELECT'citext'::pg_catalog.regtypeUNION
182+
SELECToidFROMpg_catalog.pg_type, typeoids
183+
WHERE typelem= typoidOR typbasetype= typoid )
184+
SELECToidFROMpg_catalog.pg_opclass, typeoids
185+
WHERE opcintype=typeoids.typoid
186+
);
128187

129188
-- somewhat arbitrarily, we assume no citext indexes have more than 8 columns

‎doc/src/sgml/release-9.1.sgml

Lines changed: 11 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -667,10 +667,13 @@
667667
</para>
668668

669669
<para>
670-
Also, if your installation was upgraded from a previous major release
671-
by running <application>pg_upgrade</>, and it contains table columns of
672-
the <type>citext</> data type, you should run <literal>CREATE EXTENSION
673-
citext FROM unpackaged</>. If you've already done that before
670+
Also, if you use the <type>citext</> data type, and you upgraded
671+
from a previous major release by running <application>pg_upgrade</>,
672+
you should run <literal>CREATE EXTENSION citext FROM unpackaged</>
673+
to avoid collation-related failures in <type>citext</> operations.
674+
The same is necessary if you restore a dump from a pre-9.1 database
675+
that contains an instance of the <type>citext</> data type.
676+
If you've already run the <command>CREATE EXTENSION</> command before
674677
upgrading to 9.1.2, you will instead need to do manual catalog updates
675678
as explained in the second changelog item.
676679
</para>
@@ -717,7 +720,9 @@
717720
<para>
718721
Existing <type>citext</> columns and indexes aren't correctly marked as
719722
being of a collatable data type during <application>pg_upgrade</> from
720-
a pre-9.1 server. That leads to operations on them failing with errors
723+
a pre-9.1 server, or when a pre-9.1 dump containing the <type>citext</>
724+
type is loaded into a 9.1 server.
725+
That leads to operations on these columns failing with errors
721726
such as <quote>could not determine which collation to use for string
722727
comparison</>. This change allows them to be fixed by the same
723728
script that upgrades the <type>citext</> module into a proper 9.1
@@ -732,6 +737,7 @@
732737
<filename><replaceable>SHAREDIR</>/extension/citext--unpackaged--1.0.sql</filename>.
733738
(Run <literal>pg_config --sharedir</> if you're uncertain where
734739
<replaceable>SHAREDIR</> is.)
740+
There is no harm in doing this again if unsure.
735741
</para>
736742
</listitem>
737743

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp