Обсуждение: Binary tx format for an array?
Hi there, My question relates to trying to implement the binary transmission protocol for an Array type. I understand that the only place the format is documented is in the Postgres C source code. I've had a look through it and it seems comprehensible enough (to a non-C programmer), but I was wondering how the following expression would evaluate: // arrayfuncs.c, in array_recv element_type = pq_getmsgint(buf, sizeof(Oid)); or more specifically, the int size = sizeof(Oid); expression. Knowing this value would mean knowing how many bytes describing the Oid to transmit. Cheers Michael
My guess is that it depends on the machine it is on. Dave On 21-Jun-06, at 1:26 PM, Michael Guyver wrote: > Hi there, > > My question relates to trying to implement the binary transmission > protocol for an Array type. I understand that the only place the > format is documented is in the Postgres C source code. > > I've had a look through it and it seems comprehensible enough (to a > non-C programmer), but I was wondering how the following expression > would evaluate: > > // arrayfuncs.c, in array_recv > element_type = pq_getmsgint(buf, sizeof(Oid)); > > or more specifically, the > > int size = sizeof(Oid); > > expression. Knowing this value would mean knowing how many bytes > describing the Oid to transmit. > > Cheers > > Michael > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend >
The definition of Oid is in postgres_ext.h: typedef unsigned int Oid; All the architectures that I'm familiar with (x86, x86_64, Sparc-64) use 32-bit unsigned int data types. Also, the JDBC driver code always uses 32-bit integers for Oid types, so I think you're pretty safe using a 32- bit integer. There might be some weirdness at the 2GB boundary because Java doesn't support natively unsigned integers, but if there is then it would probably occur across the whole driver and not just in the code you want to write. -- Mark On Wed, 2006-06-21 at 18:26 +0100, Michael Guyver wrote: > Hi there, > > My question relates to trying to implement the binary transmission > protocol for an Array type. I understand that the only place the > format is documented is in the Postgres C source code. > > I've had a look through it and it seems comprehensible enough (to a > non-C programmer), but I was wondering how the following expression > would evaluate: > > // arrayfuncs.c, in array_recv > element_type = pq_getmsgint(buf, sizeof(Oid)); > > or more specifically, the > > int size = sizeof(Oid); > > expression. Knowing this value would mean knowing how many bytes > describing the Oid to transmit. > > Cheers > > Michael > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
"Michael Guyver" <kenevel@googlemail.com> writes:
> ... or more specifically, the
> int size = sizeof(Oid);
sizeof(Oid) is 4 [bytes]. You can check this and other basic datatype
sizes by looking in pg_type.
regards, tom lane
Thanks very much for your prompt help on this lads. > sizeof(Oid) is 4 [bytes]. You can check this and other basic datatype > sizes by looking in pg_type. So in the following code StringInfo buf = (StringInfo) PG_GETARG_POINTER(0) // ... Oid element_type = pq_getmsgint(buf, sizeof(Oid)); are the bytes representing an int4 Oid simply 23, represented in variable buf as [0x17][0x00][0x00][0x00] The reason for asking is I'm under the impression there can be differences between network byte-order and other representations. Hopefully this isn't an inane question - apologies if it is. Regards, Michael
"Michael Guyver" <kenevel@googlemail.com> writes:
> are the bytes representing an int4 Oid simply 23, represented in variable buf as
> [0x17][0x00][0x00][0x00]
No, in the buffer they'll be in network (big-endian) byte order,
[0x00][0x00][0x00][0x17]
If you are running in a little-endian architecture (eg Intel) then at
some point you'll be wanting to reverse the byte order to make a native
integer. In C you'd use ntohl() to do this. I'm not sure what's the
appropriate thing in Java --- I was under the impression that Java tried
to hide hardware details like endianness, so there may be some
convention about how you turn a sequence of bytes into a native integer.
regards, tom lane
> appropriate thing in Java --- I was under the impression that Java tried
> to hide hardware details like endianness, so there may be some
> convention about how you turn a sequence of bytes into a native integer.
Java tried so hard to hide endianness from you that it didn't provide
any real support for those times when you DO need to be aware of it. So
the "convention" looks kind of like this (snipped from the PG JDBC
driver):
public void SendInteger4(int val) throws IOException
{
SendChar((val >> 24)&255);
SendChar((val >> 16)&255);
SendChar((val >> 8)&255);
SendChar(val&255);
}
There finally were endian-aware buffer operations added in JDK 1.4, but
using them would be a big code change and would make the driver
unavailable for users of antique JVM's.
-- Mark
On 22/06/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> No, in the buffer they'll be in network (big-endian) byte order,
>
> [0x00][0x00][0x00][0x17]
On 22/06/06, Mark Lewis <mark.lewis@mir3.com> wrote:
> Java tried so hard to hide endianness from you that it didn't provide
> any real support for those times when you DO need to be aware of it. So
> the "convention" looks kind of like this (snipped from the PG JDBC
> driver):
>
> public void SendInteger4(int val) throws IOException {
> SendChar((val >> 24)&255);
> SendChar((val >> 16)&255);
> SendChar((val >> 8)&255);
> SendChar(val&255);
> }
Of course that's right: I got myself confused.
Thanks very much for your help, no doubt I'll be back for some more
(please sir) in the future.
I'm optimistic I'm on the right track but wanted to conceive a way of
unit testing the different Java classes that represent PG types, and
their binary Tx and Rx methods.
One way I thought of doing this is simply to send the values over JDBC
(specifying binary parameters) and check the contents of a test table
once all the inserts are done, but such unit tests get very messy very
quickly, and you wind up storing your expected results in a different
file, which is firstly something you have to keep in sync with the
source code and secondly tends to grow and become unmanageable very
quickly.
Another way would be to use the JNI mechanism to talk directly to the
PG code, calling the xxxrecv() functions for the type you're testing,
reading back the type's value using the xxxsend() method. Not being a
C man, I'm not sure how easy this would be and would appreciate any
suggestions.
Regards,
Michael
Michael
I'm curious. Are you attempting to implement the binary protocol for
the jdbc driver ?
If so, how are you dealing with date types which are either 64 bit or
floating point.
Have you done any testing to see if it is actually faster ?
There's certainly no question with timestamp/date parsing, others may
be questionable .
Dave
On 23-Jun-06, at 4:22 AM, Michael Guyver wrote:
> On 22/06/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> No, in the buffer they'll be in network (big-endian) byte order,
>>
>> [0x00][0x00][0x00][0x17]
>
> On 22/06/06, Mark Lewis <mark.lewis@mir3.com> wrote:
>> Java tried so hard to hide endianness from you that it didn't provide
>> any real support for those times when you DO need to be aware of
>> it. So
>> the "convention" looks kind of like this (snipped from the PG JDBC
>> driver):
>>
>> public void SendInteger4(int val) throws IOException {
>> SendChar((val >> 24)&255);
>> SendChar((val >> 16)&255);
>> SendChar((val >> 8)&255);
>> SendChar(val&255);
>> }
>
> Of course that's right: I got myself confused.
>
> Thanks very much for your help, no doubt I'll be back for some more
> (please sir) in the future.
>
> I'm optimistic I'm on the right track but wanted to conceive a way of
> unit testing the different Java classes that represent PG types, and
> their binary Tx and Rx methods.
>
> One way I thought of doing this is simply to send the values over JDBC
> (specifying binary parameters) and check the contents of a test table
> once all the inserts are done, but such unit tests get very messy very
> quickly, and you wind up storing your expected results in a different
> file, which is firstly something you have to keep in sync with the
> source code and secondly tends to grow and become unmanageable very
> quickly.
>
> Another way would be to use the JNI mechanism to talk directly to the
> PG code, calling the xxxrecv() functions for the type you're testing,
> reading back the type's value using the xxxsend() method. Not being a
> C man, I'm not sure how easy this would be and would appreciate any
> suggestions.
>
> Regards,
>
> Michael
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
Hi, Dave, Dave Cramer wrote: > I'm curious. Are you attempting to implement the binary protocol for the > jdbc driver ? > > If so, how are you dealing with date types which are either 64 bit or > floating point. > > Have you done any testing to see if it is actually faster ? > > There's certainly no question with timestamp/date parsing, others may be > questionable . At least for PostGIS (which uses a hexed representation for text protocol currently), I expect the data volume to be exactly 50% for binary. Bytea and blobs may be similar. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
> > :) good question, I'm sure I'll stare it in the face soon enough. At > > the moment I'm labouring under the impression that individual > > parameters can be specified as binary or text. > > Unfortunately this is not possible. :( And one of the reasons that > I've not spent a lot of time on this. Hi Dave, the page Message Formats (http://www.postgresql.org/docs/8.1/static/protocol-message-formats.html) specifies that the bind message should send the following for each parameter: Int16 - The number of parameter format codes that follow (denoted C below). This can be zero to indicate that there are no parameters or that the parameters all use the default format (text); or one, in which case the specified format code is applied to all parameters; or it can equal the actual number of parameters. Int16[C] - The parameter format codes. Each must presently be zero (text) or one (binary). Does your understanding of the spec mean that the Int16[C] must either be an array containg all zero's, or all ones? It reads as though these can be mixed.
On 23-Jun-06, at 8:47 AM, Michael Guyver wrote: >> > :) good question, I'm sure I'll stare it in the face soon >> enough. At >> > the moment I'm labouring under the impression that individual >> > parameters can be specified as binary or text. >> >> Unfortunately this is not possible. :( And one of the reasons that >> I've not spent a lot of time on this. > > Hi Dave, > > the page Message Formats > (http://www.postgresql.org/docs/8.1/static/protocol-message- > formats.html) > specifies that the bind message should send the following for each > parameter: > > Int16 - The number of parameter format codes that follow (denoted > C below). This can be zero to indicate that there are no parameters or > that the parameters all use the default format (text); or one, in > which case the specified format code is applied to all parameters; or > it can equal the actual number of parameters. > > Int16[C] - The parameter format codes. Each must presently be zero > (text) or one (binary). > > Does your understanding of the spec mean that the Int16[C] must either > be an array containg all zero's, or all ones? It reads as though these > can be mixed. Actually, it's the return value that is either/or . It appears that input parameters can be mixed. Tom can they be mixed going in ? I'm very curious to see how much performance gain can be had here. As I said the real gains would be on result sets especially parsing date/ time, timestamps. >
On 23-Jun-06, at 8:56 AM, Dave Cramer wrote: > > On 23-Jun-06, at 8:47 AM, Michael Guyver wrote: > >>> > :) good question, I'm sure I'll stare it in the face soon >>> enough. At >>> > the moment I'm labouring under the impression that individual >>> > parameters can be specified as binary or text. >>> >>> Unfortunately this is not possible. :( And one of the reasons that >>> I've not spent a lot of time on this. >> >> Hi Dave, >> >> the page Message Formats >> (http://www.postgresql.org/docs/8.1/static/protocol-message- >> formats.html) >> specifies that the bind message should send the following for each >> parameter: >> >> Int16 - The number of parameter format codes that follow (denoted >> C below). This can be zero to indicate that there are no >> parameters or >> that the parameters all use the default format (text); or one, in >> which case the specified format code is applied to all parameters; or >> it can equal the actual number of parameters. >> >> Int16[C] - The parameter format codes. Each must presently be zero >> (text) or one (binary). >> >> Does your understanding of the spec mean that the Int16[C] must >> either >> be an array containg all zero's, or all ones? It reads as though >> these >> can be mixed. > > Actually, it's the return value that is either/or . It appears that > input parameters can be mixed. Hmmm maybe I should read before sending. It appears that both input, and output can be text. The only catch with output is that you have to do a describe first to get the types. This may negate any gains on small result sets, but certainly for large ones it would help. > > Tom can they be mixed going in ? > > I'm very curious to see how much performance gain can be had here. > As I said the real gains would be on result sets especially parsing > date/time, timestamps. >> > > > ---------------------------(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 >
Dave Cramer <pg@fastcrypt.com> writes:
> Hmmm maybe I should read before sending. It appears that both input,
> and output can be text. The only catch with output is that you have
> to do a describe first to get the types. This may negate any gains on
> small result sets, but certainly for large ones it would help.
Right, both parameters and results can be mixed, but to ask for
mixed-format result columns you have to know how many there are
... and in reality you probably want to know what the column
types are before you make your decisions, anyway. So unless the
driver has a-priori knowledge of the result column set, which I
gather it doesn't, there'd need to be an additional round trip
to fetch and inspect the query's result column set before issuing BIND.
I think that in practice most clients would do "all text all the time"
or "all binary all the time", which is why the protocol allows compact
selection of those two cases.
regards, tom lane
> Hmmm maybe I should read before sending. It appears that both input, > and output can be text. The only catch with output is that you have > to do a describe first to get the types. This may negate any gains on > small result sets, but certainly for large ones it would help. Disclaimer: this is my first real trip through the v3 protocol and the JDBC driver source in general. So take anything below with several grains of salt. As far as I can tell from reading the JDBC CVS code, the sequence for preparing and executing a statement for the first time is: PREPARE (name=my_statement) DESCRIBE STATEMENT (name=my_statement) SYNC/FLUSH Read Responses BIND (portal=my_portal) DESCRIBE PORTAL (name=my_portal) EXECUTE (portal=my_portal) SYNC/FLUSH Read Responses So it seems that we could do all text, mixed or all binary parameters without adding a round trip, because we already do an extra round trip when a statement is first prepared and we already know all of the parameter types. We could do all text or all binary outputs without adding a round trip as well, because you can globally set the output format, but to do mixed outputs we'd need to execute the DESCRIBE PORTAL to get the return types, and then (not sure?) execute BIND again with different format parameters? -- Mark Lewis
On 23-Jun-06, at 4:32 PM, Mark Lewis wrote: >> Hmmm maybe I should read before sending. It appears that both input, >> and output can be text. The only catch with output is that you have >> to do a describe first to get the types. This may negate any gains on >> small result sets, but certainly for large ones it would help. > > Disclaimer: this is my first real trip through the v3 protocol and the > JDBC driver source in general. So take anything below with several > grains of salt. > > As far as I can tell from reading the JDBC CVS code, the sequence for > preparing and executing a statement for the first time is: > > PREPARE (name=my_statement) > DESCRIBE STATEMENT (name=my_statement) > SYNC/FLUSH > Read Responses > > BIND (portal=my_portal) > DESCRIBE PORTAL (name=my_portal) > EXECUTE (portal=my_portal) > SYNC/FLUSH > Read Responses > > So it seems that we could do all text, mixed or all binary parameters > without adding a round trip, because we already do an extra round trip > when a statement is first prepared and we already know all of the > parameter types. Yup, you could, however the real win here is on parsing the return parameters, going from object to string is not terribly expensive. Network times are probably a red herring. > > We could do all text or all binary outputs without adding a round trip > as well, because you can globally set the output format, but to do > mixed > outputs we'd need to execute the DESCRIBE PORTAL to get the return > types, and then (not sure?) execute BIND again with different format > parameters? Yes, you can do binary results, but consider what happens if someone returns a type the driver doesn't know about ? > > -- Mark Lewis >
On Fri, 23 Jun 2006, Mark Lewis wrote: > As far as I can tell from reading the JDBC CVS code, the sequence for > preparing and executing a statement for the first time is: > > PREPARE (name=my_statement) > DESCRIBE STATEMENT (name=my_statement) > SYNC/FLUSH > Read Responses > > BIND (portal=my_portal) > DESCRIBE PORTAL (name=my_portal) > EXECUTE (portal=my_portal) > SYNC/FLUSH > Read Responses > Nope. There is no Sync in the middle there. The driver sends Parse, Describe Statement, Bind, Execute, Sync all at once. You may be confused by the driver's ability to Parse/Describe/Sync a query to implement ParameterMetaData, but that's an unusual operation, not the normal path. Kris Jurka
On Fri, 2006-06-23 at 15:46 -0500, Kris Jurka wrote: > > As far as I can tell from reading the JDBC CVS code, the sequence for > > preparing and executing a statement for the first time is: > > > > PREPARE (name=my_statement) > > DESCRIBE STATEMENT (name=my_statement) > > SYNC/FLUSH > > Read Responses > > > > BIND (portal=my_portal) > > DESCRIBE PORTAL (name=my_portal) > > EXECUTE (portal=my_portal) > > SYNC/FLUSH > > Read Responses > > > > Nope. There is no Sync in the middle there. The driver sends Parse, > Describe Statement, Bind, Execute, Sync all at once. You may be confused > by the driver's ability to Parse/Describe/Sync a query to implement > ParameterMetaData, but that's an unusual operation, not the normal path. Ah, yes that's where I was confused. Now I understand why it was such a pain to support things like timestamp with/without timezone; you don't get the describe statement back until you've already run the BIND. -- Mark
Mark Lewis <mark.lewis@mir3.com> writes:
> Java tried so hard to hide endianness from you that it didn't provide
> any real support for those times when you DO need to be aware of it.
Could you give sample cases when you DO need it? I don't think that
many people write hardware drivers in Java ;-)
I don't think network communication qualifies either, see below.
> So the "convention" looks kind of like this (snipped from the PG JDBC
> driver):
>
> public void SendInteger4(int val) throws IOException
> {
> SendChar((val >> 24)&255);
> SendChar((val >> 16)&255);
> SendChar((val >> 8)&255);
> SendChar(val&255);
> }
This code is like copied/pasted from the JDK:
* @since JDK1.0
DataOutputStream#writeInt()
Any reason for duplicating it in the driver?
> There finally were endian-aware buffer operations added in JDK 1.4,
Could you detail which ones? Thanks in advance.
On Mon, 2006-07-10 at 11:19 +0200, Marc Herbert wrote:
> Mark Lewis <mark.lewis@mir3.com> writes:
>
> > Java tried so hard to hide endianness from you that it didn't provide
> > any real support for those times when you DO need to be aware of it.
>
> Could you give sample cases when you DO need it? I don't think that
> many people write hardware drivers in Java ;-)
Any time you're working with network protocols, as was the case with
this example here. Try to send a 32-bit integer over the network, for
the other end to receive it correctly, it needs to be in the byte-order
the client expects.
> I don't think network communication qualifies either, see below.
>
>
> > So the "convention" looks kind of like this (snipped from the PG JDBC
> > driver):
> >
> > public void SendInteger4(int val) throws IOException
> > {
> > SendChar((val >> 24)&255);
> > SendChar((val >> 16)&255);
> > SendChar((val >> 8)&255);
> > SendChar(val&255);
> > }
>
> This code is like copied/pasted from the JDK:
>
> * @since JDK1.0
> DataOutputStream#writeInt()
>
>
> Any reason for duplicating it in the driver?
Well, in the general case you can only use DataOutputStream's writeInt()
method if everything is in big-endian byte order, which is true in this
case but not universally so. Here it IS big-endian, so the choice is
between duplicating one method a few lines long, or wrapping the main
OutputStream in an extra DataOutputStream which would only be used when
writing big-endian integers. Not sure if one solution is better than
the other.
> > There finally were endian-aware buffer operations added in JDK 1.4,
>
> Could you detail which ones? Thanks in advance.
It's in the java.nio stuff, look at the javadoc for ByteBuffer.order()
for a starting point.
-- Mark
Mark Lewis <mark.lewis@mir3.com> writes:
>> > public void SendInteger4(int val) throws IOException
>> > {
>> > SendChar((val >> 24)&255);
>> > SendChar((val >> 16)&255);
>> > SendChar((val >> 8)&255);
>> > SendChar(val&255);
>> > }
>>
>> This code is like copied/pasted from the JDK:
>>
>> * @since JDK1.0
>> DataOutputStream#writeInt()
>>
>>
>> Any reason for duplicating it in the driver?
>
> Well, in the general case you can only use DataOutputStream's writeInt()
> method if everything is in big-endian byte order, which is true in this
> case but not universally so.
My point is that it IS universally true in the Java universe AND in
the networking universe. There simply cannot be two opposed networking
universes... that would mean we'd have two Internets for instance?
So I think the whole point of Java is that everything is big-endian, so
you do not need to know about endianness anymore. IMHO this is a
success.
> Here it IS big-endian, so the choice is between duplicating one
> method a few lines long, or wrapping the main OutputStream in an
> extra DataOutputStream which would only be used when writing
> big-endian integers. Not sure if one solution is better than the
> other.
OK you can bypass/copy-paste some JDK code for performance
reasons. But then you can not honestly complain Java does a poor job
of hiding endianness when you fiddled with the machine internals
instead of safely staying outside.
Moreover you don't really need to think about endianness when simply
copy/pasting this code.
> It's in the java.nio stuff, look at the javadoc for
> ByteBuffer.order() for a starting point.
Interesting. Looks like you need this only when dealing with
badly-behaved applications that do not use the network order, right?
Should be very seldom, don't you think?
Thanks for answering.
On Mon, 2006-07-10 at 19:54 +0200, Marc Herbert wrote: > My point is that it IS universally true in the Java universe AND in > the networking universe. There simply cannot be two opposed networking > universes... that would mean we'd have two Internets for instance? > > So I think the whole point of Java is that everything is big-endian, so > you do not need to know about endianness anymore. IMHO this is a > success. Just because Java is big-endian and this particular protocol is big- endian does not mean that the universe is big-endian. There are protocols which either always use little-endian, or allow the endian- ness of parameters to be negotiated for performance. One example is the TDS protocol, so you may want to look at the open source jTDS JDBC driver and see how they handle it. Your point is however well taken that there are more big-endian protocols out there than little-endian protocols, and Java works easily with those. > Interesting. Looks like you need this only when dealing with > badly-behaved applications that do not use the network order, right? > Should be very seldom, don't you think? It's not badly-behaving applications, it's protocols whose designers chose to use little-endian formatting, usually for performance reasons on common x86 hardware. -- Mark