Thread: Array types

Array types

From
"John Lister"
Date:
Hi, using v8.3.5 and a number of client libraries (java, python, pgadmin) and playing about with arrays.
 
They all return arrays as text, is it possible to configure postgresql to return an array in native form (does postgresql support such a thing)? This is using both the simple and extended query forms - i couldn't see a way to say what return type i wanted in the protocol docs...
 
This would seem much better in terms of performance, both size and speed(conversion).
 
Thanks
 
--
 
Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/

Re: Array types

From
Tom Lane
Date:
"John Lister" <john.lister-ps@kickstone.com> writes:
> Hi, using v8.3.5 and a number of client libraries (java, python, pgadmin) and playing about with arrays.
> They all return arrays as text, is it possible to configure postgresql
> to return an array in native form (does postgresql support such a
> thing)?

This is something the client code would request (or not).  It would not
be sensible to try to force it from the server side, since if the client
doesn't request it it's likely that the client wouldn't understand the
data format.
        regards, tom lane


Re: Array types

From
Merlin Moncure
Date:
On Tue, Apr 7, 2009 at 3:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "John Lister" <john.lister-ps@kickstone.com> writes:
>> Hi, using v8.3.5 and a number of client libraries (java, python, pgadmin) and playing about with arrays.
>> They all return arrays as text, is it possible to configure postgresql
>> to return an array in native form (does postgresql support such a
>> thing)?
>
> This is something the client code would request (or not).  It would not
> be sensible to try to force it from the server side, since if the client
> doesn't request it it's likely that the client wouldn't understand the
> data format.

unless, of course, you are using libpqtypes :D

http://libpqtypes.esilo.com/

merlin


Re: Array types

From
Andrew Chernow
Date:
John Lister wrote:
> They all return arrays as text, is it possible to configure postgresql 
> to return an array in native form (does postgresql support such a 
> thing)? This is using both the simple and extended query forms - i 
> couldn't see a way to say what return type i wanted in the protocol docs...
>  

You need libpqtypes (client-side library).  It requires a new 8.4 
feature called libpq-events, but there is an 8.3 patch available.  If 
you willing to patch your 8.3.5 libpq client, than this should meet your 
needs.

Downloads: http://pgfoundry.org/projects/libpqtypes/
Documentation: http://libpqtypes.esilo.com/

For arrays, libpqtypes gives you a PGresult where each tuple is an array 
item.  For composite arrays, each composite field is a PGresult field.

> This would seem much better in terms of performance, both size and 
> speed(conversion).
>  

That is true.  Our testing has proven this.  It also reduces the overall 
coding effort.

-- 
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/


Re: Array types

From
"John Lister"
Date:
> This is something the client code would request (or not).  It would not
> be sensible to try to force it from the server side, since if the client
> doesn't request it it's likely that the client wouldn't understand the
> data format.

Cheers for the quick reply, any chance of a pointer to the protocol where 
the client specifies the return type(s) so i can check the client code 
(mainly jdbc) to see what is going on?

Thanks

JOHN 



Re: Array types

From
"John Lister"
Date:
Does libpqtypes pass the array "over the wire" as an array? Ideally i'd like 
to do this with jdbc, but might give me a pointer...

Thanks
----- Original Message ----- 
From: "Andrew Chernow" <ac@esilo.com>
To: "John Lister" <john.lister-ps@kickstone.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Tuesday, April 07, 2009 8:15 PM
Subject: Re: [HACKERS] Array types


> John Lister wrote:
>> They all return arrays as text, is it possible to configure postgresql to 
>> return an array in native form (does postgresql support such a thing)? 
>> This is using both the simple and extended query forms - i couldn't see a 
>> way to say what return type i wanted in the protocol docs...
>>
>
> You need libpqtypes (client-side library).  It requires a new 8.4 feature 
> called libpq-events, but there is an 8.3 patch available.  If you willing 
> to patch your 8.3.5 libpq client, than this should meet your needs.
>
> Downloads: http://pgfoundry.org/projects/libpqtypes/
> Documentation: http://libpqtypes.esilo.com/
>
> For arrays, libpqtypes gives you a PGresult where each tuple is an array 
> item.  For composite arrays, each composite field is a PGresult field.
>
>> This would seem much better in terms of performance, both size and 
>> speed(conversion).
>>
>
> That is true.  Our testing has proven this.  It also reduces the overall 
> coding effort.
>
> -- 
> Andrew Chernow
> eSilo, LLC
> every bit counts
> http://www.esilo.com/
> 



Re: Array types

