public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v33 4/5] Subscripting documentation 2+ messages / 2 participants [nested] [flat]
* [PATCH v33 4/5] Subscripting documentation @ 2019-02-01 10:47 erthalion <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: erthalion @ 2019-02-01 10:47 UTC (permalink / raw) Supporting documentation for generalized subscripting. It includes the description of a new field in pg_type, the new section for jsonb documentation about subscripting feature on this data type, and also the tutorial about how to write subscripting operator for a custom data type. Reviewed-by: Tom Lane, Arthur Zakirov, Oleksandr Shulgin --- doc/src/sgml/catalogs.sgml | 8 ++ doc/src/sgml/extend.sgml | 6 + doc/src/sgml/filelist.sgml | 1 + doc/src/sgml/json.sgml | 39 ++++++ doc/src/sgml/ref/create_type.sgml | 33 ++++- doc/src/sgml/xsubscripting.sgml | 111 +++++++++++++++++ src/tutorial/Makefile | 4 +- src/tutorial/subscripting.c | 201 ++++++++++++++++++++++++++++++ src/tutorial/subscripting.source | 71 +++++++++++ 9 files changed, 470 insertions(+), 4 deletions(-) create mode 100644 doc/src/sgml/xsubscripting.sgml create mode 100644 src/tutorial/subscripting.c create mode 100644 src/tutorial/subscripting.source diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 048ff284f7..b17e9ee382 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -8971,6 +8971,14 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l </para></entry> </row> + <row> + <entry><structfield>typsubshandler</structfield></entry> + <entry><type>regproc</type></entry> + <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry> + <entry>Custom subscripting function with type-specific logic for parsing + and validation, or 0 if this type doesn't support subscripting.</entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>typdefault</structfield> <type>text</type> diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml index 890ff97b7a..65cf5706b6 100644 --- a/doc/src/sgml/extend.sgml +++ b/doc/src/sgml/extend.sgml @@ -33,6 +33,11 @@ operators (starting in <xref linkend="xoper"/>) </para> </listitem> + <listitem> + <para> + subscripting procedure (starting in <xref linkend="xsubscripting"/>) + </para> + </listitem> <listitem> <para> operator classes for indexes (starting in <xref linkend="xindex"/>) @@ -481,6 +486,7 @@ RETURNS anycompatible AS ... &xaggr; &xtypes; &xoper; + &xsubscripting; &xindex; diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 64b5da0070..4c3fd37fc1 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -69,6 +69,7 @@ <!ENTITY xplang SYSTEM "xplang.sgml"> <!ENTITY xoper SYSTEM "xoper.sgml"> <!ENTITY xtypes SYSTEM "xtypes.sgml"> +<!ENTITY xsubscripting SYSTEM "xsubscripting.sgml"> <!ENTITY plperl SYSTEM "plperl.sgml"> <!ENTITY plpython SYSTEM "plpython.sgml"> <!ENTITY plsql SYSTEM "plpgsql.sgml"> diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml index c0a6554d4d..5c538dca05 100644 --- a/doc/src/sgml/json.sgml +++ b/doc/src/sgml/json.sgml @@ -602,6 +602,45 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu </para> </sect2> + <sect2 id="jsonb-subscripting"> + <title><type>jsonb</type> Subscripting</title> + <para> + <type>jsonb</type> data type supports array-style subscripting expressions + to extract or update particular elements. It's possible to use multiple + subscripting expressions to extract nested values. In this case, a chain of + subscripting expressions follows the same rules as the + <literal>path</literal> argument in <literal>jsonb_set</literal> function, + e.g. in case of arrays it is a 0-based operation or that negative integers + that appear in <literal>path</literal> count from the end of JSON arrays. + The result of subscripting expressions is always jsonb data type. An + example of subscripting syntax: +<programlisting> +-- Extract value by key +SELECT ('{"a": 1}'::jsonb)['a']; + +-- Extract nested value by key path +SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c']; + +-- Extract element by index +SELECT ('[1, "2", null]'::jsonb)[1]; + +-- Update value by key +UPDATE table_name SET jsonb_field['key'] = 1; + +-- Select records using where clause with subscripting. Since the result of +-- subscripting is jsonb and we basically want to compare two jsonb objects, we +-- need to put the value in double quotes to be able to convert it to jsonb. +SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"'; +</programlisting> + + There is no special indexing support for such kind of expressions, but you + can always create a functional index that includes it +<programlisting> +CREATE INDEX idx ON table_name ((jsonb_field['key'])); +</programlisting> + </para> + </sect2> + <sect2> <title>Transforms</title> diff --git a/doc/src/sgml/ref/create_type.sgml b/doc/src/sgml/ref/create_type.sgml index 111f8e65d2..a34df4d247 100644 --- a/doc/src/sgml/ref/create_type.sgml +++ b/doc/src/sgml/ref/create_type.sgml @@ -54,6 +54,7 @@ CREATE TYPE <replaceable class="parameter">name</replaceable> ( [ , ELEMENT = <replaceable class="parameter">element</replaceable> ] [ , DELIMITER = <replaceable class="parameter">delimiter</replaceable> ] [ , COLLATABLE = <replaceable class="parameter">collatable</replaceable> ] + [ , SUBSCRIPTING_HANDLER = <replaceable class="parameter">subscripting_handler_function</replaceable> ] ) CREATE TYPE <replaceable class="parameter">name</replaceable> @@ -196,8 +197,9 @@ CREATE TYPE <replaceable class="parameter">name</replaceable> <replaceable class="parameter">receive_function</replaceable>, <replaceable class="parameter">send_function</replaceable>, <replaceable class="parameter">type_modifier_input_function</replaceable>, - <replaceable class="parameter">type_modifier_output_function</replaceable> and - <replaceable class="parameter">analyze_function</replaceable> + <replaceable class="parameter">type_modifier_output_function</replaceable>, + <replaceable class="parameter">analyze_function</replaceable>, + <replaceable class="parameter">subscripting_handler_function</replaceable> are optional. Generally these functions have to be coded in C or another low-level language. </para> @@ -454,6 +456,22 @@ CREATE TYPE <replaceable class="parameter">name</replaceable> make use of the collation information; this does not happen automatically merely by marking the type collatable. </para> + + <para> + The optional + <replaceable class="parameter">subscripting_handler_function</replaceable> + contains type-specific logic for subscripting of the data type. + By default, there is no such function provided, which means that the data + type doesn't support subscripting. The subscripting function must be + declared to take a single argument of type <type>internal</type>, and return + a <type>internal</type> result. There are two examples of implementation for + subscripting functions in case of array + (<replaceable class="parameter">array_subscripting_handler</replaceable>) + and jsonb + (<replaceable class="parameter">jsonb_subscripting_handler</replaceable>) + types in <filename>src/backend/utils/adt/arrayfuncs.c</filename> and + <filename>src/backend/utils/adt/jsonfuncs.c</filename>, respectively. + </para> </refsect2> <refsect2> @@ -769,6 +787,17 @@ CREATE TYPE <replaceable class="parameter">name</replaceable> </para> </listitem> </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">subscripting_handler_function</replaceable></term> + <listitem> + <para> + The name of a function that returns list of type-specific callback functions to + support subscripting logic for the data type. + </para> + </listitem> + </varlistentry> + </variablelist> </refsect1> diff --git a/doc/src/sgml/xsubscripting.sgml b/doc/src/sgml/xsubscripting.sgml new file mode 100644 index 0000000000..7224e81fa2 --- /dev/null +++ b/doc/src/sgml/xsubscripting.sgml @@ -0,0 +1,111 @@ +<!-- doc/src/sgml/xsubscripting.sgml --> + + <sect1 id="xsubscripting"> + <title>User-defined subscripting procedure</title> + + <indexterm zone="xsubscripting"> + <primary>custom subscripting</primary> + </indexterm> + <para> + When you define a new base type, you can also specify a custom procedure to + handle subscripting expressions. It must contain logic for verification and + evaluation of this expression, i.e. fetching or updating some data in this + data type. For instance: +</para> +<programlisting><![CDATA[ +typedef struct Custom +{ + int first; + int second; +} Custom; + +PG_FUNCTION_INFO_V1(custom_subscripting_handler); + +Datum +custom_subscripting_handler(PG_FUNCTION_ARGS) +{ + SubscriptRoutines *sbsroutines = (SubscriptRoutines *) + palloc(sizeof(SubscriptRoutines)); + sbsroutines->prepare = custom_subscript_prepare; + sbsroutines->validate = custom_subscript_validate; + sbsroutines->fetch = custom_subscript_fetch; + sbsroutines->assign = custom_subscript_assign; + + PG_RETURN_POINTER(sbsroutines); +} + +SubscriptingRef * +custom_subscript_prepare(bool isAssignment, SubscriptingRef *sbsref) +{ + sbsref->refelemtype = someType; + sbsref->refassgntype = someType; + + return sbsref; +} + +SubscriptingRef * +custom_subscript_validate(bool isAssignment, SubscriptingRef *sbsref, + ParseState *pstate) +{ + // some validation and coercion logic + + return sbsref; +} + +Datum +custom_subscript_assign(Datum containerSource, SubscriptingRefState *sbstate) +{ + // Some assignment logic + + return newContainer; +} + +Datum +custom_subscript_fetch(Datum containerSource, SubscriptingRefState *sbstate) +{ + // Some fetch logic based on sbstate +}]]> +</programlisting> + +<para> + Then you can define a subscripting procedure and a custom data type: +</para> +<programlisting> +CREATE FUNCTION custom_subscripting_handler(internal) + RETURNS internal + AS '<replaceable>filename</replaceable>' + LANGUAGE C IMMUTABLE STRICT; + +CREATE TYPE custom ( + internallength = 4, + input = custom_in, + output = custom_out, + subscripting_handler = custom_subscripting_handler, +); +</programlisting> + +<para> + and use it as usual: +</para> +<programlisting> +CREATE TABLE test_subscripting ( + data custom +); + +INSERT INTO test_subscripting VALUES ('(1, 2)'); + +SELECT data[0] from test_subscripting; + +UPDATE test_subscripting SET data[1] = 3; +</programlisting> + + + <para> + The examples of custom subscripting implementation can be found in + <filename>subscripting.sql</filename> and <filename>subscripting.c</filename> + in the <filename>src/tutorial</filename> directory of the source distribution. + See the <filename>README</filename> file in that directory for instructions + about running the examples. + </para> + +</sect1> diff --git a/src/tutorial/Makefile b/src/tutorial/Makefile index 16dc390f71..0ead60c2d4 100644 --- a/src/tutorial/Makefile +++ b/src/tutorial/Makefile @@ -13,8 +13,8 @@ # #------------------------------------------------------------------------- -MODULES = complex funcs -DATA_built = advanced.sql basics.sql complex.sql funcs.sql syscat.sql +MODULES = complex funcs subscripting +DATA_built = advanced.sql basics.sql complex.sql funcs.sql syscat.sql subscripting.sql ifdef NO_PGXS subdir = src/tutorial diff --git a/src/tutorial/subscripting.c b/src/tutorial/subscripting.c new file mode 100644 index 0000000000..1eb8c45652 --- /dev/null +++ b/src/tutorial/subscripting.c @@ -0,0 +1,201 @@ +/* + * src/tutorial/subscripting.c + * + ****************************************************************************** + This file contains routines that can be bound to a Postgres backend and + called by the backend in the process of processing queries. The calling + format for these routines is dictated by Postgres architecture. +******************************************************************************/ + +#include "postgres.h" + +#include "catalog/pg_type.h" +#include "executor/executor.h" +#include "executor/execExpr.h" +#include "nodes/nodeFuncs.h" +#include "parser/parse_coerce.h" +#include "utils/builtins.h" +#include "utils/fmgrprotos.h" + +PG_MODULE_MAGIC; + +typedef struct Custom +{ + int first; + int second; +} Custom; + +SubscriptingRef * custom_subscript_prepare(bool isAssignment, SubscriptingRef *sbsref); +SubscriptingRef * custom_subscript_validate(bool isAssignment, SubscriptingRef *sbsref, + ParseState *pstate); +Datum custom_subscript_fetch(Datum containerSource, SubscriptingRefState *sbstate); +Datum custom_subscript_assign(Datum containerSource, SubscriptingRefState *sbstate); + +PG_FUNCTION_INFO_V1(custom_in); +PG_FUNCTION_INFO_V1(custom_out); +PG_FUNCTION_INFO_V1(custom_subscripting_handler); + +/***************************************************************************** + * Input/Output functions + *****************************************************************************/ + +Datum +custom_in(PG_FUNCTION_ARGS) +{ + char *str = PG_GETARG_CSTRING(0); + int firstValue, + secondValue; + Custom *result; + + if (sscanf(str, " ( %d , %d )", &firstValue, &secondValue) != 2) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("invalid input syntax for complex: \"%s\"", + str))); + + + result = (Custom *) palloc(sizeof(Custom)); + result->first = firstValue; + result->second = secondValue; + PG_RETURN_POINTER(result); +} + +Datum +custom_out(PG_FUNCTION_ARGS) +{ + Custom *custom = (Custom *) PG_GETARG_POINTER(0); + char *result; + + result = psprintf("(%d, %d)", custom->first, custom->second); + PG_RETURN_CSTRING(result); +} + +/***************************************************************************** + * Custom subscripting logic functions + *****************************************************************************/ + +Datum +custom_subscripting_handler(PG_FUNCTION_ARGS) +{ + SubscriptRoutines *sbsroutines = (SubscriptRoutines *) + palloc(sizeof(SubscriptRoutines)); + + sbsroutines->prepare = custom_subscript_prepare; + sbsroutines->validate = custom_subscript_validate; + sbsroutines->fetch = custom_subscript_fetch; + sbsroutines->assign = custom_subscript_assign; + + PG_RETURN_POINTER(sbsroutines); +} + +SubscriptingRef * +custom_subscript_prepare(bool isAssignment, SubscriptingRef *sbsref) +{ + sbsref->refelemtype = INT4OID; + sbsref->refassgntype = INT4OID; + return sbsref; +} + +SubscriptingRef * +custom_subscript_validate(bool isAssignment, SubscriptingRef *sbsref, + ParseState *pstate) +{ + List *upperIndexpr = NIL; + ListCell *l; + + if (sbsref->reflowerindexpr != NIL) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("custom subscript does not support slices"), + parser_errposition(pstate, exprLocation( + ((Node *)lfirst(sbsref->reflowerindexpr->head)))))); + + foreach(l, sbsref->refupperindexpr) + { + Node *subexpr = (Node *) lfirst(l); + + if (subexpr == NULL) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("custom subscript does not support slices"), + parser_errposition(pstate, exprLocation( + ((Node *) lfirst(sbsref->refupperindexpr->head)))))); + + subexpr = coerce_to_target_type(pstate, + subexpr, exprType(subexpr), + INT4OID, -1, + COERCION_ASSIGNMENT, + COERCE_IMPLICIT_CAST, + -1); + if (subexpr == NULL) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("custom subscript must have integer type"), + parser_errposition(pstate, exprLocation(subexpr)))); + + upperIndexpr = lappend(upperIndexpr, subexpr); + + if (isAssignment) + { + Node *assignExpr = (Node *) sbsref->refassgnexpr; + Node *new_from; + + new_from = coerce_to_target_type(pstate, + assignExpr, exprType(assignExpr), + INT4OID, -1, + COERCION_ASSIGNMENT, + COERCE_IMPLICIT_CAST, + -1); + if (new_from == NULL) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("custom assignment requires int type"), + errhint("You will need to rewrite or cast the expression."), + parser_errposition(pstate, exprLocation(assignExpr)))); + sbsref->refassgnexpr = (Expr *)new_from; + } + } + + sbsref->refupperindexpr = upperIndexpr; + + return sbsref; +} + +Datum +custom_subscript_fetch(Datum containerSource, SubscriptingRefState *sbstate) +{ + Custom *container= (Custom *) containerSource; + int index; + + if (sbstate->numupper != 1) + ereport(ERROR, (errmsg("custom does not support nested subscripting"))); + + index = DatumGetInt32(sbstate->upperindex[0]); + + if (index == 1) + return (Datum) container->first; + else + return (Datum) container->second; +} + +Datum +custom_subscript_assign(Datum containerSource, SubscriptingRefState *sbstate) +{ + int index; + Custom *container = (Custom *) containerSource; + + if (sbstate->resnull) + return containerSource; + + if (sbstate->numupper != 1) + ereport(ERROR, (errmsg("custom does not support nested subscripting"))); + + index = DatumGetInt32(sbstate->upperindex[0]); + + if (index == 1) + container->first = DatumGetInt32(sbstate->replacevalue); + else + container->second = DatumGetInt32(sbstate->replacevalue); + + return (Datum) container; +} diff --git a/src/tutorial/subscripting.source b/src/tutorial/subscripting.source new file mode 100644 index 0000000000..837cf30612 --- /dev/null +++ b/src/tutorial/subscripting.source @@ -0,0 +1,71 @@ +--------------------------------------------------------------------------- +-- +-- subscripting.sql- +-- This file shows how to create a new subscripting procedure for +-- user-defined type. +-- +-- +-- Portions Copyright (c) 1996-2016, PostgreSQL Global Development Group +-- Portions Copyright (c) 1994, Regents of the University of California +-- +-- src/tutorial/subscripting.source +-- +--------------------------------------------------------------------------- + +----------------------------- +-- Creating a new type: +-- We are going to create a new type called 'complex' which represents +-- complex numbers. +-- A user-defined type must have an input and an output function, and +-- optionally can have binary input and output functions. All of these +-- are usually user-defined C functions. +----------------------------- + +-- Assume the user defined functions are in /home/erthalion/programms/postgresql-master/src/tutorial/complex$DLSUFFIX +-- (we do not want to assume this is in the dynamic loader search path). +-- Look at $PWD/complex.c for the source. Note that we declare all of +-- them as STRICT, so we do not need to cope with NULL inputs in the +-- C code. We also mark them IMMUTABLE, since they always return the +-- same outputs given the same inputs. + +-- the input function 'complex_in' takes a null-terminated string (the +-- textual representation of the type) and turns it into the internal +-- (in memory) representation. You will get a message telling you 'complex' +-- does not exist yet but that's okay. + +CREATE FUNCTION custom_in(cstring) + RETURNS custom + AS '_OBJWD_/subscripting' + LANGUAGE C IMMUTABLE STRICT; + +-- the output function 'complex_out' takes the internal representation and +-- converts it into the textual representation. + +CREATE FUNCTION custom_out(custom) + RETURNS cstring + AS '_OBJWD_/subscripting' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION custom_subscripting_handler(internal) + RETURNS internal + AS '_OBJWD_/subscripting' + LANGUAGE C IMMUTABLE STRICT; + +CREATE TYPE custom ( + internallength = 8, + input = custom_in, + output = custom_out, + subscripting_handler = custom_subscripting_handler +); + +-- we can use it in a table + +CREATE TABLE test_subscripting ( + data custom +); + +INSERT INTO test_subscripting VALUES ('(1, 2)'); + +SELECT data[0] from test_subscripting; + +UPDATE test_subscripting SET data[1] = 3; -- 2.21.0 --u66dlpg6gaevsofr Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v33-0005-Filling-gaps-in-jsonb-arrays.patch" ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Order changes in PG16 since ICU introduction @ 2023-06-06 19:18 Jeff Davis <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: Jeff Davis @ 2023-06-06 19:18 UTC (permalink / raw) To: Daniel Verite <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Andrew Gierth <[email protected]>; Tom Lane <[email protected]>; [email protected] On Tue, 2023-06-06 at 15:09 +0200, Daniel Verite wrote: > FWIW I don't quite see how 0001 improve things or what problem it's > trying to solve. The word "locale" is generic, so we need to make LOCALE/--locale apply to whatever provider is being used. If "locale" only applies to libc, using ICU will always be confusing and never be on the same level as libc, let alone the preferred provider. The locale "C" is a special case, documented as a non-locale. So, if LOCALE/--locale apply to ICU, then either ICU needs to handle locale "C" in the expected way (v8 patch series); or when we see locale "C" we need to somehow change the provider into something that can handle it (v6 patch series changes it to the "none" provider). Please let me know if you disagree with the goal or the reasoning here. If so, please explain where you think we should end up, because the status quo does not seem great to me. > 0001 creates exceptions throughout the code so that when an ICU > collation has a locale name "C" or "POSIX" then it does not behave > like an ICU collation, even though pg_collation.collprovider='i' > To me it's neither desirable nor necessary that a collation that > has collprovider='i' is diverted to non-ICU semantics. It's not very principled, but it matches what libc does. > Also in the current state, this diversion does not apply to initdb. > > "initdb --icu-locale=C" with 0001 applied reports this: > > Using language tag "en-US-u-va-posix" for ICU locale "C". Thank you. I fixed it by skipping the canonicalization for C/POSIX locales in initdb. > Could you elaborate a bit more on what 0001 is meant to achieve, from > the point of view of the user? It makes it so the user consistently (regardless of the provider) gets the "no locale" behavior (as documented and historically expected) when they specify the C or POSIX locales. Then that enables us to change LOCALE/--locale to apply to ICU, which means that a simple command like "initdb --locale=en_US" does a sensible thing regardless of the default provider. I understand you are skeptical of trying to apply an arbitrary locale name to ICU, but if they don't specify the provider, what do you expect to happen? -- Jeff Davis PostgreSQL Contributor Team - AWS Attachments: [text/x-patch] v9-0001-ICU-support-locale-C-with-the-same-behavior-as-li.patch (12.5K, ../../[email protected]/2-v9-0001-ICU-support-locale-C-with-the-same-behavior-as-li.patch) download | inline diff: From 54a171f643504d0f8beb2a1f8f7e29dc9639654e Mon Sep 17 00:00:00 2001 From: Jeff Davis <[email protected]> Date: Mon, 24 Apr 2023 15:46:17 -0700 Subject: [PATCH v9 1/4] ICU: support locale "C" with the same behavior as libc. The "C" locale doesn't actually use a provider at all, it's a special locale that uses memcmp() and built-in character classification. Make it behave the same in ICU as libc (even though it doesn't actually make use of either provider). Discussion: https://postgr.es/m/[email protected] --- src/backend/commands/collationcmds.c | 43 ++++++---- src/backend/commands/dbcommands.c | 42 ++++++---- src/backend/utils/adt/pg_locale.c | 83 ++++++++++++++----- src/bin/initdb/initdb.c | 25 ++++-- .../regress/expected/collate.icu.utf8.out | 6 ++ src/test/regress/sql/collate.icu.utf8.sql | 4 + 6 files changed, 144 insertions(+), 59 deletions(-) diff --git a/src/backend/commands/collationcmds.c b/src/backend/commands/collationcmds.c index 2969a2bb21..dd6cd2682f 100644 --- a/src/backend/commands/collationcmds.c +++ b/src/backend/commands/collationcmds.c @@ -264,26 +264,39 @@ DefineCollation(ParseState *pstate, List *names, List *parameters, bool if_not_e (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), errmsg("parameter \"locale\" must be specified"))); - /* - * During binary upgrade, preserve the locale string. Otherwise, - * canonicalize to a language tag. - */ - if (!IsBinaryUpgrade) + if (strcmp(colliculocale, "C") == 0 || + strcmp(colliculocale, "POSIX") == 0) { - char *langtag = icu_language_tag(colliculocale, - icu_validation_level); - - if (langtag && strcmp(colliculocale, langtag) != 0) + if (!collisdeterministic) + ereport(ERROR, + (errmsg("nondeterministic collations not supported for C or POSIX locale"))); + if (collicurules != NULL) + ereport(ERROR, + (errmsg("RULES not supported for C or POSIX locale"))); + } + else + { + /* + * During binary upgrade, preserve the locale + * string. Otherwise, canonicalize to a language tag. + */ + if (!IsBinaryUpgrade) { - ereport(NOTICE, - (errmsg("using standard form \"%s\" for locale \"%s\"", - langtag, colliculocale))); + char *langtag = icu_language_tag(colliculocale, + icu_validation_level); + + if (langtag && strcmp(colliculocale, langtag) != 0) + { + ereport(NOTICE, + (errmsg("using standard form \"%s\" for locale \"%s\"", + langtag, colliculocale))); - colliculocale = langtag; + colliculocale = langtag; + } } - } - icu_validate_locale(colliculocale); + icu_validate_locale(colliculocale); + } } /* diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c index 99d4080ea9..bfce8dc348 100644 --- a/src/backend/commands/dbcommands.c +++ b/src/backend/commands/dbcommands.c @@ -1058,27 +1058,37 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt) (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("ICU locale must be specified"))); - /* - * During binary upgrade, or when the locale came from the template - * database, preserve locale string. Otherwise, canonicalize to a - * language tag. - */ - if (!IsBinaryUpgrade && dbiculocale != src_iculocale) + if (strcmp(dbiculocale, "C") == 0 || + strcmp(dbiculocale, "POSIX") == 0) { - char *langtag = icu_language_tag(dbiculocale, - icu_validation_level); - - if (langtag && strcmp(dbiculocale, langtag) != 0) + if (dbicurules != NULL) + ereport(ERROR, + (errmsg("ICU_RULES not supported for C or POSIX locale"))); + } + else + { + /* + * During binary upgrade, or when the locale came from the + * template database, preserve locale string. Otherwise, + * canonicalize to a language tag. + */ + if (!IsBinaryUpgrade && dbiculocale != src_iculocale) { - ereport(NOTICE, - (errmsg("using standard form \"%s\" for locale \"%s\"", - langtag, dbiculocale))); + char *langtag = icu_language_tag(dbiculocale, + icu_validation_level); + + if (langtag && strcmp(dbiculocale, langtag) != 0) + { + ereport(NOTICE, + (errmsg("using standard form \"%s\" for locale \"%s\"", + langtag, dbiculocale))); - dbiculocale = langtag; + dbiculocale = langtag; + } } - } - icu_validate_locale(dbiculocale); + icu_validate_locale(dbiculocale); + } } else { diff --git a/src/backend/utils/adt/pg_locale.c b/src/backend/utils/adt/pg_locale.c index 31e3b16ae0..986dcbd2a7 100644 --- a/src/backend/utils/adt/pg_locale.c +++ b/src/backend/utils/adt/pg_locale.c @@ -1246,8 +1246,15 @@ lookup_collation_cache(Oid collation, bool set_flags) } else { - cache_entry->collate_is_c = false; - cache_entry->ctype_is_c = false; + Datum datum; + const char *colliculocale; + + datum = SysCacheGetAttrNotNull(COLLOID, tp, Anum_pg_collation_colliculocale); + colliculocale = TextDatumGetCString(datum); + + cache_entry->collate_is_c = ((strcmp(colliculocale, "C") == 0) || + (strcmp(colliculocale, "POSIX") == 0)); + cache_entry->ctype_is_c = cache_entry->collate_is_c; } cache_entry->flags_valid = true; @@ -1279,16 +1286,27 @@ lc_collate_is_c(Oid collation) if (collation == DEFAULT_COLLATION_OID) { static int result = -1; - char *localeptr; - - if (default_locale.provider == COLLPROVIDER_ICU) - return false; + const char *localeptr; if (result >= 0) return (bool) result; - localeptr = setlocale(LC_COLLATE, NULL); - if (!localeptr) - elog(ERROR, "invalid LC_COLLATE setting"); + + if (default_locale.provider == COLLPROVIDER_ICU) + { +#ifdef USE_ICU + localeptr = default_locale.info.icu.locale; +#else + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("ICU is not supported in this build"))); +#endif + } + else + { + localeptr = setlocale(LC_COLLATE, NULL); + if (!localeptr) + elog(ERROR, "invalid LC_COLLATE setting"); + } if (strcmp(localeptr, "C") == 0) result = true; @@ -1332,16 +1350,27 @@ lc_ctype_is_c(Oid collation) if (collation == DEFAULT_COLLATION_OID) { static int result = -1; - char *localeptr; - - if (default_locale.provider == COLLPROVIDER_ICU) - return false; + const char *localeptr; if (result >= 0) return (bool) result; - localeptr = setlocale(LC_CTYPE, NULL); - if (!localeptr) - elog(ERROR, "invalid LC_CTYPE setting"); + + if (default_locale.provider == COLLPROVIDER_ICU) + { +#ifdef USE_ICU + localeptr = default_locale.info.icu.locale; +#else + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("ICU is not supported in this build"))); +#endif + } + else + { + localeptr = setlocale(LC_CTYPE, NULL); + if (!localeptr) + elog(ERROR, "invalid LC_CTYPE setting"); + } if (strcmp(localeptr, "C") == 0) result = true; @@ -1375,7 +1404,13 @@ make_icu_collator(const char *iculocstr, #ifdef USE_ICU UCollator *collator; - collator = pg_ucol_open(iculocstr); + if (strcmp(iculocstr, "C") == 0 || strcmp(iculocstr, "POSIX") == 0) + { + Assert(icurules == NULL); + collator = NULL; + } + else + collator = pg_ucol_open(iculocstr); /* * If rules are specified, we extract the rules of the standard collation, @@ -1650,6 +1685,9 @@ get_collation_actual_version(char collprovider, const char *collcollate) { char *collversion = NULL; + if (strcmp("C", collcollate) == 0 || strcmp("POSIX", collcollate) == 0) + return NULL; + #ifdef USE_ICU if (collprovider == COLLPROVIDER_ICU) { @@ -1668,9 +1706,7 @@ get_collation_actual_version(char collprovider, const char *collcollate) else #endif if (collprovider == COLLPROVIDER_LIBC && - pg_strcasecmp("C", collcollate) != 0 && - pg_strncasecmp("C.", collcollate, 2) != 0 && - pg_strcasecmp("POSIX", collcollate) != 0) + pg_strncasecmp("C.", collcollate, 2) != 0) { #if defined(__GLIBC__) /* Use the glibc version because we don't have anything better. */ @@ -2457,6 +2493,13 @@ pg_ucol_open(const char *loc_str) if (loc_str == NULL) elog(ERROR, "opening default collator is not supported"); + /* + * Must never open special values C or POSIX, which are treated specially + * and not passed to the provider. + */ + if (strcmp(loc_str, "C") == 0 || strcmp(loc_str, "POSIX") == 0) + elog(ERROR, "unexpected ICU locale string: %s", loc_str); + /* * In ICU versions 54 and earlier, "und" is not a recognized spelling of * the root locale. If the first component of the locale is "und", replace diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c index 09a5c98cc0..7aa2d871e3 100644 --- a/src/bin/initdb/initdb.c +++ b/src/bin/initdb/initdb.c @@ -2428,14 +2428,23 @@ setlocales(void) printf(_("Using default ICU locale \"%s\".\n"), icu_locale); } - /* canonicalize to a language tag */ - langtag = icu_language_tag(icu_locale); - printf(_("Using language tag \"%s\" for ICU locale \"%s\".\n"), - langtag, icu_locale); - pg_free(icu_locale); - icu_locale = langtag; - - icu_validate_locale(icu_locale); + if (strcmp(icu_locale, "C") == 0 || + strcmp(icu_locale, "POSIX") == 0) + { + if (icu_rules != NULL) + pg_fatal("RULES not supported for C or POSIX locale"); + } + else + { + /* canonicalize to a language tag */ + langtag = icu_language_tag(icu_locale); + printf(_("Using language tag \"%s\" for ICU locale \"%s\".\n"), + langtag, icu_locale); + pg_free(icu_locale); + icu_locale = langtag; + + icu_validate_locale(icu_locale); + } /* * In supported builds, the ICU locale ID will be opened during diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out index c658ee1404..bfc28ecfcf 100644 --- a/src/test/regress/expected/collate.icu.utf8.out +++ b/src/test/regress/expected/collate.icu.utf8.out @@ -1043,12 +1043,18 @@ ERROR: ICU locale "nonsense-nowhere" has unknown language "nonsense" HINT: To disable ICU locale validation, set parameter icu_validation_level to DISABLED. CREATE COLLATION testx (provider = icu, locale = '@colStrength=primary;nonsense=yes'); -- fails ERROR: could not convert locale name "@colStrength=primary;nonsense=yes" to language tag: U_ILLEGAL_ARGUMENT_ERROR +CREATE COLLATION testx (provider = icu, locale = 'C', deterministic = false); -- fails +ERROR: nondeterministic collations not supported for C or POSIX locale +CREATE COLLATION testx (provider = icu, locale = 'C', rules = '&V << w <<< W'); -- fails +ERROR: RULES not supported for C or POSIX locale RESET icu_validation_level; CREATE COLLATION testx (provider = icu, locale = '@colStrength=primary;nonsense=yes'); DROP COLLATION testx; WARNING: could not convert locale name "@colStrength=primary;nonsense=yes" to language tag: U_ILLEGAL_ARGUMENT_ERROR CREATE COLLATION testx (provider = icu, locale = 'nonsense-nowhere'); DROP COLLATION testx; WARNING: ICU locale "nonsense-nowhere" has unknown language "nonsense" HINT: To disable ICU locale validation, set parameter icu_validation_level to DISABLED. +CREATE COLLATION testx (provider = icu, locale = 'C'); DROP COLLATION testx; +CREATE COLLATION testx (provider = icu, locale = 'POSIX'); DROP COLLATION testx; CREATE COLLATION test4 FROM nonsense; ERROR: collation "nonsense" for encoding "UTF8" does not exist CREATE COLLATION test5 FROM test0; diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql index 7bd0901281..572dc5a50a 100644 --- a/src/test/regress/sql/collate.icu.utf8.sql +++ b/src/test/regress/sql/collate.icu.utf8.sql @@ -379,9 +379,13 @@ CREATE COLLATION test3 (provider = icu, lc_collate = 'en_US.utf8'); -- fail, nee SET icu_validation_level = ERROR; CREATE COLLATION testx (provider = icu, locale = 'nonsense-nowhere'); -- fails CREATE COLLATION testx (provider = icu, locale = '@colStrength=primary;nonsense=yes'); -- fails +CREATE COLLATION testx (provider = icu, locale = 'C', deterministic = false); -- fails +CREATE COLLATION testx (provider = icu, locale = 'C', rules = '&V << w <<< W'); -- fails RESET icu_validation_level; CREATE COLLATION testx (provider = icu, locale = '@colStrength=primary;nonsense=yes'); DROP COLLATION testx; CREATE COLLATION testx (provider = icu, locale = 'nonsense-nowhere'); DROP COLLATION testx; +CREATE COLLATION testx (provider = icu, locale = 'C'); DROP COLLATION testx; +CREATE COLLATION testx (provider = icu, locale = 'POSIX'); DROP COLLATION testx; CREATE COLLATION test4 FROM nonsense; CREATE COLLATION test5 FROM test0; -- 2.34.1 [text/x-patch] v9-0002-pg_upgrade-check-for-ICU-locale-C-in-versions-15-.patch (4.7K, ../../[email protected]/3-v9-0002-pg_upgrade-check-for-ICU-locale-C-in-versions-15-.patch) download | inline diff: From 879f68b254f2e3f531b0c92c08bebf20298376bc Mon Sep 17 00:00:00 2001 From: Jeff Davis <[email protected]> Date: Wed, 24 May 2023 12:15:33 -0700 Subject: [PATCH v9 2/4] pg_upgrade: check for ICU locale C in versions 15 and earlier. ICU collations with locale "C" (and equivalently "POSIX") were not supported before version 16, but could still be created. Users with such collations would not get expected behavior. Reject upgrading clusters with such collations, so that we can support "C" and "POSIX" locales for the ICU provider without risk of corrupting indexes during pg_upgrade. --- src/bin/pg_upgrade/check.c | 97 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 97 insertions(+) diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c index 64024e3b9e..17c75cf186 100644 --- a/src/bin/pg_upgrade/check.c +++ b/src/bin/pg_upgrade/check.c @@ -26,6 +26,7 @@ static void check_for_tables_with_oids(ClusterInfo *cluster); static void check_for_composite_data_type_usage(ClusterInfo *cluster); static void check_for_reg_data_type_usage(ClusterInfo *cluster); static void check_for_aclitem_data_type_usage(ClusterInfo *cluster); +static void check_icu_c_before_16(ClusterInfo *cluster); static void check_for_jsonb_9_4_usage(ClusterInfo *cluster); static void check_for_pg_role_prefix(ClusterInfo *cluster); static void check_for_new_tablespace_dir(ClusterInfo *new_cluster); @@ -164,6 +165,9 @@ check_and_dump_old_cluster(bool live_check) if (GET_MAJOR_VERSION(old_cluster.major_version) <= 905) check_for_pg_role_prefix(&old_cluster); + if (GET_MAJOR_VERSION(old_cluster.major_version) <= 1500) + check_icu_c_before_16(&old_cluster); + if (GET_MAJOR_VERSION(old_cluster.major_version) == 904 && old_cluster.controldata.cat_ver < JSONB_FORMAT_CHANGE_CAT_VER) check_for_jsonb_9_4_usage(&old_cluster); @@ -1233,6 +1237,99 @@ check_for_aclitem_data_type_usage(ClusterInfo *cluster) check_ok(); } +/* + * check_icu_c_before_16 + * + * Version 16 adds support for the ICU C locale, but it was possible to + * (incorrectly) create it in prior versions. Check for this invalid ICU + * locale name in version 15 and earlier. + */ +static void +check_icu_c_before_16(ClusterInfo *cluster) +{ + PGresult *dat_res; + PGconn *template1_conn = connectToServer(cluster, "template1"); + int dat_ntups; + int i_datname; + FILE *script = NULL; + char output_path[MAXPGPATH]; + + prep_status("Checking for ICU collations with locale \"C\""); + + snprintf(output_path, sizeof(output_path), "%s/%s", + log_opts.basedir, + "icu_c_before_16.txt"); + + /* check pg_database */ + dat_res = executeQueryOrDie(template1_conn, + "SELECT datname " + "FROM pg_catalog.pg_database " + "WHERE datlocprovider='i' " + "AND daticulocale IN ('C','POSIX')"); + + i_datname = PQfnumber(dat_res, "datname"); + + dat_ntups = PQntuples(dat_res); + + for (int rowno = 0; rowno < dat_ntups; rowno++) + { + if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL) + pg_fatal("could not open file \"%s\": %s", + output_path, strerror(errno)); + fprintf(script, "default collation for database %s\n", + PQgetvalue(dat_res, rowno, i_datname)); + } + + PQclear(dat_res); + PQfinish(template1_conn); + + /* check pg_collation in each database */ + for (int dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++) + { + DbInfo *active_db = &cluster->dbarr.dbs[dbnum]; + PGconn *db_conn = connectToServer(cluster, active_db->db_name); + PGresult *coll_res; + int coll_ntups; + int i_collid; + int i_collname; + + coll_res = executeQueryOrDie(db_conn, + "SELECT oid as collid, collname " + "FROM pg_catalog.pg_collation " + "WHERE collprovider='i' " + "AND colliculocale IN ('C','POSIX')"); + + i_collid = PQfnumber(coll_res, "collid"); + i_collname = PQfnumber(coll_res, "collname"); + + coll_ntups = PQntuples(coll_res); + + for (int rowno = 0; rowno < coll_ntups; rowno++) + { + if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL) + pg_fatal("could not open file \"%s\": %s", + output_path, strerror(errno)); + fprintf(script, "database %s collation %s (oid=%s)\n", + active_db->db_name, + PQgetvalue(coll_res, rowno, i_collname), + PQgetvalue(coll_res, rowno, i_collid)); + } + + PQclear(coll_res); + PQfinish(db_conn); + } + + if (script) + { + pg_log(PG_REPORT, "fatal"); + pg_fatal("Your installation contains ICU collations with the locale \"C\" or \"POSIX\",\n" + "which are not supported until version 16. Earlier versions using ICU \n" + "collations with the \"C\" or \"POSIX\" locales cannot be upgraded."); + } + + check_ok(); +} + /* * check_for_jsonb_9_4_usage() * -- 2.34.1 [text/x-patch] v9-0003-Make-LOCALE-apply-to-ICU_LOCALE-for-CREATE-DATABA.patch (15.2K, ../../[email protected]/4-v9-0003-Make-LOCALE-apply-to-ICU_LOCALE-for-CREATE-DATABA.patch) download | inline diff: From 361b81f1ea5153745b51d8215b3dcaeac4fdeedc Mon Sep 17 00:00:00 2001 From: Jeff Davis <[email protected]> Date: Tue, 25 Apr 2023 15:01:55 -0700 Subject: [PATCH v9 3/4] Make LOCALE apply to ICU_LOCALE for CREATE DATABASE. LOCALE is now an alias for LC_COLLATE, LC_CTYPE, and (if the provider is ICU) ICU_LOCALE. The ICU provider accepts more locale names than libc (e.g. language tags and locale names containing collation attributes), so in some cases LC_COLLATE, LC_CTYPE, and ICU_LOCALE will still need to be specified separately. Previously, LOCALE applied only to LC_COLLATE and LC_CTYPE (and similarly for --locale in initdb and createdb). That could lead to confusion when the provider is implicit, such as when it is inherited from the template database, or when ICU was made default at initdb time in commit 27b62377b4. Reverts incomplete fix 5cd1a5af4d. Discussion: https://postgr.es/m/[email protected] --- doc/src/sgml/ref/create_database.sgml | 6 +++-- doc/src/sgml/ref/createdb.sgml | 5 +++- doc/src/sgml/ref/initdb.sgml | 7 +++--- src/backend/commands/collationcmds.c | 2 +- src/backend/commands/dbcommands.c | 15 ++++++++---- src/bin/initdb/initdb.c | 11 ++++++--- src/bin/scripts/createdb.c | 5 ++++ src/bin/scripts/t/020_createdb.pl | 4 ++-- src/test/icu/t/010_database.pl | 23 ++++++++++++------- .../regress/expected/collate.icu.utf8.out | 22 +++++++++--------- 10 files changed, 65 insertions(+), 35 deletions(-) diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml index 13793bb6b7..844773ff44 100644 --- a/doc/src/sgml/ref/create_database.sgml +++ b/doc/src/sgml/ref/create_database.sgml @@ -145,8 +145,10 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable> <term><replaceable class="parameter">locale</replaceable></term> <listitem> <para> - This is a shortcut for setting <symbol>LC_COLLATE</symbol> - and <symbol>LC_CTYPE</symbol> at once. + This is a shortcut for setting <symbol>LC_COLLATE</symbol>, + <symbol>LC_CTYPE</symbol> and <symbol>ICU_LOCALE</symbol> at + once. Some locales are only valid for ICU, and must be set separately + with <symbol>ICU_LOCALE</symbol>. </para> <tip> <para> diff --git a/doc/src/sgml/ref/createdb.sgml b/doc/src/sgml/ref/createdb.sgml index e23419ba6c..e4647d5ce7 100644 --- a/doc/src/sgml/ref/createdb.sgml +++ b/doc/src/sgml/ref/createdb.sgml @@ -124,7 +124,10 @@ PostgreSQL documentation <listitem> <para> Specifies the locale to be used in this database. This is equivalent - to specifying both <option>--lc-collate</option> and <option>--lc-ctype</option>. + to specifying <option>--lc-collate</option>, + <option>--lc-ctype</option>, and <option>--icu-locale</option> to the + same value. Some locales are only valid for ICU and must be set with + <option>--icu-locale</option>. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/initdb.sgml b/doc/src/sgml/ref/initdb.sgml index 87945b4b62..f850dc404d 100644 --- a/doc/src/sgml/ref/initdb.sgml +++ b/doc/src/sgml/ref/initdb.sgml @@ -116,9 +116,10 @@ PostgreSQL documentation <para> To choose a different locale for the cluster, use the option <option>--locale</option>. There are also individual options - <option>--lc-*</option> (see below) to set values for the individual locale - categories. Note that inconsistent settings for different locale - categories can give nonsensical results, so this should be used with care. + <option>--lc-*</option> and <option>--icu-locale</option> (see below) to + set values for the individual locale categories. Note that inconsistent + settings for different locale categories can give nonsensical results, so + this should be used with care. </para> <para> diff --git a/src/backend/commands/collationcmds.c b/src/backend/commands/collationcmds.c index dd6cd2682f..c165922121 100644 --- a/src/backend/commands/collationcmds.c +++ b/src/backend/commands/collationcmds.c @@ -288,7 +288,7 @@ DefineCollation(ParseState *pstate, List *names, List *parameters, bool if_not_e if (langtag && strcmp(colliculocale, langtag) != 0) { ereport(NOTICE, - (errmsg("using standard form \"%s\" for locale \"%s\"", + (errmsg("using standard form \"%s\" for ICU locale \"%s\"", langtag, colliculocale))); colliculocale = langtag; diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c index bfce8dc348..a478a2287f 100644 --- a/src/backend/commands/dbcommands.c +++ b/src/backend/commands/dbcommands.c @@ -1017,7 +1017,12 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt) if (dblocprovider == '\0') dblocprovider = src_locprovider; if (dbiculocale == NULL && dblocprovider == COLLPROVIDER_ICU) - dbiculocale = src_iculocale; + { + if (dlocale && dlocale->arg) + dbiculocale = defGetString(dlocale); + else + dbiculocale = src_iculocale; + } if (dbicurules == NULL && dblocprovider == COLLPROVIDER_ICU) dbicurules = src_icurules; @@ -1031,12 +1036,14 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt) if (!check_locale(LC_COLLATE, dbcollate, &canonname)) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("invalid locale name: \"%s\"", dbcollate))); + errmsg("invalid LC_COLLATE locale name: \"%s\"", dbcollate), + errhint("If the locale name is specific to ICU, use ICU_LOCALE."))); dbcollate = canonname; if (!check_locale(LC_CTYPE, dbctype, &canonname)) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("invalid locale name: \"%s\"", dbctype))); + errmsg("invalid LC_CTYPE locale name: \"%s\"", dbctype), + errhint("If the locale name is specific to ICU, use ICU_LOCALE."))); dbctype = canonname; check_encoding_locale_matches(encoding, dbcollate, dbctype); @@ -1080,7 +1087,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt) if (langtag && strcmp(dbiculocale, langtag) != 0) { ereport(NOTICE, - (errmsg("using standard form \"%s\" for locale \"%s\"", + (errmsg("using standard form \"%s\" for ICU locale \"%s\"", langtag, dbiculocale))); dbiculocale = langtag; diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c index 7aa2d871e3..93b19b952b 100644 --- a/src/bin/initdb/initdb.c +++ b/src/bin/initdb/initdb.c @@ -2163,7 +2163,11 @@ check_locale_name(int category, const char *locale, char **canonname) if (res == NULL) { if (*locale) - pg_fatal("invalid locale name \"%s\"", locale); + { + pg_log_error("invalid locale name \"%s\"", locale); + pg_log_error_hint("If the locale name is specific to ICU, use --icu-locale."); + exit(1); + } else { /* @@ -2376,7 +2380,7 @@ setlocales(void) { char *canonname; - /* set empty lc_* values to locale config if set */ + /* set empty lc_* and iculocale values to locale config if set */ if (locale) { @@ -2392,6 +2396,8 @@ setlocales(void) lc_monetary = locale; if (!lc_messages) lc_messages = locale; + if (!icu_locale && locale_provider == COLLPROVIDER_ICU) + icu_locale = pg_strdup(locale); } /* @@ -3276,7 +3282,6 @@ main(int argc, char *argv[]) break; case 8: locale = "C"; - locale_provider = COLLPROVIDER_LIBC; break; case 9: pwfilename = pg_strdup(optarg); diff --git a/src/bin/scripts/createdb.c b/src/bin/scripts/createdb.c index b4205c4fa5..58e98ebb9c 100644 --- a/src/bin/scripts/createdb.c +++ b/src/bin/scripts/createdb.c @@ -219,6 +219,11 @@ main(int argc, char *argv[]) appendPQExpBuffer(&sql, " STRATEGY %s", fmtId(strategy)); if (template) appendPQExpBuffer(&sql, " TEMPLATE %s", fmtId(template)); + if (locale) + { + appendPQExpBufferStr(&sql, " LOCALE "); + appendStringLiteralConn(&sql, locale, conn); + } if (lc_collate) { appendPQExpBufferStr(&sql, " LC_COLLATE "); diff --git a/src/bin/scripts/t/020_createdb.pl b/src/bin/scripts/t/020_createdb.pl index d0830a4a1d..c3cd674440 100644 --- a/src/bin/scripts/t/020_createdb.pl +++ b/src/bin/scripts/t/020_createdb.pl @@ -137,7 +137,7 @@ $node->command_checks_all( 1, [qr/^$/], [ - 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 + 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 ], 'createdb with incorrect --lc-collate'); $node->command_checks_all( @@ -145,7 +145,7 @@ $node->command_checks_all( 1, [qr/^$/], [ - 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 + 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 ], 'createdb with incorrect --lc-ctype'); diff --git a/src/test/icu/t/010_database.pl b/src/test/icu/t/010_database.pl index d3901f5d3f..ea2be008af 100644 --- a/src/test/icu/t/010_database.pl +++ b/src/test/icu/t/010_database.pl @@ -51,17 +51,24 @@ b), 'sort by explicit collation upper first'); -# Test error cases in CREATE DATABASE involving locale-related options +# Test that LOCALE='C' works for ICU -my ($ret, $stdout, $stderr) = $node1->psql('postgres', - q{CREATE DATABASE dbicu LOCALE_PROVIDER icu LOCALE 'C' TEMPLATE template0 ENCODING UTF8} +my $ret1 = $node1->psql('postgres', + q{CREATE DATABASE dbicu2 LOCALE_PROVIDER icu LOCALE 'C' TEMPLATE template0 ENCODING UTF8} ); -isnt($ret, 0, - "ICU locale must be specified for ICU provider: exit code not 0"); +is($ret1, 0, + "C locale works for ICU"); + +# Test that ICU-specific locale string must be specified with ICU_LOCALE, +# not LOCALE + +my ($ret2, $stdout, $stderr) = $node1->psql('postgres', + q{CREATE DATABASE dbicu3 LOCALE_PROVIDER icu LOCALE '@colStrength=primary' TEMPLATE template0 ENCODING UTF8}); +isnt($ret2, 0, + "ICU-specific locale must be specified with ICU_LOCALE: exit code not 0"); like( $stderr, - qr/ERROR: ICU locale must be specified/, - "ICU locale must be specified for ICU provider: error message"); - + qr/ERROR: invalid LC_COLLATE locale name/, + "ICU-specific locale must be specified with ICU_LOCALE: error message"); done_testing(); diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out index bfc28ecfcf..fdf07db36e 100644 --- a/src/test/regress/expected/collate.icu.utf8.out +++ b/src/test/regress/expected/collate.icu.utf8.out @@ -1200,9 +1200,9 @@ SELECT 'coté' < 'côte' COLLATE "und-x-icu", 'coté' > 'côte' COLLATE testcoll (1 row) CREATE COLLATION testcoll_lower_first (provider = icu, locale = '@colCaseFirst=lower'); -NOTICE: using standard form "und-u-kf-lower" for locale "@colCaseFirst=lower" +NOTICE: using standard form "und-u-kf-lower" for ICU locale "@colCaseFirst=lower" CREATE COLLATION testcoll_upper_first (provider = icu, locale = '@colCaseFirst=upper'); -NOTICE: using standard form "und-u-kf-upper" for locale "@colCaseFirst=upper" +NOTICE: using standard form "und-u-kf-upper" for ICU locale "@colCaseFirst=upper" SELECT 'aaa' < 'AAA' COLLATE testcoll_lower_first, 'aaa' > 'AAA' COLLATE testcoll_upper_first; ?column? | ?column? ----------+---------- @@ -1210,7 +1210,7 @@ SELECT 'aaa' < 'AAA' COLLATE testcoll_lower_first, 'aaa' > 'AAA' COLLATE testcol (1 row) CREATE COLLATION testcoll_shifted (provider = icu, locale = '@colAlternate=shifted'); -NOTICE: using standard form "und-u-ka-shifted" for locale "@colAlternate=shifted" +NOTICE: using standard form "und-u-ka-shifted" for ICU locale "@colAlternate=shifted" SELECT 'de-luge' < 'deanza' COLLATE "und-x-icu", 'de-luge' > 'deanza' COLLATE testcoll_shifted; ?column? | ?column? ----------+---------- @@ -1227,12 +1227,12 @@ SELECT 'A-21' > 'A-123' COLLATE "und-x-icu", 'A-21' < 'A-123' COLLATE testcoll_n (1 row) CREATE COLLATION testcoll_error1 (provider = icu, locale = '@colNumeric=lower'); -NOTICE: using standard form "und-u-kn-lower" for locale "@colNumeric=lower" +NOTICE: using standard form "und-u-kn-lower" for ICU locale "@colNumeric=lower" ERROR: could not open collator for locale "und-u-kn-lower": U_ILLEGAL_ARGUMENT_ERROR -- test that attributes not handled by icu_set_collation_attributes() -- (handled by ucol_open() directly) also work CREATE COLLATION testcoll_de_phonebook (provider = icu, locale = 'de@collation=phonebook'); -NOTICE: using standard form "de-u-co-phonebk" for locale "de@collation=phonebook" +NOTICE: using standard form "de-u-co-phonebk" for ICU locale "de@collation=phonebook" SELECT 'Goldmann' < 'Götz' COLLATE "de-x-icu", 'Goldmann' > 'Götz' COLLATE testcoll_de_phonebook; ?column? | ?column? ----------+---------- @@ -1241,7 +1241,7 @@ SELECT 'Goldmann' < 'Götz' COLLATE "de-x-icu", 'Goldmann' > 'Götz' COLLATE tes -- rules CREATE COLLATION testcoll_rules1 (provider = icu, locale = '', rules = '&a < g'); -NOTICE: using standard form "und" for locale "" +NOTICE: using standard form "und" for ICU locale "" CREATE TABLE test7 (a text); -- example from https://unicode-org.github.io/icu/userguide/collation/customization/#syntax INSERT INTO test7 VALUES ('Abernathy'), ('apple'), ('bird'), ('Boston'), ('Graham'), ('green'); @@ -1269,13 +1269,13 @@ SELECT * FROM test7 ORDER BY a COLLATE testcoll_rules1; DROP TABLE test7; CREATE COLLATION testcoll_rulesx (provider = icu, locale = '', rules = '!!wrong!!'); -NOTICE: using standard form "und" for locale "" +NOTICE: using standard form "und" for ICU locale "" ERROR: could not open collator for locale "und" with rules "!!wrong!!": U_INVALID_FORMAT_ERROR -- nondeterministic collations CREATE COLLATION ctest_det (provider = icu, locale = '', deterministic = true); -NOTICE: using standard form "und" for locale "" +NOTICE: using standard form "und" for ICU locale "" CREATE COLLATION ctest_nondet (provider = icu, locale = '', deterministic = false); -NOTICE: using standard form "und" for locale "" +NOTICE: using standard form "und" for ICU locale "" CREATE TABLE test6 (a int, b text); -- same string in different normal forms INSERT INTO test6 VALUES (1, U&'\00E4bc'); @@ -1325,9 +1325,9 @@ SELECT * FROM test6a WHERE b = ARRAY['äbc'] COLLATE ctest_nondet; (2 rows) CREATE COLLATION case_sensitive (provider = icu, locale = ''); -NOTICE: using standard form "und" for locale "" +NOTICE: using standard form "und" for ICU locale "" CREATE COLLATION case_insensitive (provider = icu, locale = '@colStrength=secondary', deterministic = false); -NOTICE: using standard form "und-u-ks-level2" for locale "@colStrength=secondary" +NOTICE: using standard form "und-u-ks-level2" for ICU locale "@colStrength=secondary" SELECT 'abc' <= 'ABC' COLLATE case_sensitive, 'abc' >= 'ABC' COLLATE case_sensitive; ?column? | ?column? ----------+---------- -- 2.34.1 [text/x-patch] v9-0004-Use-database-default-collation-s-provider-as-defa.patch (6.1K, ../../[email protected]/5-v9-0004-Use-database-default-collation-s-provider-as-defa.patch) download | inline diff: From 6a1b9ca2a247f3e5111988292e148ba86d802005 Mon Sep 17 00:00:00 2001 From: Jeff Davis <[email protected]> Date: Wed, 24 May 2023 09:53:02 -0700 Subject: [PATCH v9 4/4] Use database default collation's provider as default for CREATE COLLATION. --- doc/src/sgml/ref/create_collation.sgml | 9 ++++++--- src/backend/commands/collationcmds.c | 7 ++++++- src/test/regress/expected/collate.linux.utf8.out | 10 +++++----- src/test/regress/sql/collate.linux.utf8.sql | 10 +++++----- 4 files changed, 22 insertions(+), 14 deletions(-) diff --git a/doc/src/sgml/ref/create_collation.sgml b/doc/src/sgml/ref/create_collation.sgml index f6353da5c1..a6927a7d1d 100644 --- a/doc/src/sgml/ref/create_collation.sgml +++ b/doc/src/sgml/ref/create_collation.sgml @@ -121,9 +121,12 @@ CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> FROM <replace <para> Specifies the provider to use for locale services associated with this collation. Possible values are - <literal>icu</literal><indexterm><primary>ICU</primary></indexterm> - (if the server was built with ICU support) or <literal>libc</literal>. - <literal>libc</literal> is the default. See <xref + <literal>icu</literal><indexterm><primary>ICU</primary></indexterm> (if + the server was built with ICU support) or <literal>libc</literal>. If + <replaceable>lc_colllate</replaceable> or + <replaceable>lc_ctype</replaceable> is specified, the default is + <literal>libc</literal>; otherwise, the default is the same as the + database default collation's provider. See <xref linkend="locale-providers"/> for details. </para> </listitem> diff --git a/src/backend/commands/collationcmds.c b/src/backend/commands/collationcmds.c index c165922121..8fc0ff1903 100644 --- a/src/backend/commands/collationcmds.c +++ b/src/backend/commands/collationcmds.c @@ -226,7 +226,12 @@ DefineCollation(ParseState *pstate, List *names, List *parameters, bool if_not_e collproviderstr))); } else - collprovider = COLLPROVIDER_LIBC; + { + if (lccollateEl || lcctypeEl) + collprovider = COLLPROVIDER_LIBC; + else + collprovider = default_locale.provider; + } if (localeEl) { diff --git a/src/test/regress/expected/collate.linux.utf8.out b/src/test/regress/expected/collate.linux.utf8.out index 6d34667ceb..6b0cc95ae8 100644 --- a/src/test/regress/expected/collate.linux.utf8.out +++ b/src/test/regress/expected/collate.linux.utf8.out @@ -1026,7 +1026,7 @@ CREATE SCHEMA test_schema; -- We need to do this this way to cope with varying names for encodings: do $$ BEGIN - EXECUTE 'CREATE COLLATION test0 (locale = ' || + EXECUTE 'CREATE COLLATION test0 (provider = libc, locale = ' || quote_literal(current_setting('lc_collate')) || ');'; END $$; @@ -1034,7 +1034,7 @@ CREATE COLLATION test0 FROM "C"; -- fail, duplicate name ERROR: collation "test0" already exists CREATE COLLATION IF NOT EXISTS test0 FROM "C"; -- ok, skipped NOTICE: collation "test0" already exists, skipping -CREATE COLLATION IF NOT EXISTS test0 (locale = 'foo'); -- ok, skipped +CREATE COLLATION IF NOT EXISTS test0 (provider = libc, locale = 'foo'); -- ok, skipped NOTICE: collation "test0" for encoding "UTF8" already exists, skipping do $$ BEGIN @@ -1046,7 +1046,7 @@ END $$; CREATE COLLATION test3 (lc_collate = 'en_US.utf8'); -- fail, need lc_ctype ERROR: parameter "lc_ctype" must be specified -CREATE COLLATION testx (locale = 'nonsense'); -- fail +CREATE COLLATION testx (provider = libc, locale = 'nonsense'); -- fail ERROR: could not create locale "nonsense": No such file or directory DETAIL: The operating system could not find any locale data for the locale name "nonsense". CREATE COLLATION test4 FROM nonsense; @@ -1166,8 +1166,8 @@ SELECT * FROM collate_test2 ORDER BY b COLLATE UCS_BASIC; -- nondeterministic collations -- (not supported with libc provider) -CREATE COLLATION ctest_det (locale = 'en_US.utf8', deterministic = true); -CREATE COLLATION ctest_nondet (locale = 'en_US.utf8', deterministic = false); +CREATE COLLATION ctest_det (provider = libc, locale = 'en_US.utf8', deterministic = true); +CREATE COLLATION ctest_nondet (provider = libc, locale = 'en_US.utf8', deterministic = false); ERROR: nondeterministic collations not supported with this provider -- cleanup SET client_min_messages TO warning; diff --git a/src/test/regress/sql/collate.linux.utf8.sql b/src/test/regress/sql/collate.linux.utf8.sql index 2b787507c5..cc25f95ac3 100644 --- a/src/test/regress/sql/collate.linux.utf8.sql +++ b/src/test/regress/sql/collate.linux.utf8.sql @@ -358,13 +358,13 @@ CREATE SCHEMA test_schema; -- We need to do this this way to cope with varying names for encodings: do $$ BEGIN - EXECUTE 'CREATE COLLATION test0 (locale = ' || + EXECUTE 'CREATE COLLATION test0 (provider = libc, locale = ' || quote_literal(current_setting('lc_collate')) || ');'; END $$; CREATE COLLATION test0 FROM "C"; -- fail, duplicate name CREATE COLLATION IF NOT EXISTS test0 FROM "C"; -- ok, skipped -CREATE COLLATION IF NOT EXISTS test0 (locale = 'foo'); -- ok, skipped +CREATE COLLATION IF NOT EXISTS test0 (provider = libc, locale = 'foo'); -- ok, skipped do $$ BEGIN EXECUTE 'CREATE COLLATION test1 (lc_collate = ' || @@ -374,7 +374,7 @@ BEGIN END $$; CREATE COLLATION test3 (lc_collate = 'en_US.utf8'); -- fail, need lc_ctype -CREATE COLLATION testx (locale = 'nonsense'); -- fail +CREATE COLLATION testx (provider = libc, locale = 'nonsense'); -- fail CREATE COLLATION test4 FROM nonsense; CREATE COLLATION test5 FROM test0; @@ -455,8 +455,8 @@ SELECT * FROM collate_test2 ORDER BY b COLLATE UCS_BASIC; -- nondeterministic collations -- (not supported with libc provider) -CREATE COLLATION ctest_det (locale = 'en_US.utf8', deterministic = true); -CREATE COLLATION ctest_nondet (locale = 'en_US.utf8', deterministic = false); +CREATE COLLATION ctest_det (provider = libc, locale = 'en_US.utf8', deterministic = true); +CREATE COLLATION ctest_nondet (provider = libc, locale = 'en_US.utf8', deterministic = false); -- cleanup -- 2.34.1 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-06-06 19:18 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-02-01 10:47 [PATCH v33 4/5] Subscripting documentation erthalion <[email protected]> 2023-06-06 19:18 Re: Order changes in PG16 since ICU introduction Jeff Davis <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox