Re: 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(at)momjian(dot)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(at)momjian(dot)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(at)stcorp(dot)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(dot)Green(at)us(dot)schneider-electric(dot)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(at)postgresql(dot)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(at)momjian(dot)us
> EnterpriseDB http://www.enterprisedb.com
>
>
> + If your life is a hard drive, Christ can be your backup. +
>
>