Skip site navigation (1) Skip section navigation (2)

Re: force_not_null option support for file_fdw

From: Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com>
To: Kohei Kaigai <Kohei(dot)Kaigai(at)EMEA(dot)NEC(dot)COM>
Cc: Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: force_not_null option support for file_fdw
Date: 2011-09-08 05:18:59
Message-ID: 4E685043.4030600@gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
(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

Attachment: force_not_null_v4.patch
Description: text/plain (12.0 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Fujii MasaoDate: 2011-09-08 06:05:48
Subject: Re: postgresql.conf archive_command example
Previous:From: Robert HaasDate: 2011-09-08 01:22:40
Subject: Re: FATAL: lock AccessShareLock on object 0/1260/0 is already held

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group