Subject: Re: [Pljava-dev] Arrays support for object views
To: Kris Jurka <books@ejurka.com>, Krzysztof Nienartowicz <yazuna@gmail.com>,
 "pljava-dev@lists.postgresql.org" <pljava-dev@lists.postgresql.org>
References: <707ef6b40809230929p66557facy414f760052fab9ab@mail.gmail.com>
 <707ef6b40809260702n77f7737fh703b5123457df9bd@mail.gmail.com>
 <Pine.BSO.4.64.0810081955480.23875@leary.csoft.net>
From: Chapman Flack <chap@anastigmatix.net>
Message-ID: <5F63FD3C.3080206@anastigmatix.net>
Date: Thu, 17 Sep 2020 20:20:12 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.7.0
MIME-Version: 1.0
In-Reply-To: <Pine.BSO.4.64.0810081955480.23875@leary.csoft.net>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 10/08/08 20:04, books at ejurka.com (Kris Jurka) wrote:
> On Fri, 26 Sep 2008, Krzysztof Nienartowicz wrote:
> 
>> By using simply:
>> receiver.updateObject(5, new Double[]{1.0,2.0,3.0});
>> I finally don't get any errors but the values are not related to the array
>> set and always stay the same. Seems they are not initialized properly even.
> 
> When I test the same here, I get a crash.  Pljava is trying to read the 
> Double[] as if it was a double[] and that's blowing up.  If I change the 

This email thread from 2008 got reanimated as issue 300 [1].

Here is how the support currently stands:

Among the examples in current releases, there is TypeRoundTripper, which is
handy for testing questions like this. For example:

# select * from
(select '{1.9,2.8,3.7}'::float8[]) as p,
roundtrip(p) as (class text, roundtripped float8[]);

    float8     |        class        | roundtripped
---------------+---------------------+---------------
 {1.9,2.8,3.7} | [Ljava.lang.Double; | {1.9,2.8,3.7}
(1 row)


showing that when PostgreSQL passes a float8[] to PL/Java, Java receives
an array of Double, and when PL/Java passes that to the result receiver,
the right results are received. So that has improved since 2008.

The story seems to be the same for:

    float4     |       class        | roundtripped
---------------+--------------------+---------------
 {1.9,2.8,3.7} | [Ljava.lang.Float; | {1.9,2.8,3.7}

  int8   |       class       | roundtripped
---------+-------------------+--------------
 {1,2,3} | [Ljava.lang.Long; | {1,2,3}

  int4   |        class         | roundtripped
---------+----------------------+--------------
 {1,2,3} | [Ljava.lang.Integer; | {1,2,3}

  int2   |       class        | roundtripped
---------+--------------------+--------------
 {1,2,3} | [Ljava.lang.Short; | {1,2,3}

 bool  |        class         | roundtripped
-------+----------------------+--------------
 {t,f} | [Ljava.lang.Boolean; | {t,f}


PostgreSQL "char"[] is mapped to Byte[] but the roundtrip does not go well:

# select * from
(select '{Q,R,S}'::"char"[]) as p,
roundtrip(p) as (class text, roundtripped "char"[]);
  char   |       class       | roundtripped
---------+-------------------+--------------
 {Q,R,S} | [Ljava.lang.Byte; | {�,0,�}


Other arbitrary types do what you expect:

   text    |        class        | roundtripped
-----------+---------------------+--------------
 {foo,bar} | [Ljava.lang.String; | {foo,bar}


        time         |      class       |    roundtripped
---------------------+------------------+---------------------
 {01:23:00,23:45:00} | [Ljava.sql.Time; | {01:23:00,23:45:00}


For the types corresponding to Java primitives, you must pass updateObject
the boxed array type, for example, Double[] rather than double[]. Passing
the primitive array type simply produces:

java.sql.SQLException: Cannot derive a value of class [Ljava.lang.Integer;
from an object of class [I

and the equivalent for all of the primitive types.


An explanation might be that PostgreSQL arrays can contain nulls, and
you wouldn't be able to supply those from a true Java primitive array.
That doesn't mean it would not be convenient if you had no nulls.

Disappointingly, you don't get to include nulls even when you use
the boxed array form:

# select * from
(select '{1,null,3}'::float8[]) as p,
roundtrip(p) as (class text, roundtripped float8[]);
ERROR:  java.lang.NullPointerException

That's true for all the types corresponding to Java primitives.

If you omit the 'roundtripped' column from the result descriptor,
the function succeeds, confirming that PL/Java will /receive/ an array
containing nulls with no difficulty; the problem is in /returning/ one.

There is no such problem with the non-Java-primitive types:

           time           |      class       |       roundtripped
--------------------------+------------------+--------------------------
 {01:23:00,NULL,23:45:00} | [Ljava.sql.Time; | {01:23:00,NULL,23:45:00}


Current PL/Java versions do not implement java.sql.Array, so using
getObject/updateObject this way is the way to go.


PL/Java will receive all the elements of a PostgreSQL multidimensional
array, but only as a one-dimensional array, and will return them in
that form:

# select * from
(select '{{1,2,3},{4,5,6},{7,8,9}}'::int2[]) as p,
roundtrip(p) as (class text, roundtripped int2[]);
           int2            |       class        |    roundtripped
---------------------------+--------------------+---------------------
 {{1,2,3},{4,5,6},{7,8,9}} | [Ljava.lang.Short; | {1,2,3,4,5,6,7,8,9}

That's true even if you explicitly declare the result type multidimensional:

# select * from
(select '{{1,2,3},{4,5,6},{7,8,9}}'::int2[]) as p,
roundtrip(p) as (class text, roundtripped int2[][]);
           int2            |       class        |    roundtripped
---------------------------+--------------------+---------------------
 {{1,2,3},{4,5,6},{7,8,9}} | [Ljava.lang.Short; | {1,2,3,4,5,6,7,8,9}


In the currently-released (1.5.x) and the nearing-release (1.6.x) branches,
the type-mapping machinery is very little changed since 2008; like Kris,
I have tried to touch it as little as possible. A good deal of refactoring
in 1.6.x is happening to make it more thinkable to touch the type workings,
but actually revamping the types is planned to happen later. When that
happens, I would like to have a PostgreSQL-specific class that exposes all
the features of PostgreSQL arrays: multidimensionality, arbitrary starting
index, the works.


For 1.5.x and 1.6.x, I will consider the null-not-accepted problem and
the "char"[] value corruption to be bugs, and look at whether they will
be easy to fix. I think multidimensionality is more than I want to bite
off at the moment; I'd consider a pull request if someone else wants to
work on it, but as I am already designing a future rework of the type
system, putting too much effort into the current one at this point might
be difficult to justify.

Regards,
-Chap


[1] https://github.com/tada/pljava/issues/300