[Pljava-dev] allowing *inheritance* from pgjdbc or pgjdbc-ng ?

Hi,

I've been putting some work recently into PL/Java.

It provides a direct JDBC interface for Java code on the backend,
using SPI under the hood. The JDBC code was clearly copy/pasted from
pgjdbc long ago, then adapted to the needs of running on the backend.

I'll be preaching to the choir if I say upkeep of a JDBC interface
is a lot of work. Easy or hard new workloads at any time can be dumped
onto the project from either end. New PostgreSQL release? New protocol
details, metadata queries, etc., have to be worked out, and somebody
has to do that. New Java release? New JDBC interfaces to be implemented
and figured out, and somebody has to do that. And with the copied/
pasted code, somebody has to do that for pgjdbc *and* somebody has
to do it for PL/Java, and I'm sure neither project has so many
committers as to say "hey, no problem, we love duplicating work,
gives us something to do!"

What I would really like to experiment with is how possible it
might be for PL/Java to provide JDBC by *inheritance* from pgjdbc
(or pgjdbc-ng) instead of by copy and paste. Add a dependency to
the Maven pom and it's off to the races.

I'm sure the devil is in all the little things I can't think of
up front, but in dreamland it basically works by having some way
to instantiate a Jdbc...Connection with a different underlying
ProtocolConnection subclass (one that uses SPI instead of v2 or v3,
returns TRANSACTION_OPEN from getTransactionState() at all times,
returns its own kind of QueryExecutor, etc.). I'm using the pgjdbc
names here.

Whether to try with pgjdbc or pgjdbc-ng, I don't know. The first
might be easier because of the original code similarity. The second
might be easier because the code is new and streamlined. It looks like
in either case, I would need to submit a few pull requests upstream
just to make it possible to instantiate and subclass the necessary
things with a different connection subclass.

If I made some pull requests of that sort, would you (pgjdbc or
pgjdbc-ng) be generally open to considering them? That's my basic
question #1 here.

If the idea works, it could increase the chance we can *share* effort
on improvements that benefit two projects, instead of just going on
making the same improvements twice.

Another plus on the PL/Java side could be that, if someone had backend
code with some reason to connect to another database, or have a side
connection to the same one, the same JDBC implementation would already
be there, just using a remote connection URL instead of
jdbc:default:connection, and the behavior would be as consistent as
possible given the different connection properties.

After question #1 I may have some smaller ones, one I can think of
is about logging. PL/Java and pgjdbc-ng both use java.util.logging,
pgjdbc has a homegrown org.postgresql.core.Logger, for historical
reasons I'm guessing? Would converging on the java.util.logging
API be a thinkable thought, or just way too much work, or
objected to for some philosophical or technical reason?

I guess that's enough for now, thanks for your attention.

-Chap

Hi Chap,

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.

PL/Java is all about closeness to the data. If you remove the advantages gained by that closeness, then why not use Java
in the client instead?

That said, I fully sympathize with the motivating factors that you bring up. Then again, the JDBC interfaces doesn't
change that often and when they do, the changes tend to be manageable. I'm not sure I agree that it's worth sacrificing
performance to make them easier to maintain.

Basically it all boils down to what the performance implications are. If you have a solution that words well enough to
test that and produce some numbers, then I think that would be a great input.

- thomas

On 2015-09-20 21:42, Chapman Flack wrote:
> Hi,
>
> I've been putting some work recently into PL/Java.
>
> It provides a direct JDBC interface for Java code on the backend,
> using SPI under the hood. The JDBC code was clearly copy/pasted from
> pgjdbc long ago, then adapted to the needs of running on the backend.
>
> I'll be preaching to the choir if I say upkeep of a JDBC interface
> is a lot of work. Easy or hard new workloads at any time can be dumped
> onto the project from either end. New PostgreSQL release? New protocol
> details, metadata queries, etc., have to be worked out, and somebody
> has to do that. New Java release? New JDBC interfaces to be implemented
> and figured out, and somebody has to do that. And with the copied/
> pasted code, somebody has to do that for pgjdbc *and* somebody has
> to do it for PL/Java, and I'm sure neither project has so many
> committers as to say "hey, no problem, we love duplicating work,
> gives us something to do!"
>
> What I would really like to experiment with is how possible it
> might be for PL/Java to provide JDBC by *inheritance* from pgjdbc
> (or pgjdbc-ng) instead of by copy and paste. Add a dependency to
> the Maven pom and it's off to the races.
>
> I'm sure the devil is in all the little things I can't think of
> up front, but in dreamland it basically works by having some way
> to instantiate a Jdbc...Connection with a different underlying
> ProtocolConnection subclass (one that uses SPI instead of v2 or v3,
> returns TRANSACTION_OPEN from getTransactionState() at all times,
> returns its own kind of QueryExecutor, etc.). I'm using the pgjdbc
> names here.
>
> Whether to try with pgjdbc or pgjdbc-ng, I don't know. The first
> might be easier because of the original code similarity. The second
> might be easier because the code is new and streamlined. It looks like
> in either case, I would need to submit a few pull requests upstream
> just to make it possible to instantiate and subclass the necessary
> things with a different connection subclass.
>
> If I made some pull requests of that sort, would you (pgjdbc or
> pgjdbc-ng) be generally open to considering them? That's my basic
> question #1 here.
>
> If the idea works, it could increase the chance we can *share* effort
> on improvements that benefit two projects, instead of just going on
> making the same improvements twice.
>
> Another plus on the PL/Java side could be that, if someone had backend
> code with some reason to connect to another database, or have a side
> connection to the same one, the same JDBC implementation would already
> be there, just using a remote connection URL instead of
> jdbc:default:connection, and the behavior would be as consistent as
> possible given the different connection properties.
>
> After question #1 I may have some smaller ones, one I can think of
> is about logging. PL/Java and pgjdbc-ng both use java.util.logging,
> pgjdbc has a homegrown org.postgresql.core.Logger, for historical
> reasons I'm guessing? Would converging on the java.util.logging
> API be a thinkable thought, or just way too much work, or
> objected to for some philosophical or technical reason?
>
> I guess that's enough for now, thanks for your attention.
>
> -Chap
> _______________________________________________
> Pljava-dev mailing list
> Pljava-dev at lists.pgfoundry.org
> http://lists.pgfoundry.org/mailman/listinfo/pljava-dev

I would certainly entertain any changes to pgjdbc-ng to allow this as long as it is properly decoupled and doesn’t cause loads of changes into the driver main.

The ng driver was designed with pluggable protocols as well. So in either case it seems implementing a new implementation of the protocol with the SPI is the obvious direction to go and should leave the the main driver code unchanged.

> On Sep 20, 2015, at 12:42 PM, Chapman Flack <chap(at)anastigmatix(dot)net> wrote:
>
> Hi,
>
> I've been putting some work recently into PL/Java.
>
> It provides a direct JDBC interface for Java code on the backend,
> using SPI under the hood. The JDBC code was clearly copy/pasted from
> pgjdbc long ago, then adapted to the needs of running on the backend.
>
> I'll be preaching to the choir if I say upkeep of a JDBC interface
> is a lot of work. Easy or hard new workloads at any time can be dumped
> onto the project from either end. New PostgreSQL release? New protocol
> details, metadata queries, etc., have to be worked out, and somebody
> has to do that. New Java release? New JDBC interfaces to be implemented
> and figured out, and somebody has to do that. And with the copied/
> pasted code, somebody has to do that for pgjdbc *and* somebody has
> to do it for PL/Java, and I'm sure neither project has so many
> committers as to say "hey, no problem, we love duplicating work,
> gives us something to do!"
>
> What I would really like to experiment with is how possible it
> might be for PL/Java to provide JDBC by *inheritance* from pgjdbc
> (or pgjdbc-ng) instead of by copy and paste. Add a dependency to
> the Maven pom and it's off to the races.
>
> I'm sure the devil is in all the little things I can't think of
> up front, but in dreamland it basically works by having some way
> to instantiate a Jdbc...Connection with a different underlying
> ProtocolConnection subclass (one that uses SPI instead of v2 or v3,
> returns TRANSACTION_OPEN from getTransactionState() at all times,
> returns its own kind of QueryExecutor, etc.). I'm using the pgjdbc
> names here.
>
> Whether to try with pgjdbc or pgjdbc-ng, I don't know. The first
> might be easier because of the original code similarity. The second
> might be easier because the code is new and streamlined. It looks like
> in either case, I would need to submit a few pull requests upstream
> just to make it possible to instantiate and subclass the necessary
> things with a different connection subclass.
>
> If I made some pull requests of that sort, would you (pgjdbc or
> pgjdbc-ng) be generally open to considering them? That's my basic
> question #1 here.
>
> If the idea works, it could increase the chance we can *share* effort
> on improvements that benefit two projects, instead of just going on
> making the same improvements twice.
>
> Another plus on the PL/Java side could be that, if someone had backend
> code with some reason to connect to another database, or have a side
> connection to the same one, the same JDBC implementation would already
> be there, just using a remote connection URL instead of
> jdbc:default:connection, and the behavior would be as consistent as
> possible given the different connection properties.
>
> After question #1 I may have some smaller ones, one I can think of
> is about logging. PL/Java and pgjdbc-ng both use java.util.logging,
> pgjdbc has a homegrown org.postgresql.core.Logger, for historical
> reasons I'm guessing? Would converging on the java.util.logging
> API be a thinkable thought, or just way too much work, or
> objected to for some philosophical or technical reason?
>
> I guess that's enough for now, thanks for your attention.
>
> -Chap

