Re: Regarding column reordering project for GSoc 2012 - Mailing list pgsql-hackers

On Tue, Mar 20, 2012 at 11:57 AM, Atri Sharma <atri.jiit@gmail.com> wrote:
>
>
> -----Original Message-----
> From: Merlin Moncure [mailto:mmoncure@gmail.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.jiit@gmail.com> wrote:
>> -----Original Message-----
>> From: Merlin Moncure [mailto:mmoncure@gmail.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.linnakangas@enterprisedb.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