From: chap at anastigmatix.net (Chapman Flack)
Date: Sun, 20 Sep 2015 22:41:28 -0400
Subject: [Pljava-dev] allowing *inheritance* from pgjdbc or pgjdbc-ng ?
In-Reply-To: <55FF1558.9060103@tada.se>
References: <55FF1558.9060103@tada.se>
Message-ID: <55FF6E58.406@anastigmatix.net>

Thomas Hallgren wrote:
> One major (perhaps the major) reason for the current design was to
> avoid streaming data. The current SPIResultSet reads  its data
> directly from the Tuple provided from SPI. Writing it to a stream,
> just to then parse it again in the same  process (and same thread),
> will undoubtedly result in some overhead. Especially when dealing with
> large binary objects  and/or large result sets.

I am thinking (perhaps naively for now, as I haven't written any code
yet) that this can be achieved. In the pgjdbc case, what the visible
API-implementing classes wrap is an
org.postgresql.core.ProtocolConnection (an interface, responsible for
the actual communication with the database). That interface is
implemented by v3 or v2 frontend-backend ProtocolConnectionImpl
classes, and yes, _they_ stream data. But there's really nothing
about the ProtocolConnection interface itself that seems to rule out
providing another class that implements it and uses SPI.

https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/core/ProtocolConnection.java

sendQueryCancel(), maybe. And we probably just don't care about that
one. :)

The ProtocolHandler is what gives you a QueryExecutor instance.
So naturally a PLJavaSPIProtocolConnection would give you a
PLJavaSPIQueryExecutor.  I'm not quite sure yet who instantiates
implementations of ResultCursor, but that's just a matter of reading
the code more. Is this starting to sound like it could turn out to be
possible to do what we want?

The big (if possible) win would be narrowing the scope of what
PL/Java actually has to do from scratch to the few necessary methods
on ProtocolConnection and friends for executing queries and moving
tuples about.  Somewhere, at the part of the interface that exposes
elements as certain types, we need to insert an implementation
using fooGetDatum, datumGetFoo. I'm not sure it will turn out to be
easy, but I don't see anything (yet) that makes it look impossible.

There's a whole vast remaining edifice of JDBC that might mostly
Just Work once that much is provided. Think even just of
DatabaseMetaData ... five *thousand* lines of Java and SQL queries
intimately tied to pg_catalog details and deep deep knowledge of
exactly what PG version changed the default for behavior Foo from off
to on. Eeeeyechhh! Who wants to maintain that monster? :) Any bets on
how many of the answers given by our version are currently correct,
for recent versions of PG? It was last touched a year ago, and that
was to add one new method that returns false and one that throws
NotSupported. ;) Before that? Four years ago. That implemented nine
new methods by having them all throw NotSupported.

(I guess that turns "how many of the answers currently *given*" into
kind of a trick question. :)

But if we could *inherit* from the more actively maintained pgjdbc
one and let it talk over a PLJavaSPIProtocolConnection to make the
queries it needs, it might be almost complete. Maybe we need to
override half a dozen methods to return false instead of true or
something because PL/Java is different, but that's still a giant win.

There should be *plenty* of time for thinking about this. :) It would
be foolhardy to start any such extensive mods without good regression
testing in place, and as issue #11 still says, we haven't got that yet.
But I've been thinking about how to get there, which seems like the
really important and achievable near-term goal.

-Chap