[Pljava-dev] I remembered why we might want bytecode scalar types

I mentioned a bytecode scalar type a few weeks ago and I just remember why
I thought they were important.

AFAIK right now we can install a jar and define functions that call methods
in it but we cannot write SP directly in java. That is, I can't write

CREATE FUNCTION get_colors() RETURNS SETOF varchar AS $$
BEGIN
List<String> colors = Arrays.asList("red", "green", "blue");
return colors;
END $$
LANGUAGE JAVA;

and have it automatically translated to

public class GetColorsVoid {
public Iterator<String> getColors() {
List<String> colors = Arrays.asList("red", "green", "blue");
return colors.iterator();
}
}

then compiled and stored somewhere in the classpath.

The first big win is that developers don't need to know the details of how
things fit together. E.g., they can return a Collection instead of having
to learn to return an Iterator instead. (Not that it's difficult to learn
this, it's just one more thing to deal with.)

The second big win is that devops don't need to create the infrastructure
to produce, maintain, and distribute jars if they only need a handful of
functions. Obviously nobody should write a large application this way but
sometimes you only need a handful of functions.

The compiler itself isn't an issue. Eclipse had an embedded one years ago
and I believe there are several more available now.

The translation shouldn't be a big problem if the first round only supports
primitive types. The SETOF is a little more complex but you can directly
translate the original code, use inspection to determine if it returns a
Collection, and if so add a facade that wraps the original method and
converts it to an iterator.

The problem is where do you put the results. I think other dbs put them
into the database itself as a bytecode object. Hence my earlier question.

Upon reflection it would probably be possible to define a Foreign Data
Wrapper for jar files. Write to the jar file and kick a custom classloader
to tell it that there's new content.

This brings up a second thought....

Bear
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20150809/8ae959b3/attachment.html>

Bear Giles wrote:

> CREATE FUNCTION get_colors() RETURNS SETOF varchar AS $$
> BEGIN
> List<String> colors = Arrays.asList("red", "green", "blue");
> return colors;
> END $$
> LANGUAGE JAVA;

I'll see your in-situ Java code, and raise you JSR-223. If we stick to
Java 6 and up, there's already the machinery to support a bunch of
dynamic scripting languages right out of the box, and one of them,
for that matter, JavaScript, is already /in/ the box.

If we add a piece that allows 'CREATE LANGUAGE foo ...' using a
generic javax.script invocation handler and whatever script-engine jars
have been put on the path, then PL/Java suddenly becomes PL/polyglot
for not much work, and that could be pretty neat.

DO LANGUAGE 'javascript' $$
...
$$;

I saw a blog post with some javascript examples using jdbc directly,
or using the authors' own library, jOOQ:

http://blog.jooq.org/2014/06/06/java-8-friday-javascript-goes-sql-with-nashorn-and-jooq/

(The examples are for a normal client connection and don't illustrate
anything you'd feel especially compelled to move to the backend, but
they give the flavor of the thing.)

With a lot of scripting languages more tailored to expressing some
quick idea in a few lines, they might have a wider audience than
CREATE FUNCTION LANGUAGE 'java' with the code inline. (Also, I know
Thomas put a high priority on following the SQL/JRT standard, which
does the jar thing.)

On the other hand, if we had JSR-223, I wonder how easily you could
write your "language inlinejava" and drop it in as a jar as another
scripting language. (We might want to permit some javax.script extension
for persisting compilation artifacts like class files. A 'validator'
handler for the language could expire them when a new CREATE FUNCTION
is done.) I like the way you autogenerate the Java declarations based
on the SQL types. Sort of the exact complement of how the DDR
generator now writes you the SQL part based on the Java types. Then
you'd have your choice of which half of the work you'd rather not do. :)

I need more head-wrapping around the security policy considerations
before anybody should expect a pull request....

-Chap