Re: BUG #1931: ILIKE and LIKE fails on Turkish locale

The following bug has been logged online:

Bug reference: 1931
Logged by: Devrim GUNDUZ
Email address: devrim(at)gunduz(dot)org
PostgreSQL version: 8.1beta2
Operating system: Linux (RHEL 4, Debian 3.1)
Description: ILIKE and LIKE fails on Turkish locale
Details:

Hi,

Like the bug report that was submitted a few days ago,

http://archives.postgresql.org/pgsql-bugs/2005-09/msg00233.php

I have the same thing for Turkish locale. First I thought that it was a
glibc error but glibc guys guided me here:

http://sourceware.org/bugzilla/long_list.cgi?buglist=1354

So it is PostgreSQL's bug or Glibc's?

"Devrim GUNDUZ" <devrim(at)gunduz(dot)org> writes:
> http://sourceware.org/bugzilla/long_list.cgi?buglist=1354
> So it is PostgreSQL's bug or Glibc's?

Just offhand, iwchareq() seems several bricks shy of a load:

/*
* if one of them is an ASCII while the other is not, then they must
* be different characters
*/
else if ((unsigned char) *p1 < CHARMAX || (unsigned char) *p2 < CHARMAX)
return (0);

This test is wrong per Jakub's observation. Also, the code right below
that is using tolower() not towlower() on wide characters, which seems
pretty wrong. For that matter, towlower would be wrong too :-( because
there is no certainty that libc's idea of wide characters is the same as
pg_mb2wchar_with_len's.

So yeah, ILIKE looks just about completely broken for multibyte encodings.
Maybe it would be best to pass both strings through lower() and then
do a normal LIKE comparison?

The regexp code doesn't look better, btw, just differently broken ...

regards, tom lane

Hi Tom,

On Sat, 1 Oct 2005, Tom Lane wrote:

<snip>
> So yeah, ILIKE looks just about completely broken for multibyte encodings.
> Maybe it would be best to pass both strings through lower() and then
> do a normal LIKE comparison?
>
> The regexp code doesn't look better, btw, just differently broken ...

So any chance to fix it before beta 3 (and also backpatch?) ?

Regards,
--
Devrim GUNDUZ
Kivi Bilişim Teknolojileri - http://www.kivi.com.tr
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org
>From pgsql-bugs-owner(at)postgresql(dot)org Sun Oct 2 00:14:30 2005
X-Original-To: pgsql-bugs-postgresql(dot)org(at)localhost(dot)postgresql(dot)org
Received: from localhost (av.hub.org [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id 0E04ED8BF7;
Sun, 2 Oct 2005 00:14:21 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 37446-07; Sun, 2 Oct 2005 03:14:19 +0000 (GMT)
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
by svr1.postgresql.org (Postfix) with ESMTP id 59D05D889A;
Sun, 2 Oct 2005 00:14:18 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl(at)localhost [127.0.0.1])
by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j923EDNq029724;
Sat, 1 Oct 2005 23:14:15 -0400 (EDT)
To: Devrim GUNDUZ <devrim(at)gunduz(dot)org>
Cc: pgsql-bugs(at)postgresql(dot)org, pgsql-tr-genel(at)postgresql(dot)org
Subject: Re: BUG #1931: ILIKE and LIKE fails on Turkish locale
In-reply-to: <Pine(dot)LNX(dot)4(dot)63(dot)0510020336270(dot)25019(at)mail(dot)kivi(dot)com(dot)tr>
References: <20051001153536(dot)6C9EBF0B6F(at)svr2(dot)postgresql(dot)org> <9028(dot)1128184301(at)sss(dot)pgh(dot)pa(dot)us> <Pine(dot)LNX(dot)4(dot)63(dot)0510020336270(dot)25019(at)mail(dot)kivi(dot)com(dot)tr>
Comments: In-reply-to Devrim GUNDUZ <devrim(at)gunduz(dot)org>
message dated "Sun, 02 Oct 2005 03:38:01 +0300"
Date: Sat, 01 Oct 2005 23:14:13 -0400
Message-ID: <29723(dot)1128222853(at)sss(dot)pgh(dot)pa(dot)us>
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006]
X-Spam-Level:
X-Archive-Number: 200510/5
X-Sequence-Number: 12986

Devrim GUNDUZ <devrim(at)gunduz(dot)org> writes:
> On Sat, 1 Oct 2005, Tom Lane wrote:
>> So yeah, ILIKE looks just about completely broken for multibyte encodings.
>> The regexp code doesn't look better, btw, just differently broken ...

> So any chance to fix it before beta 3 (and also backpatch?) ?

Are you volunteering?

This stuff has never worked, so I don't feel that it qualifies as "must
fix for 8.1". If you can come up with a believable low-impact patch,
I'd support putting it in now ... but my guess is that this is a
significant bit of work that will not get done in this release cycle.

regards, tom lane

:)))))))))))))))))))))))))))

Selamlar

