PostgreSQL 源码解读（215）- 查询#122(varstrfastcmp_locale)

本节介绍了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 intvarstrfastcmp_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 voidget_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 intdo_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;}intSTRCOLL (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_VERSIONweak_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_localeBreakpoint 1 at 0xa30df7: file varlena.c, line 2244.(gdb) cContinuing.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:22442244        VarStringSortSupport *sss = (VarStringSortSupport *) ssup->ssup_extra;(gdb) n2249        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) n2267        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) n2369                result = strcoll_l(sss->buf1, sss->buf2, sss->locale->info.lt);(gdb) 2380        if (result == 0 &&(gdb) p result$4 = 5(gdb) n2385        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