Thread: Regarding GSoc Application

Regarding GSoc Application

From
"Atri Sharma"
Date:

Hi All,

 

I submitted a GSoc application yesterday. Please review it and let me know if anyone needs any clarifications.

 

Atri

Re: Regarding GSoc Application

From
Dave Cramer
Date:
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 Sat, Apr 7, 2012 at 7:07 AM, Atri Sharma <atri.jiit@gmail.com> wrote:
> Hi All,
>
>
>
> I submitted a GSoc application yesterday. Please review it and let me know
> if anyone needs any clarifications.
>
>
>
> Atri

Re: [HACKERS] Regarding GSoc Application

From
Merlin Moncure
Date:
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

Re: [HACKERS] Regarding GSoc Application

From
Dave Cramer
Date:
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 11:45 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> 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

Re: [HACKERS] Regarding GSoc Application

From
Merlin Moncure
Date:
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

Re: [HACKERS] Regarding GSoc Application

From
Dave Cramer
Date:
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

Re: [HACKERS] Regarding GSoc Application

From
Merlin Moncure
Date:
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

Re: [HACKERS] Regarding GSoc Application

From
Andrew Dunstan
Date:

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

Re: [HACKERS] Regarding GSoc Application

From
Dave Cramer
Date:
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

Re: [HACKERS] Regarding GSoc Application

From
"Kevin Grittner"
Date:
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

Re: [HACKERS] Regarding GSoc Application

From
Atri Sharma
Date:
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

Re: [HACKERS] Regarding GSoc Application

From
Merlin Moncure
Date:
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

Re: [HACKERS] Regarding GSoc Application

From
Atri Sharma
Date:
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

Re: [HACKERS] Regarding GSoc Application

From
Andrew Dunstan
Date:

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

Re: [HACKERS] Regarding GSoc Application

From
Merlin Moncure
Date:
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

Re: [HACKERS] Regarding GSoc Application

From
Andrew Dunstan
Date:

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

Re: [HACKERS] Regarding GSoc Application

From
Merlin Moncure
Date:
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

Re: [HACKERS] Regarding GSoc Application

From
Andrew Dunstan
Date:

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

Re: [HACKERS] Regarding GSoc Application

From
Merlin Moncure
Date:
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

Re: [HACKERS] Regarding GSoc Application

From
Tom Lane
Date:
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

Re: [HACKERS] Regarding GSoc Application

From
Dave Cramer
Date:
On Tue, Apr 10, 2012 at 11:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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

Having pl/java as a dependancy here makes this a very complex
"feature" to setup.
The potential benefits are quite minimal since almost any decent ETL
tool can handle multiple data sources

Dave

Re: [HACKERS] Regarding GSoc Application

From
Atri Sharma
Date:
On Tue, Apr 10, 2012 at 8:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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

Hi Tom,

We plan to call SQL through SPI from the FDW,which in turn would call
the Pl/Java routine.

We are working on the JNI Invocation API approach also.We will be
currently researching the differences between the two approaches(SPI
and JNI).

Atri

--
Regards,

Atri
l'apprenant

Re: [HACKERS] Regarding GSoc Application

From
Tom Lane
Date:
Atri Sharma <atri.jiit@gmail.com> writes:
> On Tue, Apr 10, 2012 at 8:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Hm?  SPI doesn't know anything about Java either.

> We plan to call SQL through SPI from the FDW,which in turn would call
> the Pl/Java routine.

If you're saying that every Java function that the FDW needs would have
to be exposed as a SQL function, that seems like a pretty high-risk
(not to mention low performance) approach.  Not only do you have to
design a SQL representation for every datatype you need, but you have to
be sure that you do not have any security holes arising from
unscrupulous users calling those SQL functions manually with arguments
of their choosing.

            regards, tom lane

Re: [HACKERS] Regarding GSoc Application

From
Andrew Dunstan
Date:

