Re: UPDATE of partition key

On 8 November 2017 at 07:55, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> On Tue, Nov 7, 2017 at 8:03 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> The changes to trigger.c still make me super-nervous. Hey THOMAS
>> MUNRO, any chance you could review that part?
>
> Looking, but here's one silly thing that jumped out at me while
> getting started with this patch. I cannot seem to convince my macOS
> system to agree with the expected sort order from :show_data, where
> underscores precede numbers:
>
> part_a_10_a_20 | a | 10 | 200 | 1 |
> part_a_1_a_10 | a | 1 | 1 | 1 |
> - part_d_1_15 | b | 15 | 146 | 1 |
> - part_d_1_15 | b | 16 | 147 | 2 |
> part_d_15_20 | b | 17 | 155 | 16 |
> part_d_15_20 | b | 19 | 155 | 19 |
> + part_d_1_15 | b | 15 | 146 | 1 |
> + part_d_1_15 | b | 16 | 147 | 2 |
>
> It seems that macOS (like older BSDs) just doesn't know how to sort
> Unicode and falls back to sorting the bits. I expect that means that
> the test will also fail on any other OS with "make check
> LC_COLLATE=C". I believe our regression tests are supposed to pass
> with a wide range of collations including C, so I wonder if this means
> we should stick a leading zero on those single digit numbers, or
> something, to stabilise the output.

I preferably need to retain the partition names. I have now added a
LOCALE "C" for partname like this :

-\set show_data 'select tableoid::regclass::text partname, * from
range_parted order by 1, 2, 3, 4, 5, 6'
+\set show_data 'select tableoid::regclass::text COLLATE "C" partname,
* from range_parted order by 1, 2, 3, 4, 5, 6'

Thomas, can you please try the attached incremental patch
regress_locale_changes.patch and check if the test passes ? The patch
is to be applied on the main v22 patch. If the test passes, I will
include these changes (also for list_parted) in the upcoming v23
patch.

Thanks
-Amit Khandekar