Thread: Array handling in libpq
Hi everyone, I've been using postgres for several projects and I must confess I really like it so far. :-) I hope someone on this list could help me out. Suppose I have a table defined with: CREATE TABLE foo(bar double precision[]); My initial guess was that, as postgres supports arrays, there would probably also be some support for this in libpq (which I'm using via libpqxx by the way). However, from what I've been able to find, it seems that if I use the text format for parameters / results, I would have to: 1. [SEND] Convert the double array on the C(++) side to a '{}' delimited string representation. 2. [RECV] Convert something like '{3.124, 4.5234, 123.123}' to a C(++) array of doubles myself. I hope I overlooked something and there is some support for sending / receiving (parsing) arrays in libpq?? That would be a big help. Still, sending double in ASCII does not seem that efficient. It's not a trivial conversion and it takes more bytes than sending it as binary. However, it seems that using the binary format, I would have to: 1. [SEND] Convert to network byte order Send the array in the format arrayfuncs.c::array_recv() expects, i.e. including the right header (and a length per element as well). 2. [RECV] Parse the header, and convert from byte order Am I missing something here? Or is this just the way it is? (Which is also fine, then at least I know what I'm up against... ;-) Thanks, Joris van Zwieten
Condsidering all the processing required by the query, the conversion and number of bytes sent over the nework really don't affect performance. --------------------------------------------------------------------------- Joris van Zwieten wrote: > Hi everyone, > > > I've been using postgres for several projects and I must confess I really > like it so far. :-) I hope someone on this list could help me out. > > Suppose I have a table defined with: > CREATE TABLE foo(bar double precision[]); > > My initial guess was that, as postgres supports arrays, there would > probably also be some support for this in libpq (which I'm using via > libpqxx by the way). > > However, from what I've been able to find, it seems that if I use the text > format for parameters / results, I would have to: > > 1. [SEND] Convert the double array on the C(++) side to a '{}' delimited > string representation. > 2. [RECV] Convert something like '{3.124, 4.5234, 123.123}' to a C(++) > array of doubles myself. > > I hope I overlooked something and there is some support for sending / > receiving (parsing) arrays in libpq?? That would be a big help. > > Still, sending double in ASCII does not seem that efficient. It's not a > trivial conversion and it takes more bytes than sending it as binary. > However, it seems that using the binary format, I would have to: > > 1. [SEND] Convert to network byte order > Send the array in the format arrayfuncs.c::array_recv() expects, > i.e. including the right header (and a length per element as > well). > 2. [RECV] Parse the header, and convert from byte order > > Am I missing something here? Or is this just the way it is? (Which is also > fine, then at least I know what I'm up against... ;-) > > Thanks, > > Joris van Zwieten > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Wed, January 17, 2007 10:46, Bruce Momjian wrote: > > Condsidering all the processing required by the query, the conversion > and number of bytes sent over the nework really don't affect > performance. Bruce, I'm glad to hear this from someone more experienced. A lot of people request support for transmitting binary data in libpqxx, which would take a lot of work, but there's very little hard evidence that it would make things noticeably better. Joris: yes, I'm afraid there's no array support in libpqxx yet so you'll have to do the conversion yourself. I may have some time to work on this in the coming days. Would you be willing to test-drive new code? Jeroen
Hi Bruce, Jeroen, Thanks for your replies. This is good to know, although one worry remains and that is loss of precision in the conversion of double to string. But this could probably always be solved by using enough digits in the conversion, right? I did do some tests to see what an array received in binary format looks like, and the format is quite easy. But I'd rather not depend on it too much, as it's Postgres internal and could probably change in future versions. Jeroen: Yes, I would certainly be willing to test new code. I think it would be nice if one could interact with libpqxx in terms of some array type (vector?, C-style pointer?, ..?) and it would perform the conversion to/from strings automatically. On Wed, January 17, 2007 7:49 am, Jeroen T. Vermeulen wrote: > On Wed, January 17, 2007 10:46, Bruce Momjian wrote: > >> >> Condsidering all the processing required by the query, the conversion >> and number of bytes sent over the nework really don't affect performance. >> > > Bruce, I'm glad to hear this from someone more experienced. A lot of > people request support for transmitting binary data in libpqxx, which would > take a lot of work, but there's very little hard evidence that it would > make things noticeably better. > > Joris: yes, I'm afraid there's no array support in libpqxx yet so you'll > have to do the conversion yourself. I may have some time to work on this > in the coming days. Would you be willing to test-drive new code? > > > Jeroen > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > > http://www.postgresql.org/about/donate > >
Joris van Zwieten wrote: > Hi Bruce, Jeroen, > > Thanks for your replies. This is good to know, although one worry remains > and that is loss of precision in the conversion of double to string. But > this could probably always be solved by using enough digits in the > conversion, right? Also, the client and server can be different architectures, so the only portable way to do the transfer is ASCII. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Wed, Jan 17, 2007 at 09:45:09 +0100, Joris van Zwieten <vanzwieten@stcorp.nl> wrote: > > Thanks for your replies. This is good to know, although one worry remains > and that is loss of precision in the conversion of double to string. But > this could probably always be solved by using enough digits in the > conversion, right? There is a GUC variable (extra_float_digits) for doing this. Setting it to 2 will give enough extra decimal places that you should get back the same binary representation (on the same architecture). This is automatically used in some cases (e.g. pgdump).
On Wed, Jan 17, 2007 at 12:15:37 -0500, Brian.Green@us.schneider-electric.com wrote: > > Just a heads up guys.I am getting e-mail from you and i am pretty sure i'm > not supposed to. Are you on the pgsql-interfaces@postgresql.org list? Is some address that forwards to your address on that list? Checking the received headers for the messages you have received might tell you more about that. It might be that this particular discussion has drifted off topic for the list and you weren't expecting it.
Well... It wouldn't have to be ASCII, right? As long as the way it is communicated between server and client is standardized. On the server side, each type has a _send() and _recv() method that performs a conversion from the internal representation on the server side to a standard representation that is used for communication with the client, if I'm not mistaken. For instance, the int4 type is send in network byte order by the server, even if this is not the native byte order of the architecture on which the server is compiled/ran. If the client, i.e. libpq, would just convert back from this standard format to the byte order used on the host architecture everything should be nicely transparent. But from the libpq examples it seems libpq does not do this. Come to think of it, wouldn't it be possible to re-use the implementation of the _send/_recv methods from the server to perform the necessary conversion on the client side? I suppose that something similar should be possible with floating point types, although it might be more involved. Bye, Joris On Wed, January 17, 2007 4:46 pm, Bruce Momjian wrote: > Joris van Zwieten wrote: > >> Hi Bruce, Jeroen, >> >> >> Thanks for your replies. This is good to know, although one worry >> remains and that is loss of precision in the conversion of double to >> string. But this could probably always be solved by using enough digits >> in the conversion, right? > > Also, the client and server can be different architectures, so the only > portable way to do the transfer is ASCII. > > -- > Bruce Momjian bruce@momjian.us > EnterpriseDB http://www.enterprisedb.com > > > + If your life is a hard drive, Christ can be your backup. + > >