On 04/10/2012 11:36 AM, Tom Lane wrote:
> Atri Sharma<atri.jiit@gmail.com>  writes:
>> On Tue, Apr 10, 2012 at 8:55 PM, Tom Lane<tgl@sss.pgh.pa.us>  wrote:
>>> Hm?  SPI doesn't know anything about Java either.
>> We plan to call SQL through SPI from the FDW,which in turn would call
>> the Pl/Java routine.
> If you're saying that every Java function that the FDW needs would have
> to be exposed as a SQL function, that seems like a pretty high-risk
> (not to mention low performance) approach.  Not only do you have to
> design a SQL representation for every datatype you need, but you have to
> be sure that you do not have any security holes arising from
> unscrupulous users calling those SQL functions manually with arguments
> of their choosing.
>
>


Yeah. I think this design is horribly baroque and unnecessary. SPI is
for talking SQL. It's completely in the way of a straight-forward
implementation of this feature IMNSHO.

cheers

andrew

Re: [HACKERS] Regarding GSoc Application

From
Merlin Moncure
Date:
On Tue, Apr 10, 2012 at 10:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Atri Sharma <atri.jiit@gmail.com> writes:
>> On Tue, Apr 10, 2012 at 8:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Hm?  SPI doesn't know anything about Java either.
>
>> We plan to call SQL through SPI from the FDW,which in turn would call
>> the Pl/Java routine.
>
> If you're saying that every Java function that the FDW needs would have
> to be exposed as a SQL function, that seems like a pretty high-risk
> (not to mention low performance) approach.  Not only do you have to
> design a SQL representation for every datatype you need, but you have to
> be sure that you do not have any security holes arising from
> unscrupulous users calling those SQL functions manually with arguments
> of their choosing.

Hm, well, for data type representation, an 'all text' representation
would avoid that requirement (although could certainly add it back in
later for performance reasons).  That's not all that different from
what the other fdw projects are doing -- mostly wrapping
BuildTupleFromCStrings and such.  But totally agree that for top
performance you'd need direct native transfer.  I'm in the 'perfect is
the enemy of the good' mindset here.

I think the security argument is mostly bogus -- pl/java is already
well into the untrusted side of things and I was figuring being able
to bypass the fdw layer and invoke the functions dblink style was a
feature, not a bug.

But adding up all the comments I see healthy skepticism that running
through SPI is the proper approach and it is noted.  So the way
forward is a more direct hook to the jvm or to go back to the drawing
board I suppose.  I agree that JNI isn't required -- we're going to
have to study the pl/java system a bit to determine the best way to
hook in.  This could end up getting us into the 'biting of more than
can chew' territory admittedly, but Atri is enthusiastic and wants to
give it a go.

Additionally, Dave is skeptical that pl/java dependency is a good
foundation for a generally useful library.  I'm not buying that --
pl/java is the 'best of class' for implementing java inside the
database that I'm aware of.  I see absolutely no reason why it
couldn't be packaged as an extension -- the project is a bit dusty and
needs some TLC but does what it does very well.  I also respectfully
disagree that the presence of high quality ETL engines eliminate the
usefulness of a direct database to database transfer mechanism.  I
personally never go the ETL route when I can just dblink the data
across and do the massaging in SQL.  Other developers may think
differently of course.  Of course, if there was a good way to
implement jdbc/fdw without using pl/java that would be good to know.

merlin

Re: [HACKERS] Regarding GSoc Application

From
Merlin Moncure
Date:
On Tue, Apr 10, 2012 at 11:07 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
>  I agree that JNI isn't required -- we're going to
> have to study the pl/java system a bit to determine the best way to
> hook in.  This could end up getting us into the 'biting of more than
> can chew' territory admittedly, but Atri is enthusiastic and wants to
> give it a go.

Well. maybe I spoke too soon...JNI is probably the best route.  Since
SPI is off the table, all we're really pulling in from pl/java is the
(non-trivial) proper installation of a jvm into a postgres process.
pl/java is essentially a wrapper to JNI that does postgres to jni type
conversion and builds a database driven class lookup system that
allows building the java environment inside the database.   That part
is not needed at all; since we're not going to install our routines as
pl/java installed they will be installed into a separate and more
typical class path from a jvm point of view. There are no exposed
routines in pljava.so that allow for easy manual invocation of a java
routine -- it's all pretty much hardwired to the language function
call handler system.

This begs the question about why pl/java should be used at all. Well,
as I said the jvm insertion into postgres is non-trivial so that alone
is worth something.  Maybe though we should just rip out the jvm guts
that we really need (mostly some parts of backend.c and some of the
type files) and do a completely standalone jni wrapper.  pl/java's
build system is a mess anyways so we're not losing all that much in
trying to go off on our own and do something.  OTOH, throwing a build
system on top of the growing pile of things to do is turning this into
a daunting project vs the (admittedly cobbled together) approach I was
hoping to do earlier.  Anyways, it's really Atri's call if he's
comfortable proceeding.

merlin

Re: [HACKERS] Regarding GSoc Application

From
"Atri Sharma"
Date:
>>  I agree that JNI isn't required -- we're going to
>> have to study the pl/java system a bit to determine the best way to
>> hook in.  This could end up getting us into the 'biting of more than
>> can chew' territory admittedly, but Atri is enthusiastic and wants to
>> give it a go.
>
>Well. maybe I spoke too soon...JNI is probably the best route.  Since
>SPI is off the table, all we're really pulling in from pl/java is the
>(non-trivial) proper installation of a jvm into a postgres process.
>pl/java is essentially a wrapper to JNI that does postgres to jni type
>conversion and builds a database driven class lookup system that
>allows building the java environment inside the database.   That part
>is not needed at all; since we're not going to install our routines as
>pl/java installed they will be installed into a separate and more
>typical class path from a jvm point of view. There are no exposed
>routines in pljava.so that allow for easy manual invocation of a java
>routine -- it's all pretty much hardwired to the language function
>call handler system.
>
>This begs the question about why pl/java should be used at all. Well,
>as I said the jvm insertion into postgres is non-trivial so that alone
>is worth something.  Maybe though we should just rip out the jvm guts
>that we really need (mostly some parts of backend.c and some of the
>type files) and do a completely standalone jni wrapper.  pl/java's
>build system is a mess anyways so we're not losing all that much in
>trying to go off on our own and do something.  OTOH, throwing a build
>system on top of the growing pile of things to do is turning this into
>a daunting project vs the (admittedly cobbled together) approach I was
>hoping to do earlier.  Anyways, it's really Atri's call if he's
>comfortable proceeding.

Hi All,

I think we are back on the initial approach I proposed(hooking directly into
the JVM and executing Java code that calls JDBC).I think the best way to do
this is create a JVM that executes the Java code and give the control of the
JVM to the native API.

I agree,the only need of Pl/Java that is apparent here is the need of the
Java internals(JDK et al).If we set them up independently,then,we can have
the FDW wrapping JDBC directly through JNI.JNI would call pure Java
functions to connect to the JDBC.

I think we can proceed with this.Once we are done with the API calling Java
functions,I think the rest of the path is easily mapped(writing Java
functions to connect to JDBC).

Please let me know your opinions on this.

Atri


Re: [HACKERS] Regarding GSoc Application

From
John R Pierce
Date:
On 04/10/12 8:41 PM, Atri Sharma wrote:
> I agree,the only need of Pl/Java that is apparent here is the need of the
> Java internals(JDK et al).If we set them up independently,then,we can have
> the FDW wrapping JDBC directly through JNI.JNI would call pure Java
> functions to connect to the JDBC.

PL/Java also provides for passing SQL arguments in various data types
to/from your java code.    not usinig pl/java would mean you'd have to
reinvent all those wheels, they exist now in a standard mannner, why not
use them?

