Re: force_not_null option support for file_fdw

(2011/09/05 22:05), Kohei Kaigai wrote:
>> In my usual environment that test passed, but finally I've reproduced the failure with setting
>> $LC_COLLATE to "es_ES.UTF-8".  Do you have set any $LC_COLLATE in your test environment?
>>
> It is not set in my environment.
>
> I checked the behavior of ORDER BY when we set a collation on the regular relation, not a foreign table.
> Do we hit something other unexpected bug in collation here?
>
> postgres=# CREATE TABLE t1 (word1 text);
> CREATE TABLE
> postgres=# INSERT INTO t1 VALUES ('ABC'),('abc'),('123'),('NULL');
> INSERT 0 4
> postgres=# ALTER TABLE t1 ALTER word1 TYPE text COLLATE "ja_JP.utf8";
> ALTER TABLE
> postgres=# SELECT * FROM t1 ORDER BY word1;
>   word1
> -------
>   123
>   ABC
>   NULL
>   abc
> (4 rows)
>
> postgres=# ALTER TABLE t1 ALTER word1 TYPE text COLLATE "en_US.utf8";
> ALTER TABLE
> postgres=# SELECT * FROM t1 ORDER BY word1;
>   word1
> -------
>   123
>   abc
>   ABC
>   NULL
> (4 rows)

Thanks for the checking.  FYI, I mainly use Fedora 15 box with Japanese
environment for my development.

ISTM that your results are reasonable for each collation setting.
Former ordering is same as C locale, and in latter case alphabetical
order has priority over case distinctions.  Do you mean that ordering
used in file_fdw is affected from something unexpected, without
collation or locale setting?

BTW, I found a thread which is related to this issue.
  http://archives.postgresql.org/pgsql-hackers/2011-09/msg00130.php

I changed the test data so that it uses only upper case alphabets,
because case distinction is not important for that test.  I also removed
digits to avoid test failure in some locales which sort alphabets before
digits.

Regards,
--
Shigeru Hanada