12 gndr bu problem zerinde alyoruz. Projem iin Zeoslib
kullanyordum. Sorun onda diye zeos u altn stne getirdim. Araya baka
iler girdi ve ara verdim. Siz yaznca benim tekrar aklma geldi. Yaf
PostgreSQL in byle basit bir hataya debileceini hi dnmemitim :(
Neyseki siz buldunuz da telef olmaktan kurtuldum.

yi almalar..

Ali ELK

"Devrim GUNDUZ" <devrim(at)gunduz(dot)org>, haber iletisinde unlar
yazd:Pine(dot)LNX(dot)4(dot)63(dot)0510020336270(dot)25019(at)mail(dot)kivi(dot)com(dot)tr(dot)(dot)(dot)
>
> Hi Tom,
>
> On Sat, 1 Oct 2005, Tom Lane wrote:
>
> <snip>
>> So yeah, ILIKE looks just about completely broken for multibyte
>> encodings.
>> Maybe it would be best to pass both strings through lower() and then
>> do a normal LIKE comparison?
>>
>> The regexp code doesn't look better, btw, just differently broken ...
>
> So any chance to fix it before beta 3 (and also backpatch?) ?
>
> Regards,
> --
> Devrim GUNDUZ
> Kivi Biliim Teknolojileri - http://www.kivi.com.tr
> devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
> http://www.gunduz.org
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

Merhaba,

On Sun, 2 Oct 2005, ALİ ÇELİK wrote:

> 12 gündür bu problem üzerinde çalışıyoruz. Projem için Zeoslib
> kullanıyordum. Sorun onda diye zeos u altını üstüne getirdim. Araya başka
> işler girdi ve ara verdim. Siz yazınca benim tekrar aklıma geldi. Yaf
> PostgreSQL in böyle basit bir hataya düşebileceğini hiç düşünmemiştim :(
> Neyseki siz buldunuz da telef olmaktan kurtuldum.

Nicolai ile bunun üzerinde çalışacağız ama sanırım 8.1'e yetişmeyecek.
8.1.X'e yetişmesini ümit ediyoruz (gerçi 8.2 için de olabilir ama o geç
olur).

İlgi bilgi.
--
Devrim GUNDUZ
Kivi Bilişim Teknolojileri - http://www.kivi.com.tr
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org
>From pgsql-tr-genel-owner(at)postgresql(dot)org Tue Oct 4 18:51:23 2005
X-Original-To: pgsql-tr-genel-postgresql(dot)org(at)localhost(dot)postgresql(dot)org
Received: from localhost (av.hub.org [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id 1B630DA0B3;
Tue, 4 Oct 2005 18:51:20 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 90430-08; Tue, 4 Oct 2005 21:51:14 +0000 (GMT)
Received: from mail.kivi.com.tr (mail.kivi.com.tr [213.143.254.37])
by svr1.postgresql.org (Postfix) with ESMTP id 76C67DA0A9;
Tue, 4 Oct 2005 18:51:15 -0300 (ADT)
Received: by mail.kivi.com.tr (Postfix, from userid 1009)
id 4F366E81D6; Wed, 5 Oct 2005 00:50:49 +0300 (EEST)
Received: from localhost (localhost [127.0.0.1])
by mail.kivi.com.tr (Postfix) with ESMTP id 453D3E81CB;
Wed, 5 Oct 2005 00:50:49 +0300 (EEST)
Date: Wed, 5 Oct 2005 00:50:49 +0300 (EEST)
From: Devrim GUNDUZ <devrim(at)gunduz(dot)org>
X-X-Sender: devrim2(at)mail(dot)kivi(dot)com(dot)tr
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org, pgsql-tr-genel(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #1931: ILIKE and LIKE fails on Turkish locale
In-Reply-To: <29723(dot)1128222853(at)sss(dot)pgh(dot)pa(dot)us>
Message-ID: <Pine(dot)LNX(dot)4(dot)63(dot)0510021152490(dot)25019(at)mail(dot)kivi(dot)com(dot)tr>
References: <20051001153536(dot)6C9EBF0B6F(at)svr2(dot)postgresql(dot)org>
<9028(dot)1128184301(at)sss(dot)pgh(dot)pa(dot)us> <Pine(dot)LNX(dot)4(dot)63(dot)0510020336270(dot)25019(at)mail(dot)kivi(dot)com(dot)tr>
<29723(dot)1128222853(at)sss(dot)pgh(dot)pa(dot)us>
MIME-Version: 1.0
X-Kivi-MailScanner-Information: Please contact the ISP for more information
X-Kivi-MailScanner: Found to be clean
X-Kivi-MailScanner-From: devrim(at)gunduz(dot)org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006]
X-Spam-Level:
Content-Type: TEXT/PLAIN; CHARSET=iso-8859-9; FORMAT=flowed
Content-Transfer-Encoding: 8BIT
Content-ID: <Pine(dot)LNX(dot)4(dot)63(dot)0510050050171(dot)15246(at)mail(dot)kivi(dot)com(dot)tr>
X-Archive-Number: 2005101/9
X-Sequence-Number: 299

Hi,

On Sat, 1 Oct 2005, Tom Lane wrote:

>>> So yeah, ILIKE looks just about completely broken for multibyte encodings.
>>> The regexp code doesn't look better, btw, just differently broken ...
>
>> So any chance to fix it before beta 3 (and also backpatch?) ?
>
> Are you volunteering?
>
> This stuff has never worked, so I don't feel that it qualifies as "must
> fix for 8.1". If you can come up with a believable low-impact patch,
> I'd support putting it in now ... but my guess is that this is a
> significant bit of work that will not get done in this release cycle.

Yeah, I and Nicolai Tufar volunteer to fix it.

Regards,
--
Devrim GUNDUZ
Kivi Bilişim Teknolojileri - http://www.kivi.com.tr
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org
>From pgsql-tr-genel-owner(at)postgresql(dot)org Tue Oct 4 19:00:35 2005
X-Original-To: pgsql-tr-genel-postgresql(dot)org(at)localhost(dot)postgresql(dot)org
Received: from localhost (av.hub.org [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id 2737BDA09D
for <pgsql-tr-genel-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>; Tue, 4 Oct 2005 19:00:33 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 97932-03
for <pgsql-tr-genel-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>;
Tue, 4 Oct 2005 22:00:29 +0000 (GMT)
Received: from mail.kivi.com.tr (mail.kivi.com.tr [213.143.254.37])
by svr1.postgresql.org (Postfix) with ESMTP id 078FADA07E
for <pgsql-tr-genel(at)postgresql(dot)org>; Tue, 4 Oct 2005 19:00:29 -0300 (ADT)
Received: by mail.kivi.com.tr (Postfix, from userid 1009)
id F2998E81D6; Wed, 5 Oct 2005 01:00:02 +0300 (EEST)
Received: from localhost (localhost [127.0.0.1])
by mail.kivi.com.tr (Postfix) with ESMTP id E906EE81CB;
Wed, 5 Oct 2005 01:00:02 +0300 (EEST)
Date: Wed, 5 Oct 2005 01:00:02 +0300 (EEST)
From: Devrim GUNDUZ <devrim(at)gunduz(dot)org>
X-X-Sender: devrim2(at)mail(dot)kivi(dot)com(dot)tr
Reply-To: pgsql-tr-genel(at)postgresql(dot)org
To: =?iso-8859-9?Q?AL=DD_=C7EL=DDK?= <ali(at)verus(dot)com(dot)tr>
Cc: pgsql-tr-genel(at)postgresql(dot)org
Subject: Re: FreeBSD vs Linux
In-Reply-To: <dhoq15$1jat$1(at)news(dot)hub(dot)org>
Message-ID: <Pine(dot)LNX(dot)4(dot)63(dot)0510021739200(dot)25019(at)mail(dot)kivi(dot)com(dot)tr>
References: <dhoq15$1jat$1(at)news(dot)hub(dot)org>
MIME-Version: 1.0
X-Kivi-MailScanner-Information: Please contact the ISP for more information
X-Kivi-MailScanner: Found to be clean
X-Kivi-MailScanner-From: devrim(at)gunduz(dot)org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006]
X-Spam-Level:
Content-Type: TEXT/PLAIN; CHARSET=iso-8859-9; FORMAT=flowed
Content-Transfer-Encoding: 8BIT
Content-ID: <Pine(dot)LNX(dot)4(dot)63(dot)0510050052591(dot)15246(at)mail(dot)kivi(dot)com(dot)tr>
X-Archive-Number: 2005101/10
X-Sequence-Number: 300

Merhaba,

On Sun, 2 Oct 2005, ALİ ÇELİK wrote:

> PostgreSQL in Hangi platformda ne kadar başarım verdiği ile ilgili bir
> istatistik var mı?
>
> Linux,FreeBSD veya Win32 performansları nasıl?

Win32 sürümü henüz yeni sayılır. Her ne kadar içindeki büyük kod parçası
eski olsa da Windows üzerindeki kararlılık için bence biraz daha beklemek
gerekli. (Belki performans ile ilgili değil ama) Unicode vs desteği de 8.0
içinde yok (Windows sürümünde), bu nedenle tercih edilmez diye
düşünmekteyim.

FreeBSD ve Linux'a gelince...

Şimdi benim biri hariç tüm sunucularım Linux. FreeBSD olana yeni kurdum ve
yönetmeye başladım, o yüzden net bir karşılaştırma yapamayacağım.

Ancak, PostgreSQL.org sunucularının yönetiminden edindiğim tecrübeye
bakarsak, ikisi arasında birbirini " ezecek " kadar bir fark yok.

Ben RHEL ve SLES üzerinde deniyorum son zamanlarda. RHEL 3.0 üzerinde
ciddi bir sunucu çalışıyor:

[root(at)web pg_log]# ll
total 1367172
-rw------- 1 postgres postgres 184232379 Oct 1 00:00
postgresql-Fri.log
-rw------- 1 postgres postgres 334944335 Oct 4 00:00
postgresql-Mon.log
-rw------- 1 postgres postgres 193482214 Oct 1 23:59
postgresql-Sat.log
-rw------- 1 postgres postgres 164792772 Oct 2 23:59
postgresql-Sun.log
-rw------- 1 postgres postgres 376297934 Sep 29 23:59
postgresql-Thu.log
-rw------- 1 postgres postgres 143517880 Oct 5 00:00
postgresql-Tue.log
-rw------- 1 postgres postgres 1300505 Oct 5 00:57 postgresql-Wed.log

Sunucunun ürettiği "günlük" kayıtların büyüklüğüne bakarsanız yoğunluğu
tahmin edebilirsiniz. Bu sunucu 1 yıldan beri çalışıyor kesintisiz ve
gayet iyi bir durumda.

SLES üzerinde teknik testler kadar UNICODE testleri de başarılı. %100
bitiremedim testleri ama SLES de iyi bir alternatif olabilir.

Bu arada OSDL'de yapılan testlere de bir bakın. Orada 16-64 işlemcili
makinelerde PostgreSQL deniyorlar.

> adresine yollayabilirsiniz.

Her iletinin altına ekleniyor bu, nasil olduğunu bulursam mutlu olacağım
:(

--
Devrim GUNDUZ
Kivi Bilişim Teknolojileri - http://www.kivi.com.tr
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org
>From pgsql-tr-genel-owner(at)postgresql(dot)org Wed Oct 5 02:19:30 2005
X-Original-To: pgsql-tr-genel-postgresql(dot)org(at)localhost(dot)postgresql(dot)org
Received: from localhost (av.hub.org [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id CF7B8DA197
for <pgsql-tr-genel-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>; Wed, 5 Oct 2005 02:17:54 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 68071-09
for <pgsql-tr-genel-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>;
Wed, 5 Oct 2005 05:17:52 +0000 (GMT)
Received: from mail.kivi.com.tr (mail.kivi.com.tr [213.143.254.37])
by svr1.postgresql.org (Postfix) with ESMTP id 39659DA272
for <pgsql-tr-genel(at)postgresql(dot)org>; Wed, 5 Oct 2005 02:17:50 -0300 (ADT)
Received: by mail.kivi.com.tr (Postfix, from userid 1009)
id BAD3CE81D6; Wed, 5 Oct 2005 08:17:20 +0300 (EEST)
Received: from localhost (localhost [127.0.0.1])
by mail.kivi.com.tr (Postfix) with ESMTP id AE689E81CB;
Wed, 5 Oct 2005 08:17:20 +0300 (EEST)
Date: Wed, 5 Oct 2005 08:17:20 +0300 (EEST)
From: Devrim GUNDUZ <devrim(at)gunduz(dot)org>
X-X-Sender: devrim2(at)mail(dot)kivi(dot)com(dot)tr
To: pgsql-tr-genel(at)PostgreSQL(dot)org
Cc: linux-programlama(at)liste(dot)linux(dot)org(dot)tr
Subject: =?iso-8859-9?Q?Yeni_PostgreSQL_ara_s=FCr=FCmleri_duyuruldu=2E?=
Message-ID: <Pine(dot)LNX(dot)4(dot)63(dot)0510050816410(dot)20542(at)mail(dot)kivi(dot)com(dot)tr>
MIME-Version: 1.0
X-Kivi-MailScanner-Information: Please contact the ISP for more information
X-Kivi-MailScanner: Found to be clean
X-Kivi-MailScanner-From: devrim(at)gunduz(dot)org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006]
X-Spam-Level:
Content-Type: TEXT/PLAIN; charset=iso-8859-9; format=flowed
Content-Transfer-Encoding: 8BIT
X-Archive-Number: 2005101/11
X-Sequence-Number: 301

Merhaba,

Duyuru aşağıda:

---------- Forwarded message ----------
Date: Tue, 4 Oct 2005 20:15:16 -0300 (ADT)
From: Marc G. Fournier <scrappy(at)postgresql(dot)org>
To: pgsql-announce(at)postgresql(dot)org
Cc: pgsql-general(at)postgresql(dot)org
Subject: [ANNOUNCE] New Point Releases Now Available

In order to address several issues identified since our last Point Releases,
we have just released the following new versions of PostgreSQL: 7.3.11, 7.4.9
and 8.0.4.

A few of the more prominent fixes across all three versions are:

- Fix error that allowed "VACUUM" to remove ctid chains too soon
- Fix missing rows in queries like UPDATE a=... WHERE a... with GiST
index on column a
- Improve checking for partially-written WAL pages
- Various memory leakage fixes

For a complete list of fixes in each release, please see the HISTORY file.

Source downloads are available at the following URLs:

http://www.postgresql.org/ftp/source/v7.3.11

http://www.postgresql.org/ftp/source/v7.4.9

http://www.postgresql.org/ftp/source/v8.0.4

Linux/Windows Binaries for 8.0.4 are available via:

http://www.postgresql.org/ftp/binary/v8.0.4

These Point Releases do not require a dump/reload from the previous Point,
but *may* require one if upgrading from an earlier version. Please see the
Release Notes to confirm procedures for upgrading if your current version is
older then 1 Point Release.

Marc G. Fournier
PostgreSQL Core Team

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
>From pgsql-tr-genel-owner(at)postgresql(dot)org Wed Oct 5 17:23:28 2005
X-Original-To: pgsql-tr-genel-postgresql(dot)org(at)localhost(dot)postgresql(dot)org
Received: from localhost (av.hub.org [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id E6910DA26F
for <pgsql-tr-genel-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>; Wed, 5 Oct 2005 17:23:25 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 47443-05
for <pgsql-tr-genel-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>;
Wed, 5 Oct 2005 20:23:18 +0000 (GMT)
Received: from mail.kivi.com.tr (mail.kivi.com.tr [213.143.254.37])
by svr1.postgresql.org (Postfix) with ESMTP id EFB1BDA215
for <pgsql-tr-genel(at)postgresql(dot)org>; Wed, 5 Oct 2005 17:23:15 -0300 (ADT)
Received: by mail.kivi.com.tr (Postfix, from userid 1009)
id 3DF3CE81D6; Wed, 5 Oct 2005 23:22:46 +0300 (EEST)
Received: from localhost (localhost [127.0.0.1])
by mail.kivi.com.tr (Postfix) with ESMTP id 3059AE81CB;
Wed, 5 Oct 2005 23:22:46 +0300 (EEST)
Date: Wed, 5 Oct 2005 23:22:46 +0300 (EEST)
From: Devrim GUNDUZ <devrim(at)gunduz(dot)org>
X-X-Sender: devrim2(at)mail(dot)kivi(dot)com(dot)tr
Reply-To: pgsql-tr-genel(at)PostgreSQL(dot)org
To: pgsql-tr-genel(at)PostgreSQL(dot)org
Cc: linux-programlama(at)liste(dot)linux(dot)org(dot)tr
Subject: =?iso-8859-9?Q?Sun_-_PostgreSQL_yak=FDnla=FEmas=FD?=
Message-ID: <Pine(dot)LNX(dot)4(dot)63(dot)0510052317430(dot)373(at)mail(dot)kivi(dot)com(dot)tr>
MIME-Version: 1.0
X-Kivi-MailScanner-Information: Please contact the ISP for more information
X-Kivi-MailScanner: Found to be clean
X-Kivi-MailScanner-From: devrim(at)gunduz(dot)org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.005 required=5 tests=[AWL=0.005]
X-Spam-Level:
Content-Type: TEXT/PLAIN; CHARSET=iso-8859-9; FORMAT=flowed
Content-Transfer-Encoding: 8BIT
Content-ID: <Pine(dot)LNX(dot)4(dot)63(dot)0510052320301(dot)373(at)mail(dot)kivi(dot)com(dot)tr>
X-Archive-Number: 2005101/12
X-Sequence-Number: 302

Merhaba,

http://people.planetpostgresql.org/xzilla/index.php?/archives/91-Is-Sun-gettin
g-ready-to-join-the-PostgreSQL-community.html

http://www.computerworld.com.au/index.php/id;116679278;fp;16;fpid;0

http://developers.slashdot.org/developers/05/10/05/1344254.shtml?tid=221&tid=218

Saygılar.
--
Devrim GUNDUZ
Kivi Bilişim Teknolojileri - http://www.kivi.com.tr
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org
>From pgsql-tr-genel-owner(at)postgresql(dot)org Thu Oct 6 13:13:00 2005
X-Original-To: pgsql-tr-genel-postgresql(dot)org(at)localhost(dot)postgresql(dot)org
Received: from localhost (av.hub.org [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id F3F8BDA1C4
for <pgsql-tr-genel-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>; Thu, 6 Oct 2005 13:12:56 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 78986-07
for <pgsql-tr-genel-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>;
Thu, 6 Oct 2005 16:12:54 +0000 (GMT)
Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2])
by svr1.postgresql.org (Postfix) with ESMTP id 37A7ADA108
for <pgsql-tr-genel(at)postgresql(dot)org>; Thu, 6 Oct 2005 13:12:49 -0300 (ADT)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
id 9ED9A31059; Thu, 6 Oct 2005 18:12:48 +0200 (MET DST)
From: "ALİ ÇELİK" <ali(at)verus(dot)com(dot)tr>
X-Newsgroups: pgsql.tr.genel
Subject: Tek sorguyla birden fazla veritabanından bilgi çekmek
Date: Thu, 6 Oct 2005 19:12:43 +0300
Organization: Hub.Org Networking Services
Lines: 7
Message-ID: <di3idt$kf5$1(at)news(dot)hub(dot)org>
X-Complaints-To: usenet(at)news(dot)hub(dot)org
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
To: pgsql-tr-genel(at)postgresql(dot)org
X-Virus-Scanned: by amavisd-new at hub.org
X-Amavis-Alert: BAD HEADER Non-encoded 8-bit data (char DD hex) in message header 'From': From: "AL\335 \307EL\335K" <ali(at)ve(dot)(dot)(dot)
X-Spam-Status: No, hits=3.143 required=5 tests=[AWL=-0.896,
PRIORITY_NO_NAME=1.185, SUBJ_ILLEGAL_CHARS=2.854]
X-Spam-Level: ***
X-Archive-Number: 2005101/13
X-Sequence-Number: 303

Olabilir mi?

Hayırlı ramazanlar...

Ali ÇELİK

Hi,

On Sat, 1 Oct 2005, Tom Lane wrote:

>>> So yeah, ILIKE looks just about completely broken for multibyte encodings.
>>> The regexp code doesn't look better, btw, just differently broken ...
>
>> So any chance to fix it before beta 3 (and also backpatch?) ?
>
> Are you volunteering?
>
> This stuff has never worked, so I don't feel that it qualifies as "must
> fix for 8.1". If you can come up with a believable low-impact patch,
> I'd support putting it in now ... but my guess is that this is a
> significant bit of work that will not get done in this release cycle.

Yeah, I and Nicolai Tufar volunteer to fix it.

Regards,
--
Devrim GUNDUZ
Kivi Bilişim Teknolojileri - http://www.kivi.com.tr
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org
>From pgsql-bugs-owner(at)postgresql(dot)org Wed Oct 5 01:03:14 2005
X-Original-To: pgsql-bugs-postgresql(dot)org(at)localhost(dot)postgresql(dot)org
Received: from localhost (av.hub.org [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id A133FD8ED3
for <pgsql-bugs-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>; Wed, 5 Oct 2005 01:03:13 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 06112-04
for <pgsql-bugs-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>;
Wed, 5 Oct 2005 04:03:11 +0000 (GMT)
Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2])
by svr1.postgresql.org (Postfix) with ESMTP id 6452CD7FE7
for <pgsql-bugs(at)postgresql(dot)org>; Wed, 5 Oct 2005 01:03:11 -0300 (ADT)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
id CD3F331059; Wed, 5 Oct 2005 06:03:10 +0200 (MET DST)
From: "Qingqing Zhou" <zhouqq(at)cs(dot)toronto(dot)edu>
X-Newsgroups: pgsql.bugs
Subject: Re: Possibly corrupted shared memory, PostgreSQL 8.1 beta2, Windows 2000
Date: Wed, 5 Oct 2005 00:03:10 -0700
Organization: Hub.Org Networking Services
Lines: 31
Message-ID: <dhvj9t$hel$1(at)news(dot)hub(dot)org>
References: <00b401c5c900$13abb410$6401a8c0(at)JP>
X-Complaints-To: usenet(at)news(dot)hub(dot)org
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Response
To: pgsql-bugs(at)postgresql(dot)org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.19 required=5 tests=[AWL=0.005,
PRIORITY_NO_NAME=1.185]
X-Spam-Level: *
X-Archive-Number: 200510/43
X-Sequence-Number: 13024

""Jean-Pierre Pelletier"" <pelletier_32(at)sympatico(dot)ca> wrote
>
> I've installed PostgreSQL 8.1 beta2 five days ago and it crashed 3 times
> since then.
> Here is what's been logged for the last crash
>
> 2005-10-04 11:00:19 FATAL: could not read block 121 of relation
> 1663/16384/2608: Invalid argument
>
> relation 2608 is pg_depend
> ----------------------------------------------------------------------------------
> The crash before that was on relation pg_type, the first line logged was:
> 2005-10-03 10:51:06 FATAL: could not read block 38 of relation
> 1663/16384/1247: Invalid argument
> ----------------------------------------------------------------------------------
> The first crash was also on relation pg_depend, but with open instead or
> read
> 2005-09-30 18:38:53 FATAL: could not open relation 1663/16384/2608:
> Invalid argument
> ----------------------------------------------------------------------------------
>

This problem was reported several times before, but not necessarily system
tables. Is there any anti-virus softwares installed on the same machine? Is
the database under intensive IO pressure?

Regards,
Qingqing

FreeBSD 5.4 de PostgreSQL 8.0.1 datasn

-E UNICODE --locale=tr_TR.UTF-8

parametresi ile kurdum. Sadece -> i ve I -> ilikileri bozuk. Sanrm
sebebi ingilizce. :)
Harflerin boyu deiince kiiliide deiiyor.

Fakat LATIN5 (ISO 8859-9) ile yapnca, hata tm Trke harfler de oluyordu.

Herkese iyi almalar.

"Devrim GUNDUZ" <devrim(at)gunduz(dot)org>, haber iletisinde unlar
yazd:Pine(dot)LNX(dot)4(dot)63(dot)0510021733050(dot)25019(at)mail(dot)kivi(dot)com(dot)tr(dot)(dot)(dot)
>
> Merhaba,
>
> On Sun, 2 Oct 2005, AL ELK wrote:
>
>> 12 gndr bu problem zerinde alyoruz. Projem iin Zeoslib
>> kullanyordum. Sorun onda diye zeos u altn stne getirdim. Araya baka
>> iler girdi ve ara verdim. Siz yaznca benim tekrar aklma geldi. Yaf
>> PostgreSQL in byle basit bir hataya debileceini hi dnmemitim :(
>> Neyseki siz buldunuz da telef olmaktan kurtuldum.
>
> Nicolai ile bunun zerinde alacaz ama sanrm 8.1'e yetimeyecek.
> 8.1.X'e yetimesini mit ediyoruz (geri 8.2 iin de olabilir ama o ge
> olur).
>
> lgi bilgi.
> --
> Devrim GUNDUZ
> Kivi Biliim Teknolojileri - http://www.kivi.com.tr
> devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
> http://www.gunduz.org
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

This has been saved for the 8.2 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Tom Lane wrote:
> "Devrim GUNDUZ" <devrim(at)gunduz(dot)org> writes:
> > http://sourceware.org/bugzilla/long_list.cgi?buglist=1354
> > So it is PostgreSQL's bug or Glibc's?
>
> Just offhand, iwchareq() seems several bricks shy of a load:
>
> /*
> * if one of them is an ASCII while the other is not, then they must
> * be different characters
> */
> else if ((unsigned char) *p1 < CHARMAX || (unsigned char) *p2 < CHARMAX)
> return (0);
>
> This test is wrong per Jakub's observation. Also, the code right below
> that is using tolower() not towlower() on wide characters, which seems
> pretty wrong. For that matter, towlower would be wrong too :-( because
> there is no certainty that libc's idea of wide characters is the same as
> pg_mb2wchar_with_len's.
>
> So yeah, ILIKE looks just about completely broken for multibyte encodings.
> Maybe it would be best to pass both strings through lower() and then
> do a normal LIKE comparison?
>
> The regexp code doesn't look better, btw, just differently broken ...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Did we make any progress on this? If so, I can't find it.

---------------------------------------------------------------------------

Tom Lane wrote:
> "Devrim GUNDUZ" <devrim(at)gunduz(dot)org> writes:
> > http://sourceware.org/bugzilla/long_list.cgi?buglist=1354
> > So it is PostgreSQL's bug or Glibc's?
>
> Just offhand, iwchareq() seems several bricks shy of a load:
>
> /*
> * if one of them is an ASCII while the other is not, then they must
> * be different characters
> */
> else if ((unsigned char) *p1 < CHARMAX || (unsigned char) *p2 < CHARMAX)
> return (0);
>
> This test is wrong per Jakub's observation. Also, the code right below
> that is using tolower() not towlower() on wide characters, which seems
> pretty wrong. For that matter, towlower would be wrong too :-( because
> there is no certainty that libc's idea of wide characters is the same as
> pg_mb2wchar_with_len's.
>
> So yeah, ILIKE looks just about completely broken for multibyte encodings.
> Maybe it would be best to pass both strings through lower() and then
> do a normal LIKE comparison?
>
> The regexp code doesn't look better, btw, just differently broken ...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom Lane wrote:
>> So yeah, ILIKE looks just about completely broken for multibyte encodings.

> Did we make any progress on this? If so, I can't find it.

No, nobody submitted a patch AFAIR. There should be a TODO item for
this:

* fix ILIKE and regular expressions to handle case insensitivity
properly in multibyte encodings

BTW, while looking to see if TODO had anything about this, I noted
the following items that shouldn't be there anymore --- they are
done and pushed out:

o Add new version of PQescapeString() that doesn't double backslashes
that are part of a client-only multibyte sequence

Single-quote is not a valid byte in any supported client-only
encoding. This requires using mblen() to determine if the
backslash is inside or outside a multi-byte sequence.

o Add new version of PQescapeString() that doesn't double
backslashes when standard_conforming_strings is true and
non-E strings are used

regards, tom lane

Thanks, both done.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Tom Lane wrote:
> >> So yeah, ILIKE looks just about completely broken for multibyte encodings.
>
> > Did we make any progress on this? If so, I can't find it.
>
> No, nobody submitted a patch AFAIR. There should be a TODO item for
> this:
>
> * fix ILIKE and regular expressions to handle case insensitivity
> properly in multibyte encodings
>
> BTW, while looking to see if TODO had anything about this, I noted
> the following items that shouldn't be there anymore --- they are
> done and pushed out:
>
> o Add new version of PQescapeString() that doesn't double backslashes
> that are part of a client-only multibyte sequence
>
> Single-quote is not a valid byte in any supported client-only
> encoding. This requires using mblen() to determine if the
> backslash is inside or outside a multi-byte sequence.
>
> o Add new version of PQescapeString() that doesn't double
> backslashes when standard_conforming_strings is true and
> non-E strings are used
>
>
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Jun 14 05:00, Bruce Momjian wrote:
> Did we make any progress on this? If so, I can't find it.

I've made some tests for upper(), lower(), ILIKE and ~* using cvs tip.
Below are the details:

Cluster Locale | client_encoding | upper() | lower() | ILIKE | ~*
-----------------+-----------------+---------+---------+-------+----
tr_TR.iso8859-9 | LATIN5 | OK | OK | OK | OK
tr_TR.iso8859-9 | UTF8 | OK | OK | OK | OK
tr_TR.UTF-8 | LATIN5 | OK | OK | FAILS | FAILS
tr_TR.UTF-8 | UTF8 | OK | OK | FAILS | FAILS

Also, if you'd wish, I can prepare an ad-hoc regression tests patch
for LATIN5 and UTF-8 support of Turkish characters.

Regards.

Volkan YAZICI <yazicivo(at)ttnet(dot)net(dot)tr> writes:
> Also, if you'd wish, I can prepare an ad-hoc regression tests patch
> for LATIN5 and UTF-8 support of Turkish characters.

We know it's broken. What's needed is a patch.

regards, tom lane

On Jun 15 09:33, Tom Lane wrote:
> Volkan YAZICI <yazicivo(at)ttnet(dot)net(dot)tr> writes:
> > Also, if you'd wish, I can prepare an ad-hoc regression tests patch
> > for LATIN5 and UTF-8 support of Turkish characters.
>
> We know it's broken. What's needed is a patch.

I couldn't understand why you're so aggressive. I'm just trying to help.
And, IMNSHO, posted test results are quite helpful to determine the
exact problem.

As I understand from the tests, ILIKE and ~* don't work properly while
using UTF-8, despite lower() and upper() work without any problem.
Therefore, I've tried to imitate the code of lower() to form a working
iwchareq() method. [Related patch is attached.] It succeded in all of my
previous tests (and plus in regression tests).

As you can see, it's a quite ad-hoc patch. (No win32 support added yet.)
Also, it needs a HAVE_MBTOWC definition too. I just wanted to give it a
V0 speed.

I think, like.c and oracle_compat.c files should be written from
scratch by somebody with more experience. They look like deprecated in
some aspects. (For instance, like.c is still using CHARMAX despite Bruce
generalized it as HIGHBIT in c.h)

Regards.

"Devrim GUNDUZ" <devrim(at)gunduz(dot)org> writes:
> Like the bug report that was submitted a few days ago,
> http://archives.postgresql.org/pgsql-bugs/2005-09/msg00233.php
> I have the same thing for Turkish locale.

Would you confirm that this is fixed by my recent commit?

regards, tom lane

Hello Tom,

On Mon, 2006-09-04 at 14:35 -0400, Tom Lane wrote:
> "Devrim GUNDUZ" <devrim(at)gunduz(dot)org> writes:
> > Like the bug report that was submitted a few days ago,
> > http://archives.postgresql.org/pgsql-bugs/2005-09/msg00233.php
> > I have the same thing for Turkish locale.
>
> Would you confirm that this is fixed by my recent commit?

If I did not miss any test; I can confirm that this bug is fixed for
Turkish locale. I does return the correct results for each query, each
special char.

Thanks Tom.

Regards,
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

On Sep 04 02:35, Tom Lane wrote:
> "Devrim GUNDUZ" <devrim(at)gunduz(dot)org> writes:
> > Like the bug report that was submitted a few days ago,
> > http://archives.postgresql.org/pgsql-bugs/2005-09/msg00233.php
> > I have the same thing for Turkish locale.
>
> Would you confirm that this is fixed by my recent commit?

I've made some tests. [AFAICS, below results are applicable for all
latinN encodings.]

Cluster Locale | client_encoding | upper() | lower() | ILIKE | ~*
-----------------+-----------------+---------+---------+-------+----
tr_TR.iso8859-9 | LATIN5 | OK | OK | OK | OK
tr_TR.iso8859-9 | UTF8 | OK | OK | OK | OK
tr_TR.UTF-8 | LATIN5 | OK | OK | OK | FAILS
tr_TR.UTF-8 | UTF8 | OK | OK | OK | FAILS

Looks like regex code needs same modifications done to ILIKE command.

Regards.

Volkan YAZICI <yazicivo(at)ttnet(dot)net(dot)tr> writes:

> On Sep 04 02:35, Tom Lane wrote:
>> "Devrim GUNDUZ" <devrim(at)gunduz(dot)org> writes:
>> > Like the bug report that was submitted a few days ago,
>> > http://archives.postgresql.org/pgsql-bugs/2005-09/msg00233.php
>> > I have the same thing for Turkish locale.
>>
>> Would you confirm that this is fixed by my recent commit?
>
> I've made some tests. [AFAICS, below results are applicable for all
> latinN encodings.]
>
> Cluster Locale | client_encoding | upper() | lower() | ILIKE | ~*
> -----------------+-----------------+---------+---------+-------+----
> tr_TR.iso8859-9 | LATIN5 | OK | OK | OK | OK
> tr_TR.iso8859-9 | UTF8 | OK | OK | OK | OK
> tr_TR.UTF-8 | LATIN5 | OK | OK | OK | FAILS
> tr_TR.UTF-8 | UTF8 | OK | OK | OK | FAILS
>
> Looks like regex code needs same modifications done to ILIKE command.

I have applied the patch for like.c: r 1.64 -> r 1.65, to my
postgresql 8.1.4 sources, and ILIKE now, obviously, works for
ru_RU.UTF-8 locale.

I still have a couple of issues, though, not sure if anyone reported
them yet.

1) When table or column names are multibyte, they are not displayed by
either psql or pgAdmin. What I see is empty strings instead of
table names. It works with latin table names and with national
table names on single-byte encodings.

2) When I try to create a stored procedure to create table (e.g., for
creating table only if it doesn't already exist), it fails to
compile if i use cyrillic letter "х" (unicode id: 0x0445, utf-8
representation is D1 85) with the following weird error:

ERROR: expected dot between identifiers: х
CONTEXT: compile of PL/pgSQL function "createoraltertable" near line 2

the following query fails:

CREATE OR REPLACE FUNCTION TestFunction()
RETURNS int
AS $$
BEGIN
SELECT х FROM test_table;
RETURN 0;
END;
$$ LANGUAGE plpgsql;

(the only multibyte symbol is that 0x0445 letter, and it's between
select and from).

Simple create table and select, outside of function, works (except the
first issue).

What can I do to help to debug this?
Should I try the sources from CVS HEAD?

--
WBR, Victor V. Snezhko
E-mail: snezhko(at)indorsoft(dot)ru

Victor Snezhko <snezhko(at)indorsoft(dot)ru> writes:

> 2) When I try to create a stored procedure to create table (e.g., for
> creating table only if it doesn't already exist), it fails to
> compile if i use cyrillic letter "х" (unicode id: 0x0445, utf-8
> representation is D1 85) with the following weird error:
>
> ERROR: expected dot between identifiers: х
> CONTEXT: compile of PL/pgSQL function "createoraltertable" near line 2
>
> the following query fails:
>
> CREATE OR REPLACE FUNCTION TestFunction()
> RETURNS int
> AS $$
> BEGIN
> SELECT х FROM test_table;
> RETURN 0;
> END;
> $$ LANGUAGE plpgsql;

Sorry, I was a bit sloppy here: I said about creating table and
specified error output from function createoraltertable(),
which contained CREATE TABLE statement instead of SELECT, but
specified another example function.

Clarification: TestFunction() also fails with the same error about
expected dot between identifiers and can be used for debugging.

--
WBR, Victor V. Snezhko
E-mail: snezhko(at)indorsoft(dot)ru

Volkan YAZICI <yazicivo(at)ttnet(dot)net(dot)tr> writes:
> Looks like regex code needs same modifications done to ILIKE command.

Regex is still broken but I don't think the same fix is feasible -- we
can't blindly downcase the strings outside the regex package (consider
embedded ARE options). And even if we did that, it would fix only one
aspect of regex's locale dependence. The only real fix is to
de-lobotomize the locale-specific functions in regc_locale.c, and then
you run up against the same problem that pg_wchar may or may not use the
same representation that the <wchar.h> functions expect. So I fear
regex will have to stay as it is until we have our own locale support.

regards, tom lane

Victor Snezhko <snezhko(at)indorsoft(dot)ru> writes:

> ERROR: expected dot between identifiers: х
> CONTEXT: compile of PL/pgSQL function "createoraltertable" near line 2
>
> the following query fails:
>
> CREATE OR REPLACE FUNCTION TestFunction()
> RETURNS int
> AS $$
> BEGIN
> SELECT х FROM test_table;
> RETURN 0;
> END;
> $$ LANGUAGE plpgsql;

Hmm, seems like plpgsql is also broken for multibyte encodings. it
contains some single-byte ops, like the following:

while (*s && isspace((unsigned char) *s))
s++;
if (*s++ != '.')
elog(ERROR, "expected dot between identifiers: %s", sstart);

If we state that there are fixes for multibyte encodings in 8.2, we
should note that plpgsql is still broken. Being unfamiliar with
postgresql multibyte internals, I don't see a simple way to fix it
now.

--
WBR, Victor V. Snezhko
E-mail: snezhko(at)indorsoft(dot)ru

Victor Snezhko <snezhko(at)indorsoft(dot)ru> writes:
> Hmm, seems like plpgsql is also broken for multibyte encodings. it
> contains some single-byte ops, like the following:

> while (*s && isspace((unsigned char) *s))
> s++;
> if (*s++ != '.')
> elog(ERROR, "expected dot between identifiers: %s", sstart);

That code is not broken ... at least not unless isspace() is.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

>> Hmm, seems like plpgsql is also broken for multibyte encodings. it
>> contains some single-byte ops, like the following:
>
>> while (*s && isspace((unsigned char) *s))
>> s++;
>> if (*s++ != '.')
>> elog(ERROR, "expected dot between identifiers: %s", sstart);
>
> That code is not broken ... at least not unless isspace() is.

OK, sorry for the FUD, I'm just trying to find a cause... I will not
bring such ungrounded accusations next time.

But... maybe I misunderstand something fundamental, but should isspace
work when we pass one byte of multibyte character?

I'm digging this procedure (plpgsql_convert_ident()) and will report
if I find something wrong inside it and not inside isspace.

--
WBR, Victor V. Snezhko
E-mail: snezhko(at)indorsoft(dot)ru

Victor Snezhko <snezhko(at)indorsoft(dot)ru> writes:
> But... maybe I misunderstand something fundamental, but should isspace
> work when we pass one byte of multibyte character?

Yes, it should, because we only allow ASCII-superset encodings inside
the backend. That is, characters that actually occupy multiple bytes
must have the high bit set in each such byte, and we expect that
isspace, isdigit, etc will not return true for any such byte.

regards, tom lane

Victor Snezhko <snezhko(at)indorsoft(dot)ru> writes:
> 2) When I try to create a stored procedure to create table (e.g., for
> creating table only if it doesn't already exist), it fails to
> compile if i use cyrillic letter "" (unicode id: 0x0445, utf-8
> representation is D1 85) with the following weird error:

> ERROR: expected dot between identifiers:
> CONTEXT: compile of PL/pgSQL function "createoraltertable" near line 2

> the following query fails:

> CREATE OR REPLACE FUNCTION TestFunction()
> RETURNS int
> AS $$
> BEGIN
> SELECT FROM test_table;
> RETURN 0;
> END;
> $$ LANGUAGE plpgsql;

I tried to duplicate this without success on Fedora Core 5. Either your
ru_RU.utf8 locale works differently from Fedora's, or something else is
wrong. Looking at the code, the only very plausible theory is that
isspace() is doing something different than we expect it to. I wonder
whether you have the database encoding set to something else than what
the postmaster's LC_CTYPE locale expects?

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Victor Snezhko <snezhko(at)indorsoft(dot)ru> writes:
>> 2) When I try to create a stored procedure to create table (e.g., for
>> creating table only if it doesn't already exist), it fails to
>> compile if i use cyrillic letter "х" (unicode id: 0x0445, utf-8
>> representation is D1 85) with the following weird error:
>
>> ERROR: expected dot between identifiers: х
>> CONTEXT: compile of PL/pgSQL function "createoraltertable" near line 2
>
>> the following query fails:
>
>> CREATE OR REPLACE FUNCTION TestFunction()
>> RETURNS int
>> AS $$
>> BEGIN
>> SELECT х FROM test_table;
>> RETURN 0;
>> END;
>> $$ LANGUAGE plpgsql;
>
> I tried to duplicate this without success on Fedora Core 5. Either your
> ru_RU.utf8 locale works differently from Fedora's, or something else is
> wrong.
> Looking at the code, the only very plausible theory is that
> isspace() is doing something different than we expect it to. I wonder
> whether you have the database encoding set to something else than what
> the postmaster's LC_CTYPE locale expects?

No, the database encoding is UTF-8, the cluster was built with
ru_RU.UTF-8 locale, and I haven't modified any defaults.

It's on FreeBSD 6.1, and it's quite possible that it has something
wrong with utf-8 locale, I saw some complaints at least about
collation.

However, isspace doesn't seem like a culprit to me. Let's ensure that
I have done everything right to come to this conclusion. I have
applied the following patch to pl_funcs.c:

But, if I'm doing everything right, isspace seems not to be the
culprit to me. I have run the above query on the server with the
following applied patch (the server is 8.1.4, but I don't see any
changes to the plpgsql_convert_ident() in revs 1.46-1.54):

*** src/pl/plpgsql/src/pl_funcs.c.orig Wed Nov 23 00:23:30 2005
--- src/pl/plpgsql/src/pl_funcs.c Fri Sep 22 14:48:09 2006
***************
*** 363,368 ****
--- 363,381 ----
{
const char *sstart = s;
int identctr = 0;
+ char *isspacebuf;
+ int i;
+
+ isspacebuf = (char*)malloc(strlen(s) + 1);
+ for (i=0; s[i]; ++i) {
+ if (isspace(s[i]))
+ isspacebuf[i] = 't';
+ else
+ isspacebuf[i] = 'f';
+ }
+
+ ereport(LOG,
+ (errmsg("plpgsql_convert_ident: ident=%s, isspace array=%s, isspace(0)=%c", s, isspacebuf, isspace(0)?'t':'f')));

/* Outer loop over identifiers */
while (*s)

and got the following line in the logfile:

LOG: plpgsql_convert_ident: ident=х, isspace array=ff, isspace(0)=f

The symbol after "input=" is that 0x0445 (D1 85 in utf-8) letter which
I mentioned above. I also double-checked this: got a stack trace (by
inserting NULL-pointer assignment instead of reporting "expected dot
between identifiers") and saw that the value passed to the
plpgsql_convert_ident points to the following bytes: D1 85 00.

So, isspace returns false on all three of them.

I have also run the same query on another cluster (and the same
binaries), initialized with a single-byte encoding: ru_RU.KOI8-R,
where everything works. isspace returns the same results:

LOG: plpgsql_convert_ident: ident=х, isspace array=f, isspace(0)=f
LOG: plpgsql_convert_ident: ident=test_table, isspace array=ffffffffff, isspace(0)=f

Strange...

--
WBR, Victor V. Snezhko
E-mail: snezhko(at)indorsoft(dot)ru

Victor Snezhko <snezhko(at)indorsoft(dot)ru> writes:

> LOG: plpgsql_convert_ident: ident=х, isspace array=f, isspace(0)=f
> LOG: plpgsql_convert_ident: ident=test_table, isspace array=ffffffffff, isspace(0)=f

Hmm, there was something wrong in my utf-8 cluster setup, I
re-attached it and now see that isspace indeed fails:
LOG: plpgsql_convert_ident: ident=х, isspace array=ft, isspace(0)=f

--
WBR, Victor V. Snezhko
E-mail: snezhko(at)indorsoft(dot)ru

Victor Snezhko <snezhko(at)indorsoft(dot)ru> writes:
> Hmm, there was something wrong in my utf-8 cluster setup, I
> re-attached it and now see that isspace indeed fails:
> LOG: plpgsql_convert_ident: ident=, isspace array=ft, isspace(0)=f

Hm. Given that we've not seen this reported elsewhere, I wonder if we
shouldn't conclude that it's a FreeBSD bug.

We could fix the problem by not using isspace() but some homegrown
equivalent ... but that seems a tad ugly, especially if it's to work
around a problem on just one locale on one platform. Can anyone else
check the result of isspace(0x85) ?

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

>> Hmm, there was something wrong in my utf-8 cluster setup, I
>> re-attached it and now see that isspace indeed fails:
>> LOG: plpgsql_convert_ident: ident=х, isspace array=ft, isspace(0)=f
>
> Hm. Given that we've not seen this reported elsewhere, I wonder if we
> shouldn't conclude that it's a FreeBSD bug.

OK, for 0x85 maybe, but see below.

> We could fix the problem by not using isspace() but some homegrown
> equivalent ... but that seems a tad ugly,

I wonder why simple create table (outside of plpgsql, with the table
name equal to the character D1 85) works? Are multibyte strings
handled differently in query parser compared to plpgsql?

> especially if it's to work around a problem on just one locale on
> one platform. Can anyone else check the result of isspace(0x85) ?

Not sure about one platform, we need to check this. And here are some
news: MacOSX's isspace also returns 1 on 0x85. So, 1.5 platforms.

The test I have run is:

#include <locale.h>
#include <ctype.h>
#include <stdio.h>

int main()
{
setlocale(LC_ALL, "");
printf("%d %d\n", isspace(0x85), isspace(0xA0));
printf("%d %d\n", iswspace(0x85), iswspace(0xA0));
return 0;
}

0xA0 is added because is's true space, see below.

On FreeBSD & Mac this yields the following output:
1 1
1 1

On recent ALTLinux (Compact 3.0), which uses ru_RU.UTF-8 locale by
default:
0 0
0 0

Here is an excerpt from the unicode character database
(http://www.unicode.org/Public/UNIDATA/UnicodeData.txt):

0085;<control>;Cc;0;B;;;;;N;NEXT LINE (NEL);;;;
00A0;NO-BREAK SPACE;Zs;0;CS;<noBreak> 0020;;;;N;NON-BREAKING SPACE;;;;

0x85 is the alternative next-line, and 0xA0 is the real space (note
the mark Zs - http://www.unicode.org/Public/UNIDATA/UCD.html
describes symbols with such marks as "Separator, Space")

So, that linux doesn't return true for 0x85 and 0xA0 characters - but
not because it's is* functions return false for any character above
0x80, but because it's LC_CTYPE doesn't know anything about these
particular characters! (iswspace calls also return false). So linux
has working isspace, but broken iswspace for these characters...

So, could you run one more test on that Fedora to see if at least
isspace() and isalpha() meet out expectations?

#include <locale.h>
#include <ctype.h>
#include <stdio.h>

int main()
{
int i;
setlocale(LC_ALL, "");
for (i=0x80; i <= 0xFF; ++i) {
if (isspace(i) != 0) printf("character 0x%x is a space\n", i);
if (isalpha(i) != 0) printf("character 0x%x is alphabetical\n", i);
if (isdigit(i) != 0) printf("character 0x%x is a digit\n", i);
}
return 0;
}

My FreeBSD lists a whole heck of characters:

character 0x85 is a space
character 0xa0 is a space
character 0xaa is alphabetical
character 0xb5 is alphabetical
character 0xba is alphabetical
character 0xc0 is alphabetical
... 0xc1-0xfe is alphabetical
character 0xff is alphabetical

--
WBR, Victor V. Snezhko
E-mail: snezhko(at)indorsoft(dot)ru

Mac OS X is based on FreeBSD. I'm not sure if this is what you want,
because I'm not in a Turkish locale, but ...

#include <stdio.h>
#include <ctype.h>

int main()
{
printf("isspace(0x85) returned %d\n", isspace(0x85));
}

produces:

isspace(0x85) returned 0

... on my Mac OS X 10.4.7 PPC box.

On Sep 22, 2006, at 12:48 PM, Tom Lane wrote:

> Victor Snezhko <snezhko(at)indorsoft(dot)ru> writes:
>> Hmm, there was something wrong in my utf-8 cluster setup, I
>> re-attached it and now see that isspace indeed fails:
>> LOG: plpgsql_convert_ident: ident=È, isspace array=ft, isspace(0)=f
>
> Hm. Given that we've not seen this reported elsewhere, I wonder if we
> shouldn't conclude that it's a FreeBSD bug.
>
> We could fix the problem by not using isspace() but some homegrown
> equivalent ... but that seems a tad ugly, especially if it's to work
> around a problem on just one locale on one platform. Can anyone else
> check the result of isspace(0x85) ?
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

Douglas Toltzman
doug(at)oakstreetsoftware(dot)com
(910) 526-5938

Victor Snezhko <snezhko(at)indorsoft(dot)ru> writes:
> My FreeBSD lists a whole heck of characters:

> character 0x85 is a space
> character 0xa0 is a space
> character 0xaa is alphabetical
> character 0xb5 is alphabetical
> character 0xba is alphabetical
> character 0xc0 is alphabetical
> ... 0xc1-0xfe is alphabetical
> character 0xff is alphabetical

Hm. I'm still thinking that this behavior is wrong for UTF8 encoding,
but it would be reasonable in LATINn and related encodings, so we
probably ought to do something about it.

After further thought, it's not so much that we can't tolerate
locale-dependent behavior of isspace() in general, as that in this
particular case we are expecting it to match the scanner's idea
of a space: scan.l has

space [ \t\n\r\f]

which obviously is not locale-aware. I think we need convert_ident to
use a plpgsql_isspace() that accepts these and only these as spaces.
Any high-bit-set byte is part of an identifier according to scan.l's
rules, and convert_ident must have the same behavior regardless of locale.

There may be related risks in and around the other flex scanners
... will look.

regards, tom lane

I wrote:
> ... I think we need convert_ident to
> use a plpgsql_isspace() that accepts these and only these as spaces.
> Any high-bit-set byte is part of an identifier according to scan.l's
> rules, and convert_ident must have the same behavior regardless of locale.

> There may be related risks in and around the other flex scanners
> ... will look.

I've committed a fix along these lines. ecpg had the identical bug, but
I couldn't find any other places where we seemed to be assuming that
<ctype.h> macros would match the behavior of our flex scanners.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

>> ... I think we need convert_ident to
>> use a plpgsql_isspace() that accepts these and only these as spaces.
>> Any high-bit-set byte is part of an identifier according to scan.l's
>> rules, and convert_ident must have the same behavior regardless of locale.
>
>> There may be related risks in and around the other flex scanners
>> ... will look.
>
> I've committed a fix along these lines. ecpg had the identical bug, but
> I couldn't find any other places where we seemed to be assuming that
> <ctype.h> macros would match the behavior of our flex scanners.

Thank you, your commit has fixed this issue.

The issue with empty table names (when they are multibyte) remains.

The following queries work as expected:

(I use table named as "т" - Unicode 0442, and column named as "к" -
Unicode id 043A)

CREATE TABLE т (
к int NOT NULL,
PRIMARY KEY (к)
);

INSERT INTO т (к) VALUES (1);

SELECT * FROM т;

However, in system catalogs (SELECT * FROM pg_tables WHERE
schemaname='public') there appears to be empty strings instead
of table names.

This is on patched 8.1.4 (with ILIKE and ctype.h fixes), I'm upgrading
to HEAD now to see if anything improved.

--
WBR, Victor V. Snezhko
E-mail: snezhko(at)indorsoft(dot)ru

Victor Snezhko <snezhko(at)indorsoft(dot)ru> writes:

> However, in system catalogs (SELECT * FROM pg_tables WHERE
> schemaname='public') there appears to be empty strings instead
> of table names.
>
> This is on patched 8.1.4 (with ILIKE and ctype.h fixes), I'm upgrading
> to HEAD now to see if anything improved.

Yes, upgrading to HEAD didn't change anything here.

--
WBR, Victor V. Snezhko
E-mail: snezhko(at)indorsoft(dot)ru

Has this been addressed?

---------------------------------------------------------------------------

Tom Lane wrote:
> Victor Snezhko <snezhko(at)indorsoft(dot)ru> writes:
> > My FreeBSD lists a whole heck of characters:
>
> > character 0x85 is a space
> > character 0xa0 is a space
> > character 0xaa is alphabetical
> > character 0xb5 is alphabetical
> > character 0xba is alphabetical
> > character 0xc0 is alphabetical
> > ... 0xc1-0xfe is alphabetical
> > character 0xff is alphabetical
>
> Hm. I'm still thinking that this behavior is wrong for UTF8 encoding,
> but it would be reasonable in LATINn and related encodings, so we
> probably ought to do something about it.
>
> After further thought, it's not so much that we can't tolerate
> locale-dependent behavior of isspace() in general, as that in this
> particular case we are expecting it to match the scanner's idea
> of a space: scan.l has
>
> space [ \t\n\r\f]
>
> which obviously is not locale-aware. I think we need convert_ident to
> use a plpgsql_isspace() that accepts these and only these as spaces.
> Any high-bit-set byte is part of an identifier according to scan.l's
> rules, and convert_ident must have the same behavior regardless of locale.
>
> There may be related risks in and around the other flex scanners
> ... will look.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Has this been addressed?

Yes, at least as far as we can take it given the current locale
infrastructure. There doesn't seem any reasonable way to get regex
locale-dependent character classes to work for multibyte chars :-(

regards, tom lane