Skip to content

Commit a14e75e

Browse files
jeff-davisjeff-davis
committed
CREATE DATABASE: make LOCALE apply to all collation providers.
For CREATE DATABASE, make LOCALE parameter apply regardless of the provider used. Also affects initdb and createdb --locale arguments. Previously, LOCALE (and --locale) only affected the database default collation when using the libc provider. Discussion: https://postgr.es/m/1a63084d-221e-4075-619e-6b3e590f673e@enterprisedb.com Reviewed-by: Peter Eisentraut
1 parent c0d9512 commit a14e75e

File tree

12 files changed

+155
-60
lines changed

12 files changed

+155
-60
lines changed

‎doc/src/sgml/ref/create_collation.sgml

Lines changed: 16 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -85,9 +85,16 @@ CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> FROM <replace
8585

8686
<listitem>
8787
<para>
88-
This is a shortcut for setting <symbol>LC_COLLATE</symbol>
89-
and <symbol>LC_CTYPE</symbol> at once. If you specify this,
90-
you cannot specify either of those parameters.
88+
The locale name for this collation. See <xref
89+
linkend="collation-managing-create-libc"/> and <xref
90+
linkend="collation-managing-create-icu"/> for details.
91+
</para>
92+
<para>
93+
If <replaceable>provider</replaceable> is <literal>libc</literal>, this
94+
is a shortcut for setting <symbol>LC_COLLATE</symbol> and
95+
<symbol>LC_CTYPE</symbol> at once. If you specify
96+
<replaceable>locale</replaceable>, you cannot specify either of those
97+
parameters.
9198
</para>
9299
</listitem>
93100
</varlistentry>
@@ -97,8 +104,9 @@ CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> FROM <replace
97104

98105
<listitem>
99106
<para>
100-
Use the specified operating system locale for
101-
the <symbol>LC_COLLATE</symbol> locale category.
107+
If <replaceable>provider</replaceable> is <literal>libc</literal>, use
108+
the specified operating system locale for the
109+
<symbol>LC_COLLATE</symbol> locale category.
102110
</para>
103111
</listitem>
104112
</varlistentry>
@@ -108,8 +116,9 @@ CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> FROM <replace
108116

109117
<listitem>
110118
<para>
111-
Use the specified operating system locale for
112-
the <symbol>LC_CTYPE</symbol> locale category.
119+
If <replaceable>provider</replaceable> is <literal>libc</literal>, use
120+
the specified operating system locale for the <symbol>LC_CTYPE</symbol>
121+
locale category.
113122
</para>
114123
</listitem>
115124
</varlistentry>

‎doc/src/sgml/ref/create_database.sgml

Lines changed: 45 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -145,8 +145,22 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
145145
<term><replaceable class="parameter">locale</replaceable></term>
146146
<listitem>
147147
<para>
148-
This is a shortcut for setting <symbol>LC_COLLATE</symbol>
149-
and <symbol>LC_CTYPE</symbol> at once.
148+
Sets the default collation order and character classification in the
149+
new database. Collation affects the sort order applied to strings,
150+
e.g., in queries with <literal>ORDER BY</literal>, as well as the order used in indexes
151+
on text columns. Character classification affects the categorization
152+
of characters, e.g., lower, upper, and digit. Also sets the
153+
associated aspects of the operating system environment,
154+
<literal>LC_COLLATE</literal> and <literal>LC_CTYPE</literal>. The
155+
default is the same setting as the template database. See <xref
156+
linkend="collation-managing-create-libc"/> and <xref
157+
linkend="collation-managing-create-icu"/> for details.
158+
</para>
159+
<para>
160+
Can be overridden by setting <xref
161+
linkend="create-database-lc-collate"/>, <xref
162+
linkend="create-database-lc-ctype"/>, or <xref
163+
linkend="create-database-icu-locale"/> individually.
150164
</para>
151165
<tip>
152166
<para>
@@ -164,22 +178,35 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
164178
<term><replaceable class="parameter">lc_collate</replaceable></term>
165179
<listitem>
166180
<para>
167-
Collation order (<literal>LC_COLLATE</literal>) to use in the new database.
168-
This affects the sort order applied to strings, e.g., in queries with
169-
ORDER BY, as well as the order used in indexes on text columns.
170-
The default is to use the collation order of the template database.
171-
See below for additional restrictions.
181+
Sets <literal>LC_COLLATE</literal> in the database server's operating
182+
system environment. The default is the setting of <xref
183+
linkend="create-database-locale"/> if specified, otherwise the same
184+
setting as the template database. See below for additional
185+
restrictions.
186+
</para>
187+
<para>
188+
If <xref linkend="create-database-locale-provider"/> is
189+
<literal>libc</literal>, also sets the default collation order to use
190+
in the new database, overriding the setting <xref
191+
linkend="create-database-locale"/>.
172192
</para>
173193
</listitem>
174194
</varlistentry>
175195
<varlistentry id="create-database-lc-ctype">
176196
<term><replaceable class="parameter">lc_ctype</replaceable></term>
177197
<listitem>
178198
<para>
179-
Character classification (<literal>LC_CTYPE</literal>) to use in the new
180-
database. This affects the categorization of characters, e.g., lower,
181-
upper and digit. The default is to use the character classification of
182-
the template database. See below for additional restrictions.
199+
Sets <literal>LC_CTYPE</literal> in the database server's operating
200+
system environment. The default is the setting of <xref
201+
linkend="create-database-locale"/> if specified, otherwise the same
202+
setting as the template database. See below for additional
203+
restrictions.
204+
</para>
205+
<para>
206+
If <xref linkend="create-database-locale-provider"/> is
207+
<literal>libc</literal>, also sets the default character
208+
classification to use in the new database, overriding the setting
209+
<xref linkend="create-database-locale"/>.
183210
</para>
184211
</listitem>
185212
</varlistentry>
@@ -188,7 +215,13 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
188215
<term><replaceable class="parameter">icu_locale</replaceable></term>
189216
<listitem>
190217
<para>
191-
Specifies the ICU locale ID if the ICU locale provider is used.
218+
Specifies the ICU locale (see <xref
219+
linkend="collation-managing-create-icu"/>) for the database default
220+
collation order and character classification, overriding the setting
221+
<xref linkend="create-database-locale"/>. The <link
222+
linkend="create-database-locale-provider">locale provider</link> must be ICU. The default
223+
is the setting of <xref linkend="create-database-locale"/> if
224+
specified; otherwise the same setting as the template database.
192225
</para>
193226
</listitem>
194227
</varlistentry>

‎doc/src/sgml/ref/createdb.sgml

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -124,7 +124,10 @@ PostgreSQL documentation
124124
<listitem>
125125
<para>
126126
Specifies the locale to be used in this database. This is equivalent
127-
to specifying both <option>--lc-collate</option> and <option>--lc-ctype</option>.
127+
to specifying <option>--lc-collate</option>,
128+
<option>--lc-ctype</option>, and <option>--icu-locale</option> to the
129+
same value. Some locales are only valid for ICU and must be set with
130+
<option>--icu-locale</option>.
128131
</para>
129132
</listitem>
130133
</varlistentry>

‎doc/src/sgml/ref/initdb.sgml

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -116,9 +116,10 @@ PostgreSQL documentation
116116
<para>
117117
To choose a different locale for the cluster, use the option
118118
<option>--locale</option>. There are also individual options
119-
<option>--lc-*</option> (see below) to set values for the individual locale
120-
categories. Note that inconsistent settings for different locale
121-
categories can give nonsensical results, so this should be used with care.
119+
<option>--lc-*</option> and <option>--icu-locale</option> (see below) to
120+
set values for the individual locale categories. Note that inconsistent
121+
settings for different locale categories can give nonsensical results, so
122+
this should be used with care.
122123
</para>
123124

124125
<para>

‎src/backend/commands/collationcmds.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -276,7 +276,7 @@ DefineCollation(ParseState *pstate, List *names, List *parameters, bool if_not_e
276276
if (langtag && strcmp(colliculocale, langtag) != 0)
277277
{
278278
ereport(NOTICE,
279-
(errmsg("using standard form \"%s\" for locale \"%s\"",
279+
(errmsg("using standard form \"%s\" for ICU locale \"%s\"",
280280
langtag, colliculocale)));
281281

282282
colliculocale = langtag;

‎src/backend/commands/dbcommands.c

Lines changed: 12 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1017,7 +1017,12 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
10171017
if (dblocprovider == '\0')
10181018
dblocprovider = src_locprovider;
10191019
if (dbiculocale == NULL && dblocprovider == COLLPROVIDER_ICU)
1020-
dbiculocale = src_iculocale;
1020+
{
1021+
if (dlocale && dlocale->arg)
1022+
dbiculocale = defGetString(dlocale);
1023+
else
1024+
dbiculocale = src_iculocale;
1025+
}
10211026
if (dbicurules == NULL && dblocprovider == COLLPROVIDER_ICU)
10221027
dbicurules = src_icurules;
10231028

@@ -1031,12 +1036,14 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
10311036
if (!check_locale(LC_COLLATE, dbcollate, &canonname))
10321037
ereport(ERROR,
10331038
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
1034-
errmsg("invalid locale name: \"%s\"", dbcollate)));
1039+
errmsg("invalid LC_COLLATE locale name: \"%s\"", dbcollate),
1040+
errhint("If the locale name is specific to ICU, use ICU_LOCALE.")));
10351041
dbcollate = canonname;
10361042
if (!check_locale(LC_CTYPE, dbctype, &canonname))
10371043
ereport(ERROR,
10381044
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
1039-
errmsg("invalid locale name: \"%s\"", dbctype)));
1045+
errmsg("invalid LC_CTYPE locale name: \"%s\"", dbctype),
1046+
errhint("If the locale name is specific to ICU, use ICU_LOCALE.")));
10401047
dbctype = canonname;
10411048

10421049
check_encoding_locale_matches(encoding, dbcollate, dbctype);
@@ -1056,7 +1063,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
10561063
if (!dbiculocale)
10571064
ereport(ERROR,
10581065
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1059-
errmsg("ICU locale must be specified")));
1066+
errmsg("LOCALE or ICU_LOCALE must be specified")));
10601067

10611068
/*
10621069
* During binary upgrade, or when the locale came from the template
@@ -1071,7 +1078,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
10711078
if (langtag && strcmp(dbiculocale, langtag) != 0)
10721079
{
10731080
ereport(NOTICE,
1074-
(errmsg("using standard form \"%s\" for locale \"%s\"",
1081+
(errmsg("using standard form \"%s\" for ICU locale \"%s\"",
10751082
langtag, dbiculocale)));
10761083

10771084
dbiculocale = langtag;

‎src/bin/initdb/initdb.c

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2163,7 +2163,11 @@ check_locale_name(int category, const char *locale, char **canonname)
21632163
if (res == NULL)
21642164
{
21652165
if (*locale)
2166-
pg_fatal("invalid locale name \"%s\"", locale);
2166+
{
2167+
pg_log_error("invalid locale name \"%s\"", locale);
2168+
pg_log_error_hint("If the locale name is specific to ICU, use --icu-locale.");
2169+
exit(1);
2170+
}
21672171
else
21682172
{
21692173
/*
@@ -2376,7 +2380,7 @@ setlocales(void)
23762380
{
23772381
char *canonname;
23782382

2379-
/* set empty lc_* values to locale config if set */
2383+
/* set empty lc_* and iculocale values to locale config if set */
23802384

23812385
if (locale)
23822386
{
@@ -2392,6 +2396,8 @@ setlocales(void)
23922396
lc_monetary = locale;
23932397
if (!lc_messages)
23942398
lc_messages = locale;
2399+
if (!icu_locale && locale_provider == COLLPROVIDER_ICU)
2400+
icu_locale = locale;
23952401
}
23962402