From
Merlin Moncure
Date:
On Tue, Apr 7, 2009 at 3:35 PM, John Lister
<john.lister-ps@kickstone.com> wrote:
> Does libpqtypes pass the array "over the wire" as an array? Ideally i'd like
> to do this with jdbc, but might give me a pointer...

We send/receive the server's array format.  This is not quite a C
array, and is definitely not a java array.  It's a packed postgres
specific (network byte order) format.  It is much faster and tighter
than text in some cases however.  We present an interface to copy C
arrays to postgres style formats such as:

PGarray a;
PQgetf(res, tuple_num, "%int4[]", field_num, &a);

This will 'pop' a result out of your result that presents the array
internals (which you could then loop).

merlin


Re: Array types

From
Andrew Chernow
Date:
John Lister wrote:
> Cheers, nice to know it is possible... Now to see if i can get 
> java/python to do the same :) or to use a modified libpq somehow...
> 
> 

If performance is your concern, you would probably get the best results 
using the languages C glue interfrace.  For instance, in java I think 
you would want to create a JNI wrapper to libpqtypes that converts 
PGarray to a java array.

-- 
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/


Re: Array types

From
John Lister
Date:
Cheers, nice to know it is possible... Now to see if i can get 
java/python to do the same :) or to use a modified libpq somehow...


Merlin Moncure wrote:
> On Tue, Apr 7, 2009 at 3:35 PM, John Lister
> <john.lister-ps@kickstone.com> wrote:
>   
>> Does libpqtypes pass the array "over the wire" as an array? Ideally i'd like
>> to do this with jdbc, but might give me a pointer...
>>     
>
> We send/receive the server's array format.  This is not quite a C
> array, and is definitely not a java array.  It's a packed postgres
> specific (network byte order) format.  It is much faster and tighter
> than text in some cases however.  We present an interface to copy C
> arrays to postgres style formats such as:
>
> PGarray a;
> PQgetf(res, tuple_num, "%int4[]", field_num, &a);
>
> This will 'pop' a result out of your result that presents the array
> internals (which you could then loop).
>
> merlin
>   


Re: Array types

From
James Pye
Date:
On Apr 7, 2009, at 12:54 PM, John Lister wrote:

> Cheers, nice to know it is possible... Now to see if i can get java/ 
> python to do the same :) or to use a modified libpq somehow...

http://python.projects.postgresql.org will do it for Python. =D

tho, only supports Python 3, which is still quite new.


Re: Array types

From
Merlin Moncure
Date:
On Tue, Apr 7, 2009 at 6:42 PM, James Pye <lists@jwp.name> wrote:
> On Apr 7, 2009, at 12:54 PM, John Lister wrote:
>
>> Cheers, nice to know it is possible... Now to see if i can get java/python
>> to do the same :) or to use a modified libpq somehow...
>
> http://python.projects.postgresql.org will do it for Python. =D
>
> tho, only supports Python 3, which is still quite new.

I took a quick look at the pg python driver and was very impressed.
They implemented a full top to bottom binary driver with type i/o
functions in the vein of libpqtypes.  If you are writing python, this
is definitely the way to go.  Kudos to them for seeing the light and
doing it that way...it's much better coupling with the server than
marshaling everything through text.

merlin


Re: Array types

From
John Lister
Date:
brilliant i'll give it a go...  Now to sort out java :)

James Pye wrote:
> On Apr 7, 2009, at 12:54 PM, John Lister wrote:
>
>> Cheers, nice to know it is possible... Now to see if i can get 
>> java/python to do the same :) or to use a modified libpq somehow...
>
> http://python.projects.postgresql.org will do it for Python. =D
>
> tho, only supports Python 3, which is still quite new.
>


Re: Array types

From
"John Lister"
Date:
Following this up, is there any docs on the binary wire format for arrays?
 
Thanks
----- Original Message -----
Sent: Tuesday, April 07, 2009 7:54 PM
Subject: [HACKERS] Array types

Hi, using v8.3.5 and a number of client libraries (java, python, pgadmin) and playing about with arrays.
 
They all return arrays as text, is it possible to configure postgresql to return an array in native form (does postgresql support such a thing)? This is using both the simple and extended query forms - i couldn't see a way to say what return type i wanted in the protocol docs...
 
This would seem much better in terms of performance, both size and speed(conversion).
 
Thanks
 
--
 
Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/

Re: Array types

From
Andrew Chernow
Date:
John Lister wrote:
> Following this up, is there any docs on the binary wire format for arrays?
>  

None that I know of.

Check out the backend source: (array_recv() and array_send() functions)
http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/arrayfuncs.c?rev=1.154

Or, look at libpqtypes array.c:
http://libpqtypes.esilo.com/browse_source.html?file=array.c

