Subject: [Pljava-dev] Arrays support for object views

From: yazuna at gmail.com (Krzysztof Nienartowicz)
Date: Tue, 23 Sep 2008 18:29:54 +0200

Hello,
Thanks for the nice project. I encountered some traces of efforts to use
arrays from within pljava but could not find a working example.
I'm trying to create a function that would return a setof complex objects
where some fields of the object are plain old dataype arrays. i.e.:

CREATE TYPE tsTest2 AS
(
     id  bigint ,
     tsType integer  ,
     residuals boolean ,
     length integer,
     obs    float8[]
) ;

then I create a mapping function:

CREATE or replace FUNCTION tt2(integer ,float8 , float8 , float8 )
  RETURNS SETOF tstest2
  AS 'gaia.cu7.omimpl.TimeSeriesImplSQL.timeseriesReturn'
  IMMUTABLE LANGUAGE javaU;

then, whatever I try to do in a java code for   assignRowValues fails:
i.e.: receiver.updateString(6, "{1.0,2.0,3.0}");
would result in:

-- Executing query:
select tt2(0,0.0,0.0,0.0);

ERROR:  java.sql.SQLException: Cannot derive a value of class
[Ljava.lang.Double; from an object of class java.lang.String

you do have updateArray but could not spot any usage example with it.
Please help, otherwise we will have to drop pljava from the project which
has a chance to impact astronomy community thus leverage pljava.
Best regards,
Krzysztof
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20080923/b21a2004/attachment.html>

---

From: yazuna at gmail.com (Krzysztof Nienartowicz)
Date: Fri, 26 Sep 2008 16:02:01 +0200

Hello,

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.

--wrapper with dummy parameters
select * from tt2(0,0.0,0.0,0.0);

gives:

1000;1;f;10;"{-1.06280184473225e-250,2.47032822920623e-323,0}";"";""
1000;1;f;10;"{-1.06291373259335e-250,2.47032822920623e-323,0}";"";""
1000;1;f;10;"{-1.06302562045446e-250,2.47032822920623e-323,0}";"";""
1000;1;f;10;"{-1.06313750831557e-250,2.47032822920623e-323,0}";"";""
1000;1;f;10;"{-1.06324939617667e-250,2.47032822920623e-323,0}";"";""

 The good thing is the size of an array is reflected properly, so if I
change the size of an array in java it gets reflected on sql side. Also, if
arrays passed are constant - i.e. values of Integer arrays are still wrong
but stay the same between the calls.
I implemented java.sql.Array but got similaar exception:

STATEMENT:  select vals,vali from tt3(1,10000.0,0.0,0.0);

java.sql.SQLException: Cannot derive a value of class [Ljava.lang.Double;
from an object of class mySqlArrayImpl
        at
org.postgresql.pljava.jdbc.SPIConnection.basicCoersion(SPIConnection.java:718)
        at
org.postgresql.pljava.jdbc.SingleRowWriter.updateObject(SingleRowWriter.java:82)
        at
org.postgresql.pljava.jdbc.ObjectResultSet.updateArray(ObjectResultSet.java:264)
        at
gaia.cu7.omimpl.TimeSeriesImplSQL.assignRowValues(TimeSeriesImplSQL.java:329)
ERROR:  java.sql.SQLException: Cannot derive a value of class
[Ljava.lang.Double; from an object of class mySqlArrayImpl

Could you please comment?

2008/9/23 Krzysztof Nienartowicz <yazuna at gmail.com>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20080926/a544bde0/attachment.html>

---

From: books at ejurka.com (Kris Jurka)
Date: Wed, 8 Oct 2008 20:04:37 -0400 (EDT)

> 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 
array to Datum conversion code to expect Double[] it works, but naturally 
other places that are now passing double[] blow up.  Pljava has some 
smarts about how double and Double are interchangeable, but I'm guessing 
that hasn't propagated all the way through the array code.  I don't really 
understand how the whole type system is supposed to work, so it's 
difficult to figure out how to fix this.

Kris Jurka

---

From: Chapman Flack <[email-masked]>
Date: Thu, 17 Sep 2020 20:20:12 -0400

> 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

---

From: Chapman Flack <[email-masked]>
Date: Mon, 5 Oct 2020 23:25:07 -0400

On 09/17/20 20:20, Chapman Flack wrote:
> off at the moment;

1.5.6 was released last night and resolves the null issue, the "char"[]
value issue, and the must-use-Java's-boxed-array issue.

For a PostgreSQL int4[], for example, the plain ResultSet.getObject(int)
will return a boxed array java.lang.Integer[] and it may contain nulls.

If you want a primitive array, use the getObject overload that takes
an extra Class<?>, and pass int[].class, and you'll get back an int[].
If there were nulls in the input, those elements will be zero in the
int[], and there will be no way to distinguish those from true zero values.
(In the case of a boolean array, nulls will map to false.)

Either a boxed or a primitive array can be passed to updateObject. If a
primitive array, none of its elements will be treated as null.

A multidimensional PostgreSQL array will become a one-dimensional Java
array with the same number of elements. In the other direction, only a
one-dimensional Java array is accepted, and will produce a one-dimensional
PostgreSQL array.

So, that's the support we've got now. Krzysztof expressed some interest
in improving that. I would eventually like to have an interface that
exposes all the properties of PostgreSQL arrays:

* They can have any number of dimensions
* They do not have to be indexed from 0 or from 1; the start index and
  size can be specified for each dimension independently
* Elements can be null

Internally, they are stored with a null bitmap (if nulls are allowed)
followed by a linear sequence of the non-null values. When there is no
null bitmap, the offset of the desired element is a straightforward
calculation from the indices; if there is a null bitmap, it has to
be consulted to determine not just whether the wanted element is null,
but also, if not, how many intervening values are, to subtract from
the offset.

The most interesting challenge is to design a Java API that will
expose that type in the most useful way.

JDBC offers the java.sql.Array interface, but its design assumes
just one-dimensional arrays, indexed from 1. It has methods to
retrieve the values, or a slice of the values, either as a Java
array or as a ResultSet (two columns, the index and the value).

The pgjdbc-ng driver offers a PGArray class that does not really
go beyond implementing java.sql.Array. It has some clever behavior
to present you a PostgreSQL array with multiple dimensions: if
you call getArray(), it gives you a multidimensional Java array,
and if you call getResultSet(), it gives you a ResultSet whose
values are themselves PGArray instances.

Still, the only way offered to create and populate an Array instance
to pass /to/ PostgreSQL is with the Connection.createArrayOf() method,
which assumes one dimension and requires all of the values in the form
of a Java array at once. And for primitives, only the boxed array can
be given. (PGArray has an accessible constructor, but that works
about the same way.)

Experimenting with pgjdbc-ng, I see that if the array you pass to
createArrayOf is a multidimensional Java array, it will make a
multidimensional PostgreSQL array. I get the impression they kind of
shoehorned that into an interface that never really anticipated it.
Still. I think it would be better to be able to specify the dimensions
and index bases, nulls or not, and have the option of supplying values
all at once or over subsequent calls, maybe a slice at a time.

The older pgjdbc driver has a similar class, PgArray (small g), again
little changed from java.sql.Array. I see that it, too, will accept
a multidimensional Java array and do the right thing, but again, it
needs the whole thing at once. pgjdbc's PGConnection has an overload
of createArrayOf that takes Object rather than Object[], so that
primitive arrays can be passed.

There seems to be a wide-open field for designing an API that presents
PostgreSQL's array capabilities nicely.

So much for designing the API to expose. As for doing the marshalling/
unmarshalling, PL/Java can provide you a VarlenaWrapper.Input over an
existing PostgreSQL array, and a VarlenaWrapper.Output for supplying
an array to PostgreSQL.

(I will have to add that ability, and control it with a permission, as
it will be a footgun, but those classes exist now).

VarlenaWrapper.Input is little more than a java.nio.ByteBuffer over
the array in PostgreSQL native memory. Using the various primitive-typed
get... methods of ByteBuffer, you can do the index->offset computations
and read elements freely. That's easy, and you never even have to copy
the thing into Java's heap if you don't want to.

One complication: the java.nio.ByteBuffer API does not make any provision
for invalidating a ByteBuffer. If you have one, you might try to read from
it, even after PostgreSQL has reused that memory. VarlenaWrapper.Input
has pin and unpin methods, and you are on your honor never to read from
the buffer unless you have pinned it at the time, nor to hold it pinned
forever. If you try to pin it after the native object has gone away,
pin() will throw an appropriate exception for you.

VarlenaWrapper.Output looks, at present, like an OutputStream that can
only be written sequentially, again accumulating in PostgreSQL's memory.

That was all that was needed to implement SQLXML, but it might not be
perfect for arrays. It is fine for an array with no nulls; if you can
be fed the elements in sequence you can write them straight out to the
stream and probably have a marshaller written before lunch.

For an array with nulls, because the bitmap is toward the front,
being limited to sequential writing means you would need to know
where all the nulls were before beginning to write values. That
could lead to an awkward API.

The restriction to sequential writing was partly to support the notion
of a VarlenaWrapper.Verifier, a thread that reads the content as you are
writing it to verify the format. That is needed to implement an API like
SQLXML (which allows the caller to supply a String or character stream,
and therefore has to verify it is XML). Clearly it would be no good to
write something acceptable to the verifier, then back up and change it.
Sequential writing also allows the implementation to keep only a small
ByteBuffer windowing the region of the native object currently being
written.

An array marshaller would presumably only write valid arrays and not
offer the caller a "write raw bytes" option needing a verifier. So
it could use the NoOp verifier, and I might be able to relax the
behavior of VarlenaWrapper.Output when the NoOp verifier is used,
Short of full random access, maybe the ability to 'bookmark' a certain
region while writing, like the null bitmap, and return to it later,
would be enough. I could be talked into adding that.

Krzysztof, I think I have a better understanding now of what you
might be doing, having found your 2014 PGCon slides. That was only
shortly after I got involved with PL/Java and I missed your presentation
at the time.

When you say you have tens of terabytes of these arrays, how big
are the individual arrays? Is it a huge number of small arrays?
Are the individual arrays large enough that PG's toaster compresses
them? Or are they below that threshold but packed into larger tuples
that will get toasted? Or are the tuples also smaller than that
threshold, but there are staggering numbers of them?

I'm just, at this point, kind of brainstorming what would be useful
capabilities to have.

Are you in much of a hurry? 1.5.6 is released now and will run on
Java 8, but if you are willing to use Java >= 9 and wait a little
for 1.6.x, that will make many things easier. I may want to get
1.6.0 out the door (shortly), without provisions for this, and
then shoot for a 1.6.1 that will offer a way to expose the
VarlenaWrapper functionality for you.

This does all align with refactoring I have already been wanting to do,
so it could be very useful to have someone with an application ready
to pound on it some.

Regards,
-Chap