On Thu, Feb 18, 2021 at 8:15 PM Michael Paquier <mich...@paquier.xyz> wrote:
> Could you just add a test with pg_collation_current_version(0)?
Done.
> + pg_strncasecmp("POSIX.", collcollate, 6) != 0)
>
> I didn't know that "POSIX." was possible.
Yeah, that isn't valid on my (quite current) GNU or FreeBSD systems,
and doesn't show up in their "locale -a" output, but I wondered about
that theoretical possibility and googled it, and that showed that it
does exist out there, though I don't know where/which versions,
possibly only a long time ago. You know what, let's just forget that
bit, it's not necessary. Removed.
> While on it, I guess that you could add tab completion support for
> CREATE COLLATION foo FROM.
Good point. Added.
> And shouldn't CREATE COLLATION complete
> with the list of existing collation?
Rght, fixed.
From 1a3092e6ca386bac3292aa60c20e3b2a2ce08fff Mon Sep 17 00:00:00 2001
From: Thomas Munro <thomas.mu...@gmail.com>
Date: Wed, 17 Feb 2021 14:10:40 +1300
Subject: [PATCH v3 1/4] Hide internal error for
pg_collation_actual_version(<bad OID>).
Instead of an unsightly internal "cache lookup failed" message, just
return NULL for bad OIDs, as seems to be the convention for other
similar things.
Reported-by: Justin Pryzby <pry...@telsasoft.com>
Reviewed-by: Michael Paquier <mich...@paquier.xyz>
Discussion: https://postgr.es/m/20210117215940.GE8560%40telsasoft.com
---
src/backend/catalog/index.c | 5 +++--
src/backend/catalog/pg_depend.c | 3 ++-
src/backend/commands/collationcmds.c | 2 +-
src/backend/utils/adt/pg_locale.c | 9 +++++++--
src/include/utils/pg_locale.h | 2 +-
src/test/regress/expected/collate.icu.utf8.out | 14 ++++++++++++++
src/test/regress/sql/collate.icu.utf8.sql | 5 +++++
7 files changed, 33 insertions(+), 7 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index b4ab0b88ad..a9e3679631 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1290,7 +1290,8 @@ do_collation_version_check(const ObjectAddress *otherObject,
return false;
/* Ask the provider for the current version. Give up if unsupported. */
- current_version = get_collation_version_for_oid(otherObject->objectId);
+ current_version = get_collation_version_for_oid(otherObject->objectId,
+ false);
if (!current_version)
return false;
@@ -1369,7 +1370,7 @@ do_collation_version_update(const ObjectAddress *otherObject,
if (OidIsValid(*coll) && otherObject->objectId != *coll)
return false;
- *new_version = get_collation_version_for_oid(otherObject->objectId);
+ *new_version = get_collation_version_for_oid(otherObject->objectId, false);
return true;
}
diff --git a/src/backend/catalog/pg_depend.c b/src/backend/catalog/pg_depend.c
index 63da24322d..362db7fe91 100644
--- a/src/backend/catalog/pg_depend.c
+++ b/src/backend/catalog/pg_depend.c
@@ -116,7 +116,8 @@ recordMultipleDependencies(const ObjectAddress *depender,
referenced->objectId == POSIX_COLLATION_OID)
continue;
- version = get_collation_version_for_oid(referenced->objectId);
+ version = get_collation_version_for_oid(referenced->objectId,
+ false);
/*
* Default collation is pinned, so we need to force recording
diff --git a/src/backend/commands/collationcmds.c b/src/backend/commands/collationcmds.c
index 9634ae6809..a7ee452e19 100644
--- a/src/backend/commands/collationcmds.c
+++ b/src/backend/commands/collationcmds.c
@@ -273,7 +273,7 @@ pg_collation_actual_version(PG_FUNCTION_ARGS)
Oid collid = PG_GETARG_OID(0);
char *version;
- version = get_collation_version_for_oid(collid);
+ version = get_collation_version_for_oid(collid, true);
if (version)
PG_RETURN_TEXT_P(cstring_to_text(version));
diff --git a/src/backend/utils/adt/pg_locale.c b/src/backend/utils/adt/pg_locale.c
index e9c1231f9b..34b82b9335 100644
--- a/src/backend/utils/adt/pg_locale.c
+++ b/src/backend/utils/adt/pg_locale.c
@@ -1726,10 +1726,11 @@ get_collation_actual_version(char collprovider, const char *collcollate)
/*
* Get provider-specific collation version string for a given collation OID.
* Return NULL if the provider doesn't support versions, or the collation is
- * unversioned (for example "C").
+ * unversioned (for example "C"). Unknown OIDs result in NULL if missing_ok is
+ * true.
*/
char *
-get_collation_version_for_oid(Oid oid)
+get_collation_version_for_oid(Oid oid, bool missing_ok)
{
HeapTuple tp;
char *version;
@@ -1751,7 +1752,11 @@ get_collation_version_for_oid(Oid oid)
tp = SearchSysCache1(COLLOID, ObjectIdGetDatum(oid));
if (!HeapTupleIsValid(tp))
+ {
+ if (missing_ok)
+ return NULL;
elog(ERROR, "cache lookup failed for collation %u", oid);
+ }
collform = (Form_pg_collation) GETSTRUCT(tp);
version = get_collation_actual_version(collform->collprovider,
NameStr(collform->collcollate));
diff --git a/src/include/utils/pg_locale.h b/src/include/utils/pg_locale.h
index 34dff74bd1..5a37caefbe 100644
--- a/src/include/utils/pg_locale.h
+++ b/src/include/utils/pg_locale.h
@@ -103,7 +103,7 @@ typedef struct pg_locale_struct *pg_locale_t;
extern pg_locale_t pg_newlocale_from_collation(Oid collid);
-extern char *get_collation_version_for_oid(Oid collid);
+extern char *get_collation_version_for_oid(Oid collid, bool missing_ok);
#ifdef USE_ICU
extern int32_t icu_to_uchar(UChar **buff_uchar, const char *buff, size_t nbytes);
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index bc3752e923..de70cb1212 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2155,3 +2155,17 @@ DROP SCHEMA collate_tests CASCADE;
RESET client_min_messages;
-- leave a collation for pg_upgrade test
CREATE COLLATION coll_icu_upgrade FROM "und-x-icu";
+-- Test user-visible function for inspecting versions
+SELECT pg_collation_actual_version('"en-x-icu"'::regcollation) is not null;
+ ?column?
+----------
+ t
+(1 row)
+
+-- Invalid OIDs are silently ignored
+SELECT pg_collation_actual_version(0) is null;
+ ?column?
+----------
+ t
+(1 row)
+
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index 0de2ed8d85..dd5d208854 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -883,3 +883,8 @@ RESET client_min_messages;
-- leave a collation for pg_upgrade test
CREATE COLLATION coll_icu_upgrade FROM "und-x-icu";
+
+-- Test user-visible function for inspecting versions
+SELECT pg_collation_actual_version('"en-x-icu"'::regcollation) is not null;
+-- Invalid OIDs are silently ignored
+SELECT pg_collation_actual_version(0) is null;
--
2.30.0
From ff2f63d3a98b059edc2c0e868f0734c017ec3825 Mon Sep 17 00:00:00 2001
From: Thomas Munro <thomas.mu...@gmail.com>
Date: Wed, 17 Feb 2021 14:19:40 +1300
Subject: [PATCH v3 2/4] pg_collation_actual_version() ->
pg_collation_current_version().
The new name seems a bit more natural.
Discussion: https://postgr.es/m/20210117215940.GE8560%40telsasoft.com
---
doc/src/sgml/func.sgml | 8 ++++----
src/backend/commands/collationcmds.c | 2 +-
src/backend/utils/adt/pg_locale.c | 14 +++++++-------
src/include/catalog/pg_proc.dat | 4 ++--
src/test/regress/expected/collate.icu.utf8.out | 6 +++---
src/test/regress/sql/collate.icu.utf8.sql | 6 +++---
6 files changed, 20 insertions(+), 20 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 1ab31a9056..d8224272a5 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26227,14 +26227,14 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
- <primary>pg_collation_actual_version</primary>
+ <primary>pg_collation_current_version</primary>
</indexterm>
- <function>pg_collation_actual_version</function> ( <type>oid</type> )
+ <function>pg_collation_current_version</function> ( <type>oid</type> )
<returnvalue>text</returnvalue>
</para>
<para>
- Returns the actual version of the collation object as it is currently
- installed in the operating system. <literal>null</literal> is returned
+ Returns the version of the collation object as reported by the ICU
+ library or operating system. <literal>null</literal> is returned
on operating systems where <productname>PostgreSQL</productname>
doesn't have support for versions.
</para></entry>
diff --git a/src/backend/commands/collationcmds.c b/src/backend/commands/collationcmds.c
index a7ee452e19..4b76a6051d 100644
--- a/src/backend/commands/collationcmds.c
+++ b/src/backend/commands/collationcmds.c
@@ -268,7 +268,7 @@ IsThereCollationInNamespace(const char *collname, Oid nspOid)
}
Datum
-pg_collation_actual_version(PG_FUNCTION_ARGS)
+pg_collation_current_version(PG_FUNCTION_ARGS)
{
Oid collid = PG_GETARG_OID(0);
char *version;
diff --git a/src/backend/utils/adt/pg_locale.c b/src/backend/utils/adt/pg_locale.c
index 34b82b9335..2e4c6e9a26 100644
--- a/src/backend/utils/adt/pg_locale.c
+++ b/src/backend/utils/adt/pg_locale.c
@@ -127,8 +127,8 @@ static char *IsoLocaleName(const char *); /* MSVC specific */
static void icu_set_collation_attributes(UCollator *collator, const char *loc);
#endif
-static char *get_collation_actual_version(char collprovider,
- const char *collcollate);
+static char *get_collation_current_version(char collprovider,
+ const char *collcollate);
/*
* pg_perm_setlocale
@@ -1610,7 +1610,7 @@ pg_newlocale_from_collation(Oid collid)
* the operating system/library.
*/
static char *
-get_collation_actual_version(char collprovider, const char *collcollate)
+get_collation_current_version(char collprovider, const char *collcollate)
{
char *collversion = NULL;
@@ -1743,8 +1743,8 @@ get_collation_version_for_oid(Oid oid, bool missing_ok)
if (!HeapTupleIsValid(tp))
elog(ERROR, "cache lookup failed for database %u", MyDatabaseId);
dbform = (Form_pg_database) GETSTRUCT(tp);
- version = get_collation_actual_version(COLLPROVIDER_LIBC,
- NameStr(dbform->datcollate));
+ version = get_collation_current_version(COLLPROVIDER_LIBC,
+ NameStr(dbform->datcollate));
}
else
{
@@ -1758,8 +1758,8 @@ get_collation_version_for_oid(Oid oid, bool missing_ok)
elog(ERROR, "cache lookup failed for collation %u", oid);
}
collform = (Form_pg_collation) GETSTRUCT(tp);
- version = get_collation_actual_version(collform->collprovider,
- NameStr(collform->collcollate));
+ version = get_collation_current_version(collform->collprovider,
+ NameStr(collform->collcollate));
}
ReleaseSysCache(tp);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 1487710d59..16044125ba 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11321,9 +11321,9 @@
{ oid => '3448',
descr => 'get actual version of collation from operating system',
- proname => 'pg_collation_actual_version', procost => '100',
+ proname => 'pg_collation_current_version', procost => '100',
provolatile => 'v', prorettype => 'text', proargtypes => 'oid',
- prosrc => 'pg_collation_actual_version' },
+ prosrc => 'pg_collation_current_version' },
# system management/monitoring related functions
{ oid => '3353', descr => 'list files in the log directory',
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index de70cb1212..43fbff1de2 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2018,7 +2018,7 @@ SELECT objid::regclass::text collate "C", refobjid::regcollation::text collate "
CASE
WHEN refobjid = 'default'::regcollation THEN 'XXX' -- depends on libc version support
WHEN refobjversion IS NULL THEN 'version not tracked'
-WHEN refobjversion = pg_collation_actual_version(refobjid) THEN 'up to date'
+WHEN refobjversion = pg_collation_current_version(refobjid) THEN 'up to date'
ELSE 'out of date'
END AS version
FROM pg_depend d
@@ -2156,14 +2156,14 @@ RESET client_min_messages;
-- leave a collation for pg_upgrade test
CREATE COLLATION coll_icu_upgrade FROM "und-x-icu";
-- Test user-visible function for inspecting versions
-SELECT pg_collation_actual_version('"en-x-icu"'::regcollation) is not null;
+SELECT pg_collation_current_version('"en-x-icu"'::regcollation) is not null;
?column?
----------
t
(1 row)
-- Invalid OIDs are silently ignored
-SELECT pg_collation_actual_version(0) is null;
+SELECT pg_collation_current_version(0) is null;
?column?
----------
t
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index dd5d208854..8b341dbb24 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -820,7 +820,7 @@ SELECT objid::regclass::text collate "C", refobjid::regcollation::text collate "
CASE
WHEN refobjid = 'default'::regcollation THEN 'XXX' -- depends on libc version support
WHEN refobjversion IS NULL THEN 'version not tracked'
-WHEN refobjversion = pg_collation_actual_version(refobjid) THEN 'up to date'
+WHEN refobjversion = pg_collation_current_version(refobjid) THEN 'up to date'
ELSE 'out of date'
END AS version
FROM pg_depend d
@@ -885,6 +885,6 @@ RESET client_min_messages;
CREATE COLLATION coll_icu_upgrade FROM "und-x-icu";
-- Test user-visible function for inspecting versions
-SELECT pg_collation_actual_version('"en-x-icu"'::regcollation) is not null;
+SELECT pg_collation_current_version('"en-x-icu"'::regcollation) is not null;
-- Invalid OIDs are silently ignored
-SELECT pg_collation_actual_version(0) is null;
+SELECT pg_collation_current_version(0) is null;
--
2.30.0
From 5ebcead5e357a67d26a257c041e898002d0b16f7 Mon Sep 17 00:00:00 2001
From: Thomas Munro <thomas.mu...@gmail.com>
Date: Wed, 17 Feb 2021 14:36:43 +1300
Subject: [PATCH v3 3/4] Refactor get_collation_current_version().
The code paths for three different OSes finished up with three different
ways of excluding C[.xxx] and POSIX from consideration. Merge them.
Reviewed-by: Michael Paquier <mich...@paquier.xyz>
Discussion: https://postgr.es/m/20210117215940.GE8560%40telsasoft.com
---
src/backend/utils/adt/pg_locale.c | 34 ++++---------------------------
1 file changed, 4 insertions(+), 30 deletions(-)
diff --git a/src/backend/utils/adt/pg_locale.c b/src/backend/utils/adt/pg_locale.c
index 2e4c6e9a26..df1f36132d 100644
--- a/src/backend/utils/adt/pg_locale.c
+++ b/src/backend/utils/adt/pg_locale.c
@@ -1636,37 +1636,17 @@ get_collation_current_version(char collprovider, const char *collcollate)
}
else
#endif
- if (collprovider == COLLPROVIDER_LIBC)
+ if (collprovider == COLLPROVIDER_LIBC &&
+ pg_strcasecmp("C", collcollate) != 0 &&
+ pg_strncasecmp("C.", collcollate, 2) != 0 &&
+ pg_strcasecmp("POSIX", collcollate) != 0)
{
#if defined(__GLIBC__)
- char *copy = pstrdup(collcollate);
- char *copy_suffix = strstr(copy, ".");
- bool need_version = true;
-
- /*
- * Check for names like C.UTF-8 by chopping off the encoding suffix on
- * our temporary copy, so we can skip the version.
- */
- if (copy_suffix)
- *copy_suffix = '\0';
- if (pg_strcasecmp("c", copy) == 0 ||
- pg_strcasecmp("posix", copy) == 0)
- need_version = false;
- pfree(copy);
- if (!need_version)
- return NULL;
-
/* Use the glibc version because we don't have anything better. */
collversion = pstrdup(gnu_get_libc_version());
#elif defined(LC_VERSION_MASK)
locale_t loc;
- /* C[.encoding] and POSIX never change. */
- if (strcmp("C", collcollate) == 0 ||
- strncmp("C.", collcollate, 2) == 0 ||
- strcmp("POSIX", collcollate) == 0)
- return NULL;
-
/* Look up FreeBSD collation version. */
loc = newlocale(LC_COLLATE, collcollate, NULL);
if (loc)
@@ -1687,12 +1667,6 @@ get_collation_current_version(char collprovider, const char *collcollate)
NLSVERSIONINFOEX version = {sizeof(NLSVERSIONINFOEX)};
WCHAR wide_collcollate[LOCALE_NAME_MAX_LENGTH];
- /* These would be invalid arguments, but have no version. */
- if (pg_strcasecmp("c", collcollate) == 0 ||
- pg_strcasecmp("posix", collcollate) == 0)
- return NULL;
-
- /* For all other names, ask the OS. */
MultiByteToWideChar(CP_ACP, 0, collcollate, -1, wide_collcollate,
LOCALE_NAME_MAX_LENGTH);
if (!GetNLSVersionEx(COMPARE_STRING, wide_collcollate, &version))
--
2.30.0
From bdaadc82251bb490d25cf55e4aaca4957d52e32d Mon Sep 17 00:00:00 2001
From: Thomas Munro <thomas.mu...@gmail.com>
Date: Wed, 17 Feb 2021 15:05:04 +1300
Subject: [PATCH v3 4/4] Tab-complete CREATE COLLATION.
Reviewed-by: Michael Paquier <mich...@paquier.xyz>
Discussion: https://postgr.es/m/20210117215940.GE8560%40telsasoft.com
---
src/bin/psql/tab-complete.c | 23 ++++++++++++++++++++++-
1 file changed, 22 insertions(+), 1 deletion(-)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b64db82f02..62e1ccc397 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -981,6 +981,11 @@ static const SchemaQuery Query_for_list_of_statistics = {
" FROM pg_catalog.pg_cursors "\
" WHERE substring(pg_catalog.quote_ident(name),1,%d)='%s'"
+#define Query_for_list_of_collations \
+" SELECT pg_catalog.quote_ident(collname) "\
+" FROM pg_catalog.pg_collation "\
+" WHERE collencoding IN (-1, pg_catalog.pg_char_to_encoding(pg_catalog.getdatabaseencoding())) AND substring(pg_catalog.quote_ident(collname),1,%d)='%s'"
+
/*
* These object types were introduced later than our support cutoff of
* server version 7.4. We use the VersionedQuery infrastructure so that
@@ -1031,7 +1036,7 @@ static const pgsql_thing_t words_after_create[] = {
{"AGGREGATE", NULL, NULL, Query_for_list_of_aggregates},
{"CAST", NULL, NULL, NULL}, /* Casts have complex structures for names, so
* skip it */
- {"COLLATION", "SELECT pg_catalog.quote_ident(collname) FROM pg_catalog.pg_collation WHERE collencoding IN (-1, pg_catalog.pg_char_to_encoding(pg_catalog.getdatabaseencoding())) AND substring(pg_catalog.quote_ident(collname),1,%d)='%s'"},
+ {"COLLATION", Query_for_list_of_collations},
/*
* CREATE CONSTRAINT TRIGGER is not supported here because it is designed
@@ -2433,6 +2438,22 @@ psql_completion(const char *text, int start, int end)
else if (Matches("CREATE", "ACCESS", "METHOD", MatchAny, "TYPE", MatchAny))
COMPLETE_WITH("HANDLER");
+ /* CREATE COLLATION */
+ else if (Matches("CREATE", "COLLATION", MatchAny))
+ COMPLETE_WITH("(", "FROM");
+ else if (Matches("CREATE", "COLLATION", MatchAny, "FROM"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_collations);
+ else if (HeadMatches("CREATE", "COLLATION", MatchAny, "(*"))
+ {
+ if (TailMatches("(|*,"))
+ COMPLETE_WITH("LOCALE =", "LC_COLLATE =", "LC_CTYPE =",
+ "PROVIDER =", "DETERMINISTIC =");
+ else if (TailMatches("PROVIDER", "="))
+ COMPLETE_WITH("libc", "icu");
+ else if (TailMatches("DETERMINISTIC", "="))
+ COMPLETE_WITH("true", "false");
+ }
+
/* CREATE DATABASE */
else if (Matches("CREATE", "DATABASE", MatchAny))
COMPLETE_WITH("OWNER", "TEMPLATE", "ENCODING", "TABLESPACE",
--
2.30.0