as I see it, your proposal could be implemented as a set of pljava
functions callable from SQL that would in turn invoke JDBC to connect to
and fetch data from your foreign database, then return it to the calling
SQL procedure, much as dblink does now for pg to pg database links.



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast


Re: [HACKERS] Regarding GSoc Application

From
"Atri Sharma"
Date:
Hi John,

Yes,I agree,that can be done,but we had an extensive discussion on it
yesterday and Andrew and Tom believe that would pose serious security issues
as any malicious user can change the arguments sent to the SQL and cause
problems.

Atri

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of John R Pierce
Sent: 11 April 2012 10:00
To: pgsql-jdbc@postgresql.org
Subject: Re: [HACKERS] [JDBC] Regarding GSoc Application

On 04/10/12 8:41 PM, Atri Sharma wrote:
> I agree,the only need of Pl/Java that is apparent here is the need of the
> Java internals(JDK et al).If we set them up independently,then,we can have
> the FDW wrapping JDBC directly through JNI.JNI would call pure Java
> functions to connect to the JDBC.

PL/Java also provides for passing SQL arguments in various data types
to/from your java code.    not usinig pl/java would mean you'd have to
reinvent all those wheels, they exist now in a standard mannner, why not
use them?

as I see it, your proposal could be implemented as a set of pljava
functions callable from SQL that would in turn invoke JDBC to connect to
and fetch data from your foreign database, then return it to the calling
SQL procedure, much as dblink does now for pg to pg database links.








Re: [HACKERS] Regarding GSoc Application

From
John R Pierce
Date:
On 04/10/12 9:36 PM, Atri Sharma wrote:
> Hi John,
>
> Yes,I agree,that can be done,but we had an extensive discussion on it
> yesterday and Andrew and Tom believe that would pose serious security issues
> as any malicious user can change the arguments sent to the SQL and cause
> problems.
>

I'm not sure what "change the arguments sent to SQL" means.   A
malicious user with sufficient privileges can do all sorts of damage,
and there's not much much you can do about it short of not letting
malicious users have privileges.

your foreign data wrapper code should probably require that the user who
creates a FDW connection to an external database have adequate
permissions.   the foreign database servr already has its own
authentication hoops tha this FDW user will have to provide.




--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast


Re: [HACKERS] Regarding GSoc Application

From
"Atri Sharma"
Date:
>I'm not sure what "change the arguments sent to SQL" means.   A
>malicious user with sufficient privileges can do all sorts of damage,
>and there's not much much you can do about it short of not letting
>malicious users have privileges.
>
>your foreign data wrapper code should probably require that the user who
>creates a FDW connection to an external database have adequate
>permissions.   the foreign database servr already has its own
>authentication hoops tha this FDW user will have to provide.

Hi John,

Please find the reply from Tom on the idea of SQL-Pl/Java calls:

This was what I wrote:

> We plan to call SQL through SPI from the FDW,which in turn would call
> the Pl/Java routine.

This was what Tom replied:

*******

If you're saying that every Java function that the FDW needs would have to
be exposed as a SQL function, that seems like a pretty high-risk (not to
mention low performance) approach.  Not only do you have to design a SQL
representation for every datatype you need, but you have to be sure that you
do not have any security holes arising from unscrupulous users calling those
SQL functions manually with arguments of their choosing.

************

Please let me know your opinion on this.

Atri






Re: [HACKERS] Regarding GSoc Application

From
John R Pierce
Date:
On 04/10/12 10:44 PM, Atri Sharma wrote:
> We plan to call SQL through SPI from the FDW,which in turn would call
> >  the Pl/Java routine.

that seems overly complex.   SPI is an internal mechanism used by C
functions that are called from SQL

why not just implement it as....

user application -> SQL -> PLjava FDW functions -> pljava code -> JDBC
-> foreign database

(and of course, back to return the data from said foreign database)

this would look to the user very much like dblink looks now, and have
similar security issues and mitigations.


--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast


Re: [HACKERS] Regarding GSoc Application

From
"Atri Sharma"
Date:
>
>that seems overly complex.   SPI is an internal mechanism used by C
>functions that are called from SQL
>
>why not just implement it as....
>
>user application -> SQL -> PLjava FDW functions -> pljava code -> JDBC
>-> foreign database
>
>(and of course, back to return the data from said foreign database)
>

Hi John,

Hmm.That sounds good,but where does the FDW come in?Will the user
application interact with the FDW,and FDW handle the SQL calls and
everything else from there i.e. FDW wraps the entire process,right?

Also,will it solve Tom's concern?

Atri


Re: [HACKERS] Regarding GSoc Application

From
Hitoshi Harada
Date:
On Tue, Apr 10, 2012 at 8:41 PM, Atri Sharma <atri.jiit@gmail.com> wrote:
> Hi All,
>
> I think we are back on the initial approach I proposed(hooking directly into
> the JVM and executing Java code that calls JDBC).I think the best way to do
> this is create a JVM that executes the Java code and give the control of the
> JVM to the native API.
>
> I agree,the only need of Pl/Java that is apparent here is the need of the
> Java internals(JDK et al).If we set them up independently,then,we can have
> the FDW wrapping JDBC directly through JNI.JNI would call pure Java
> functions to connect to the JDBC.
>
> I think we can proceed with this.Once we are done with the API calling Java
> functions,I think the rest of the path is easily mapped(writing Java
> functions to connect to JDBC).
>
> Please let me know your opinions on this.
>

I think Multicorn is a good example, which invokes Python from FDW
routines though it is not using PL/Python.

http://multicorn.org/

Thanks,
--
Hitoshi Harada

Re: [HACKERS] Regarding GSoc Application

From
John R Pierce
Date:
On 04/10/12 11:09 PM, Atri Sharma wrote:
>> why not just implement it as....
>> >
>> >user application ->  SQL ->  PLjava FDW functions ->  pljava code ->  JDBC
>> >->  foreign database
>> >
>> >(and of course, back to return the data from said foreign database)
>> >
> Hi John,
>
> Hmm.That sounds good,but where does the FDW come in?Will the user
> application interact with the FDW,and FDW handle the SQL calls and
> everything else from there i.e. FDW wraps the entire process,right?

see dblink ofr an example API
http://www.postgresql.org/docs/current/static/dblink.html

the 'pljava fdw functions' are the publicly visible pljava functions
implementing your api... while my 'pljava code' are additional
functions/methods you use internally that aren't visible externally



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast


Re: [HACKERS] Regarding GSoc Application

From
"Atri Sharma"
Date:
>
>I think Multicorn is a good example, which invokes Python from FDW
>routines though it is not using PL/Python.
>
>http://multicorn.org/
>
>

Hi Hitoshi,

Thanks for the link.

You mean,I should try to build something like Multicorn for Java?

Atri


Re: [HACKERS] Regarding GSoc Application

From
"Atri Sharma"
Date:
>see dblink ofr an example API
>http://www.postgresql.org/docs/current/static/dblink.html
>
>the 'pljava fdw functions' are the publicly visible pljava functions
>implementing your api... while my 'pljava code' are additional
>functions/methods you use internally that aren't visible externally
>
>

Hi John,

Thanks for the link and advice.

Ok,now I think I got your point.The Pl/Java FDW functions are what are used
for the public FDW API....while Pl/Java code actually does the job,but it is
hidden from the user.The user actually never gets to talk to the SQL part of
the FDW,so he cannot cause much harm,right?

If my understanding is correct,I think it will be a sweet solution.

Please let me know your opinion.

Atri




Re: [HACKERS] Regarding GSoc Application

From
Merlin Moncure
Date:
On Tue, Apr 10, 2012 at 10:41 PM, Atri Sharma <atri.jiit@gmail.com> wrote:
>>Well. maybe I spoke too soon...JNI is probably the best route.  Since
>>SPI is off the table, all we're really pulling in from pl/java is the
>>(non-trivial) proper installation of a jvm into a postgres process.
>>pl/java is essentially a wrapper to JNI that does postgres to jni type
>>conversion and builds a database driven class lookup system that
>>allows building the java environment inside the database.   That part
>>is not needed at all; since we're not going to install our routines as
>>pl/java installed they will be installed into a separate and more
>>typical class path from a jvm point of view. There are no exposed
>>routines in pljava.so that allow for easy manual invocation of a java
>>routine -- it's all pretty much hardwired to the language function
>>call handler system.
>>
>>This begs the question about why pl/java should be used at all. Well,
>>as I said the jvm insertion into postgres is non-trivial so that alone
>>is worth something.  Maybe though we should just rip out the jvm guts
>>that we really need (mostly some parts of backend.c and some of the
>>type files) and do a completely standalone jni wrapper.  pl/java's
>>build system is a mess anyways so we're not losing all that much in
>>trying to go off on our own and do something.  OTOH, throwing a build
>>system on top of the growing pile of things to do is turning this into
>>a daunting project vs the (admittedly cobbled together) approach I was
>>hoping to do earlier.  Anyways, it's really Atri's call if he's
>>comfortable proceeding.
>
> Hi All,
>
> I think we are back on the initial approach I proposed(hooking directly into
> the JVM and executing Java code that calls JDBC).I think the best way to do
> this is create a JVM that executes the Java code and give the control of the
> JVM to the native API.
>
> I agree,the only need of Pl/Java that is apparent here is the need of the
> Java internals(JDK et al).If we set them up independently,then,we can have
> the FDW wrapping JDBC directly through JNI.JNI would call pure Java
> functions to connect to the JDBC.
>
> I think we can proceed with this.Once we are done with the API calling Java
> functions,I think the rest of the path is easily mapped(writing Java
> functions to connect to JDBC).

yeah -- it sound plausible.  I think the next step is to pull one of
the fdw projects that is working and currently builds with pgxs. Make
sure it is bsd licensed and that you retain the original copyright in
the code (feel free to add your own).   The mysql_fdw project for
example is a good candidate.  After that we need to strip out all the
mysql specific parts so that we have a basic skeleton of the fdw
wrapper that still builds.   From there we will need to build in the
jni calls as well as jvm initialization code we are going to more or
less directly copy from pl/java, but one thing at a time:  IMO,
getting the jvm installed and executing a 'hello world' jni is a good
milestone to reach before getting into all the mechanics of the remote
querying.

The jvm initialization code in pl/java we are going to use is in
src/C/pljava/backend.c.  A lot of the initialization work done there
is irrelevant to what we are trying to do but there are some very
important bits like the on_proc_exit handler that does cleanup when
the postgres process exits.  Ideally we can drop just the stuff we
need from there and get our project to compile.

merlin

Re: [HACKERS] Regarding GSoc Application

From
"Atri Sharma"
Date:
Hi all,

In continuation to the discussion regarding my JDBC wrapping FDW,I have been
talking to members of the community and I have two approaches on which I
would request your suggestions and opinions:



>I think we are back on the initial approach I proposed(hooking directly
into the JVM and executing Java code that calls JDBC).
>I think the best way to do this is create a JVM that executes the Java code
and give the control of the JVM to the native API.

>I agree,the only need of Pl/Java that is apparent here is the need of the
Java internals(JDK et al).If we set them up >independently,then,we can have
the FDW wrapping JDBC directly through JNI.JNI would call pure Java
functions to connect to the >JDBC.

In the above context,I think if we directly wrap JNI and call pure Java
routines(not using Pl/Java codes directly),we can build something on the
lines of Multicorn(a nice suggestion by Hitoshi) http://multicorn.org/

