本节介绍了PostgreSQL在指定执行排序规则collate时的实现逻辑.
在指定collate为zh_CN时,排序规则与默认的C大不一致
[local]:5432 pg12@testdb=# SELECT name FROM unnest(ARRAY['MYNAME', 'my-image.jpg', 'my-third-image.jpg']) name ORDER BY name collate "C";
name
--------------------
MYNAME
my-image.jpg
my-third-image.jpg
(3 rows)
Time: 78.843 ms
[local]:5432 pg12@testdb=# SELECT name FROM unnest(ARRAY['MYNAME', 'my-image.jpg', 'my-third-image.jpg']) name ORDER BY name collate "zh_CN";
name
--------------------
my-image.jpg
MYNAME
my-third-image.jpg
(3 rows)
Time: 70.125 ms
一、数据结构
VarStringSortSupport
变长字符串排序支持
typedef struct
{
char *buf1; /* 1st string, or abbreviation original string
* buf */
char *buf2; /* 2nd string, or abbreviation strxfrm() buf */
int buflen1;
int buflen2;
int last_len1; /* Length of last buf1 string/strxfrm() input */
int last_len2; /* Length of last buf2 string/strxfrm() blob */
int last_returned; /* Last comparison result (cache) */
bool cache_blob; /* Does buf2 contain strxfrm() blob, etc? */
bool collate_c;
Oid typid; /* Actual datatype (text/bpchar/bytea/name) */
hyperLogLogState abbr_card; /* Abbreviated key cardinality state */
hyperLogLogState full_card; /* Full key cardinality state */
double prop_card; /* Required cardinality proportion */
pg_locale_t locale;
} VarStringSortSupport;
pg_locale_t
PG自定义的locale包装器
/*
* Locale stuff.
*
* Extended locale functions with gratuitous underscore prefixes.
* (These APIs are nevertheless fully documented by Microsoft.)
*/
#define locale_t _locale_t
/*
* We define our own wrapper around locale_t so we can keep the same
* function signatures for all builds, while not having to create a
* fake version of the standard type locale_t in the global namespace.
* pg_locale_t is occasionally checked for truth, so make it a pointer.
* 这是PG自己定义的包装器.
*/
struct pg_locale_struct
{
char provider;
bool deterministic;
union
{
#ifdef HAVE_LOCALE_T
locale_t lt;
#endif
#ifdef USE_ICU
struct
{
const char *locale;
UCollator *ucol;
} icu;
#endif
int dummy; /* in case we have neither LOCALE_T nor ICU */
} info;
};
typedef struct pg_locale_struct *pg_locale_t;
二、源码解读
varstrfastcmp_locale函数用于locale定制化排序实现,主要的实现函数是strcoll_l,该函数是C库函数,如collate设置为zh_CN,则使用拼音进行排序,不区分大小写.
为麻阳等地区用户提供了全套网页设计制作服务，及麻阳网站建设行业解决方案。主营业务为成都做网站、网站建设、麻阳网站设计，以传统方式定制建设网站，并提供域名空间备案等一条龙服务，秉承以专业、用心的态度为用户提供真诚的服务。我们深信只要达到每一位用户的要求，就会得到认可，从而选择与我们长期合作。这样，我们也可以走得更远！
/*
* sortsupport comparison func for locale cases
*/
static int
varstrfastcmp_locale(char *a1p, int len1, char *a2p, int len2, SortSupport ssup)
{
VarStringSortSupport *sss = (VarStringSortSupport *) ssup->ssup_extra;
int result;
bool arg1_match;
/* Fast pre-check for equality, as discussed in varstr_cmp() */
if (len1 == len2 && memcmp(a1p, a2p, len1) == 0)
{
/*
* No change in buf1 or buf2 contents, so avoid changing last_len1 or
* last_len2. Existing contents of buffers might still be used by
* next call.
*
* It's fine to allow the comparison of BpChar padding bytes here,
* even though that implies that the memcmp() will usually be
* performed for BpChar callers (though multibyte characters could
* still prevent that from occurring). The memcmp() is still very
* cheap, and BpChar's funny semantics have us remove trailing spaces
* (not limited to padding), so we need make no distinction between
* padding space characters and "real" space characters.
*/
return 0;
}
if (sss->typid == BPCHAROID)
{
/* Get true number of bytes, ignoring trailing spaces */
len1 = bpchartruelen(a1p, len1);
len2 = bpchartruelen(a2p, len2);
}
if (len1 >= sss->buflen1)
{
pfree(sss->buf1);
sss->buflen1 = Max(len1 + 1, Min(sss->buflen1 * 2, MaxAllocSize));
sss->buf1 = MemoryContextAlloc(ssup->ssup_cxt, sss->buflen1);
}
if (len2 >= sss->buflen2)
{
pfree(sss->buf2);
sss->buflen2 = Max(len2 + 1, Min(sss->buflen2 * 2, MaxAllocSize));
sss->buf2 = MemoryContextAlloc(ssup->ssup_cxt, sss->buflen2);
}
/*
* We're likely to be asked to compare the same strings repeatedly, and
* memcmp() is so much cheaper than strcoll() that it pays to try to cache
* comparisons, even though in general there is no reason to think that
* that will work out (every string datum may be unique). Caching does
* not slow things down measurably when it doesn't work out, and can speed
* things up by rather a lot when it does. In part, this is because the
* memcmp() compares data from cachelines that are needed in L1 cache even
* when the last comparison's result cannot be reused.
*/
//拷贝到sss的buf1中
arg1_match = true;
if (len1 != sss->last_len1 || memcmp(sss->buf1, a1p, len1) != 0)
{
arg1_match = false;
memcpy(sss->buf1, a1p, len1);
sss->buf1[len1] = '\0';
sss->last_len1 = len1;
}
/*
* If we're comparing the same two strings as last time, we can return the
* same answer without calling strcoll() again. This is more likely than
* it seems (at least with moderate to low cardinality sets), because
* quicksort compares the same pivot against many values.
*/
//拷贝到sss的buf2中
if (len2 != sss->last_len2 || memcmp(sss->buf2, a2p, len2) != 0)
{
memcpy(sss->buf2, a2p, len2);
sss->buf2[len2] = '\0';
sss->last_len2 = len2;
}
else if (arg1_match && !sss->cache_blob)
{
/* Use result cached following last actual strcoll() call */
return sss->last_returned;
}
if (sss->locale)
{
//设置了locale
if (sss->locale->provider == COLLPROVIDER_ICU)
{
#ifdef USE_ICU
#ifdef HAVE_UCOL_STRCOLLUTF8
if (GetDatabaseEncoding() == PG_UTF8)
{
UErrorCode status;
status = U_ZERO_ERROR;
result = ucol_strcollUTF8(sss->locale->info.icu.ucol,
a1p, len1,
a2p, len2,
&status);
if (U_FAILURE(status))
ereport(ERROR,
(errmsg("collation failed: %s", u_errorName(status))));
}
else
#endif
{
int32_t ulen1,
ulen2;
UChar *uchar1,
*uchar2;
ulen1 = icu_to_uchar(&uchar1, a1p, len1);
ulen2 = icu_to_uchar(&uchar2, a2p, len2);
result = ucol_strcoll(sss->locale->info.icu.ucol,
uchar1, ulen1,
uchar2, ulen2);
pfree(uchar1);
pfree(uchar2);
}
#else /* not USE_ICU */
/* shouldn't happen */
elog(ERROR, "unsupported collprovider: %c", sss->locale->provider);
#endif /* not USE_ICU */
}
else
{
#ifdef HAVE_LOCALE_T
//调用库函数strcoll_l
result = strcoll_l(sss->buf1, sss->buf2, sss->locale->info.lt);
#else
/* shouldn't happen */
elog(ERROR, "unsupported collprovider: %c", sss->locale->provider);
#endif
}
}
else
//没有设置locale,调用库函数strcoll
result = strcoll(sss->buf1, sss->buf2);
/* Break tie if necessary. */
if (result == 0 &&
(!sss->locale || sss->locale->deterministic))
result = strcmp(sss->buf1, sss->buf2);
/* Cache result, perhaps saving an expensive strcoll() call next time */
sss->cache_blob = false;
sss->last_returned = result;
return result;
}
strcoll_l.c
/* Copyright (C) 1995-2018 Free Software Foundation, Inc.
This file is part of the GNU C Library.
Written by Ulrich Drepper , 1995.
The GNU C Library is free software; you can redistribute it and/or
modify it under the terms of the GNU Lesser General Public
License as published by the Free Software Foundation; either
version 2.1 of the License, or (at your option) any later version.
The GNU C Library is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
Lesser General Public License for more details.
You should have received a copy of the GNU Lesser General Public
License along with the GNU C Library; if not, see
. */
#include
#include
#include
#include
#include
#include
#include
#include
#ifndef STRING_TYPE
# define STRING_TYPE char
# define USTRING_TYPE unsigned char
# define STRCOLL __strcoll_l
# define STRCMP strcmp
# define WEIGHT_H "../locale/weight.h"
# define SUFFIX MB
# define L(arg) arg
#endif
#define CONCAT(a,b) CONCAT1(a,b)
#define CONCAT1(a,b) a##b
#include "../locale/localeinfo.h"
#include WEIGHT_H
/* Track status while looking for sequences in a string. */
typedef struct
{
int len; /* Length of the current sequence. */
size_t val; /* Position of the sequence relative to the
previous non-ignored sequence. */
size_t idxmax; /* Maximum index in sequences. */
size_t idxcnt; /* Current count of indices. */
size_t backw; /* Current Backward sequence index. */
size_t backw_stop; /* Index where the backward sequences stop. */
const USTRING_TYPE *us; /* The string. */
unsigned char rule; /* Saved rule for the first sequence. */
int32_t idx; /* Index to weight of the current sequence. */
int32_t save_idx; /* Save looked up index of a forward
sequence after the last backward
sequence. */
const USTRING_TYPE *back_us; /* Beginning of the backward sequence. */
} coll_seq;
/* Get next sequence. Traverse the string as required. */
static __always_inline void
get_next_seq (coll_seq *seq, int nrules, const unsigned char *rulesets,
const USTRING_TYPE *weights, const int32_t *table,
const USTRING_TYPE *extra, const int32_t *indirect,
int pass)
{
size_t val = seq->val = 0;
int len = seq->len;
size_t backw_stop = seq->backw_stop;
size_t backw = seq->backw;
size_t idxcnt = seq->idxcnt;
size_t idxmax = seq->idxmax;
int32_t idx = seq->idx;
const USTRING_TYPE *us = seq->us;
while (len == 0)
{
++val;
if (backw_stop != ~0ul)
{
/* There is something pushed. */
if (backw == backw_stop)
{
/* The last pushed character was handled. Continue
with forward characters. */
if (idxcnt < idxmax)
{
idx = seq->save_idx;
backw_stop = ~0ul;
}
else
{
/* Nothing anymore. The backward sequence ended with
the last sequence in the string. Note that len is
still zero. */
idx = 0;
break;
}
}
else
{
/* XXX Traverse BACKW sequences from the beginning of
BACKW_STOP to get the next sequence. Is ther a quicker way
to do this? */
size_t i = backw_stop;
us = seq->back_us;
while (i < backw)
{
int32_t tmp = findidx (table, indirect, extra, &us, -1);
idx = tmp & 0xffffff;
i++;
}
--backw;
us = seq->us;
}
}
else
{
backw_stop = idxmax;
int32_t prev_idx = idx;
while (*us != L('\0'))
{
int32_t tmp = findidx (table, indirect, extra, &us, -1);
unsigned char rule = tmp >> 24;
prev_idx = idx;
idx = tmp & 0xffffff;
idxcnt = idxmax++;
/* Save the rule for the first sequence. */
if (__glibc_unlikely (idxcnt == 0))
seq->rule = rule;
if ((rulesets[rule * nrules + pass]
& sort_backward) == 0)
/* No more backward characters to push. */
break;
++idxcnt;
}
if (backw_stop >= idxcnt)
{
/* No sequence at all or just one. */
if (idxcnt == idxmax || backw_stop > idxcnt)
/* Note that len is still zero. */
break;
backw_stop = ~0ul;
}
else
{
/* We pushed backward sequences. If the stream ended with the
backward sequence, then we process the last sequence we
found. Otherwise we process the sequence before the last
one since the last one was a forward sequence. */
seq->back_us = seq->us;
seq->us = us;
backw = idxcnt;
if (idxmax > idxcnt)
{
backw--;
seq->save_idx = idx;
idx = prev_idx;
}
if (backw > backw_stop)
backw--;
}
}
/* With GCC 5.3 when compiling with -Os the compiler complains
that idx, taken from seq->idx (seq1 or seq2 from STRCOLL) may
be used uninitialized. In general this can't possibly be true
since seq1.idx and seq2.idx are initialized to zero in the
outer function. Only one case where seq->idx is restored from
seq->save_idx might result in an uninitialized idx value, but
it is guarded by a sequence of checks against backw_stop which
ensures that seq->save_idx was saved to first and contains a
valid value. */
DIAG_PUSH_NEEDS_COMMENT;
DIAG_IGNORE_Os_NEEDS_COMMENT (5, "-Wmaybe-uninitialized");
len = weights[idx++];
DIAG_POP_NEEDS_COMMENT;
/* Skip over indices of previous levels. */
for (int i = 0; i < pass; i++)
{
idx += len;
len = weights[idx];
idx++;
}
}
/* Update the structure. */
seq->val = val;
seq->len = len;
seq->backw_stop = backw_stop;
seq->backw = backw;
seq->idxcnt = idxcnt;
seq->idxmax = idxmax;
seq->us = us;
seq->idx = idx;
}
/* Compare two sequences. */
static __always_inline int
do_compare (coll_seq *seq1, coll_seq *seq2, int position,
const USTRING_TYPE *weights)
{
int seq1len = seq1->len;
int seq2len = seq2->len;
size_t val1 = seq1->val;
size_t val2 = seq2->val;
int idx1 = seq1->idx;
int idx2 = seq2->idx;
int result = 0;
/* Test for position if necessary. */
if (position && val1 != val2)
{
result = val1 > val2 ? 1 : -1;
goto out;
}
/* Compare the two sequences. */
do
{
if (weights[idx1] != weights[idx2])
{
/* The sequences differ. */
result = weights[idx1] - weights[idx2];
goto out;
}
/* Increment the offsets. */
++idx1;
++idx2;
--seq1len;
--seq2len;
}
while (seq1len > 0 && seq2len > 0);
if (position && seq1len != seq2len)
result = seq1len - seq2len;
out:
seq1->len = seq1len;
seq2->len = seq2len;
seq1->idx = idx1;
seq2->idx = idx2;
return result;
}
int
STRCOLL (const STRING_TYPE *s1, const STRING_TYPE *s2, locale_t l)
{
struct __locale_data *current = l->__locales[LC_COLLATE];
uint_fast32_t nrules = current->values[_NL_ITEM_INDEX (_NL_COLLATE_NRULES)].word;
/* We don't assign the following values right away since it might be
unnecessary in case there are no rules. */
const unsigned char *rulesets;
const int32_t *table;
const USTRING_TYPE *weights;
const USTRING_TYPE *extra;
const int32_t *indirect;
if (nrules == 0)
return STRCMP (s1, s2);
/* Catch empty strings. */
if (__glibc_unlikely (*s1 == '\0') || __glibc_unlikely (*s2 == '\0'))
return (*s1 != '\0') - (*s2 != '\0');
rulesets = (const unsigned char *)
current->values[_NL_ITEM_INDEX (_NL_COLLATE_RULESETS)].string;
table = (const int32_t *)
current->values[_NL_ITEM_INDEX (CONCAT(_NL_COLLATE_TABLE,SUFFIX))].string;
weights = (const USTRING_TYPE *)
current->values[_NL_ITEM_INDEX (CONCAT(_NL_COLLATE_WEIGHT,SUFFIX))].string;
extra = (const USTRING_TYPE *)
current->values[_NL_ITEM_INDEX (CONCAT(_NL_COLLATE_EXTRA,SUFFIX))].string;
indirect = (const int32_t *)
current->values[_NL_ITEM_INDEX (CONCAT(_NL_COLLATE_INDIRECT,SUFFIX))].string;
assert (((uintptr_t) table) % __alignof__ (table[0]) == 0);
assert (((uintptr_t) weights) % __alignof__ (weights[0]) == 0);
assert (((uintptr_t) extra) % __alignof__ (extra[0]) == 0);
assert (((uintptr_t) indirect) % __alignof__ (indirect[0]) == 0);
int result = 0, rule = 0;
/* With GCC 7 when compiling with -Os the compiler warns that
seq1.back_us and seq2.back_us might be used uninitialized.
Sometimes this warning appears at locations in locale/weightwc.h
where the actual use is, but on architectures other than x86_64,
x86 and s390x, a warning appears at the definitions of seq1 and
seq2. This uninitialized use is impossible for the same reason
as described in comments in locale/weightwc.h. */
DIAG_PUSH_NEEDS_COMMENT;
DIAG_IGNORE_Os_NEEDS_COMMENT (7, "-Wmaybe-uninitialized");
coll_seq seq1, seq2;
DIAG_POP_NEEDS_COMMENT;
seq1.len = 0;
seq1.idxmax = 0;
seq1.rule = 0;
seq2.len = 0;
seq2.idxmax = 0;
for (int pass = 0; pass < nrules; ++pass)
{
seq1.idxcnt = 0;
seq1.idx = 0;
seq2.idx = 0;
seq1.backw_stop = ~0ul;
seq1.backw = ~0ul;
seq2.idxcnt = 0;
seq2.backw_stop = ~0ul;
seq2.backw = ~0ul;
/* We need the elements of the strings as unsigned values since they
are used as indices. */
seq1.us = (const USTRING_TYPE *) s1;
seq2.us = (const USTRING_TYPE *) s2;
/* We assume that if a rule has defined `position' in one section
this is true for all of them. Please note that the localedef programs
makes sure that `position' is not used at the first level. */
int position = rulesets[rule * nrules + pass] & sort_position;
while (1)
{
get_next_seq (&seq1, nrules, rulesets, weights, table,
extra, indirect, pass);
get_next_seq (&seq2, nrules, rulesets, weights, table,
extra, indirect, pass);
/* See whether any or both strings are empty. */
if (seq1.len == 0 || seq2.len == 0)
{
if (seq1.len == seq2.len)
{
/* Both strings ended and are equal at this level. Do a
byte-level comparison to ensure that we don't waste time
going through multiple passes for totally equal strings
before proceeding to subsequent passes. */
if (pass == 0 && STRCMP (s1, s2) == 0)
return result;
else
break;
}
/* This means one string is shorter than the other. Find out
which one and return an appropriate value. */
return seq1.len == 0 ? -1 : 1;
}
result = do_compare (&seq1, &seq2, position, weights);
if (result != 0)
return result;
}
rule = seq1.rule;
}
return result;
}
libc_hidden_def (STRCOLL)
#ifndef WIDE_CHAR_VERSION
weak_alias (__strcoll_l, strcoll_l)
#endif
三、跟踪分析
查询SQL
[local]:5432 pg12@testdb=# SELECT name FROM unnest(ARRAY['MYNAME', 'my-image.jpg', 'my-third-image.jpg']) name ORDER BY name collate "zh_CN";
name
--------------------
my-image.jpg
MYNAME
my-third-image.jpg
(3 rows)
Time: 70.125 ms
gdb跟踪
(gdb) b varstrfastcmp_locale
Breakpoint 1 at 0xa30df7: file varlena.c, line 2244.
(gdb) c
Continuing.
Breakpoint 1, varstrfastcmp_locale (a1p=0x17bbe69 "MYNAME\017MYNAME~\177@", len1=6,
a2p=0x17bbea1 "my-image.jpg\033my-image.jpg~", '\177' , "@", len2=12, ssup=0x17ba260)
at varlena.c:2244
2244 VarStringSortSupport *sss = (VarStringSortSupport *) ssup->ssup_extra;
(gdb) n
2249 if (len1 == len2 && memcmp(a1p, a2p, len1) == 0)
(gdb) p *sss
$1 = {buf1 = 0x17ba3d0 '\177' ..., buf2 = 0x17ba7e8 '\177' ..., buflen1 = 1024,
buflen2 = 1024, last_len1 = -1, last_len2 = -1, last_returned = 0, cache_blob = true, collate_c = false, typid = 25,
abbr_card = {registerWidth = 127 '\177', nRegisters = 9187201950435737471, alphaMM = 1.3824172084878715e+306,
hashesArr = 0x7f7f7f7f7f7f7f7f , arrSize = 9187201950435737471}, full_card = {
registerWidth = 127 '\177', nRegisters = 9187201950435737471, alphaMM = 1.3824172084878715e+306,
hashesArr = 0x7f7f7f7f7f7f7f7f , arrSize = 9187201950435737471},
prop_card = 1.3824172084878715e+306, locale = 0x1717d90}
(gdb) n
2267 if (sss->typid == BPCHAROID)
(gdb)
2274 if (len1 >= sss->buflen1)
(gdb)
2280 if (len2 >= sss->buflen2)
(gdb)
2297 arg1_match = true;
(gdb)
2298 if (len1 != sss->last_len1 || memcmp(sss->buf1, a1p, len1) != 0)
(gdb)
2300 arg1_match = false;
(gdb)
2301 memcpy(sss->buf1, a1p, len1);
(gdb)
2302 sss->buf1[len1] = '\0';
(gdb)
2303 sss->last_len1 = len1;
(gdb)
2312 if (len2 != sss->last_len2 || memcmp(sss->buf2, a2p, len2) != 0)
(gdb)
2314 memcpy(sss->buf2, a2p, len2);
(gdb)
2315 sss->buf2[len2] = '\0';
(gdb)
2316 sss->last_len2 = len2;
(gdb)
2324 if (sss->locale)
(gdb)
2326 if (sss->locale->provider == COLLPROVIDER_ICU)
(gdb) p sss->locale
$2 = (pg_locale_t) 0x1717d90
(gdb) p *sss->locale
$3 = {provider = 99 'c', deterministic = true, info = {lt = 0x1707220, dummy = 24146464}}
(gdb) n
2369 result = strcoll_l(sss->buf1, sss->buf2, sss->locale->info.lt);
(gdb)
2380 if (result == 0 &&
(gdb) p result
$4 = 5
(gdb) n
2385 sss->cache_blob = false;
(gdb)
2386 sss->last_returned = result;
(gdb)
2387 return result;
(gdb) p result
$5 = 5
(gdb) p *sss->locale->info.lt
$6 = {__locales = {0x1706ce0, 0x7f115a2cc260 <_nl_C_LC_NUMERIC>, 0x7f115a2cc2e0 <_nl_C_LC_TIME>, 0x17a78b0,
0x7f115a2cc0a0 <_nl_C_LC_MONETARY>, 0x7f115a2cc020 <_nl_C_LC_MESSAGES>, 0x0, 0x7f115a2cc6a0 <_nl_C_LC_PAPER>,
0x7f115a2cc700 <_nl_C_LC_NAME>, 0x7f115a2cc780 <_nl_C_LC_ADDRESS>, 0x7f115a2cc840 <_nl_C_LC_TELEPHONE>,
0x7f115a2cc8c0 <_nl_C_LC_MEASUREMENT>, 0x7f115a2cc920 <_nl_C_LC_IDENTIFICATION>}, __ctype_b = 0x7f115410e0e0,
__ctype_tolower = 0x7f115410eae0, __ctype_toupper = 0x7f115410e4e0, __names = {0x1707308 "zh_CN.utf8",
0x7f115a090fd5 <_nl_C_name> "C", 0x7f115a090fd5 <_nl_C_name> "C", 0x1707313 "zh_CN.utf8",
0x7f115a090fd5 <_nl_C_name> "C", 0x7f115a090fd5 <_nl_C_name> "C", 0x7f115a090fd5 <_nl_C_name> "C",
0x7f115a090fd5 <_nl_C_name> "C", 0x7f115a090fd5 <_nl_C_name> "C", 0x7f115a090fd5 <_nl_C_name> "C",
0x7f115a090fd5 <_nl_C_name> "C", 0x7f115a090fd5 <_nl_C_name> "C", 0x7f115a090fd5 <_nl_C_name> "C"}}
(gdb)
四、参考资料
N/A
