Regarding GSoc Application

Hi Atri,

Is there some JDBC API that supports this in newer versions of the API ?

Dave Cramer

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

On Sun, Apr 8, 2012 at 8:56 AM, Dave Cramer <pg@fastcrypt.com> wrote:

Hi Atri,

Is there some JDBC API that supports this in newer versions of the API ?

Didn't parse that question. My understanding is that the only JDBC
features needed are what's already there, to make connections to
databases and execute queries.

The GSoC proposal is here:

https://google-melange.appspot.com/gsoc/proposal/review/google/gsoc2012/atrisharma/1001

merlin

How will the user access this? Will it be a normal query through the
existing API ? Will it be a private postgresql API ?

How will they set it up ? It appears complicated as you have to setup
PL/Java as well

Dave Cramer

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

On Mon, Apr 9, 2012 at 10:47 AM, Dave Cramer <pg@fastcrypt.com> wrote:

How will the user access this? Will it be a normal query through the
existing API ? Will it be a private postgresql API ?

How will they set it up ? It appears complicated as you have to setup
PL/Java as well

Yeah -- it will run through pl/java (at least, that's the idea). What
pl/java brings to the table is well thought out integration of the JVM
to postgres so that you can invoke java as functions from postgres.
PL/java of course is a heavy dependency and non-trivial to set up and
install. But to access the jdbc from postgres I think it's the
easiest way forward. Straight JNI to the JVM from FDW might be a
better/cleaner route but we haven't done the research to see exactly
what's involved there. I suspect that invoking java from postgres is
non trivial any way you slice it and that's not a wheel worth
re-inventing.

In other words, the basic idea is to do two things: a dblink-ish
wrapper for JDBC via pl/java and a FDW wrapper through that via SPI.
Better ideas and criticism are welcome of course.

merlin

On Mon, Apr 9, 2012 at 11:55 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Mon, Apr 9, 2012 at 10:47 AM, Dave Cramer <pg@fastcrypt.com> wrote:

How will the user access this? Will it be a normal query through the
existing API ? Will it be a private postgresql API ?

How will they set it up ? It appears complicated as you have to setup
PL/Java as well

Yeah -- it will run through pl/java (at least, that's the idea). What
pl/java brings to the table is well thought out integration of the JVM
to postgres so that you can invoke java as functions from postgres.
PL/java of course is a heavy dependency and non-trivial to set up and
install.  But to access the jdbc from postgres I think it's the
easiest way forward.  Straight JNI to the JVM from FDW might be a
better/cleaner route but we haven't done the research to see exactly
what's involved there.  I suspect that invoking java from postgres is
non trivial any way you slice it and that's not a wheel worth
re-inventing.

In other words, the basic idea is to do two things: a dblink-ish
wrapper for JDBC via pl/java and a FDW wrapper through that via SPI.
Better ideas and criticism are welcome of course.

merlin

So I'm confused, once they link a file to an FDW can't you just read
it with an normal select ?

What additional functionality will this provide ?

Dave

On Mon, Apr 9, 2012 at 11:14 AM, Dave Cramer <pg@fastcrypt.com> wrote:

So I'm confused, once they link a file to an FDW can't you just read
it with an normal select ?

What additional functionality will this provide ?

Dave

The basic objective is to expose the JDBC to postgres for grabbing
external data. FDW is a C API and JDBC is java routines so the main
challenge is to figure out how to jump from a FDW call into java.
pl/java is one way to solve that problem.

Once done, you should be able to FDW to any jdbc supporting data
source, which is basically everything.

merlin

On 04/09/2012 12:14 PM, Dave Cramer wrote:

So I'm confused, once they link a file to an FDW can't you just read
it with an normal select ?

What additional functionality will this provide ?

I'm confused about what you're confused about. Surely this won't be
linking files to an FDW, but foreign DBMS tables, in anything you can
access via JDBC. All you'll need on the postgres side is the relevant
JDBC driver, so you'd have instant access via standard select queries to
anything you can get a JDBC driver to talk to. That seems to me
something worth having.

I imagine it would look rather like this:

CREATE FOREIGN DATA WRAPPER foodb HANDLER pljava_jdbc_handler
OPTIONS (driver 'jdbc.foodb.org');
CREATE SERVER myfoodb FOREIGN DATA WRAPPER foodb OPTIONS(host
'1.2.3.4', user 'foouser', password 'foopw');
CREATE FOREIGN TABLE footbl (id int, data text) SERVER myfoodb;
SELECT * from footbl;

cheers

andrew

On Mon, Apr 9, 2012 at 12:45 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

On 04/09/2012 12:14 PM, Dave Cramer wrote:

So I'm confused, once they link a file to an FDW can't you just read
it with an normal select ?

What additional functionality will this provide ?

I'm confused about what you're confused about. Surely this won't be linking
files to an FDW, but foreign DBMS tables, in anything you can access via
JDBC. All you'll need on the postgres side is the relevant JDBC driver, so
you'd have instant access via standard select queries to anything you can
get a JDBC driver to talk to. That seems to me something worth having.

I imagine it would look rather like this:

  CREATE FOREIGN DATA WRAPPER foodb HANDLER pljava_jdbc_handler
  OPTIONS (driver 'jdbc.foodb.org');
  CREATE SERVER myfoodb FOREIGN DATA WRAPPER foodb OPTIONS(host
  '1.2.3.4', user 'foouser', password 'foopw');
  CREATE FOREIGN TABLE footbl (id int, data text) SERVER myfoodb;
  SELECT * from footbl;

Well this is certainly more explanation than we have so far.

Is this the intended use case ?

Dave

Dave Cramer <pg@fastcrypt.com> wrote:

Andrew Dunstan <andrew@dunslane.net> wrote:

All you'll need on the postgres side is the relevant JDBC driver,
so you'd have instant access via standard select queries to
anything you can get a JDBC driver to talk to. That seems to me
something worth having.

I imagine it would look rather like this:

CREATE FOREIGN DATA WRAPPER foodb HANDLER pljava_jdbc_handler
OPTIONS (driver 'jdbc.foodb.org');
CREATE SERVER myfoodb FOREIGN DATA WRAPPER foodb OPTIONS(host
'1.2.3.4', user 'foouser', password 'foopw');
CREATE FOREIGN TABLE footbl (id int, data text) SERVER myfoodb;
SELECT * from footbl;

Well this is certainly more explanation than we have so far.

Is this the intended use case ?

That is how I've understood it from the discussion I've seen -- an
FDW to connect to JDBC so that you can wrap anything accessible from
JDBC. The use of pl/Java seems to be the easiest way to get there.

-Kevin

On Mon, Apr 9, 2012 at 10:15 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

On 04/09/2012 12:14 PM, Dave Cramer wrote:

So I'm confused, once they link a file to an FDW can't you just read
it with an normal select ?

What additional functionality will this provide ?

I'm confused about what you're confused about. Surely this won't be linking
files to an FDW, but foreign DBMS tables, in anything you can access via
JDBC. All you'll need on the postgres side is the relevant JDBC driver, so
you'd have instant access via standard select queries to anything you can
get a JDBC driver to talk to. That seems to me something worth having.

I imagine it would look rather like this:

  CREATE FOREIGN DATA WRAPPER foodb HANDLER pljava_jdbc_handler
  OPTIONS (driver 'jdbc.foodb.org');
  CREATE SERVER myfoodb FOREIGN DATA WRAPPER foodb OPTIONS(host
  '1.2.3.4', user 'foouser', password 'foopw');
  CREATE FOREIGN TABLE footbl (id int, data text) SERVER myfoodb;
  SELECT * from footbl;

cheers

andrew

Hi Andrew,

Thanks for going through my proposal and commenting on it.

I think you have hit the nail on the head.We will be connecting the
foreign DBMS tables.The main aim of the project is to wrap JDBC so we
can connect to anything that can be reached through a JDBC URL.

I am considering two paths for doing this:
The first one takes the help of the SPI(Server Programming Interface)
and the second one directly connects through Pl/Java and JNI(Java
Native Interface).

Please let me know your further comments and also,please advise me on
how to proceed further.

Atri
--
Regards,

Atri
l'apprenant

On Mon, Apr 9, 2012 at 12:25 PM, Atri Sharma <atri.jiit@gmail.com> wrote:

On Mon, Apr 9, 2012 at 10:15 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

On 04/09/2012 12:14 PM, Dave Cramer wrote:

So I'm confused, once they link a file to an FDW can't you just read
it with an normal select ?

What additional functionality will this provide ?

I'm confused about what you're confused about. Surely this won't be linking
files to an FDW, but foreign DBMS tables, in anything you can access via
JDBC. All you'll need on the postgres side is the relevant JDBC driver, so
you'd have instant access via standard select queries to anything you can
get a JDBC driver to talk to. That seems to me something worth having.

I imagine it would look rather like this:

  CREATE FOREIGN DATA WRAPPER foodb HANDLER pljava_jdbc_handler
  OPTIONS (driver 'jdbc.foodb.org');
  CREATE SERVER myfoodb FOREIGN DATA WRAPPER foodb OPTIONS(host
  '1.2.3.4', user 'foouser', password 'foopw');
  CREATE FOREIGN TABLE footbl (id int, data text) SERVER myfoodb;
  SELECT * from footbl;

cheers

andrew

Hi Andrew,

Thanks for going through my proposal and commenting on it.

I think you have hit the nail on the head.We will be connecting the
foreign DBMS tables.The main aim of the project is to wrap JDBC so we
can connect to anything that can be reached through a JDBC URL.

I am considering two paths for doing this:
The first one takes the help of the SPI(Server Programming Interface)
and the second one directly connects through Pl/Java and JNI(Java
Native Interface).

Please let me know your further comments and also,please advise me on
how to proceed further.

I think the best way to go is as planned. Step one is to get pl/java
installed and attempt a minimal set of functions that can connect to
and gather data from an external source...let's start with pl/java
'hello world' and go from there. Once done it's time to start
thinking about how the java internals will look like -- we can crib
from dblink for that though.

merlin

On Mon, Apr 9, 2012 at 11:40 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Mon, Apr 9, 2012 at 12:25 PM, Atri Sharma <atri.jiit@gmail.com> wrote:

On Mon, Apr 9, 2012 at 10:15 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

On 04/09/2012 12:14 PM, Dave Cramer wrote:

So I'm confused, once they link a file to an FDW can't you just read
it with an normal select ?

What additional functionality will this provide ?

I'm confused about what you're confused about. Surely this won't be linking
files to an FDW, but foreign DBMS tables, in anything you can access via
JDBC. All you'll need on the postgres side is the relevant JDBC driver, so
you'd have instant access via standard select queries to anything you can
get a JDBC driver to talk to. That seems to me something worth having.

I imagine it would look rather like this:

  CREATE FOREIGN DATA WRAPPER foodb HANDLER pljava_jdbc_handler
  OPTIONS (driver 'jdbc.foodb.org');
  CREATE SERVER myfoodb FOREIGN DATA WRAPPER foodb OPTIONS(host
  '1.2.3.4', user 'foouser', password 'foopw');
  CREATE FOREIGN TABLE footbl (id int, data text) SERVER myfoodb;
  SELECT * from footbl;

cheers

andrew

Hi Andrew,

Thanks for going through my proposal and commenting on it.

I think you have hit the nail on the head.We will be connecting the
foreign DBMS tables.The main aim of the project is to wrap JDBC so we
can connect to anything that can be reached through a JDBC URL.

I am considering two paths for doing this:
The first one takes the help of the SPI(Server Programming Interface)
and the second one directly connects through Pl/Java and JNI(Java
Native Interface).

Please let me know your further comments and also,please advise me on
how to proceed further.

I think the best way to go is as planned.  Step one is to get pl/java
installed and attempt a minimal set of functions that can connect to
and gather data from an external source...let's start with pl/java
'hello world' and go from there.  Once done it's time to start
thinking about how the java internals will look like -- we can crib
from dblink for that though.

merlin

I agree,this should be the correct path.

Atri
--
Regards,

Atri
l'apprenant

On 04/09/2012 01:25 PM, Atri Sharma wrote:

On Mon, Apr 9, 2012 at 10:15 PM, Andrew Dunstan<andrew@dunslane.net> wrote:

On 04/09/2012 12:14 PM, Dave Cramer wrote:

So I'm confused, once they link a file to an FDW can't you just read
it with an normal select ?

What additional functionality will this provide ?

I'm confused about what you're confused about. Surely this won't be linking
files to an FDW, but foreign DBMS tables, in anything you can access via
JDBC. All you'll need on the postgres side is the relevant JDBC driver, so
you'd have instant access via standard select queries to anything you can
get a JDBC driver to talk to. That seems to me something worth having.

I imagine it would look rather like this:

CREATE FOREIGN DATA WRAPPER foodb HANDLER pljava_jdbc_handler
OPTIONS (driver 'jdbc.foodb.org');
CREATE SERVER myfoodb FOREIGN DATA WRAPPER foodb OPTIONS(host
'1.2.3.4', user 'foouser', password 'foopw');
CREATE FOREIGN TABLE footbl (id int, data text) SERVER myfoodb;
SELECT * from footbl;

cheers

andrew

Hi Andrew,

Thanks for going through my proposal and commenting on it.

I think you have hit the nail on the head.We will be connecting the
foreign DBMS tables.The main aim of the project is to wrap JDBC so we
can connect to anything that can be reached through a JDBC URL.

I am considering two paths for doing this:
The first one takes the help of the SPI(Server Programming Interface)
and the second one directly connects through Pl/Java and JNI(Java
Native Interface).

I'd say forget SPI - I don't think it's going to help you here. Just
concentrate on getting the functionality via a PL/Java wrapper. I
wouldn't worry too much about jdbc style URLs either, since logically I
think you'd want to specify the connection parameters via server and FDW
options as in my example above - that way it would be consistent with
other FDWs. But that's a piece of bikeshedding for now. Basically, you
want to implement the handler function to start with.

cheers

andrew

On Tue, Apr 10, 2012 at 8:42 AM, Andrew Dunstan <andrew@dunslane.net> wrote:

I am considering two paths for doing this:
The first one takes the help of the SPI(Server Programming Interface)
and the second one directly connects through Pl/Java and JNI(Java
Native Interface).

I'd say forget SPI - I don't think it's going to help you here. Just
concentrate on getting the functionality via a PL/Java wrapper. I wouldn't
worry too much about jdbc style URLs either, since logically I think you'd
want to specify the connection parameters via server and FDW options as in
my example above - that way it would be consistent with other FDWs. But
that's a piece of bikeshedding for now. Basically, you want to implement the
handler function to start with.

how do you cross from FDW into a pl/java routine without SPI?

merlin

On 04/10/2012 09:48 AM, Merlin Moncure wrote:

On Tue, Apr 10, 2012 at 8:42 AM, Andrew Dunstan<andrew@dunslane.net> wrote:

I am considering two paths for doing this:
The first one takes the help of the SPI(Server Programming Interface)
and the second one directly connects through Pl/Java and JNI(Java
Native Interface).

I'd say forget SPI - I don't think it's going to help you here. Just
concentrate on getting the functionality via a PL/Java wrapper. I wouldn't
worry too much about jdbc style URLs either, since logically I think you'd
want to specify the connection parameters via server and FDW options as in
my example above - that way it would be consistent with other FDWs. But
that's a piece of bikeshedding for now. Basically, you want to implement the
handler function to start with.

how do you cross from FDW into a pl/java routine without SPI?

Add the FDW handler as a sibling function of the function call handler.
At least that would be my first approach to writing a DBI::DBD FDW
wrapper for plperl, which I naturally know rather better than the
PL/Java code.

cheers

andrew

On Tue, Apr 10, 2012 at 9:15 AM, Andrew Dunstan <andrew@dunslane.net> wrote:

On 04/10/2012 09:48 AM, Merlin Moncure wrote:

On Tue, Apr 10, 2012 at 8:42 AM, Andrew Dunstan<andrew@dunslane.net>
 wrote:

I am considering two paths for doing this:
The first one takes the help of the SPI(Server Programming Interface)
and the second one directly connects through Pl/Java and JNI(Java
Native Interface).

I'd say forget SPI - I don't think it's going to help you here. Just
concentrate on getting the functionality via a PL/Java wrapper. I
wouldn't
worry too much about jdbc style URLs either, since logically I think
you'd
want to specify the connection parameters via server and FDW options as
in
my example above - that way it would be consistent with other FDWs. But
that's a piece of bikeshedding for now. Basically, you want to implement
the
handler function to start with.

how do you cross from FDW into a pl/java routine without SPI?

Add the FDW handler as a sibling function of the function call handler. At
least that would be my first approach to writing a DBI::DBD FDW wrapper for
plperl, which I naturally know rather better than the PL/Java code.

right -- well looking at plperl.c, I take it you are meaning to invoke
(or write a similar function to) the plperl_call_handler, right? hm
-- if you could do that then yes, that would work for pl/java as well
and it would bypass the SPI interface. this is avoiding the public
APIs, so it's going to take some time to figure out how to set up the
call and walk the result since I don't know how to do that off the top
of my head.

merlin

On 04/10/2012 10:34 AM, Merlin Moncure wrote:

On Tue, Apr 10, 2012 at 9:15 AM, Andrew Dunstan<andrew@dunslane.net> wrote:

On 04/10/2012 09:48 AM, Merlin Moncure wrote:

On Tue, Apr 10, 2012 at 8:42 AM, Andrew Dunstan<andrew@dunslane.net>
wrote:

I am considering two paths for doing this:
The first one takes the help of the SPI(Server Programming Interface)
and the second one directly connects through Pl/Java and JNI(Java
Native Interface).

I'd say forget SPI - I don't think it's going to help you here. Just
concentrate on getting the functionality via a PL/Java wrapper. I
wouldn't
worry too much about jdbc style URLs either, since logically I think
you'd
want to specify the connection parameters via server and FDW options as
in
my example above - that way it would be consistent with other FDWs. But
that's a piece of bikeshedding for now. Basically, you want to implement
the
handler function to start with.

how do you cross from FDW into a pl/java routine without SPI?

Add the FDW handler as a sibling function of the function call handler. At
least that would be my first approach to writing a DBI::DBD FDW wrapper for
plperl, which I naturally know rather better than the PL/Java code.

right -- well looking at plperl.c, I take it you are meaning to invoke
(or write a similar function to) the plperl_call_handler, right? hm
-- if you could do that then yes, that would work for pl/java as well
and it would bypass the SPI interface. this is avoiding the public
APIs, so it's going to take some time to figure out how to set up the
call and walk the result since I don't know how to do that off the top
of my head.

I don't understand what the heck you're talking about, TBH. From a user
perspective there is nothing to work out. It will look like any other
FDW. The implementor of the FDW handler will have to work out the glue
between postgres and the JVM, but that's not going to be you, right?

cheers

andrew

On Tue, Apr 10, 2012 at 9:47 AM, Andrew Dunstan <andrew@dunslane.net> wrote:

I don't understand what the heck you're talking about, TBH. From a user
perspective there is nothing to work out. It will look like any other FDW.

yes, that is correct.

The implementor of the FDW handler will have to work out the glue between
postgres and the JVM, but that's not going to be you, right?

Correct. I think I understand what you're driving at. Basically,
pl/java is the glue. my thinking was inside the FDW callbacks to to do
SPI calls to invoke the pl/java routines. Unlike other fdw
implementations which mostly wrap C libraries -- which makes things
very easy since you can directly jump into the routine for foreign
execution -- a luxury we don't have. We have to invoke java and there
are two basic ways to tie into the java runtime: one is to jump
through SPI via the SQL executor. The other is JNI into the pl/java
jvm which I think you were hinting was the better approach.

Doing an SPI call from a FDW callback is inefficient -- that's an
extra call into the executor (although you can prepare it) and you
have to walk the SPI result just to build it up again in the FDW
iterator. A JNI solution instead would jump into the jvm and do java
invocation and I believe would drive the difficulty of this project up
a couple of notches whereas a SPI approach utilizes a well documented
interface. We're not stuck on the approach though -- I'm pushing Atri
to get the environment set up so we can explore alternative solutions.

In other words, our proposal is basically pretty similar to what you'd
end up with if you wrapped dblink into a fdw making dblink calls
inside the fdw over spi.

merlin

Merlin Moncure <mmoncure@gmail.com> writes:

... We have to invoke java and there
are two basic ways to tie into the java runtime: one is to jump
through SPI via the SQL executor. The other is JNI into the pl/java
jvm which I think you were hinting was the better approach.

Hm? SPI doesn't know anything about Java either.

regards, tom lane