I'm would say the same. The challenge of course will be finding the happy
medium

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 20 September 2015 at 18:34, Kevin Wooten <kdubb(at)me(dot)com> wrote:

> I would certainly entertain any changes to pgjdbc-ng to allow this as long
> as it is properly decoupled and doesn’t cause loads of changes into the
> driver main.
>
> The ng driver was designed with pluggable protocols as well. So in either
> case it seems implementing a new implementation of the protocol with the
> SPI is the obvious direction to go and should leave the the main driver
> code unchanged.
>
> > On Sep 20, 2015, at 12:42 PM, Chapman Flack <chap(at)anastigmatix(dot)net>
> wrote:
> >
> > Hi,
> >
> > I've been putting some work recently into PL/Java.
> >
> > It provides a direct JDBC interface for Java code on the backend,
> > using SPI under the hood. The JDBC code was clearly copy/pasted from
> > pgjdbc long ago, then adapted to the needs of running on the backend.
> >
> > I'll be preaching to the choir if I say upkeep of a JDBC interface
> > is a lot of work. Easy or hard new workloads at any time can be dumped
> > onto the project from either end. New PostgreSQL release? New protocol
> > details, metadata queries, etc., have to be worked out, and somebody
> > has to do that. New Java release? New JDBC interfaces to be implemented
> > and figured out, and somebody has to do that. And with the copied/
> > pasted code, somebody has to do that for pgjdbc *and* somebody has
> > to do it for PL/Java, and I'm sure neither project has so many
> > committers as to say "hey, no problem, we love duplicating work,
> > gives us something to do!"
> >
> > What I would really like to experiment with is how possible it
> > might be for PL/Java to provide JDBC by *inheritance* from pgjdbc
> > (or pgjdbc-ng) instead of by copy and paste. Add a dependency to
> > the Maven pom and it's off to the races.
> >
> > I'm sure the devil is in all the little things I can't think of
> > up front, but in dreamland it basically works by having some way
> > to instantiate a Jdbc...Connection with a different underlying
> > ProtocolConnection subclass (one that uses SPI instead of v2 or v3,
> > returns TRANSACTION_OPEN from getTransactionState() at all times,
> > returns its own kind of QueryExecutor, etc.). I'm using the pgjdbc
> > names here.
> >
> > Whether to try with pgjdbc or pgjdbc-ng, I don't know. The first
> > might be easier because of the original code similarity. The second
> > might be easier because the code is new and streamlined. It looks like
> > in either case, I would need to submit a few pull requests upstream
> > just to make it possible to instantiate and subclass the necessary
> > things with a different connection subclass.
> >
> > If I made some pull requests of that sort, would you (pgjdbc or
> > pgjdbc-ng) be generally open to considering them? That's my basic
> > question #1 here.
> >
> > If the idea works, it could increase the chance we can *share* effort
> > on improvements that benefit two projects, instead of just going on
> > making the same improvements twice.
> >
> > Another plus on the PL/Java side could be that, if someone had backend
> > code with some reason to connect to another database, or have a side
> > connection to the same one, the same JDBC implementation would already
> > be there, just using a remote connection URL instead of
> > jdbc:default:connection, and the behavior would be as consistent as
> > possible given the different connection properties.
> >
> > After question #1 I may have some smaller ones, one I can think of
> > is about logging. PL/Java and pgjdbc-ng both use java.util.logging,
> > pgjdbc has a homegrown org.postgresql.core.Logger, for historical
> > reasons I'm guessing? Would converging on the java.util.logging
> > API be a thinkable thought, or just way too much work, or
> > objected to for some philosophical or technical reason?
> >
> > I guess that's enough for now, thanks for your attention.
> >
> > -Chap
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>

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

On 2015-09-21 04:41, Chapman Flack wrote:
> 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. :)
We definitely do not care about that one since using it would imply using more than one thread.

> 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?
Yes. I haven't been into these details for quite some time (as you might notice :-) ). This is starting to make a lot of
sense. Watch out for threads though. PL/Java *must* use the caller thread when accessing the SPI layer.

- thomas