Re: Regarding column reordering project for GSoc 2012

On Tue, Mar 20, 2012 at 12:34 PM, Atri Sharma <atri(dot)jiit(at)gmail(dot)com> wrote:
>
>
> -----Original Message-----
> From: Merlin Moncure [mailto:mmoncure(at)gmail(dot)com]
> Sent: 20 March 2012 22:50
> To: Atri Sharma
> Cc: Heikki Linnakangas; Alvaro Herrera; Daniel Farina; Andrew Dunstan; Dave
> Page; Pg Hackers
> Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012
>
> On Tue, Mar 20, 2012 at 11:57 AM, Atri Sharma <atri(dot)jiit(at)gmail(dot)com> wrote:
>>
>>
>> -----Original Message-----
>> From: Merlin Moncure [mailto:mmoncure(at)gmail(dot)com]
>> Sent: 20 March 2012 20:52
>> To: Atri Sharma
>> Cc: Heikki Linnakangas; Alvaro Herrera; Daniel Farina; Andrew Dunstan;
> Dave
>> Page; Pg Hackers
>> Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012
>>
>> On Tue, Mar 20, 2012 at 6:58 AM, Atri Sharma <atri(dot)jiit(at)gmail(dot)com> wrote:
>>> -----Original Message-----
>>> From: Merlin Moncure [mailto:mmoncure(at)gmail(dot)com]
>>> Sent: 20 March 2012 03:15
>>> To: Heikki Linnakangas
>>> Cc: Alvaro Herrera; Atri Sharma; Daniel Farina; Andrew Dunstan; Dave
> Page;
>>> Pg Hackers
>>> Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012
>>>
>>> On Mon, Mar 19, 2012 at 2:49 PM, Heikki Linnakangas
>>> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>>>> On 19.03.2012 21:29, Alvaro Herrera wrote:
>>>>>
>>>>>
>>>>> Excerpts from Atri Sharma's message of lun mar 19 16:20:09 -0300 2012:
>>>>>
>>>>>> I was just going through PGfoundry and I think I will be able to work
>>>>>> on the PL/Java project.Please let me know If I can proceed further on
>>>>>> ot for GSOC and also,if currently there is any work that needs to be
>>>>>> done on PL/java.
>>>>>
>>>>>
>>>>> Hm, I'm not sure on PL/Java.  As far as I know, it is a dead project.
>>>>
>>>>
>>>> I haven't followed PL/java either, but it doesn't look quite dead to me.
>>> The
>>>> last release was in September 2011, and there are active discussions on
>>> the
>>>> mailing lists. If it's not completely up-to-date with latest JDK and
>>>> PostgreSQL releases, perhaps bringing it up-to-date and then adding some
>>>> missing functionality would be a useful GSoC project.
>>>>
>>>> I would suggest that you ask on the pl/java mailing list if there is
>>>> something suitable for a GSoC project there, and if one of the pl/java
>>>> developers would be willing to mentor.
>>>
>>> pl/java works pretty well and is somewhat widely used although it
>>> might need some more active maintainers.  just jumping into the
>>> project and nailing some old bugs and getting the juices flowing would
>>> be a tremendous help, as well as the less glamorous work of
>>> documentation and regular status updates.
>>>
>>> one pl/java based project that IMO is just screaming to be done is a
>>> pl/java based FDW (wrapping JDBC) that would approximately reproduce
>>> dblink...maybe with some extra features geared towards ETL type jobs
>>> like a row based callback for transformations in java.
>>>
>>> Merlin
>>>
>>>
>>> ---------------------------
>>> Hi Merlin
>>>
>>> Thanks for the idea.
>>>
>>> Could you please elaborate more on the project idea? It would be a great
>>> help for me.
>>
>> sure:
>> first start here: http://wiki.postgresql.org/wiki/Foreign_data_wrappers
>> and take a look at all the various fdw projects.  they all utilize the
>> high level interfaces that postgresql provides to incorporate external
>> datasources and expose them you can query them as if they were views
>> or tables.  as you can see, this interface is popular with the
>> proliferation of projects to expose this or that.  what i'm thinking
>> is you make a fdw that invokes pl/java routines that make jdbc calls
>> out to external databases.  for fetching data and building sets.  as
>> long as you stick to vanilla jdbc calls, you then have a fdw that can
>> gather data from pretty much anything you can access via jdbc url
>> which is quite nice.
>>
>> The fdw API is a C api, so you need to figure out how to proxy to your
>> pl/java calls where the real work is done -- I  haven't done that so
>> I'm not sure if this is even technically feasible.  If you can make
>> SPI calls from your fdw routines, that's one path through.   Tou have
>> lots of previous work to crib from though and hopefully there's a path
>> through.  As far as the ETL stuff I was talking about -- that was just
>> me thinking out loud...better to just get the basic mechanics working
>> up front..  For now, if you're interested in doing this, start
>> thinking about how to marry the fdw interface to pl/java...one you get
>> 'hello world' there, it's all about where you want tot take it.
>>
>> This is an interesting project -- you should entertain no illusions of
>> it making it into core, but if done right and completed it will raise
>> visibility of both pl/java and postgresql.
>>
>> Merlin
>>
>>
>> -----------------------
>> Hi Merlin,
>>
>> First of all,thanks a lot for the extensive reply and explanation.
>>
>> The project seems very exciting,and I would consider it and honour to be
>> able to do it for PostGreSQL.
>>
>> Basically,from what I understood,I will be building a fdw which will
> invoke
>> PL/Java routines which will make JDBC calls to external databases.So,the
> fdw
>> can be used to access any database that supports JDBC.That will be truly
>> awesome.
>>
>> For connecting the C API and Pl/Java code,we can use JNI(Java Native
>> Interface).It's not too stable,but it can work if taken care of.
>
> That's backwards -- the C code will invoke java, not the other way
> around -- so JNI is no help.  SPI is a C interface that allows you to
> (in C) invoke any function in the database, including those written in
> pl/java.  That's a possible path to doing this but it's fairly
> circuitous since pl/java wold be building the result set only to have
> the fdw api tear it down again and rebuild it and there may be other
> problems.  The main issue here would be to figure out how to hook into
> java the best/fastest way from C code.
>
> Merlin
>
> ---------------
> Hi Merlin,
>
> I am a bit confused here.Please correct me if I am wrong.
>
> http://www.codeproject.com/Articles/22881/How-to-Call-Java-Functions-from-C-
> Using-JNI
>
> I found this article.Please see it and guide me.
>
> Waiting for your reply,

well that might work, but you'd have to think about how this
integrates with pl/java which is control of the JVM. ne of the things
pl/java does is to manage all the complicated mechanics of running a
jvm inside of postgres -- i think a pure JNI route would probably mean
not using pl/java. I could be wrong though -- some research and
experimentation might be in order.

merlin