Hi guys of the pgsql-hackers list.

I've received a bug report on the OLE DB list, which I suspect is
actually a server bug. The correspondence so far is listed further on,
but, in a nutshell, user runs an OLE DB client on windows (OLE DB uses
the binary interface), and server version 8.1.9 on Windows, and all is
fine. When the server is switched to 8.1.8 on Debian Etch on ARM9,
float8 type is not received properly by OLE DB.

Since OLE DB doesn't really care what version the server is running, the
chances of this being a server side bug are really high. I don't know
ARM9 well enough to comment on floating point format there.

Julian Heeb wrote:
> Shachar Shemesh schrieb:
>
>> Julian Heeb wrote:
>>
>>
>>> Hello
>>>
>>> Our acounting software can use the PostgreSQL OLE DB driver to access
>>> a postgreSQL database. With the pg server installed on windows,
>>> everything works fine.
>>>
>>> I moved now the database to a postgreSQL server on a linux server, but
>>> now every floating point number gets wrongly interpreted by the
>>> acounting software, either by replacing it with a 0 or a very large
>>> number (e.g. xxxE+308). Only the floating point numbers are affected,
>>> integer or characters are correct. pgAdmin shows even the fp numbers
>>> correctly, so I guess it has something to do with the pgoledb driver.
>>>
>>> Can someone give me a hint, how to solve the problem?
>>>
>>>
>> It's hard to give a precise answer. Let's try a couple of venues.
>>
>> First of all, what platform is the Linux server? Is that an Intel, or
>> something else?
>>
>>
> It is an ARM9 platform with Debian Etch (Linkstation Pro Fileserver with
> Freelink).
>
>> Also, what is the precise type of the floating point var on the server?
>> Can you give the SQL line that generated the table?
>>
>>
> The table has been generated by the following SQL line. The problem
> occures at the double precision fields.
>
I have some bad news. This is the comment in the Postgresql source code.
This seems to be a core problem at the server side of things:
> /* --------------------------------
> * pq_sendfloat8 - append a float8 to a StringInfo buffer
> *
> * The point of this routine is to localize knowledge of the external
> binary
> * representation of float8, which is a component of several datatypes.
> *
> * We currently assume that float8 should be byte-swapped in the same way
> * as int8. This rule is not perfect but it gives us portability across
> * most IEEE-float-using architectures.
> * --------------------------------
> */
Could it be that ARM9 is not IEEE float standard? Can anyone from the
"hackers" list give any insight into this? The function for the data
type import on the client side seems to be in order (switch the byte
order around, and assume it's a valid "double" C type).

Shachar

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Shachar Shemesh wrote:
> Hi guys of the pgsql-hackers list.
>
> I've received a bug report on the OLE DB list, which I suspect is
> actually a server bug. The correspondence so far is listed further on,
> but, in a nutshell, user runs an OLE DB client on windows (OLE DB uses
> the binary interface), and server version 8.1.9 on Windows, and all is
> fine. When the server is switched to 8.1.8 on Debian Etch on ARM9,
> float8 type is not received properly by OLE DB.

if 8.1.8 is built based on the debian packages it is probably compiled
with --enable-integer-datetimes.
Is the oledb client library able to cope with that ?

Stefan

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Shachar Shemesh <shachar@shemesh.biz> writes:
> I've received a bug report on the OLE DB list, which I suspect is
> actually a server bug. The correspondence so far is listed further on,
> but, in a nutshell, user runs an OLE DB client on windows (OLE DB uses
> the binary interface), and server version 8.1.9 on Windows, and all is
> fine. When the server is switched to 8.1.8 on Debian Etch on ARM9,
> float8 type is not received properly by OLE DB.

Perhaps OLE is trying to use binary instead of text transmission of
data? It's not a server bug if ARM has a different FP format than
the client hardware; it's the client's responsibility to either use
text format or be prepared to cope with whatever the binary format is.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Shachar Shemesh <shachar@shemesh.biz> writes:
> I'll reiterate - the problem is not that PG is exporting the internal
> ARM FP format. The problem is that the server is exporting the internal
> ARM FP format when the server is ARM, and the IEEE format when the
> server is Intel. It's not the format, it's the inconsistency.

This is not a bug, it's intentional. While IEEE-spec floats are
reasonably interchangeable these days (modulo the endianness issue),
other FP formats tend to have different ranges, special cases, etc.
If we try to force them to IEEE spec we may have problems with overflow,
loss of precision, who knows what.

> Like I said elsewhere, I'm willing to write a patch to "pq_sendfloat8"
> (and probably "pq_getmsgfloat8" too) to make sure it does the conversion
> on ARM platforms. Hell, I think I can even write it portable enough so
> that it will work on all non-IEEE platforms

Really? Will it be faster and more reliable than conversion to text?
(In this context "reliable" means "can reproduce the original datum
exactly when transmitted back".)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Stefan Kaltenbrunner wrote:
> Shachar Shemesh wrote:
>
>> Hi guys of the pgsql-hackers list.
>>
>> I've received a bug report on the OLE DB list, which I suspect is
>> actually a server bug. The correspondence so far is listed further on,
>> but, in a nutshell, user runs an OLE DB client on windows (OLE DB uses
>> the binary interface), and server version 8.1.9 on Windows, and all is
>> fine. When the server is switched to 8.1.8 on Debian Etch on ARM9,
>> float8 type is not received properly by OLE DB.
>>
>
> if 8.1.8 is built based on the debian packages it is probably compiled
> with --enable-integer-datetimes.
> Is the oledb client library able to cope with that ?
>
I'm calling "GetPgStatus(&stat, _bstr_t("integer_datetimes") );" to find
out what the server representation is, and cope accordingly. This only
doesn't work on 7.4 servers, but otherwise I'm fine.

either way, this is off topic to this problem.

From further reading, it seems that ARM indeed uses its own
representation for IEEE floats. I'll try to poll my sources, try and
find out what the %(!@#&$ this format actually is (google was no help),
and try and formulate a patch for PG to export it in IEEE despite the
discrepancies.
> Stefan
>
Shachar

---------------------------(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

Tom Lane wrote:
> Shachar Shemesh <shachar@shemesh.biz> writes:
>
>> I've received a bug report on the OLE DB list, which I suspect is
>> actually a server bug. The correspondence so far is listed further on,
>> but, in a nutshell, user runs an OLE DB client on windows (OLE DB uses
>> the binary interface), and server version 8.1.9 on Windows, and all is
>> fine. When the server is switched to 8.1.8 on Debian Etch on ARM9,
>> float8 type is not received properly by OLE DB.
>>
>
> Perhaps OLE is trying to use binary instead of text transmission of
> data?
Of course it does. That's what the OLE DB specs say. Said so in my
original email.
> It's not a server bug if ARM has a different FP format than
> the client hardware;
No. The server can use, internally, whatever it wants.
> it's the client's responsibility to either use
> text format or be prepared to cope with whatever the binary format is.
>
I agree 100%.

I'll reiterate - the problem is not that PG is exporting the internal
ARM FP format. The problem is that the server is exporting the internal
ARM FP format when the server is ARM, and the IEEE format when the
server is Intel. It's not the format, it's the inconsistency.

I can (and I do) handle, in PgOleDb, binary format that are vastly
different than those that I need (anyone said "timestamps"?). Handling a
format that is inconsistent across same version backends merely because
of platform, now that's a server bug if I ever saw one.
> regards, tom lane
>
Like I said elsewhere, I'm willing to write a patch to "pq_sendfloat8"
(and probably "pq_getmsgfloat8" too) to make sure it does the conversion
on ARM platforms. Hell, I think I can even write it portable enough so
that it will work on all non-IEEE platforms (I'm not sure yet, but I
have a general idea). What I'm hoping for, however, is that if I do, you
(or another comitter) will get it in.

Shachar


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane wrote:
> Shachar Shemesh <shachar@shemesh.biz> writes:
>
>> I'll reiterate - the problem is not that PG is exporting the internal
>> ARM FP format. The problem is that the server is exporting the internal
>> ARM FP format when the server is ARM, and the IEEE format when the
>> server is Intel. It's not the format, it's the inconsistency.
>>
>
> This is not a bug, it's intentional. While IEEE-spec floats are
> reasonably interchangeable these days (modulo the endianness issue),
> other FP formats tend to have different ranges, special cases, etc.
> If we try to force them to IEEE spec we may have problems with overflow,
> loss of precision, who knows what.
>
Yes, but if we do not then we have a data interchange library that is
useless for data interchange. I think overflow and precision loss is
preferable.

Please remember that I'm only trying to help Postgresql here. I have a
spec to work with on the outside. I'm more than willing to do what's
necessary (see the STRRRANGE date conversion code) in order to adapt
whatever PG throws my way to the no less strange representation expected
of me. That's what I do as a driver hacker.

Sometimes, the specs don't help me. Windows' notion of "timezone free
timestamps" is nothing short of a disgrace, and some of the hacks that
are needed around that issues are, well, hacks. I don't come complaining
here, because this has nothing to do with PG. It's bad design on the
other end of the two ends that a driver has to make meet.

But sometimes, like now, PG puts me in an impossible position. You are
essentially telling me "you will get the numbers in an unknown format,
you will not have any way of knowing whether you got them in a strange
format or not, nor will you have any docs on what that format is going
to be". That is no way to treat your driver developers.
>
>> Like I said elsewhere, I'm willing to write a patch to "pq_sendfloat8"
>> (and probably "pq_getmsgfloat8" too) to make sure it does the conversion
>> on ARM platforms. Hell, I think I can even write it portable enough so
>> that it will work on all non-IEEE platforms
>>
>
> Really? Will it be faster
Absolutely. Do you honestly believe that turning a 64bit binary number
into a 40 something byte decimal number will be quicker than turning a
64 bit binary number into another 64 bit number? For one thing, I really
doubt that my technique will require division, modulo or, in fact, any
math operations at all. It will likely be done with a few bit shifting
and that's it.

I also find it strange, though, that you berate me for using binary
rather than text format, and then complain about speed. That's what
makes OLE DB faster than ODBC - binary interface.
> and more reliable than conversion to text?
>
Well, depends on how you define "more reliable". If you define it to
mean "exactly represents what happens in the server internals", then the
answer is "no". If you define it to mean "make more sense to the client,
and have better chances of producing results that more closely
approximate the right number than the current code", then the answer is
a definite yes.
> (In this context "reliable" means "can reproduce the original datum
> exactly when transmitted back".)
>
Who cares? If you are using the same function for binary communication
inside the server and for communications to the clients (or, for that
matter, another server), then there is something wrong in your design.
What are the "send" functions used for, beside server to client
communication, anyways? You are asking me to treat the binary data as an
opaque. Well, I'll counter with a question - what good is that to me?

Please note that the current code is useless for communicating binary
data between two servers, even if they are guaranteed to be of the same
version! How much less reliable can you get?

Please, give your own interface designers something to work with. Your
attitude essentially leaves me out in the cold.
> regards, tom lane
>
Shachar

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Shachar Shemesh wrote:
> Tom Lane wrote:
>> Shachar Shemesh <shachar@shemesh.biz> writes:
>>
>>> I'll reiterate - the problem is not that PG is exporting the internal
>>> ARM FP format. The problem is that the server is exporting the internal
>>> ARM FP format when the server is ARM, and the IEEE format when the
>>> server is Intel. It's not the format, it's the inconsistency.
>>>
>> This is not a bug, it's intentional. While IEEE-spec floats are
>> reasonably interchangeable these days (modulo the endianness issue),
>> other FP formats tend to have different ranges, special cases, etc.
>> If we try to force them to IEEE spec we may have problems with overflow,
>> loss of precision, who knows what.
>>
> Yes, but if we do not then we have a data interchange library that is
> useless for data interchange. I think overflow and precision loss is
> preferable.

I agree in principle that the wire protocol should be
platform-independent. At the very least, if the server always sends in
native format, the client needs to know which format it's receiving and
be able to convert between all formats. Switching to a common format on
all platforms would of course make life easier for client library
developers.

> But sometimes, like now, PG puts me in an impossible position. You are
> essentially telling me "you will get the numbers in an unknown format,
> you will not have any way of knowing whether you got them in a strange
> format or not, nor will you have any docs on what that format is going
> to be". That is no way to treat your driver developers.

You seem to be ignoring the fact that the text format is
platform-independent. That's what for example JDBC uses, and I'd imagine
other interfaces as well. Is it not possible to use text format in OLE
DB, for floating points?

>> (In this context "reliable" means "can reproduce the original datum
>> exactly when transmitted back".)
>>
> Who cares? If you are using the same function for binary communication
> inside the server and for communications to the clients (or, for that
> matter, another server), then there is something wrong in your design.

> What are the "send" functions used for, beside server to client
> communication, anyways? You are asking me to treat the binary data as an
> opaque. Well, I'll counter with a question - what good is that to me?

Imagine an application that does this:

Issue query "SELECT foofloat FROM footable", and store the value to a
variable
Issue "INSERT INTO footable2 VALUES (?)", and send back the same value.

Don't you think footable and footable2 should now have the same value?
If "SELECT * FROM footable, footable2 WHERE foofloat=foofloat" returns 0
rows, I'd be pissed.

It's possible that the conversion within the driver loses some
precision, depending on the data types supported by the language and
platform, but the wire protocol should at least give the driver a chance
to get it right.

> Please note that the current code is useless for communicating binary
> data between two servers, even if they are guaranteed to be of the same
> version! How much less reliable can you get?

The current code is not used for communicating between two servers. And
it works fine as long as the client and the server are on the same platform.

Don't get me wrong, I agree that the binary format is broken as it is,
but the cure mustn't be worse than the disease.

> Please, give your own interface designers something to work with. Your
> attitude essentially leaves me out in the cold.

Design a wire protocol that
1. Doesn't lose information on any platform
2. Is more efficient than text format

and I'm pretty sure it'll be accepted.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Heikki Linnakangas <heikki@enterprisedb.com> writes:
> I agree in principle that the wire protocol should be
> platform-independent.

The *TEXT* format is for that. The problem here is that Shachar is
insisting on using binary format in a context where it is inappropriate.
Binary format has other goals that are not always compatible with 100%
platform independence --- that's unfortunate, sure, but it's reality.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tom Lane wrote:
> Binary format has other goals that are not always compatible with 100%
> platform independence --- that's unfortunate, sure, but it's reality.
>
Maybe the misunderstanding is mine. What are the goals for the binary
format?

Shachar

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster