pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDistinct oddity
20+ messages / 6 participants
[nested] [flat]
* Distinct oddity
@ 2009-05-07 08:21 Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: Maximilian Tyrtania @ 2009-05-07 08:21 UTC (permalink / raw)
To: pgsql-sql
Hi there,
does this look right?
FAKDB=# select count(distinct(f.land)) from firmen f where
f.typlist='Redaktion';
count
-------
1975
(1 row)
FAKDB=# select count(distinct(f.land||'1')) from firmen f where
f.typlist='Redaktion';
count
-------
4944
(1 row)
FAKDB=# select version();
version
----------------------------------------------------------------------------
----------------------------------------------------------
PostgreSQL 8.3.3 on powerpc-apple-darwin, compiled by GCC
i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370)
(1 row)
Why would the 2 queries give different results? No inserts occurred in the
meantime.
tia,
Maximilian Tyrtania
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
@ 2009-05-07 18:54 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
0 siblings, 1 reply; 20+ messages in thread
From: Scott Marlowe @ 2009-05-07 18:54 UTC (permalink / raw)
To: Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>; +Cc: pgsql-sql
On Thu, May 7, 2009 at 2:21 AM, Maximilian Tyrtania
<maximilian.tyrtania@onlinehome.de> wrote:
> Hi there,
>
> does this look right?
>
> FAKDB=# select count(distinct(f.land)) from firmen f where
> f.typlist='Redaktion';
> count
> -------
> 1975
> (1 row)
>
> FAKDB=# select count(distinct(f.land||'1')) from firmen f where
> f.typlist='Redaktion';
> count
> -------
> 4944
> (1 row)
Yeah, that does seem odd. Could it be something like nulls in your
data set? just guessing really. If you could make a small test case
that shows it happening and allows others to reproduce it you're
likely to get more bites.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
@ 2009-05-08 09:28 ` Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-08 11:35 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 19:52 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 20+ messages in thread
From: Maximilian Tyrtania @ 2009-05-08 09:28 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-sql
am 07.05.2009 20:54 Uhr schrieb Scott Marlowe unter scott.marlowe@gmail.com:
> On Thu, May 7, 2009 at 2:21 AM, Maximilian Tyrtania
> <maximilian.tyrtania@onlinehome.de> wrote:
>> Hi there,
>>
>> does this look right?
>>
>> FAKDB=# select count(distinct(f.land)) from firmen f where
>> f.typlist='Redaktion';
>> count
>> -------
>> 1975
>> (1 row)
>>
>> FAKDB=# select count(distinct(f.land||'1')) from firmen f where
>> f.typlist='Redaktion';
>> count
>> -------
>> 4944
>> (1 row)
>
> Yeah, that does seem odd. Could it be something like nulls in your
> data set? just guessing really. If you could make a small test case
> that shows it happening and allows others to reproduce it you're
> likely to get more bites.
It doesn't seem to be related to null values (which wouldn't explain it
anyway) nor to this particular field...
FAKDB=# select count(*) from firmen where bezeichnung is null;
count
-------
0
(1 row)
FAKDB=# select count(distinct(f.bezeichnung)) from firmen f;
count
-------
72698
(1 row)
FAKDB=# select count(distinct(f.bezeichnung||'e')) from firmen f;
count
-------
72892
(1 row)
My attempts at reproducing this with a freshly created table failed, of
course.
FAKDB=# create table concattest(mytext text);
CREATE TABLE
FAKDB=# insert into concattest (mytext)
select(generate_series(1,10000)::text);
INSERT 0 10000
FAKDB=# insert into concattest (mytext)
select(generate_series(1,10000)::text);
INSERT 0 10000
FAKDB=# select count(distinct(mytext)) from concattest;
count
-------
10000
(1 row)
FAKDB=# select count(distinct(mytext||'2')) from concattest;
count
-------
10000
(1 row)
best,
Maximilian Tyrtania
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
@ 2009-05-08 11:35 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 14:55 ` Re: Distinct oddity Rob Sargent <robsargent@rocketmail.com>
1 sibling, 1 reply; 20+ messages in thread
From: Scott Marlowe @ 2009-05-08 11:35 UTC (permalink / raw)
To: Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>; +Cc: pgsql-sql
On Fri, May 8, 2009 at 3:28 AM, Maximilian Tyrtania
<maximilian.tyrtania@onlinehome.de> wrote:
> am 07.05.2009 20:54 Uhr schrieb Scott Marlowe unter scott.marlowe@gmail.com:
>
>> On Thu, May 7, 2009 at 2:21 AM, Maximilian Tyrtania
>> <maximilian.tyrtania@onlinehome.de> wrote:
>>> Hi there,
>>>
>>> does this look right?
>>>
>>> FAKDB=# select count(distinct(f.land)) from firmen f where
>>> f.typlist='Redaktion';
>>> count
>>> -------
>>> 1975
>>> (1 row)
>>>
>>> FAKDB=# select count(distinct(f.land||'1')) from firmen f where
>>> f.typlist='Redaktion';
>>> count
>>> -------
>>> 4944
>>> (1 row)
>>
>> Yeah, that does seem odd. Could it be something like nulls in your
>> data set? just guessing really. If you could make a small test case
>> that shows it happening and allows others to reproduce it you're
>> likely to get more bites.
>
> It doesn't seem to be related to null values (which wouldn't explain it
> anyway) nor to this particular field...
>
> FAKDB=# select count(*) from firmen where bezeichnung is null;
> count
> -------
> 0
> (1 row)
That's not the same field as in the original query.
> My attempts at reproducing this with a freshly created table failed, of
> course.
Instead of trying to create a test case from scratch, isolate some
rows that cause this, put them in another table, and then pg_dump that
one table, cleaned as needed for privacy, here.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-08 11:35 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
@ 2009-05-08 14:55 ` Rob Sargent <robsargent@rocketmail.com>
0 siblings, 0 replies; 20+ messages in thread
From: Rob Sargent @ 2009-05-08 14:55 UTC (permalink / raw)
To: pgsql-sql
Is firmen a table or a view?
________________________________
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
Cc: pgsql-sql@postgresql.org
Sent: Friday, May 8, 2009 5:35:21 AM
Subject: Re: [SQL] Distinct oddity
On Fri, May 8, 2009 at 3:28 AM, Maximilian Tyrtania
<maximilian.tyrtania@onlinehome.de> wrote:
> am 07.05.2009 20:54 Uhr schrieb Scott Marlowe unter scott.marlowe@gmail.com:
>
>> On Thu, May 7, 2009 at 2:21 AM, Maximilian Tyrtania
>> <maximilian.tyrtania@onlinehome.de> wrote:
>>> Hi there,
>>>
>>> does this look right?
>>>
>>> FAKDB=# select count(distinct(f.land)) from firmen f where
>>> f.typlist='Redaktion';
>>> count
>>> -------
>>> 1975
>>> (1 row)
>>>
>>> FAKDB=# select count(distinct(f.land||'1')) from firmen f where
>>> f.typlist='Redaktion';
>>> count
>>> -------
>>> 4944
>>> (1 row)
>>
>> Yeah, that does seem odd. Could it be something like nulls in your
>> data set? just guessing really. If you could make a small test case
>> that shows it happening and allows others to reproduce it you're
>> likely to get more bites.
>
> It doesn't seem to be related to null values (which wouldn't explain it
> anyway) nor to this particular field...
>
> FAKDB=# select count(*) from firmen where bezeichnung is null;
> count
> -------
> 0
> (1 row)
That's not the same field as in the original query.
> My attempts at reproducing this with a freshly created table failed, of
> course.
Instead of trying to create a test case from scratch, isolate some
rows that cause this, put them in another table, and then pg_dump that
one table, cleaned as needed for privacy, here.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
@ 2009-05-08 19:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-05-09 07:22 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
1 sibling, 1 reply; 20+ messages in thread
From: Tom Lane @ 2009-05-08 19:52 UTC (permalink / raw)
To: Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-sql
Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de> writes:
> am 07.05.2009 20:54 Uhr schrieb Scott Marlowe unter scott.marlowe@gmail.com:
>> Yeah, that does seem odd. Could it be something like nulls in your
>> data set? just guessing really. If you could make a small test case
>> that shows it happening and allows others to reproduce it you're
>> likely to get more bites.
> It doesn't seem to be related to null values (which wouldn't explain it
> anyway) nor to this particular field...
Can you reproduce it in other contexts than specifically count(distinct)?
In particular I'd try
select count(*) from
(select distinct f.bezeichnung from firmen f) ss;
select count(*) from
(select distinct f.bezeichnung||'e' from firmen f) ss;
If those give the same numbers as you're showing here, then the
next step would be to dump out the actual results of the SELECT DISTINCT
queries and compare them --- looking at the actual data values should
give some insight as to what's happening.
BTW, what is the datatype of f.bezeichnung, and what locale are you
running in?
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-08 19:52 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-05-09 07:22 ` Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-09 14:33 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
2009-05-10 02:58 ` Re: Distinct oddity Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 2 replies; 20+ messages in thread
From: Maximilian Tyrtania @ 2009-05-09 07:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
am 08.05.2009 16:55 Uhr schrieb Rob Sargent unter robsargent@rocketmail.com:
>Is firmen a table or a view?
It's a table.
am 08.05.2009 21:52 Uhr schrieb Tom Lane unter tgl@sss.pgh.pa.us:
>> It doesn't seem to be related to null values (which wouldn't explain it
>> anyway) nor to this particular field...
>
> Can you reproduce it in other contexts than specifically count(distinct)?
> In particular I'd try
>
> select count(*) from
> (select distinct f.bezeichnung from firmen f) ss;
FAKDB=# select count(*) from
FAKDB-# (select distinct f.bezeichnung from firmen f) ss;
count
-------
73437
(1 row)
>
> select count(*) from
> (select distinct f.bezeichnung||'e' from firmen f) ss;
FAKDB=# select count(*) from
(select distinct f.bezeichnung||'e' from firmen f) ss;
count
-------
72535
(1 row)
> If those give the same numbers as you're showing here, then the
> next step would be to dump out the actual results of the SELECT DISTINCT
> queries and compare them --- looking at the actual data values should
> give some insight as to what's happening.
FAKDB=# select distinct f.bezeichnung from firmen f order by 1 limit 5
FAKDB-# ;
bezeichnung
-----------------------------------------
Šsterreichisches Verkehrsbro AG
\x01Assistenz
\x10Frohstoff Design & Textilveredelung
"1. Mittelschule ""Am Kupferberg"""
(5 rows)
FAKDB=# select distinct f.bezeichnung||'e' from firmen f order by 1 limit 5
;
?column?
------------------------------------------
Österreich/Welt (Ltg.)e
Šsterreichisches Verkehrsbro AGe
\x01Assistenze
\x10Frohstoff Design & Textilveredelunge
"1. Mittelschule ""Am Kupferberg"""e
(5 rows)
Aha, the "Österreich/Welt (Ltg.)"-entry is missing in the 1st query. So that
does smell like a locale problem.
> BTW, what is the datatype of f.bezeichnung,
It's character varying(255). Just for the record:
FAKDB=# explain analyze select distinct f.bezeichnung||'e' from firmen f
order by 1 limit 5;
QUERY PLAN
----------------------------------------------------------------------------
---------------------------------------------------------
Limit (cost=16173.07..16174.08 rows=5 width=18) (actual
time=1667.841..1667.855 rows=5 loops=1)
-> Unique (cost=16173.07..16933.66 rows=3765 width=18) (actual
time=1667.839..1667.851 rows=5 loops=1)
-> Sort (cost=16173.07..16553.36 rows=152117 width=18) (actual
time=1667.837..1667.844 rows=5 loops=1)
Sort Key: (((bezeichnung)::text || 'e'::text))
Sort Method: external merge Disk: 4640kB
-> Seq Scan on firmen f (cost=0.00..13646.46 rows=152117
width=18) (actual time=0.069..353.777 rows=152118 loops=1)
Total runtime: 1669.998 ms
(7 rows)
> and what locale are you
> running in?
lc_collate | de_DE
| Shows the collation order locale.
lc_ctype | de_DE
| Shows the character classification and case conversion locale.
The encoding is UTF-8.
Best,
Maximilian Tyrtania
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-08 19:52 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
2009-05-09 07:22 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
@ 2009-05-09 14:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-05-09 19:15 ` Re: Distinct oddity Glenn Maynard <glennfmaynard@gmail.com>
2009-05-13 12:37 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
1 sibling, 2 replies; 20+ messages in thread
From: Tom Lane @ 2009-05-09 14:33 UTC (permalink / raw)
To: Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>; +Cc: pgsql-sql
Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de> writes:
> FAKDB=# select distinct f.bezeichnung from firmen f order by 1 limit 5
> FAKDB-# ;
> bezeichnung
> -----------------------------------------
> sterreichisches Verkehrsbro AG
> \x01Assistenz
> \x10Frohstoff Design & Textilveredelung
> "1. Mittelschule ""Am Kupferberg"""
> (5 rows)
> FAKDB=# select distinct f.bezeichnung||'e' from firmen f order by 1 limit 5
> ;
> ?column?
> ------------------------------------------
> Österreich/Welt (Ltg.)e
> sterreichisches Verkehrsbro AGe
> \x01Assistenze
> \x10Frohstoff Design & Textilveredelunge
> "1. Mittelschule ""Am Kupferberg"""e
> (5 rows)
> Aha, the "Österreich/Welt (Ltg.)"-entry is missing in the 1st query. So that
> does smell like a locale problem.
That only proves that adding the 'e' changes the sort order, which is
completely unsurprising for any non-C locale. What you need to do is
dump out the *entire* results of the DISTINCT queries and look for the
unmatched lines. I'd try dumping to two files, stripping the 'e' with
sed, and then sort/diff.
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-08 19:52 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
2009-05-09 07:22 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-09 14:33 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-05-09 19:15 ` Glenn Maynard <glennfmaynard@gmail.com>
1 sibling, 0 replies; 20+ messages in thread
From: Glenn Maynard @ 2009-05-09 19:15 UTC (permalink / raw)
To: pgsql-sql
On Sat, May 9, 2009 at 10:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> That only proves that adding the 'e' changes the sort order, which is
> completely unsurprising for any non-C locale. What you need to do is
> dump out the *entire* results of the DISTINCT queries and look for the
> unmatched lines. I'd try dumping to two files, stripping the 'e' with
> sed, and then sort/diff.
How could adding an "e" change the sorting of "Österreich/Welt (Ltg.)"
compared to "Šsterreichisches Verkehrsb ro AG" in de_DE or en_US (or
any locale)?
It's also odd that the "1. Mittelschule ..." line is getting sorted after those.
--
Glenn Maynard
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-08 19:52 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
2009-05-09 07:22 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-09 14:33 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-05-13 12:37 ` Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-13 18:48 ` Re: Distinct oddity Glenn Maynard <glennfmaynard@gmail.com>
1 sibling, 1 reply; 20+ messages in thread
From: Maximilian Tyrtania @ 2009-05-13 12:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
am 09.05.2009 16:33 Uhr schrieb Tom Lane unter tgl@sss.pgh.pa.us:
> What you need to do is
> dump out the *entire* results of the DISTINCT queries and look for the
> unmatched lines. I'd try dumping to two files, stripping the 'e' with
> sed, and then sort/diff.
Okay, that's what I did, and the results are, well, surprising (for me, mind
you).
FAKDB=# \o /withapp
FAKDB=# select distinct f.bezeichnung||'$' from firmen f order by 1;
FAKDB=# \o /withoutapp
FAKDB=# select distinct f.bezeichnung from firmen f order by 1;
Opened those files (with textwrangler, as I 've never used sed), stripped
off the '$', sorted and looked at the differences (using textwranglers
"compare documents"-feature).
The file "withoutapp" has those lines:
"Abendschau"
"Abendschau"
They don't look distinct to me. The "Abendschau"-Entry appears only once in
"withapp". But then again the "withapp" file has these entries:
Adformatie
Adformatie
which in turn only appear once in "withoutapp". I turned "show invisibles"
on in textwrangler, so I don't think there is some gremlin business going
on.
Actually I'm interested in but in no way dependant on what's going on here,
I just thought as a good pg-citizen I should report whatever might be wrong.
Best,
Maximilian Tyrtania
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-08 19:52 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
2009-05-09 07:22 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-09 14:33 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
2009-05-13 12:37 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
@ 2009-05-13 18:48 ` Glenn Maynard <glennfmaynard@gmail.com>
2009-05-13 19:03 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 20+ messages in thread
From: Glenn Maynard @ 2009-05-13 18:48 UTC (permalink / raw)
To: pgsql-sql
For purposes of DISTINCT, I'd expect any sort order should do; all it
needs is for equal values to be grouped together. If strcoll() ever
fails to do that, I'd call it a critical bug--even throwing total
garbage at it should result in a consistent ordering, even if the
ordering itself is totally meaningless. Many sort functions depend on
this.
On Wed, May 13, 2009 at 8:37 AM, Maximilian Tyrtania
<maximilian.tyrtania@onlinehome.de> wrote:
> Opened those files (with textwrangler, as I 've never used sed), stripped
> off the '$', sorted and looked at the differences (using textwranglers
> "compare documents"-feature).
Can you narrow down what triggers this? Try copying off the table,
and running this:
******
CREATE FUNCTION is_inconsistent() RETURNS BOOLEAN LANGUAGE SQL AS $$
select (select count(distinct(f.bezeichnung)) from firmen_copy f) <>
(select count(distinct(f.bezeichnung||'1')) from firmen_copy f)
$$;
-- If deleting [first,last] leaves the results inconsistent, return
true; otherwise
-- roll back the deletion and return false.
CREATE FUNCTION test_delete_range(first BIGINT, last BIGINT)
RETURNS BOOLEAN LANGUAGE plpgsql AS $$
BEGIN
DELETE FROM firmen_copy WHERE id BETWEEN first AND last;
IF is_inconsistent() THEN RETURN true; END IF;
SELECT 1/0;
EXCEPTION
WHEN division_by_zero THEN
RETURN false;
END;
$$;
CREATE FUNCTION test_func()
RETURNS INTEGER LANGUAGE plpgsql AS $$
DECLARE
total bigint;
BEGIN
IF NOT is_inconsistent() THEN RETURN -1; END IF;
LOOP
total := (SELECT MAX(id) FROM firmen_copy);
IF test_delete_range(0, total/2) THEN CONTINUE; END IF;
IF test_delete_range(total*1/4, total*3/4) THEN
CONTINUE; END IF;
IF test_delete_range(total/2, total) THEN CONTINUE; END IF;
RETURN 0;
END LOOP;
END;
$$;
SELECT test_func();
******
This assumes you have a primary key named "id", and that your IDs
start around 0 and are vaguely monotonic (renumber them in the copy if
necessary). I can't easily test this code, of course, but it's a
simple binary search. Depending on what's triggering this, it may or
may not be able to narrow in on a test case.
Tangentally, is there a better way of rolling back a function than a
dumb hack like "SELECT 1/0"?
--
Glenn Maynard
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-08 19:52 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
2009-05-09 07:22 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-09 14:33 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
2009-05-13 12:37 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-13 18:48 ` Re: Distinct oddity Glenn Maynard <glennfmaynard@gmail.com>
@ 2009-05-13 19:03 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 20+ messages in thread
From: Tom Lane @ 2009-05-13 19:03 UTC (permalink / raw)
To: Glenn Maynard <glennfmaynard@gmail.com>; +Cc: pgsql-sql
Glenn Maynard <glennfmaynard@gmail.com> writes:
> For purposes of DISTINCT, I'd expect any sort order should do; all it
> needs is for equal values to be grouped together. If strcoll() ever
> fails to do that, I'd call it a critical bug--even throwing total
> garbage at it should result in a consistent ordering, even if the
> ordering itself is totally meaningless.
Feel free to complain to the libc authors for your platform --- there
are quite a number of platforms where we know that strcoll does in fact
return garbage (as in inconsistent results) when given garbage input
(which in this case typically means data that's invalid according to
what strcoll thinks the encoding is). That's one reason why we've been
tightening up the encoding-validity enforcement in recent PG releases.
It's not entirely clear to me how things are going wrong in Maximilian's
particular example, but I suspect that the root of it is a problem of
this type.
> Tangentally, is there a better way of rolling back a function than a
> dumb hack like "SELECT 1/0"?
RAISE ERROR?
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-08 19:52 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
2009-05-09 07:22 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
@ 2009-05-10 02:58 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-05-11 06:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
1 sibling, 1 reply; 20+ messages in thread
From: Alvaro Herrera @ 2009-05-10 02:58 UTC (permalink / raw)
To: Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
Maximilian Tyrtania wrote:
> am 08.05.2009 16:55 Uhr schrieb Rob Sargent unter robsargent@rocketmail.com:
> > and what locale are you running in?
>
> lc_collate | de_DE
> | Shows the collation order locale.
> lc_ctype | de_DE
> | Shows the character classification and case conversion locale.
>
> The encoding is UTF-8.
Note that the de_DE locale uses Latin9 encoding, which is incompatible
with UTF8. I'd try checking if the problem is reproducible in
de_DE.utf8 (you need to create a new database for testing, obviously).
If it's not, then the incompatible locale definition is causing the
problem.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-08 19:52 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
2009-05-09 07:22 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-10 02:58 ` Re: Distinct oddity Alvaro Herrera <alvherre@commandprompt.com>
@ 2009-05-11 06:28 ` Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-11 14:38 ` Re: Distinct oddity Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 20+ messages in thread
From: Maximilian Tyrtania @ 2009-05-11 06:28 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-sql
am 10.05.2009 4:58 Uhr schrieb Alvaro Herrera unter
alvherre@commandprompt.com:
>>> and what locale are you running in?
>>
>> lc_collate | de_DE
>> | Shows the collation order locale.
>> lc_ctype | de_DE
>> | Shows the character classification and case conversion locale.
>>
>> The encoding is UTF-8.
>
> Note that the de_DE locale uses Latin9 encoding, which is incompatible
> with UTF8.
Ah, good catch.
> I'd try checking if the problem is reproducible in
> de_DE.utf8 (you need to create a new database for testing, obviously).
Wait a minute. I need to re- initdb with de_DE.UTF-8, don't I?
> If it's not, then the incompatible locale definition is causing the
> problem.
I'll try that, thanks.
M
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-08 19:52 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
2009-05-09 07:22 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-10 02:58 ` Re: Distinct oddity Alvaro Herrera <alvherre@commandprompt.com>
2009-05-11 06:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
@ 2009-05-11 14:38 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-05-12 16:39 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
0 siblings, 1 reply; 20+ messages in thread
From: Alvaro Herrera @ 2009-05-11 14:38 UTC (permalink / raw)
To: Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>; +Cc: pgsql-sql
Maximilian Tyrtania wrote:
> am 10.05.2009 4:58 Uhr schrieb Alvaro Herrera unter
> alvherre@commandprompt.com:
> > I'd try checking if the problem is reproducible in
> > de_DE.utf8 (you need to create a new database for testing, obviously).
>
> Wait a minute. I need to re- initdb with de_DE.UTF-8, don't I?
Well, either that, or create a new database with Latin9 encoding.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-08 19:52 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
2009-05-09 07:22 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-10 02:58 ` Re: Distinct oddity Alvaro Herrera <alvherre@commandprompt.com>
2009-05-11 06:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-11 14:38 ` Re: Distinct oddity Alvaro Herrera <alvherre@commandprompt.com>
@ 2009-05-12 16:39 ` Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-12 17:23 ` Re: Distinct oddity Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 20+ messages in thread
From: Maximilian Tyrtania @ 2009-05-12 16:39 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-sql
am 11.05.2009 16:38 Uhr schrieb Alvaro Herrera unter
alvherre@commandprompt.com:
>>>Note that the de_DE locale uses Latin9 encoding, which is incompatible
>>>with UTF8.
>>> I'd try checking if the problem is reproducible in
>>> de_DE.utf8 (you need to create a new database for testing, obviously).
>>
>> Wait a minute. I need to re- initdb with de_DE.UTF-8, don't I?
>
> Well, either that, or create a new database with Latin9 encoding.
FAKDB=# CREATE DATABASE "TestLatin9"
FAKDB-# WITH ENCODING='LATIN9'
FAKDB-# OWNER=postgres;
ERROR: encoding LATIN9 does not match server's locale de_DE
DETAIL: The server's LC_CTYPE setting requires encoding UTF8.
FAKDB=#
Now i'm deeply confused...So do i have to re-initdb?
Best,
Maximilian Tyrtania
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-08 19:52 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
2009-05-09 07:22 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-10 02:58 ` Re: Distinct oddity Alvaro Herrera <alvherre@commandprompt.com>
2009-05-11 06:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-11 14:38 ` Re: Distinct oddity Alvaro Herrera <alvherre@commandprompt.com>
2009-05-12 16:39 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
@ 2009-05-12 17:23 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-05-12 18:29 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
0 siblings, 1 reply; 20+ messages in thread
From: Alvaro Herrera @ 2009-05-12 17:23 UTC (permalink / raw)
To: Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>; +Cc: pgsql-sql
Maximilian Tyrtania wrote:
> am 11.05.2009 16:38 Uhr schrieb Alvaro Herrera unter
> alvherre@commandprompt.com:
>
> >>>Note that the de_DE locale uses Latin9 encoding, which is incompatible
> >>>with UTF8.
> >>> I'd try checking if the problem is reproducible in
> >>> de_DE.utf8 (you need to create a new database for testing, obviously).
> >>
> >> Wait a minute. I need to re- initdb with de_DE.UTF-8, don't I?
> >
> > Well, either that, or create a new database with Latin9 encoding.
>
> FAKDB=# CREATE DATABASE "TestLatin9"
> FAKDB-# WITH ENCODING='LATIN9'
> FAKDB-# OWNER=postgres;
> ERROR: encoding LATIN9 does not match server's locale de_DE
> DETAIL: The server's LC_CTYPE setting requires encoding UTF8.
> FAKDB=#
>
> Now i'm deeply confused...So do i have to re-initdb?
Hmm, I didn't expect this. I guess I assumed de_DE was an alias for
the Latin1- or Latin9- encoded locale, but it seems your system uses it
as an alias for the UTF-8 encoded one. So my initial comment seems to
be wrong as well. Please paste the output of the "locale" command.
What platform are you using anyway?
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-08 19:52 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
2009-05-09 07:22 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-10 02:58 ` Re: Distinct oddity Alvaro Herrera <alvherre@commandprompt.com>
2009-05-11 06:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-11 14:38 ` Re: Distinct oddity Alvaro Herrera <alvherre@commandprompt.com>
2009-05-12 16:39 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-12 17:23 ` Re: Distinct oddity Alvaro Herrera <alvherre@commandprompt.com>
@ 2009-05-12 18:29 ` Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-13 14:47 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 20+ messages in thread
From: Maximilian Tyrtania @ 2009-05-12 18:29 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-sql
am 12.05.2009 19:23 Uhr schrieb Alvaro Herrera unter
alvherre@commandprompt.com:
>> FAKDB=# CREATE DATABASE "TestLatin9"
>> FAKDB-# WITH ENCODING='LATIN9'
>> FAKDB-# OWNER=postgres;
>> ERROR: encoding LATIN9 does not match server's locale de_DE
>> DETAIL: The server's LC_CTYPE setting requires encoding UTF8.
>> FAKDB=#
>>
>> Now i'm deeply confused...So do i have to re-initdb?
>
> Hmm, I didn't expect this. I guess I assumed de_DE was an alias for
> the Latin1- or Latin9- encoded locale, but it seems your system uses it
> as an alias for the UTF-8 encoded one. So my initial comment seems to
> be wrong as well. Please paste the output of the "locale" command.
Sputnik-Server:~ administrator$ locale
LANG=
LC_COLLATE="C"
LC_CTYPE="C"
LC_MESSAGES="C"
LC_MONETARY="C"
LC_NUMERIC="C"
LC_TIME="C"
LC_ALL="C"
> What platform are you using anyway?
Mac OS 10.4.11
Best,
Maximilian Tyrtania
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-08 19:52 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
2009-05-09 07:22 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-10 02:58 ` Re: Distinct oddity Alvaro Herrera <alvherre@commandprompt.com>
2009-05-11 06:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-11 14:38 ` Re: Distinct oddity Alvaro Herrera <alvherre@commandprompt.com>
2009-05-12 16:39 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-12 17:23 ` Re: Distinct oddity Alvaro Herrera <alvherre@commandprompt.com>
2009-05-12 18:29 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
@ 2009-05-13 14:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-05-13 15:47 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 20+ messages in thread
From: Tom Lane @ 2009-05-13 14:47 UTC (permalink / raw)
To: Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; pgsql-sql
Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de> writes:
> am 12.05.2009 19:23 Uhr schrieb Alvaro Herrera unter
> alvherre@commandprompt.com:
>> What platform are you using anyway?
> Mac OS 10.4.11
I have some vague recollection that UTF8-using locales don't actually
work well on OSX ... check the archives ...
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Distinct oddity
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Re: Distinct oddity Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-08 19:52 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
2009-05-09 07:22 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-10 02:58 ` Re: Distinct oddity Alvaro Herrera <alvherre@commandprompt.com>
2009-05-11 06:28 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-11 14:38 ` Re: Distinct oddity Alvaro Herrera <alvherre@commandprompt.com>
2009-05-12 16:39 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-12 17:23 ` Re: Distinct oddity Alvaro Herrera <alvherre@commandprompt.com>
2009-05-12 18:29 ` Re: Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-13 14:47 ` Re: Distinct oddity Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-05-13 15:47 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 20+ messages in thread
From: Tom Lane @ 2009-05-13 15:47 UTC (permalink / raw)
To: Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-sql
I wrote:
> Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de> writes:
>> am 12.05.2009 19:23 Uhr schrieb Alvaro Herrera unter
>> alvherre@commandprompt.com:
>>> What platform are you using anyway?
>> Mac OS 10.4.11
> I have some vague recollection that UTF8-using locales don't actually
> work well on OSX ... check the archives ...
OK, the thread (or one of the threads) I was remembering is here:
http://archives.postgresql.org//pgsql-general/2005-11/msg00047.php
I am too lazy to boot up 10.4 right now, but looking on a 10.5.6 machine
indicates that Apple is still being pretty lame about this:
$ ls -l /usr/share/locale/de_DE
total 40
lrwxr-xr-x 1 root wheel 28 Feb 27 2008 LC_COLLATE -> ../la_LN.US-ASCII/LC_COLLATE
lrwxr-xr-x 1 root wheel 17 Feb 27 2008 LC_CTYPE -> ../UTF-8/LC_CTYPE
drwxr-xr-x 3 root wheel 102 Feb 27 2008 LC_MESSAGES
lrwxr-xr-x 1 root wheel 30 Feb 27 2008 LC_MONETARY -> ../de_DE.ISO8859-1/LC_MONETARY
lrwxr-xr-x 1 root wheel 29 Feb 27 2008 LC_NUMERIC -> ../de_DE.ISO8859-1/LC_NUMERIC
-r--r--r-- 1 root wheel 370 Jan 2 2008 LC_TIME
So it looks like they understand UTF-8 to the extent of supporting
character classification fairly well, but sort order is "just ASCII".
I'm not sure exactly how that might result in the observed odd behavior
of DISTINCT, but I bet it's causing it somehow. You'd probably have
better luck in the de_DE.ISO8859-1 or de_DE.ISO8859-15 locales.
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2009-05-13 19:03 UTC | newest]
Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-05-07 08:21 Distinct oddity Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-07 18:54 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 09:28 ` Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-08 11:35 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-05-08 14:55 ` Rob Sargent <robsargent@rocketmail.com>
2009-05-08 19:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-05-09 07:22 ` Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-09 14:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-05-09 19:15 ` Glenn Maynard <glennfmaynard@gmail.com>
2009-05-13 12:37 ` Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-13 18:48 ` Glenn Maynard <glennfmaynard@gmail.com>
2009-05-13 19:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-05-10 02:58 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-05-11 06:28 ` Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-11 14:38 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-05-12 16:39 ` Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-12 17:23 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-05-12 18:29 ` Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
2009-05-13 14:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-05-13 15:47 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox