Thread: Is it possible to sort strings in EBCDIC order in PostgreSQL server?

Hello,

It is embarrassing to ask such a thing, but is there any way to sort character column values in EBCDIC order inside the
PostgreSQLserver?  i.e. is it possible to use EBCDIC collation in PostgreSQL, say, by using ICU or something?  We need
torun on a certain mainframe.
 

I see almost no hope from the following, but let me make sure.

https://www.postgresql.org/message-id/flat/28548.1424884373%40sss.pgh.pa.us#28548.1424884373@sss.pgh.pa.us

"Hmm ... EBCDIC ... is that mandatory?  Because there are a pretty large
number of ASCII dependencies in PG, mostly arising from our support of
multibyte character sets, which are all expected to be ASCII supersets.
If there's not a way to run the server process in ASCII-land, you're
likely dead in the water.  I would not want to try to ferret out all
the dependencies."


Regards
Takayuki Tsunakawa

Tsunakawa, Takayuki wrote:
> It is embarrassing to ask such a thing, but is there any way to sort character column values in EBCDIC order inside
thePostgreSQL server?
 
> i.e. is it possible to use EBCDIC collation in PostgreSQL, say, by using ICU or something?  We need to run on a
certainmainframe.
 
> 
> I see almost no hope from the following, but let me make sure.

That should not be too difficult.  PostgreSQL is extensible!

Just define operators that implement <, <=, =, >= and > for EBCDIC,
create an operator class for these (for indexing) and use
ORDER BY ... USING.

Yours,
Laurenz Albe

"Tsunakawa, Takayuki" <tsunakawa.takay@jp.fujitsu.com> writes:
> It is embarrassing to ask such a thing, but is there any way to sort character column values in EBCDIC order inside
thePostgreSQL server?  i.e. is it possible to use EBCDIC collation in PostgreSQL, say, by using ICU or something?  We
needto run on a certain mainframe. 

> I see almost no hope from the following, but let me make sure.
> https://www.postgresql.org/message-id/flat/28548.1424884373%40sss.pgh.pa.us#28548.1424884373@sss.pgh.pa.us

> "Hmm ... EBCDIC ... is that mandatory?  Because there are a pretty large
> number of ASCII dependencies in PG, mostly arising from our support of
> multibyte character sets, which are all expected to be ASCII supersets.
> If there's not a way to run the server process in ASCII-land, you're
> likely dead in the water.  I would not want to try to ferret out all
> the dependencies."

As I recall, that question was about converting the server to use
EBCDIC strings everywhere.  That seems impractical for the reasons
I mentioned.  But you could probably sort according to EBCDIC order
if you were willing to accept some inefficiency: internal to the
comparison function, convert each string to EBCDIC on each call.

Another way you could imagine is to store EBCDIC strings embedded
in bytea values.

            regards, tom lane

On Sun, Dec 10, 2017 at 11:09 PM, Tsunakawa, Takayuki
<tsunakawa.takay@jp.fujitsu.com> wrote:
> It is embarrassing to ask such a thing, but is there any way to sort character column values in EBCDIC order inside
thePostgreSQL server?  i.e. is it possible to use EBCDIC collation in PostgreSQL, say, by using ICU or something?  We
needto run on a certain mainframe. 

There is no need for embarrassment. I think that you mean that you'd
like to get behavior equivalent to the "C" locale with an EBCDIC
encoding. Right? I think that you could use ICU to do this with some
work. ICU collations are not tied to a particular encoding, unlike the
libc collations (they support most but not all collations).

That said, the idea of an "EBCDIC collation" seems limiting. Why
should a system like DB2 for the mainframe (that happens to use EBCDIC
as its encoding) not have a more natural, human-orientated collation
even while using EBCDIC? ISTM that the point of using the "C" locale
(with EBDIC or with UTF-8 or with any other encoding) is to get a
performance benefit where the actual collation's behavior doesn't
matter much to users. Are you sure it's really important to be
*exactly* compatible with EBCDIC order? As long as you're paying for a
custom collation, why not just use a collation that is helpful to
humans?

--
Peter Geoghegan

Hi Laurenz, Tom, Peter,

Thanks for your suggestions.  The practical solution seems to be to override comparison operators of char, varchar and
textdata types with UDFs that behave as Tom mentioned.
 