-- 
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/


Re: Array types

From
Andrew Chernow
Date:
Andrew Chernow wrote:
> John Lister wrote:
>> Following this up, is there any docs on the binary wire format for 
>> arrays?
>>  
> 
> None that I know of.
> 
> Check out the backend source: (array_recv() and array_send() functions)
> http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/arrayfuncs.c?rev=1.154 
> 
> 
> Or, look at libpqtypes array.c:
> http://libpqtypes.esilo.com/browse_source.html?file=array.c
> 

Forgot to mention, this is not as simple as understanding the array 
format.  You have to understand the wire format for all types that can 
be array elements.  The array wire format serializes its array elements 
as [elem_len][elem_data].  elem_data is the wire format of the array 
element type, like an int, timestamp, polygon, bytea, etc...  So once 
you unravel the array container format, you still have to demarshal the 
type data.

-- 
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/


Re: Array types

From
Merlin Moncure
Date:
On Wed, Apr 8, 2009 at 10:48 AM, John Lister
<john.lister-ps@kickstone.com> wrote:
> Following this up, is there any docs on the binary wire format for arrays?
>
> Thanks
>

Does java wrap libpq? If so, your best bet is probably going to be to
go the libpqtypes route.  If you want help doing that, you are more
than welcome to ask (probably should move this thread to the
libqptypes list).  If not, you are headed for a 'much bigger than it
looks on the surface' challenge...there are a lot of types...trust me
on this one.  If you want help with libpqtypes you can ask on our list
on pgfoundry.

merlin


Re: Array types

From
"John Lister"
Date:
Cheers for the pointers. Am i right in thinking that if i get an array of 
arrays, the nested arrays are sent in wire format as well - it seems to be 
from the docs.

Secondly, comments are a bit scarse in the code, but am i also right in 
thinking that an array indexing can start at an arbitrary value? This seems 
to be what the lbound value is for... or is this a addition to deal with 
nulls eg, {null, null, null, 4} would have a lbound of 3.... (or both)

Thanks

----- Original Message ----- 
From: "Andrew Chernow" <ac@esilo.com>
To: "John Lister" <john.lister-ps@kickstone.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Wednesday, April 08, 2009 4:07 PM
Subject: Re: [HACKERS] Array types


> Andrew Chernow wrote:
>> John Lister wrote:
>>> Following this up, is there any docs on the binary wire format for 
>>> arrays?
>>>
>>
>> None that I know of.
>>
>> Check out the backend source: (array_recv() and array_send() functions)
>> http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/arrayfuncs.c?rev=1.154 
>> Or, look at libpqtypes array.c:
>> http://libpqtypes.esilo.com/browse_source.html?file=array.c
>>
>
> Forgot to mention, this is not as simple as understanding the array 
> format.  You have to understand the wire format for all types that can be 
> array elements.  The array wire format serializes its array elements as 
> [elem_len][elem_data].  elem_data is the wire format of the array element 
> type, like an int, timestamp, polygon, bytea, etc...  So once you unravel 
> the array container format, you still have to demarshal the type data.
>
> -- 
> Andrew Chernow
> eSilo, LLC
> every bit counts
> http://www.esilo.com/
> 



Re: Array types

From
"John Lister"
Date:
No unfortunately not, it is a JDBC type 4 java which is entirely written in 
java. I've patched (as pointed out in another list) the base version to 
handle binary data (still a couple of issues that seem unfinished) which has 
given me clues, but the patch only supports simple types. I'm looking to 
create translator for arrays now..

I was hoping to use the java type handling for the internals of the array 
and throw an error on any "unknown" ones... I only use ints and floats in my 
arrays, so may leave it at that if it works and look at doing it properly 
later...


> On Wed, Apr 8, 2009 at 10:48 AM, John Lister
> <john.lister-ps@kickstone.com> wrote:
>> Following this up, is there any docs on the binary wire format for 
>> arrays?
>>
>> Thanks
>>
>
> Does java wrap libpq? If so, your best bet is probably going to be to
> go the libpqtypes route.  If you want help doing that, you are more
> than welcome to ask (probably should move this thread to the
> libqptypes list).  If not, you are headed for a 'much bigger than it
> looks on the surface' challenge...there are a lot of types...trust me
> on this one.  If you want help with libpqtypes you can ask on our list
> on pgfoundry.
>
> merlin
>
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
> 



Re: Array types

From
Andrew Dunstan
Date:

Merlin Moncure wrote:
> On Wed, Apr 8, 2009 at 10:48 AM, John Lister
> <john.lister-ps@kickstone.com> wrote:
>   
>> Following this up, is there any docs on the binary wire format for arrays?
>>
>> Thanks
>>
>>     
>
> Does java wrap libpq? 
>   

