public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v16 04/10] Add pg_ls_dir_metadata to list a dir with file metadata..
4+ messages / 3 participants
[nested] [flat]
* [PATCH v16 04/10] Add pg_ls_dir_metadata to list a dir with file metadata..
@ 2020-03-10 03:40 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Justin Pryzby @ 2020-03-10 03:40 UTC (permalink / raw)
Generalize pg_ls_dir_files and retire pg_ls_dir
Need catversion bumped?
---
doc/src/sgml/func.sgml | 19 ++
src/backend/catalog/system_views.sql | 1 +
src/backend/utils/adt/genfile.c | 229 +++++++++++--------
src/include/catalog/pg_proc.dat | 12 +
src/test/regress/expected/misc_functions.out | 24 ++
src/test/regress/input/tablespace.source | 5 +
src/test/regress/output/tablespace.source | 8 +
src/test/regress/sql/misc_functions.sql | 11 +
8 files changed, 218 insertions(+), 91 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 96b08d0500..9d617f95b9 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25300,6 +25300,17 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
List the contents of a directory. Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
</entry>
</row>
+ <row>
+ <entry>
+ <literal><function>pg_ls_dir_metadata(<parameter>dirname</parameter> <type>text</type> [, <parameter>missing_ok</parameter> <type>boolean</type>, <parameter>include_dot_dirs</parameter> <type>boolean</type>])</function></literal>
+ </entry>
+ <entry><type>setof text</type></entry>
+ <entry>
+ For each file in a directory, list the file and its metadata.
+ Restricted to superusers by default, but other users can be granted
+ EXECUTE to run the function.
+ </entry>
+ </row>
<row>
<entry>
<literal><function>pg_ls_logdir()</function></literal>
@@ -25400,6 +25411,14 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
empty directory from an non-existent directory.
</para>
+ <indexterm>
+ <primary>pg_ls_dir_metadata</primary>
+ </indexterm>
+ <para>
+ <function>pg_ls_dir_metadata</function> lists the files in the specified
+ directory along with the file's metadata.
+ </para>
+
<indexterm>
<primary>pg_ls_logdir</primary>
</indexterm>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 2bd5f5ea14..1c77430f0c 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1467,6 +1467,7 @@ REVOKE EXECUTE ON FUNCTION pg_stat_file(text,boolean) FROM public;
REVOKE EXECUTE ON FUNCTION pg_ls_dir(text) FROM public;
REVOKE EXECUTE ON FUNCTION pg_ls_dir(text,boolean,boolean) FROM public;
+REVOKE EXECUTE ON FUNCTION pg_ls_dir_metadata(text,boolean,boolean) FROM public;
--
-- We also set up some things as accessible to standard roles.
diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index 219ac160f8..4824a55480 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -36,6 +36,21 @@
#include "utils/syscache.h"
#include "utils/timestamp.h"
+static Datum pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags);
+
+#define LS_DIR_ISDIR (1<<0) /* Show column: isdir */
+#define LS_DIR_METADATA (1<<1) /* Show columns: mtime, size */
+#define LS_DIR_MISSING_OK (1<<2) /* Ignore ENOENT if the toplevel dir is missing */
+#define LS_DIR_SKIP_DOT_DIRS (1<<3) /* Do not show . or .. */
+#define LS_DIR_SKIP_HIDDEN (1<<4) /* Do not show anything begining with . */
+#define LS_DIR_SKIP_DIRS (1<<5) /* Do not show directories */
+#define LS_DIR_SKIP_SPECIAL (1<<6) /* Do not show special file types */
+
+/*
+ * Shortcut for the historic behavior of the pg_ls_* functions (not including
+ * pg_ls_dir, which skips different files and doesn't show metadata.
+ */
+#define LS_DIR_HISTORIC (LS_DIR_SKIP_DIRS|LS_DIR_SKIP_HIDDEN|LS_DIR_SKIP_SPECIAL|LS_DIR_METADATA)
/*
* Convert a "text" filename argument to C string, and check it's allowable.
@@ -413,6 +428,11 @@ pg_stat_file(PG_FUNCTION_ARGS)
values[4] = TimestampTzGetDatum(time_t_to_timestamptz(fst.st_ctime));
#endif
values[5] = BoolGetDatum(S_ISDIR(fst.st_mode));
+#ifdef WIN32
+ /* Links should have isdir=false */
+ if (pgwin32_is_junction(filename))
+ values[5] = BoolGetDatum(false);
+#endif
tuple = heap_form_tuple(tupdesc, values, isnull);
@@ -440,79 +460,9 @@ pg_stat_file_1arg(PG_FUNCTION_ARGS)
Datum
pg_ls_dir(PG_FUNCTION_ARGS)
{
- ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
- char *location;
- bool missing_ok = false;
- bool include_dot_dirs = false;
- bool randomAccess;
- TupleDesc tupdesc;
- Tuplestorestate *tupstore;
- DIR *dirdesc;
- struct dirent *de;
- MemoryContext oldcontext;
-
- location = convert_and_check_filename(PG_GETARG_TEXT_PP(0));
-
- /* check the optional arguments */
- if (PG_NARGS() == 3)
- {
- if (!PG_ARGISNULL(1))
- missing_ok = PG_GETARG_BOOL(1);
- if (!PG_ARGISNULL(2))
- include_dot_dirs = PG_GETARG_BOOL(2);
- }
-
- /* check to see if caller supports us returning a tuplestore */
- if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("set-valued function called in context that cannot accept a set")));
- if (!(rsinfo->allowedModes & SFRM_Materialize))
- ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("materialize mode required, but it is not allowed in this context")));
-
- /* The tupdesc and tuplestore must be created in ecxt_per_query_memory */
- oldcontext = MemoryContextSwitchTo(rsinfo->econtext->ecxt_per_query_memory);
-
- tupdesc = CreateTemplateTupleDesc(1);
- TupleDescInitEntry(tupdesc, (AttrNumber) 1, "pg_ls_dir", TEXTOID, -1, 0);
-
- randomAccess = (rsinfo->allowedModes & SFRM_Materialize_Random) != 0;
- tupstore = tuplestore_begin_heap(randomAccess, false, work_mem);
- rsinfo->returnMode = SFRM_Materialize;
- rsinfo->setResult = tupstore;
- rsinfo->setDesc = tupdesc;
-
- MemoryContextSwitchTo(oldcontext);
-
- dirdesc = AllocateDir(location);
- if (!dirdesc)
- {
- /* Return empty tuplestore if appropriate */
- if (missing_ok && errno == ENOENT)
- return (Datum) 0;
- /* Otherwise, we can let ReadDir() throw the error */
- }
-
- while ((de = ReadDir(dirdesc, location)) != NULL)
- {
- Datum values[1];
- bool nulls[1];
-
- if (!include_dot_dirs &&
- (strcmp(de->d_name, ".") == 0 ||
- strcmp(de->d_name, "..") == 0))
- continue;
-
- values[0] = CStringGetTextDatum(de->d_name);
- nulls[0] = false;
-
- tuplestore_putvalues(tupstore, tupdesc, values, nulls);
- }
-
- FreeDir(dirdesc);
- return (Datum) 0;
+ text *filename_t = PG_GETARG_TEXT_PP(0);
+ char *filename = convert_and_check_filename(filename_t);
+ return pg_ls_dir_files(fcinfo, filename, LS_DIR_SKIP_DOT_DIRS);
}
/*
@@ -525,7 +475,9 @@ pg_ls_dir(PG_FUNCTION_ARGS)
Datum
pg_ls_dir_1arg(PG_FUNCTION_ARGS)
{
- return pg_ls_dir(fcinfo);
+ text *filename_t = PG_GETARG_TEXT_PP(0);
+ char *filename = convert_and_check_filename(filename_t);
+ return pg_ls_dir_files(fcinfo, filename, LS_DIR_SKIP_DOT_DIRS);
}
/*
@@ -535,7 +487,7 @@ pg_ls_dir_1arg(PG_FUNCTION_ARGS)
* Other unreadable-directory cases throw an error.
*/
static Datum
-pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, bool missing_ok)
+pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags)
{
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
bool randomAccess;
@@ -544,6 +496,32 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, bool missing_ok)
DIR *dirdesc;
struct dirent *de;
MemoryContext oldcontext;
+ TypeFuncClass tuptype ;
+
+ /* isdir depends on metadata */
+ Assert(!(flags&LS_DIR_ISDIR) || (flags&LS_DIR_METADATA));
+ /* Unreasonable to show isdir and skip dirs */
+ Assert(!(flags&LS_DIR_ISDIR) || !(flags&LS_DIR_SKIP_DIRS));
+
+ /* check the optional arguments */
+ if (PG_NARGS() == 3)
+ {
+ if (!PG_ARGISNULL(1))
+ {
+ if (PG_GETARG_BOOL(1))
+ flags |= LS_DIR_MISSING_OK;
+ else
+ flags &= ~LS_DIR_MISSING_OK;
+ }
+
+ if (!PG_ARGISNULL(2))
+ {
+ if (PG_GETARG_BOOL(2))
+ flags &= ~LS_DIR_SKIP_DOT_DIRS;
+ else
+ flags |= LS_DIR_SKIP_DOT_DIRS;
+ }
+ }
/* check to see if caller supports us returning a tuplestore */
if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
@@ -558,8 +536,20 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, bool missing_ok)
/* The tupdesc and tuplestore must be created in ecxt_per_query_memory */
oldcontext = MemoryContextSwitchTo(rsinfo->econtext->ecxt_per_query_memory);
- if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
- elog(ERROR, "return type must be a row type");
+ tuptype = get_call_result_type(fcinfo, NULL, &tupdesc);
+ if (flags & LS_DIR_METADATA)
+ {
+ if (tuptype != TYPEFUNC_COMPOSITE)
+ elog(ERROR, "return type must be a row type");
+ }
+ else
+ {
+ /* pg_ls_dir returns a simple scalar */
+ if (tuptype != TYPEFUNC_SCALAR)
+ elog(ERROR, "return type must be a scalar type");
+ tupdesc = CreateTemplateTupleDesc(1);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "column", TEXTOID, -1, 0);
+ }
randomAccess = (rsinfo->allowedModes & SFRM_Materialize_Random) != 0;
tupstore = tuplestore_begin_heap(randomAccess, false, work_mem);
@@ -578,20 +568,27 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, bool missing_ok)
if (!dirdesc)
{
/* Return empty tuplestore if appropriate */
- if (missing_ok && errno == ENOENT)
+ if (flags & LS_DIR_MISSING_OK && errno == ENOENT)
return (Datum) 0;
/* Otherwise, we can let ReadDir() throw the error */
}
while ((de = ReadDir(dirdesc, dir)) != NULL)
{
- Datum values[3];
- bool nulls[3];
+ Datum values[4];
+ bool nulls[4];
char path[MAXPGPATH * 2];
struct stat attrib;
- /* Skip hidden files */
- if (de->d_name[0] == '.')
+ /* Skip dot dirs? */
+ if (flags & LS_DIR_SKIP_DOT_DIRS &&
+ (strcmp(de->d_name, ".") == 0 ||
+ strcmp(de->d_name, "..") == 0))
+ continue;
+
+ /* Skip hidden files? */
+ if (flags & LS_DIR_SKIP_HIDDEN &&
+ de->d_name[0] == '.')
continue;
/* Get the file info */
@@ -606,13 +603,34 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, bool missing_ok)
errmsg("could not stat file \"%s\": %m", path)));
}
- /* Ignore anything but regular files */
- if (!S_ISREG(attrib.st_mode))
- continue;
+ /* Skip dirs or special files? */
+ if (S_ISDIR(attrib.st_mode))
+ {
+ if (flags & LS_DIR_SKIP_DIRS)
+ continue;
+ }
+ else if (!S_ISREG(attrib.st_mode))
+ {
+ if (flags & LS_DIR_SKIP_SPECIAL)
+ continue;
+ }
values[0] = CStringGetTextDatum(de->d_name);
- values[1] = Int64GetDatum((int64) attrib.st_size);
- values[2] = TimestampTzGetDatum(time_t_to_timestamptz(attrib.st_mtime));
+ if (flags & LS_DIR_METADATA)
+ {
+ values[1] = Int64GetDatum((int64) attrib.st_size);
+ values[2] = TimestampTzGetDatum(time_t_to_timestamptz(attrib.st_mtime));
+ if (flags & LS_DIR_ISDIR)
+ {
+ values[3] = BoolGetDatum(S_ISDIR(attrib.st_mode));
+#ifdef WIN32
+ /* Links should have isdir=false */
+ if (pgwin32_is_junction(path))
+ values[3] = BoolGetDatum(false);
+#endif
+ }
+ }
+
memset(nulls, 0, sizeof(nulls));
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
@@ -626,14 +644,14 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, bool missing_ok)
Datum
pg_ls_logdir(PG_FUNCTION_ARGS)
{
- return pg_ls_dir_files(fcinfo, Log_directory, false);
+ return pg_ls_dir_files(fcinfo, Log_directory, LS_DIR_HISTORIC);
}
/* Function to return the list of files in the WAL directory */
Datum
pg_ls_waldir(PG_FUNCTION_ARGS)
{
- return pg_ls_dir_files(fcinfo, XLOGDIR, false);
+ return pg_ls_dir_files(fcinfo, XLOGDIR, LS_DIR_HISTORIC);
}
/*
@@ -651,7 +669,8 @@ pg_ls_tmpdir(FunctionCallInfo fcinfo, Oid tblspc)
tblspc)));
TempTablespacePath(path, tblspc);
- return pg_ls_dir_files(fcinfo, path, true);
+ return pg_ls_dir_files(fcinfo, path,
+ LS_DIR_HISTORIC | LS_DIR_MISSING_OK);
}
/*
@@ -680,5 +699,33 @@ pg_ls_tmpdir_1arg(PG_FUNCTION_ARGS)
Datum
pg_ls_archive_statusdir(PG_FUNCTION_ARGS)
{
- return pg_ls_dir_files(fcinfo, XLOGDIR "/archive_status", true);
+ return pg_ls_dir_files(fcinfo, XLOGDIR "/archive_status",
+ LS_DIR_HISTORIC | LS_DIR_MISSING_OK);
+}
+
+/*
+ * Function to return the list of files and metadata in an arbitrary directory.
+ */
+Datum
+pg_ls_dir_metadata(PG_FUNCTION_ARGS)
+{
+ char *dirname = convert_and_check_filename(PG_GETARG_TEXT_PP(0));
+
+ return pg_ls_dir_files(fcinfo, dirname,
+ LS_DIR_METADATA | LS_DIR_SKIP_SPECIAL | LS_DIR_ISDIR);
+}
+
+/*
+ * Function to return the list of files and metadata in an arbitrary directory.
+ * note: this wrapper is necessary to pass the sanity check in opr_sanity,
+ * which checks that all built-in functions that share the implementing C
+ * function take the same number of arguments.
+ */
+Datum
+pg_ls_dir_metadata_1arg(PG_FUNCTION_ARGS)
+{
+ char *dirname = convert_and_check_filename(PG_GETARG_TEXT_PP(0));
+
+ return pg_ls_dir_files(fcinfo, dirname,
+ LS_DIR_METADATA | LS_DIR_SKIP_SPECIAL | LS_DIR_ISDIR);
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 4bce3ad8de..9f46cba5ed 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10904,6 +10904,18 @@
proallargtypes => '{oid,text,int8,timestamptz}', proargmodes => '{i,o,o,o}',
proargnames => '{tablespace,name,size,modification}',
prosrc => 'pg_ls_tmpdir_1arg' },
+{ oid => '5032', descr => 'list directory with metadata',
+ proname => 'pg_ls_dir_metadata', procost => '10', prorows => '20', proretset => 't',
+ provolatile => 'v', prorettype => 'record', proargtypes => 'text bool bool',
+ proallargtypes => '{text,bool,bool,text,int8,timestamptz,bool}', proargmodes => '{i,i,i,o,o,o,o}',
+ proargnames => '{dirname,missing_ok,include_dot_dirs,name,size,modification,isdir}',
+ prosrc => 'pg_ls_dir_metadata' },
+{ oid => '5033', descr => 'list directory with metadata',
+ proname => 'pg_ls_dir_metadata', procost => '10', prorows => '20', proretset => 't',
+ provolatile => 'v', prorettype => 'record', proargtypes => 'text',
+ proallargtypes => '{text,text,int8,timestamptz,bool}', proargmodes => '{i,o,o,o,o}',
+ proargnames => '{dirname,name,size,modification,isdir}',
+ prosrc => 'pg_ls_dir_metadata_1arg' },
# hash partitioning constraint function
{ oid => '5028', descr => 'hash partition CHECK constraint',
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 2e87c548eb..7930909f02 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -219,6 +219,30 @@ select pg_ls_dir('does not exist', true, false); -- ok with missingok=true
select pg_ls_dir('does not exist'); -- fails with missingok=false
ERROR: could not open directory "does not exist": No such file or directory
+-- This tests the missing_ok parameter, which causes pg_ls_tmpdir to succeed even if the tmpdir doesn't exist yet
+-- The name='' condition is never true, so the function runs to completion but returns zero rows.
+select * from pg_ls_tmpdir() where name='Does not exist';
+ name | size | modification
+------+------+--------------
+(0 rows)
+
+select name, isdir from pg_ls_dir_metadata('.') where name='.';
+ name | isdir
+------+-------
+ . | t
+(1 row)
+
+select name, isdir from pg_ls_dir_metadata('.', false, false) where name='.'; -- include_dot_dirs=false
+ name | isdir
+------+-------
+(0 rows)
+
+-- Check that expected columns are present
+select * from pg_ls_dir_metadata('.') limit 0;
+ name | size | modification | isdir
+------+------+--------------+-------
+(0 rows)
+
--
-- Test adding a support function to a subject function
--
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index a5f61a35dc..0b9cfe615e 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -11,6 +11,11 @@ DROP TABLESPACE regress_tblspacewith;
-- create a tablespace we can use
CREATE TABLESPACE regress_tblspace LOCATION '@testtablespace@';
+-- This tests the missing_ok parameter, which causes pg_ls_tmpdir to succeed even if the tmpdir doesn't exist yet
+-- The name='' condition is never true, so the function runs to completion but returns zero rows.
+-- The query is written to ERROR if the tablespace doesn't exist, rather than silently failing to call pg_ls_tmpdir()
+SELECT c.* FROM (SELECT oid FROM pg_tablespace b WHERE b.spcname='regress_tblspace' UNION SELECT 0 ORDER BY 1 DESC LIMIT 1) AS b , pg_ls_tmpdir(oid) AS c WHERE c.name='Does not exist';
+
-- try setting and resetting some properties for the new tablespace
ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1.1);
ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true); -- fail
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 162b591b31..a42714bf40 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -13,6 +13,14 @@ SELECT spcoptions FROM pg_tablespace WHERE spcname = 'regress_tblspacewith';
DROP TABLESPACE regress_tblspacewith;
-- create a tablespace we can use
CREATE TABLESPACE regress_tblspace LOCATION '@testtablespace@';
+-- This tests the missing_ok parameter, which causes pg_ls_tmpdir to succeed even if the tmpdir doesn't exist yet
+-- The name='' condition is never true, so the function runs to completion but returns zero rows.
+-- The query is written to ERROR if the tablespace doesn't exist, rather than silently failing to call pg_ls_tmpdir()
+SELECT c.* FROM (SELECT oid FROM pg_tablespace b WHERE b.spcname='regress_tblspace' UNION SELECT 0 ORDER BY 1 DESC LIMIT 1) AS b , pg_ls_tmpdir(oid) AS c WHERE c.name='Does not exist';
+ name | size | modification
+------+------+--------------
+(0 rows)
+
-- try setting and resetting some properties for the new tablespace
ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1.1);
ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true); -- fail
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index f6857ad177..372345720d 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -65,6 +65,17 @@ select * from (select pg_ls_dir('.', false, false) as name) as ls where ls.name=
select pg_ls_dir('does not exist', true, false); -- ok with missingok=true
select pg_ls_dir('does not exist'); -- fails with missingok=false
+-- This tests the missing_ok parameter, which causes pg_ls_tmpdir to succeed even if the tmpdir doesn't exist yet
+-- The name='' condition is never true, so the function runs to completion but returns zero rows.
+select * from pg_ls_tmpdir() where name='Does not exist';
+
+select name, isdir from pg_ls_dir_metadata('.') where name='.';
+
+select name, isdir from pg_ls_dir_metadata('.', false, false) where name='.'; -- include_dot_dirs=false
+
+-- Check that expected columns are present
+select * from pg_ls_dir_metadata('.') limit 0;
+
--
-- Test adding a support function to a subject function
--
--
2.17.0
--2FkSFaIQeDFoAt0B
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v16-0005-pg_ls_tmpdir-to-show-directories-and-isdir-argum.patch"
^ permalink raw reply [nested|flat] 4+ messages in thread
* Pre-proposal: unicode normalized text
@ 2023-09-12 22:47 Jeff Davis <[email protected]>
2023-10-02 20:27 ` Re: Pre-proposal: unicode normalized text Nico Williams <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Jeff Davis @ 2023-09-12 22:47 UTC (permalink / raw)
To: pgsql-hackers
One of the frustrations with using the "C" locale (or any deterministic
locale) is that the following returns false:
SELECT 'á' = 'á'; -- false
because those are the unicode sequences U&'\0061\0301' and U&'\00E1',
respectively, so memcmp() returns non-zero. But it's really the same
character with just a different representation, and if you normalize
them they are equal:
SELECT normalize('á') = normalize('á'); -- true
The idea is to have a new data type, say "UTEXT", that normalizes the
input so that it can have an improved notion of equality while still
using memcmp().
Unicode guarantees that "the results of normalizing a string on one
version will always be the same as normalizing it on any other version,
as long as the string contains only assigned characters according to
both versions"[1]. It also guarantees that it "will not reallocate,
remove, or reassign" characters[2]. That means that we can normalize in
a forward-compatible way as long as we don't allow the use of
unassigned code points.
I looked at the standard to see what it had to say, and is discusses
normalization, but a standard UCS string with an unassigned code point
is not an error. Without a data type to enforce the constraint that
there are no unassigned code points, we can't guarantee forward
compatibility. Some other systems support NVARCHAR, but I didn't see
any guarantee of normalization or blocking unassigned code points
there, either.
UTEXT benefits:
* slightly better natural language semantics than TEXT with
deterministic collation
* still deterministic=true
* fast memcmp()-based comparisons
* no breaking semantic changes as unicode evolves
TEXT allows unassigned code points, and generally returns the same byte
sequences that were orgiinally entered; therefore UTEXT is not a
replacement for TEXT.
UTEXT could be built-in or it could be an extension or in contrib. If
an extension, we'd probably want to at least expose a function that can
detect unassigned code points, so that it's easy to be consistent with
the auto-generated unicode tables. I also notice that there already is
an unassigned code points table in saslprep.c, but it seems to be
frozen as of Unicode 3.2, and I'm not sure why.
Questions:
* Would this be useful enough to justify a new data type? Would it be
confusing about when to choose one versus the other?
* Would cross-type comparisons between TEXT and UTEXT become a major
problem that would reduce the utility?
* Should "some_utext_value = some_text_value" coerce the LHS to TEXT
or the RHS to UTEXT?
* Other comments or am I missing something?
Regards,
Jeff Davis
[1] https://unicode.org/reports/tr15/
[2] https://www.unicode.org/policies/stability_policy.html
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Pre-proposal: unicode normalized text
2023-09-12 22:47 Pre-proposal: unicode normalized text Jeff Davis <[email protected]>
@ 2023-10-02 20:27 ` Nico Williams <[email protected]>
2023-10-03 19:15 ` Re: Pre-proposal: unicode normalized text Jeff Davis <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Nico Williams @ 2023-10-02 20:27 UTC (permalink / raw)
To: Jeff Davis <[email protected]>; +Cc: pgsql-hackers
On Tue, Sep 12, 2023 at 03:47:10PM -0700, Jeff Davis wrote:
> One of the frustrations with using the "C" locale (or any deterministic
> locale) is that the following returns false:
>
> SELECT 'á' = 'á'; -- false
>
> because those are the unicode sequences U&'\0061\0301' and U&'\00E1',
> respectively, so memcmp() returns non-zero. But it's really the same
> character with just a different representation, and if you normalize
> them they are equal:
>
> SELECT normalize('á') = normalize('á'); -- true
I think you misunderstand Unicode normalization and equivalence. There
is no standard Unicode `normalize()` that would cause the above equality
predicate to be true. If you normalize to NFD (normal form decomposed)
then a _prefix_ of those two strings will be equal, but that's clearly
not what you're looking for.
PostgreSQL already has Unicode normalization support, though it would be
nice to also have form-insensitive indexing and equality predicates.
There are two ways to write 'á' in Unicode: one is pre-composed (one
codepoint) and the other is decomposed (two codepoints in this specific
case), and it would be nice to be able to preserve input form when
storing strings but then still be able to index and match them
form-insensitively (in the case of 'á' both equivalent representations
should be considered equal, and for UNIQUE indexes they should be
considered the same).
You could also have functions that perform lossy normalization in the
sort of way that soundex does, such as first normalizing to NFD then
dropping all combining codepoints which then could allow 'á' to be eq to
'a'. But this would not be a Unicode normalization function.
Nico
--
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Pre-proposal: unicode normalized text
2023-09-12 22:47 Pre-proposal: unicode normalized text Jeff Davis <[email protected]>
2023-10-02 20:27 ` Re: Pre-proposal: unicode normalized text Nico Williams <[email protected]>
@ 2023-10-03 19:15 ` Jeff Davis <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Jeff Davis @ 2023-10-03 19:15 UTC (permalink / raw)
To: Nico Williams <[email protected]>; +Cc: pgsql-hackers
On Mon, 2023-10-02 at 15:27 -0500, Nico Williams wrote:
> I think you misunderstand Unicode normalization and equivalence.
> There
> is no standard Unicode `normalize()` that would cause the above
> equality
> predicate to be true. If you normalize to NFD (normal form
> decomposed)
> then a _prefix_ of those two strings will be equal, but that's
> clearly
> not what you're looking for.
From [1]:
"Unicode Normalization Forms are formally defined normalizations of
Unicode strings which make it possible to determine whether any two
Unicode strings are equivalent to each other. Depending on the
particular Unicode Normalization Form, that equivalence can either be a
canonical equivalence or a compatibility equivalence... A binary
comparison of the transformed strings will then determine equivalence."
NFC and NFD are based on Canonical Equivalence.
"Canonical equivalence is a fundamental equivalency between characters
or sequences of characters which represent the same abstract character,
and which when correctly displayed should always have the same visual
appearance and behavior."
Can you explain why NFC (the default form of normalization used by the
postgres normalize() function), followed by memcmp(), is not the right
thing to use to determine Canonical Equivalence?
Or are you saying that Canonical Equivalence is not a useful thing to
test?
What do you mean about the "prefix"?
In Postgres today:
SELECT normalize(U&'\0061\0301', nfc)::bytea; -- \xc3a1
SELECT normalize(U&'\00E1', nfc)::bytea; -- \xc3a1
SELECT normalize(U&'\0061\0301', nfd)::bytea; -- \x61cc81
SELECT normalize(U&'\00E1', nfd)::bytea; -- \x61cc81
which looks useful to me, but I assume you are saying that it doesn't
generalize well to other cases?
[1] https://unicode.org/reports/tr15/
> There are two ways to write 'á' in Unicode: one is pre-composed (one
> codepoint) and the other is decomposed (two codepoints in this
> specific
> case), and it would be nice to be able to preserve input form when
> storing strings but then still be able to index and match them
> form-insensitively (in the case of 'á' both equivalent
> representations
> should be considered equal, and for UNIQUE indexes they should be
> considered the same).
Sometimes preserving input differences is a good thing, other times
it's not, depending on the context. Almost any data type has some
aspects of the input that might not be preserved -- leading zeros in a
number, or whitespace in jsonb, etc.
If text is stored as normalized with NFC, it could be frustrating if
the retrieved string has a different binary representation than the
source data. But it could also be frustrating to look at two strings
made up of ordinary characters that look identical and for the database
to consider them unequal.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-10-03 19:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-10 03:40 [PATCH v16 04/10] Add pg_ls_dir_metadata to list a dir with file metadata.. Justin Pryzby <[email protected]>
2023-09-12 22:47 Pre-proposal: unicode normalized text Jeff Davis <[email protected]>
2023-10-02 20:27 ` Re: Pre-proposal: unicode normalized text Nico Williams <[email protected]>
2023-10-03 19:15 ` Re: Pre-proposal: unicode normalized text 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