Shulou(Shulou.com)06/01 Report--

This section describes the implementation logic of PostgreSQL when specifying the execution of collation collate.

When collate is specified as zh_CN, the collation is inconsistent with the default C big

[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 I. data structure

VarStringSortSupport

Variable length string sorting support

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 custom locale wrapper

/ * 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. * this is a wrapper defined by PG itself. * / 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; II. Source code interpretation

The varstrfastcmp_locale function is used for locale customized sorting. The main implementation function is strcoll_l, which is a C library function. If collate is set to zh_CN, Pinyin is used for sorting and is not case-sensitive.

/ * * 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. * / / copy to buf1 of sss 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] ='\ 0mm; 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. * / / copy to buf2 of sss if (len2! = sss- > last_len2 | | memcmp (sss- > buf2, A2P, len2)! = 0) {memcpy (sss- > buf2, A2P, len2); sss- > buf2 [len2] ='\ 0operations; 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) {/ / set 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 / / call the library function 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 / / No locale set, call the library function 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.1of 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 # 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 (aprimeb) CONCAT1 (amemb) # define CONCAT1 (ameminb) 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 & 0xfffff; 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 = 1-> _ 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 = 127277, nRegisters = 9187201950435737471, alphaMM = 1.3824172084878715e+306, hashesArr = 0x7f7f7f7f7f7f7f7f ArrSize = 9187201950435737471, full_card = {registerWidth = 1271950435737471, 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] ='\ 0mm; (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] ='\ 0mm; (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 'cations, 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, 0x7f115a2cc2e0, 0x17a78b0, 0x7f115a2cc0a0, 0x7f115a2cc020, 0x0, 0x7f115a2cc6a0, 0x7f115a2cc700, 0x7f115a2cc780, 0x7f115a2cc840, 0x7f115a2cc8c0, 0x7f115a2cc920}, _ _ ctype_b = 0x7f115410e0e0, _ _ ctype_tolower = 0x7f115410eae0, _ _ ctype_toupper = 0x7f115410e4e0, _ _ names = {0x1707308 "zh_CN.utf8", 0x7f115a090fd5 "C", 0x7f115a090fd5 "C", 0x1707313 "zh_CN.utf8" 0x7f115a090fd5 "C", 0x7f115a090fd5 "C"} (gdb) IV. References.

N/A

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.