pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCurious sorting puzzle
3+ messages / 2 participants
[nested] [flat]
* Curious sorting puzzle
@ 2006-06-07 19:26 Ivan Voras <ivoras@fer.hr>
2006-06-07 21:16 ` Re: Curious sorting puzzle Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Ivan Voras @ 2006-06-07 19:26 UTC (permalink / raw)
To: pgsql-performance
The situation is this: we're using a varchar column to store
alphanumeric codes which are by themselves 7-bit clean. But we are
operating under a locale which has its own special collation rules, and
is also utf-8 encoded. Recently we've discovered a serious "d'oh!"-type
bug which we tracked down to the fact that when we sort by this column
the collation respects locale sorting rules, which is messing up other
parts of the application.
The question is: what is the most efficient way to solve this problem
(the required operation is to sort data using binary "collation" - i.e.
compare byte by byte)? Since this field gets queried a lot it must have
an index. Some of the possible solutions we thought of are: replacing
the varchar type with numeric and do magical transcoding (bad, needs
changes thoughout the application) and inserting spaces after every
character (not as bad, but still requires modifying both the application
and the data). An ideal solution would be to have a
"not-locale-affected-varchar" field type :)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Curious sorting puzzle
2006-06-07 19:26 Curious sorting puzzle Ivan Voras <ivoras@fer.hr>
@ 2006-06-07 21:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-07 23:04 ` Re: Curious sorting puzzle Ivan Voras <ivoras@fer.hr>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2006-06-07 21:16 UTC (permalink / raw)
To: Ivan Voras <ivoras@fer.hr>; +Cc: pgsql-performance
Ivan Voras <ivoras@fer.hr> writes:
> The situation is this: we're using a varchar column to store
> alphanumeric codes which are by themselves 7-bit clean. But we are
> operating under a locale which has its own special collation rules, and
> is also utf-8 encoded. Recently we've discovered a serious "d'oh!"-type
> bug which we tracked down to the fact that when we sort by this column
> the collation respects locale sorting rules, which is messing up other
> parts of the application.
> The question is: what is the most efficient way to solve this problem
> (the required operation is to sort data using binary "collation" - i.e.
> compare byte by byte)? Since this field gets queried a lot it must have
> an index. Some of the possible solutions we thought of are: replacing
> the varchar type with numeric and do magical transcoding (bad, needs
> changes thoughout the application) and inserting spaces after every
> character (not as bad, but still requires modifying both the application
> and the data). An ideal solution would be to have a
> "not-locale-affected-varchar" field type :)
If you're just storing ASCII then I think bytea might work for this.
Do you need any actual text operations (like concatenation), or this
just a store-and-retrieve field?
If you need text ops too then probably the best answer is to make your
own datatype. It's not that hard --- look at the citext datatype (on
pgfoundry IIRC, or else gborg) for a closely related example.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Curious sorting puzzle
2006-06-07 19:26 Curious sorting puzzle Ivan Voras <ivoras@fer.hr>
2006-06-07 21:16 ` Re: Curious sorting puzzle Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-06-07 23:04 ` Ivan Voras <ivoras@fer.hr>
0 siblings, 0 replies; 3+ messages in thread
From: Ivan Voras @ 2006-06-07 23:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
>>An ideal solution would be to have a
>>"not-locale-affected-varchar" field type :)
>
>
> If you're just storing ASCII then I think bytea might work for this.
> Do you need any actual text operations (like concatenation), or this
> just a store-and-retrieve field?
I've just tested bytea and it looks like a perfect solution - it supports:
- character-like syntax
- indexes
- uses indexes with LIKE 'x%' queries
- SUBSTRING()
That's good enough for us - it seems it's just what we need - a
string-like type with byte collation.
Thanks!
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-06-07 23:04 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-07 19:26 Curious sorting puzzle Ivan Voras <ivoras@fer.hr>
2006-06-07 21:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-07 23:04 ` Ivan Voras <ivoras@fer.hr>
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