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

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