Re: Regarding column reordering project for GSoc 2012

On Wed, Mar 21, 2012 at 12:42 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> 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

---------------------------
Hi Merlin,

Thanks for your reply.
Yes,I agree that a pure JNI routine would not be too helpful.I'll try
to hook directly onto the JVM in PL/Java.That way,JNI can help us.

Please let me know how to proceed further.

Atri

--
Regards,

Atri
l'apprenant