On the other hand,as suggested by John,we can use the Pl/Java routines that
already exist to build our FDW's API.This shall be the flow:


user application -> SQL -> PLjava FDW functions -> pljava code -> JDBC
-> foreign database

The user will have to pass security levels and permissions before being able
to get to the FDW.This would look to the user very much like dblink looks
now, and have similar security issues and mitigations.

Please let me know how to proceed further.

Atri



Re: [HACKERS] Regarding GSoc Application

From
"Atri Sharma"
Date:
On Tue, Apr 10, 2012 at 10:41 PM, Atri Sharma <atri.jiit@gmail.com> wrote:
>>>Well. maybe I spoke too soon...JNI is probably the best route.  Since
>>>SPI is off the table, all we're really pulling in from pl/java is the
>>>(non-trivial) proper installation of a jvm into a postgres process.
>>>pl/java is essentially a wrapper to JNI that does postgres to jni type
>>>conversion and builds a database driven class lookup system that
>>>allows building the java environment inside the database.   That part
>>>is not needed at all; since we're not going to install our routines as
>>>pl/java installed they will be installed into a separate and more
>>>typical class path from a jvm point of view. There are no exposed
>>>routines in pljava.so that allow for easy manual invocation of a java
>>>routine -- it's all pretty much hardwired to the language function
>>>call handler system.
>>>
>>>This begs the question about why pl/java should be used at all. Well,
>>>as I said the jvm insertion into postgres is non-trivial so that alone
>>>is worth something.  Maybe though we should just rip out the jvm guts
>>>that we really need (mostly some parts of backend.c and some of the
>>>type files) and do a completely standalone jni wrapper.  pl/java's
>>>build system is a mess anyways so we're not losing all that much in
>>>trying to go off on our own and do something.  OTOH, throwing a build
>>>system on top of the growing pile of things to do is turning this into
>>>a daunting project vs the (admittedly cobbled together) approach I was
>>>hoping to do earlier.  Anyways, it's really Atri's call if he's
>>>comfortable proceeding.
>>
>> Hi All,
>>
>> I think we are back on the initial approach I proposed(hooking directly
into
>> the JVM and executing Java code that calls JDBC).I think the best way to
do
>> this is create a JVM that executes the Java code and give the control of
the
>> JVM to the native API.
>>
>> I agree,the only need of Pl/Java that is apparent here is the need of the
>> Java internals(JDK et al).If we set them up independently,then,we can
have
>> the FDW wrapping JDBC directly through JNI.JNI would call pure Java
>> functions to connect to the JDBC.
>>
>> I think we can proceed with this.Once we are done with the API calling
Java
>> functions,I think the rest of the path is easily mapped(writing Java
>> functions to connect to JDBC).
>
>yeah -- it sound plausible.  I think the next step is to pull one of
>the fdw projects that is working and currently builds with pgxs. Make
>sure it is bsd licensed and that you retain the original copyright in
>the code (feel free to add your own).   The mysql_fdw project for
>example is a good candidate.  After that we need to strip out all the
>mysql specific parts so that we have a basic skeleton of the fdw
>wrapper that still builds.   From there we will need to build in the
>jni calls as well as jvm initialization code we are going to more or
>less directly copy from pl/java, but one thing at a time:  IMO,
>getting the jvm installed and executing a 'hello world' jni is a good
>milestone to reach before getting into all the mechanics of the remote
>querying.
>
>The jvm initialization code in pl/java we are going to use is in
>src/C/pljava/backend.c.  A lot of the initialization work done there
>is irrelevant to what we are trying to do but there are some very
>important bits like the on_proc_exit handler that does cleanup when
>the postgres process exits.  Ideally we can drop just the stuff we
>need from there and get our project to compile.
>

I agree,atm I'll work on getting JNI set up and to build a JVM using it and
getting the "Hello,World" running in it in PostGreSQl.

I'll keep you posted,

Atri