From: Peter Geoghegan [mailto:pg@bowt.ie]
> That said, the idea of an "EBCDIC collation" seems limiting. Why
> should a system like DB2 for the mainframe (that happens to use EBCDIC
> as its encoding) not have a more natural, human-orientated collation
> even while using EBCDIC? ISTM that the point of using the "C" locale
> (with EBDIC or with UTF-8 or with any other encoding) is to get a
> performance benefit where the actual collation's behavior doesn't
> matter much to users. Are you sure it's really important to be
> *exactly* compatible with EBCDIC order? As long as you're paying for a
> custom collation, why not just use a collation that is helpful to
> humans?

You are right.  I'd like to ask the customer whether and why they need EBCDIC ordering.

Regards
Takayuki Tsunakawa

On Tue, 12 Dec 2017 07:18:10 -0600, John McKown
<john.archie.mckown@gmail.com> wrote:

>?This is a guess on my part, based on many years on an EBCDIC system. But
>I'll bet that they are doing a conversion off of the EBCDIC system (maybe
>Db2 on z/OS) to an ASCII system (Linux or Windows) running PostgreSQL. They
>want to be able to compare the output from the existing system to the
>output on the new system. 

[he asks naively:]

If a port off the EBCDIC system really is the case, wouldn't it be
easier just to write an external compare tool?   Or maybe a PG
function to convert UTF strings as EBCDIC so as to compare string
column dumps?

Either of the above seems more rational than putting a whole lot of
effort into creating an EBCDIC collation.   

[Not that it is terribly hard to create a new collation.  It's more a
matter of "why bother?" when you are trying to move away from that
system in the 1st place.]

YMMV,
George

On Tue, Dec 12, 2017 at 5:18 AM, John McKown
<john.archie.mckown@gmail.com> wrote:
> On Tue, Dec 12, 2017 at 2:17 AM, Tsunakawa, Takayuki
> <tsunakawa.takay@jp.fujitsu.com> wrote:
>>
>> Hi Laurenz, Tom, Peter,
>>
>> Thanks for your suggestions.  The practical solution seems to be to
>> override comparison operators of char, varchar and text data types with UDFs
>> that behave as Tom mentioned.
>>
>> From: Peter Geoghegan [mailto:pg@bowt.ie]
>> > That said, the idea of an "EBCDIC collation" seems limiting. Why
>> > should a system like DB2 for the mainframe (that happens to use EBCDIC
>> > as its encoding) not have a more natural, human-orientated collation
>> > even while using EBCDIC? ISTM that the point of using the "C" locale
>> > (with EBDIC or with UTF-8 or with any other encoding) is to get a
>> > performance benefit where the actual collation's behavior doesn't
>> > matter much to users. Are you sure it's really important to be
>> > *exactly* compatible with EBCDIC order? As long as you're paying for a
>> > custom collation, why not just use a collation that is helpful to
>> > humans?
>>
>> You are right.  I'd like to ask the customer whether and why they need
>> EBCDIC ordering.
>
>
> This is a guess on my part, based on many years on an EBCDIC system. But
> I'll bet that they are doing a conversion off of the EBCDIC system (maybe
> Db2 on z/OS) to an ASCII system (Linux or Windows) running PostgreSQL. They
> want to be able to compare the output from the existing system to the output
> on the new system. EBCDIC orders "lower case", "upper case", then "digits".

ICU supports creating custom collations that reorder upper and lower
case, or digits with scripts (e.g. Latin alphabet characters). See the
documentation -- "23.2.2.3.2. ICU collations". Advanced customization
is possible.

-- 
Peter Geoghegan

On 12/12/17 10:21, Peter Geoghegan wrote:
> ICU supports creating custom collations that reorder upper and lower
> case, or digits with scripts (e.g. Latin alphabet characters). See the
> documentation -- "23.2.2.3.2. ICU collations". Advanced customization
> is possible.

I just gave this a try and I'm not sure I completely understand what
it's doing but it definitely doesn't look like it's sorting according to
EBCDIC byte orders. (This was on centos 7 with libicu-50.1.2) I might
have created the collation incorrectly. I'd love to hear if anyone else
finds a way to get this working outside of custom comparison operators.

pops-10.1 root@db1=# create collation "ebcdic" (provider=icu,
locale='cp037');
CREATE COLLATION

pops-10.1 root@db1=# create table test (data text);
CREATE TABLE

pops-10.1 root@db1=# insert into test
values('a'),('b'),('A'),('B'),('1'),('2'),('!'),('^');
INSERT 0 8

pops-10.1 root@db1=# select * from test order by data collate "ebcdic";
 data
------
 !
 ^
 1
 2
 a
 A
 b
 B
(8 rows)


-- 
http://about.me/jeremy_schneider