23972403
/*

‎src/bin/initdb/t/001_initdb.pl

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -111,6 +111,17 @@
111111
],
112112
'option --icu-locale');
113113

114+
command_like(
115+
[
116+
'initdb', '--no-sync', '-A', 'trust',
117+
'--locale-provider=icu', '--locale=und',
118+
'--lc-collate=C', '--lc-ctype=C', '--lc-messages=C',
119+
'--lc-numeric=C', '--lc-monetary=C', '--lc-time=C',
120+
"$tempdir/data4"
121+
],
122+
qr/^\s+ICU locale:\s+und\n/ms,
123+
'options --locale-provider=icu --locale=und --lc-*=C');
124+
114125
command_fails_like(
115126
[
116127
'initdb', '--no-sync',

‎src/bin/scripts/createdb.c

Lines changed: 5 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -164,14 +164,6 @@ main(int argc, char *argv[])
164164
exit(1);
165165
}
166166

167-
if (locale)
168-
{
169-
if (!lc_ctype)
170-
lc_ctype = locale;
171-
if (!lc_collate)
172-
lc_collate = locale;
173-
}
174-
175167
if (encoding)
176168
{
177169
if (pg_char_to_encoding(encoding) < 0)
@@ -219,6 +211,11 @@ main(int argc, char *argv[])
219211
appendPQExpBuffer(&sql, " STRATEGY %s", fmtId(strategy));
220212
if (template)
221213
appendPQExpBuffer(&sql, " TEMPLATE %s", fmtId(template));
214+
if (locale)
215+
{
216+
appendPQExpBufferStr(&sql, " LOCALE ");
217+
appendStringLiteralConn(&sql, locale, conn);
218+
}
222219
if (lc_collate)
223220
{
224221
appendPQExpBufferStr(&sql, " LC_COLLATE ");

‎src/bin/scripts/t/020_createdb.pl

Lines changed: 12 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -86,6 +86,15 @@
8686
],
8787
'create database with icu locale from template database with icu provider'
8888
);
89+
90+
$node2->command_ok(
91+
[
92+
'createdb', '-T', 'template0', '--locale-provider', 'icu',
93+
'--locale', 'en', '--lc-collate', 'C', '--lc-ctype', 'C',
94+
'foobar57'
95+
],
96+
'create database with locale as ICU locale'
97+
);
8998
}
9099
else
91100
{
@@ -110,7 +119,7 @@
110119
CREATE POLICY pol_foobar ON tab_foobar FOR ALL TO role_foobar;');
111120
$node->issues_sql_like(
112121
[ 'createdb', '-l', 'C', '-T', 'foobar2', 'foobar3' ],
113-
qr/statement: CREATE DATABASE foobar3 TEMPLATE foobar2/,
122+
qr/statement: CREATE DATABASE foobar3 TEMPLATE foobar2 LOCALE 'C'/,
114123
'create database with template');
115124
($ret, $stdout, $stderr) = $node->psql(
116125
'foobar3',
@@ -137,15 +146,15 @@
137146
1,
138147
[qr/^$/],
139148
[
140-
qr/^createdb: error: database creation failed: ERROR: invalid locale name|^createdb: error: database creation failed: ERROR: new collation \(foo'; SELECT '1\) is incompatible with the collation of the template database/s
149+
qr/^createdb: error: database creation failed: ERROR: invalid LC_COLLATE locale name|^createdb: error: database creation failed: ERROR: new collation \(foo'; SELECT '1\) is incompatible with the collation of the template database/s
141150
],
142151
'createdb with incorrect --lc-collate');
143152
$node->command_checks_all(
144153
[ 'createdb', '--lc-ctype', "foo'; SELECT '1", 'foobar2' ],
145154
1,
146155
[qr/^$/],
147156
[
148-
qr/^createdb: error: database creation failed: ERROR: invalid locale name|^createdb: error: database creation failed: ERROR: new LC_CTYPE \(foo'; SELECT '1\) is incompatible with the LC_CTYPE of the template database/s
157+
qr/^createdb: error: database creation failed: ERROR: invalid LC_CTYPE locale name|^createdb: error: database creation failed: ERROR: new LC_CTYPE \(foo'; SELECT '1\) is incompatible with the LC_CTYPE of the template database/s
149158
],
150159
'createdb with incorrect --lc-ctype');
151160

‎src/test/icu/t/010_database.pl

Lines changed: 26 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -51,17 +51,36 @@
5151
'sort by explicit collation upper first');
5252

5353

54-
# Test error cases in CREATE DATABASE involving locale-related options
54+
# Test that LOCALE='C' works for ICU
55+
is( $node1->psql(
56+
'postgres',
57+
q{CREATE DATABASE dbicu1 LOCALE_PROVIDER icu LOCALE 'C' TEMPLATE template0 ENCODING UTF8}
58+
),
59+
0,
60+
"C locale works for ICU");
5561

56-
my ($ret, $stdout, $stderr) = $node1->psql('postgres',
57-
q{CREATE DATABASE dbicu LOCALE_PROVIDER icu LOCALE 'C' TEMPLATE template0 ENCODING UTF8}
58-
);
62+
# Test that LOCALE works for ICU locales if LC_COLLATE and LC_CTYPE
63+
# are specified
64+
is( $node1->psql(
65+
'postgres',
66+
q{CREATE DATABASE dbicu2 LOCALE_PROVIDER icu LOCALE '@colStrength=primary'
67+
LC_COLLATE='C' LC_CTYPE='C' TEMPLATE template0 ENCODING UTF8}
68+
),
69+
0,
70+
"LOCALE works for ICU locales if LC_COLLATE and LC_CTYPE are specified");
71+
72+
# Test that ICU-specific LOCALE without LC_COLLATE and LC_CTYPE must
73+
# be specified with ICU_LOCALE
74+
my ($ret, $stdout, $stderr) = $node1->psql(
75+
'postgres',
76+
q{CREATE DATABASE dbicu3 LOCALE_PROVIDER icu LOCALE '@colStrength=primary'
77+
TEMPLATE template0 ENCODING UTF8});
5978
isnt($ret, 0,
60-
"ICU locale must be specified for ICU provider: exit code not 0");
79+
"ICU-specific locale must be specified with ICU_LOCALE: exit code not 0");
6180
like(
6281
$stderr,
63-
qr/ERROR: ICU locale must be specified/,
64-
"ICU locale must be specified for ICU provider: error message");
82+
qr/ERROR: invalid LC_COLLATE locale name/,
83+
"ICU-specific locale must be specified with ICU_LOCALE: error message");
6584

6685

6786
done_testing();

0 commit comments

Comments
 (0)