No. The JDBC driver is a Type 4 pure java driver. It implements the wire 
protocol.

That's is what makes it portable.

Cheers

andrew


Re: Array types

From
Tom Lane
Date:
"John Lister" <john.lister-ps@kickstone.com> writes:
> Cheers for the pointers. Am i right in thinking that if i get an array of 
> arrays, the nested arrays are sent in wire format as well - it seems to be 
> from the docs.

Postgres doesn't have arrays of arrays.  There are multi-dimensional
arrays, which aren't conceptually the same thing.
        regards, tom lane


Re: Array types

From
Greg Stark
Date:
On Wed, Apr 8, 2009 at 4:11 PM, John Lister
<john.lister-ps@kickstone.com> wrote:
> Cheers for the pointers. Am i right in thinking that if i get an array of
> arrays, the nested arrays are sent in wire format as well - it seems to be
> from the docs.

No, you can't easily get an array of arrays in Postgres. You can get
multi-dimensional arrays but that's one big array with multiple
dimensions.  The text output form does look like an array of arrays
but they don't behave like you might think they would:

postgres=# select array[array[1,2,3,4],array[5,6,7,8]];        array
-----------------------{{1,2,3,4},{5,6,7,8}}
(1 row)


postgres=# select '{{1,2,3,4},{5,6,7,8}}'::int[];        int4
-----------------------{{1,2,3,4},{5,6,7,8}}
(1 row)

postgres=# select ('{{1,2,3,4},{5,6,7,8}}'::int[])[1];int4
------

(1 row)

postgres=# select ('{{1,2,3,4},{5,6,7,8}}'::int[])[1][1];int4
------   1
(1 row)


>
> Secondly, comments are a bit scarse in the code, but am i also right in
> thinking that an array indexing can start at an arbitrary value? This seems
> to be what the lbound value is for... or is this a addition to deal with
> nulls eg, {null, null, null, 4} would have a lbound of 3.... (or both)

No, nulls are handled using a bitmap inside the array data structure.

Array bounds don't have to start at 1, they can start below 1 or above 1.

postgres=# select ('[-2:-1][5:8]={{1,2,3,4},{5,6,7,8}}'::int[])[-2][5];int4
------   1
(1 row)



-- 
greg


Re: Array types

From
"John Lister"
Date:

> On Wed, Apr 8, 2009 at 4:11 PM, John Lister
> <john.lister-ps@kickstone.com> wrote:
>> Cheers for the pointers. Am i right in thinking that if i get an array of
>> arrays, the nested arrays are sent in wire format as well - it seems to 
>> be
>> from the docs.
>
> No, you can't easily get an array of arrays in Postgres. You can get
> multi-dimensional arrays but that's one big array with multiple
> dimensions.  The text output form does look like an array of arrays
> but they don't behave like you might think they would:

Cheers, it wasn't clear if you have an array of arrays of which the nested 
ones were of a different type. but it looks like all the values have to be 
the same type,
eg

select (array[array[1,2,3,4],array['test']])

fails..

this makes life simpler :)

>> Secondly, comments are a bit scarse in the code, but am i also right in
>> thinking that an array indexing can start at an arbitrary value? This 
>> seems
>> to be what the lbound value is for... or is this a addition to deal with
>> nulls eg, {null, null, null, 4} would have a lbound of 3.... (or both)
>
> No, nulls are handled using a bitmap inside the array data structure.
>
> Array bounds don't have to start at 1, they can start below 1 or above 1.
>
> postgres=# select ('[-2:-1][5:8]={{1,2,3,4},{5,6,7,8}}'::int[])[-2][5];
> int4
> ------

Somehow missed the bounds in the docs. Cheers that has cleared that up...

JOHN 



Re: Array types

From
Merlin Moncure
Date:
On Wed, Apr 8, 2009 at 11:35 AM, Greg Stark <stark@enterprisedb.com> wrote:
> On Wed, Apr 8, 2009 at 4:11 PM, John Lister
> <john.lister-ps@kickstone.com> wrote:
>> Cheers for the pointers. Am i right in thinking that if i get an array of
>> arrays, the nested arrays are sent in wire format as well - it seems to be
>> from the docs.
>
> No, you can't easily get an array of arrays in Postgres. You can get
> multi-dimensional arrays but that's one big array with multiple
> dimensions.  The text output form does look like an array of arrays
> but they don't behave like you might think they would:

one note about that: you can have array of composites with arrays in
them, so you can get arbitrary levels of nesting.

merlin