Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-17 Thread Atri Sharma
On Wed, Apr 11, 2012 at 8:17 PM, Atri Sharma atri.j...@gmail.com wrote:

 On Tue, Apr 10, 2012 at 10:41 PM, Atri Sharma atri.j...@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


Hi all,

I just had a small doubt I wanted to clarify.I initially said in my
proposal that I would be using SPI for getting the FDW API to call
Pl/Java functions,but now,after discussion with the community,I have
changed the approach and I will be using JNI Invocation API instead
for the same purpose.

I wanted to know if I need to send a modified proposal for the same or
the original proposal shall still hold good.

Atri

-- 
Regards,

Atri
l'apprenant

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-17 Thread Andrew Dunstan



On 04/17/2012 09:12 AM, Atri Sharma wrote:
I just had a small doubt I wanted to clarify.I initially said in my 
proposal that I would be using SPI for getting the FDW API to call 
Pl/Java functions,but now,after discussion with the community,I have 
changed the approach and I will be using JNI Invocation API instead 
for the same purpose. I wanted to know if I need to send a modified 
proposal for the same or the original proposal shall still hold good.


These are not the right forums to ask this question. You need to ask on 
the GSOC specific forum.


cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-17 Thread Atri Sharma
On Tue, Apr 17, 2012 at 7:37 PM, Andrew Dunstan and...@dunslane.net wrote:


 On 04/17/2012 09:12 AM, Atri Sharma wrote:

 I just had a small doubt I wanted to clarify.I initially said in my
 proposal that I would be using SPI for getting the FDW API to call Pl/Java
 functions,but now,after discussion with the community,I have changed the
 approach and I will be using JNI Invocation API instead for the same
 purpose. I wanted to know if I need to send a modified proposal for the same
 or the original proposal shall still hold good.


 These are not the right forums to ask this question. You need to ask on the
 GSOC specific forum.

 cheers

 andrew

Thanks Andrew.I'll keep that in mind.


-- 
Regards,

Atri
l'apprenant

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-11 Thread Hitoshi Harada
On Tue, Apr 10, 2012 at 8:41 PM, Atri Sharma atri.j...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-11 Thread Atri Sharma

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-11 Thread Merlin Moncure
On Tue, Apr 10, 2012 at 10:41 PM, Atri Sharma atri.j...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-11 Thread Atri Sharma
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



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-11 Thread Atri Sharma

On Tue, Apr 10, 2012 at 10:41 PM, Atri Sharma atri.j...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-10 Thread Andrew Dunstan



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

On Mon, Apr 9, 2012 at 10:15 PM, Andrew Dunstanand...@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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-10 Thread Merlin Moncure
On Tue, Apr 10, 2012 at 8:42 AM, Andrew Dunstan and...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-10 Thread Andrew Dunstan



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

On Tue, Apr 10, 2012 at 8:42 AM, Andrew Dunstanand...@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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-10 Thread Merlin Moncure
On Tue, Apr 10, 2012 at 9:15 AM, Andrew Dunstan and...@dunslane.net wrote:


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

 On Tue, Apr 10, 2012 at 8:42 AM, Andrew Dunstanand...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-10 Thread Andrew Dunstan



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

On Tue, Apr 10, 2012 at 9:15 AM, Andrew Dunstanand...@dunslane.net  wrote:


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

On Tue, Apr 10, 2012 at 8:42 AM, Andrew Dunstanand...@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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-10 Thread Merlin Moncure
On Tue, Apr 10, 2012 at 9:47 AM, Andrew Dunstan and...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-10 Thread Tom Lane
Merlin Moncure mmonc...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-10 Thread Dave Cramer
On Tue, Apr 10, 2012 at 11:25 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Merlin Moncure mmonc...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-10 Thread Atri Sharma
On Tue, Apr 10, 2012 at 8:55 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Merlin Moncure mmonc...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-10 Thread Tom Lane
Atri Sharma atri.j...@gmail.com writes:
 On Tue, Apr 10, 2012 at 8:55 PM, Tom Lane t...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-10 Thread Andrew Dunstan



On 04/10/2012 11:36 AM, Tom Lane wrote:

Atri Sharmaatri.j...@gmail.com  writes:

On Tue, Apr 10, 2012 at 8:55 PM, Tom Lanet...@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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-10 Thread Merlin Moncure
On Tue, Apr 10, 2012 at 10:36 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Atri Sharma atri.j...@gmail.com writes:
 On Tue, Apr 10, 2012 at 8:55 PM, Tom Lane t...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-10 Thread Merlin Moncure
On Tue, Apr 10, 2012 at 11:07 AM, Merlin Moncure mmonc...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-10 Thread Atri Sharma
  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-09 Thread Merlin Moncure
On Sun, Apr 8, 2012 at 8:56 AM, Dave Cramer p...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-09 Thread Dave Cramer
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 mmonc...@gmail.com wrote:
 On Sun, Apr 8, 2012 at 8:56 AM, Dave Cramer p...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-09 Thread Merlin Moncure
On Mon, Apr 9, 2012 at 10:47 AM, Dave Cramer p...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-09 Thread Dave Cramer
On Mon, Apr 9, 2012 at 11:55 AM, Merlin Moncure mmonc...@gmail.com wrote:
 On Mon, Apr 9, 2012 at 10:47 AM, Dave Cramer p...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-09 Thread Merlin Moncure
On Mon, Apr 9, 2012 at 11:14 AM, Dave Cramer p...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-09 Thread Andrew Dunstan



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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-09 Thread Dave Cramer
On Mon, Apr 9, 2012 at 12:45 PM, Andrew Dunstan and...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-09 Thread Kevin Grittner
Dave Cramer p...@fastcrypt.com wrote: 
 Andrew Dunstan and...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-09 Thread Atri Sharma
On Mon, Apr 9, 2012 at 10:15 PM, Andrew Dunstan and...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-09 Thread Merlin Moncure
On Mon, Apr 9, 2012 at 12:25 PM, Atri Sharma atri.j...@gmail.com wrote:
 On Mon, Apr 9, 2012 at 10:15 PM, Andrew Dunstan and...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-09 Thread Atri Sharma
On Mon, Apr 9, 2012 at 11:40 PM, Merlin Moncure mmonc...@gmail.com wrote:
 On Mon, Apr 9, 2012 at 12:25 PM, Atri Sharma atri.j...@gmail.com wrote:
 On Mon, Apr 9, 2012 at 10:15 PM, Andrew Dunstan and...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [JDBC] Regarding GSoc Application

2012-04-08 Thread Dave Cramer
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.j...@gmail.com wrote:
 Hi All,



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



 Atri

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers