Thread: Regarding column reordering project for GSoc 2012
Hi All, I am Atri Sharma.I am a C developer with experience in database designing and database management systems.I have worked with MySql,Oracle and other database systems.I have designed databases for various projects as well. I would be interested in developing the column reordering project by a single ALTER command.I have an idea in mind that would solve theproblem in a very easy way. Please let me know how to proceed further on this. My IRC handle is digitalknight.I work on IST. -- Regards, Atri l'apprenant
On 03/17/2012 04:39 AM, Atri Sharma wrote: > Hi All, > > I am Atri Sharma.I am a C developer with experience in database > designing and database management systems.I have worked with > MySql,Oracle and other database systems.I have designed databases for > various projects as well. > > I would be interested in developing the column reordering project by a > single ALTER command.I have an idea in mind that would solve > theproblem in a very easy way. > > Please let me know how to proceed further on this. > You should review the history on this. See <http://archives.postgresql.org/message-id/1324412114-sup-9608@alvh.no-ip.org> as a good place to start. You should be aware that anything that doesn't follow the previous discussions is likely to be rejected. But if you can do something that does implement what was previously discussed, that would be awesome. cheers andrew
Thanks Andrew. I went through the link. Here is my idea: I believe we can build a "middle" layer that encapsulates the physical details from the user and gives him/her a VIEW that is actually generated by the system itself. That means, we have a system such as: LOGICAL LAYER -------------- MIDDLE LAYER -------------- PHYSICAL LAYER. Now, the punch would be, the middle layer would be on the client. The middle layer is more of an interface for the user which he/she can completely customize. Now, what is actually happening is that each component is mapped to their physical address. I am planning to make a struct which will be the mapping between the physical address and the logical address.I have not yet decided on the design of the struct,but I plan an id , the type and the physical address.When a component is used in the middle layer,it is represented in an instantiation of the struct which is unchangeable throughout.Whenever we need to customize the middle layer,we just need to reorder the components. For our specific case,the reordering of table columns,we need not change the schema or the physical ordering.All we need to do is,reorder the components.One ALTER statement(with added customized clauses) will do the reordering.The mapping remains constant,because,the struct instantiations of the components remain constant,hence,the mapping remains constant and we can access the physical address of any component at any time. Please let me know your feedback on the idea. Atri -----Original Message----- From: Andrew Dunstan [mailto:andrew@dunslane.net] Sent: 17 March 2012 21:30 To: Atri Sharma Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 On 03/17/2012 04:39 AM, Atri Sharma wrote: > Hi All, > > I am Atri Sharma.I am a C developer with experience in database > designing and database management systems.I have worked with > MySql,Oracle and other database systems.I have designed databases for > various projects as well. > > I would be interested in developing the column reordering project by a > single ALTER command.I have an idea in mind that would solve > theproblem in a very easy way. > > Please let me know how to proceed further on this. > You should review the history on this. See <http://archives.postgresql.org/message-id/1324412114-sup-9608@alvh.no-ip.or g> as a good place to start. You should be aware that anything that doesn't follow the previous discussions is likely to be rejected. But if you can do something that does implement what was previously discussed, that would be awesome. cheers andrew
Hi all, In follow up to my last mail, attached below, for performance, we can build a graph with edges having weight in sense of performance wise and padding saving wise . Then , we can build a minimal spanning tree, and the tree generated would be our order of columns . When any changes are made , changes in the graph will be done and minimal spanning tree would be recomputed. Atri -----Original Message----- From: Atri Sharma [mailto:atri.jiit@gmail.com] Sent: 17 March 2012 22:43 To: 'Andrew Dunstan' Cc: 'pgsql-hackers@postgresql.org' Subject: RE: [HACKERS] Regarding column reordering project for GSoc 2012 Thanks Andrew. I went through the link. Here is my idea: I believe we can build a "middle" layer that encapsulates the physical details from the user and gives him/her a VIEW that is actually generated by the system itself. That means, we have a system such as: LOGICAL LAYER -------------- MIDDLE LAYER -------------- PHYSICAL LAYER. Now, the punch would be, the middle layer would be on the client. The middle layer is more of an interface for the user which he/she can completely customize. Now, what is actually happening is that each component is mapped to their physical address. I am planning to make a struct which will be the mapping between the physical address and the logical address.I have not yet decided on the design of the struct,but I plan an id , the type and the physical address.When a component is used in the middle layer,it is represented in an instantiation of the struct which is unchangeable throughout.Whenever we need to customize the middle layer,we just need to reorder the components. For our specific case,the reordering of table columns,we need not change the schema or the physical ordering.All we need to do is,reorder the components.One ALTER statement(with added customized clauses) will do the reordering.The mapping remains constant,because,the struct instantiations of the components remain constant,hence,the mapping remains constant and we can access the physical address of any component at any time. Please let me know your feedback on the idea. Atri -----Original Message----- From: Andrew Dunstan [mailto:andrew@dunslane.net] Sent: 17 March 2012 21:30 To: Atri Sharma Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 On 03/17/2012 04:39 AM, Atri Sharma wrote: > Hi All, > > I am Atri Sharma.I am a C developer with experience in database > designing and database management systems.I have worked with > MySql,Oracle and other database systems.I have designed databases for > various projects as well. > > I would be interested in developing the column reordering project by a > single ALTER command.I have an idea in mind that would solve > theproblem in a very easy way. > > Please let me know how to proceed further on this. > You should review the history on this. See <http://archives.postgresql.org/message-id/1324412114-sup-9608@alvh.no-ip.or g> as a good place to start. You should be aware that anything that doesn't follow the previous discussions is likely to be rejected. But if you can do something that does implement what was previously discussed, that would be awesome. cheers andrew
Excerpts from Atri Sharma's message of sáb mar 17 05:39:10 -0300 2012: > Hi All, > > I am Atri Sharma.I am a C developer with experience in database > designing and database management systems.I have worked with > MySql,Oracle and other database systems.I have designed databases for > various projects as well. > > I would be interested in developing the column reordering project by a > single ALTER command.I have an idea in mind that would solve > theproblem in a very easy way. This is far too complex to be done in a GSoC project. I don't see how having a middle layer client-side would fix anything. You'd need to explain how it'd work, but to me that description sounds bogus. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
HI Alvaro, I did send a complete description to the mailing list, based on your past discussions. I don’t know if you got it. Please let me know if I should re-send the mail trail. Atri -----Original Message----- From: Alvaro Herrera [mailto:alvherre@commandprompt.com] Sent: 18 March 2012 00:17 To: Atri Sharma Cc: Pg Hackers Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 Excerpts from Atri Sharma's message of sáb mar 17 05:39:10 -0300 2012: > Hi All, > > I am Atri Sharma.I am a C developer with experience in database > designing and database management systems.I have worked with > MySql,Oracle and other database systems.I have designed databases for > various projects as well. > > I would be interested in developing the column reordering project by a > single ALTER command.I have an idea in mind that would solve > theproblem in a very easy way. This is far too complex to be done in a GSoC project. I don't see how having a middle layer client-side would fix anything. You'd need to explain how it'd work, but to me that description sounds bogus. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Excerpts from Atri Sharma's message of sáb mar 17 15:48:13 -0300 2012: > > HI Alvaro, > I did send a complete description to the mailing list, based on your past discussions. I don’t know if you got it. I read it, but I don't see how is the client involved. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Hi Alvaro, Thanks for going through my description. I was going through the posts,when I realised that if we give each client the flexibility of defining his/her own order withcomplete control,that would be probably the right way(IMHO). I mean,if the user sees,not the physical implementation,but a customizable interface instead,which is mapped to the physicalimplementation,then,we can solve to some extent,the problem of logical/physical decoupling. If you are free,I can come on IRC right now and explain my idea to you. Atri -----Original Message----- From: Alvaro Herrera [mailto:alvherre@commandprompt.com] Sent: 18 March 2012 00:34 To: Atri Sharma Cc: Pg Hackers Subject: RE: [HACKERS] Regarding column reordering project for GSoc 2012 Excerpts from Atri Sharma's message of sáb mar 17 15:48:13 -0300 2012: > > HI Alvaro, > I did send a complete description to the mailing list, based on your past discussions. I don’t know if you got it. I read it, but I don't see how is the client involved. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
<br /><br />On Saturday, March 17, 2012, Atri Sharma <<a href="mailto:atri.jiit@gmail.com">atri.jiit@gmail.com</a>>wrote:<br />> Hi Alvaro,<br />> Thanks for going throughmy description.<br />><br />><br />> I was going through the posts,when I realised that if we give each clientthe flexibility of defining his/her own order with complete control,that would be probably the right way(IMHO).<br/> ><br />> I mean,if the user sees,not the physical implementation,but a customizable interface instead,whichis mapped to the physical implementation,then,we can solve to some extent,the problem of logical/physical decoupling.<br/><br />Anything done in the client bar changing the column it orders on (which ideally should be avoided tooif practical) is going to lead to pain and suffering as some will do it, some won't, and a few might get it wrong.<br/><br />-- <br />Dave Page<br />Blog: <a href="http://pgsnake.blogspot.com" target="_blank">http://pgsnake.blogspot.com</a><br/>Twitter: @pgsnake<br /><br />EnterpriseDB UK: <a href="http://www.enterprisedb.com"target="_blank">http://www.enterprisedb.com</a><br /> The Enterprise PostgreSQL Company<br/><br />
<div class="WordSection1"><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">HiDave,</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><pclass="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Ibelieve,with the ALTER command,we plan to givethe user power to modify ordering . Don’t we?</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><pclass="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Atri</span><pclass="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><div style="border:none;border-top:solid#B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm"><p class="MsoNormal"><b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><spanlang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">Dave Page [mailto:dpage@pgadmin.org] <br /><b>Sent:</b> 18 March2012 00:46<br /><b>To:</b> Atri Sharma<br /><b>Cc:</b> Alvaro Herrera; Pg Hackers<br /><b>Subject:</b> Re: [HACKERS]Regarding column reordering project for GSoc 2012</span></div><p class="MsoNormal"> <p class="MsoNormal" style="margin-bottom:12.0pt"><br/><br />On Saturday, March 17, 2012, Atri Sharma <<a href="mailto:atri.jiit@gmail.com">atri.jiit@gmail.com</a>>wrote:<br />> Hi Alvaro,<br />> Thanks for going throughmy description.<br />><br />><br />> I was going through the posts,when I realised that if we give each clientthe flexibility of defining his/her own order with complete control,that would be probably the right way(IMHO).<br/>><br />> I mean,if the user sees,not the physical implementation,but a customizable interface instead,whichis mapped to the physical implementation,then,we can solve to some extent,the problem of logical/physical decoupling.<br/><br />Anything done in the client bar changing the column it orders on (which ideally should be avoided tooif practical) is going to lead to pain and suffering as some will do it, some won't, and a few might get it wrong.<br/><br />-- <br />Dave Page<br />Blog: <a href="http://pgsnake.blogspot.com" target="_blank">http://pgsnake.blogspot.com</a><br/>Twitter: @pgsnake<br /><br />EnterpriseDB UK: <a href="http://www.enterprisedb.com"target="_blank">http://www.enterprisedb.com</a><br />The Enterprise PostgreSQL Company</div>
On Sat, Mar 17, 2012 at 2:16 PM, Atri Sharma <atri.jiit@gmail.com> wrote: > Hi Dave, > > I believe,with the ALTER command,we plan to give the user power to modify > ordering . Don’t we? > And how is that different from a view that orders the columns as the user wishes? -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
On 03/17/2012 03:07 PM, Atri Sharma wrote: > Hi Alvaro, > Thanks for going through my description. > > > I was going through the posts,when I realised that if we give each client the flexibility of defining his/her own orderwith complete control,that would be probably the right way(IMHO). > > I mean,if the user sees,not the physical implementation,but a customizable interface instead,which is mapped to the physicalimplementation,then,we can solve to some extent,the problem of logical/physical decoupling. I think your design is a complete non-starter. Please review what was previously discussed. If you're not going to attempt that then my strong advice is to try a different project. > > If you are free,I can come on IRC right now and explain my idea to you. The place to discuss designs is on the mailing lists. That's the way we do things. The vast majority of hackers are not to be found in IRC. cheers andrew
<br /><br />On Saturday, March 17, 2012, Atri Sharma <<a href="mailto:atri.jiit@gmail.com">atri.jiit@gmail.com</a>>wrote:<br />> Hi Dave,<br />><br />> <br />><br/>> I believe,with the ALTER command,we plan to give the user power to modify ordering . Don’t we?<br /><br />Probably,but that's not relevant to my point as far as I can see - I was objecting to your suggestion that the "middlelayer" that translates the physical to logical ordering and vice-versa (if I understood your description correctly)be in the client.<br /><br />-- <br />Dave Page<br />Blog: <a href="http://pgsnake.blogspot.com" target="_blank">http://pgsnake.blogspot.com</a><br/>Twitter: @pgsnake<br /><br />EnterpriseDB UK: <a href="http://www.enterprisedb.com"target="_blank">http://www.enterprisedb.com</a><br /> The Enterprise PostgreSQL Company<br/><br />
On 03/17/2012 04:42 PM, Dave Page wrote: > > > On Saturday, March 17, 2012, Atri Sharma <atri.jiit@gmail.com > <mailto:atri.jiit@gmail.com>> wrote: > > Hi Dave, > > > > > > > > I believe,with the ALTER command,we plan to give the user power to > modify ordering . Don’t we? > > Probably, but that's not relevant to my point as far as I can see - I > was objecting to your suggestion that the "middle layer" that > translates the physical to logical ordering and vice-versa (if I > understood your description correctly) be in the client. > > Mine too. We don't want a column ordering that's different for everyone. That's a recipe for mass confusion. We want to be able to mutate the ordering for everyone, and for everyone to see the same ordering. That means it MUST be done purely in the backend (as indeed all ALTER TABLE commands must be.) cheers andrew
On Sat, Mar 17, 2012 at 1:48 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > Mine too. We don't want a column ordering that's different for everyone. > That's a recipe for mass confusion. We want to be able to mutate the > ordering for everyone, and for everyone to see the same ordering. That means > it MUST be done purely in the backend (as indeed all ALTER TABLE commands > must be.) I am in agreement. This state would have to live in the session between client and server, so that semantic analysis could do the right thing by looking at the environment on the backend, too. Life is already really difficult because of session state: consider connection poolers, which are both common and have subtle caveats that are close to being completely silent if one uses the wrong construct. The only reason people get away with this in practice is because most of them do not use constructs like temp tables and dblink in conjunction with pooling, but if they did, the prospect of what could happen when sessions get crossed is a nightmare (clearly, I'd like a way to treat this, because session state is also useful, but that's not the here and now...) Reordering attributes totally backends-side, though, would be a wonderful feature, and already has plenty of work just to realize the physical/logical decoupling in the backend, so I have a lot of enthusiasm for the project in general. -- fdr
-----Original Message----- From: Daniel Farina [mailto:daniel@heroku.com] Sent: 18 March 2012 06:30 To: Andrew Dunstan Cc: Dave Page; Atri Sharma; Alvaro Herrera; Pg Hackers Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 On Sat, Mar 17, 2012 at 1:48 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > Mine too. We don't want a column ordering that's different for everyone. > That's a recipe for mass confusion. We want to be able to mutate the > ordering for everyone, and for everyone to see the same ordering. That means > it MUST be done purely in the backend (as indeed all ALTER TABLE commands > must be.) I am in agreement. This state would have to live in the session between client and server, so that semantic analysis could do the right thing by looking at the environment on the backend, too. Life is already really difficult because of session state: consider connection poolers, which are both common and have subtle caveats that are close to being completely silent if one uses the wrong construct. The only reason people get away with this in practice is because most of them do not use constructs like temp tables and dblink in conjunction with pooling, but if they did, the prospect of what could happen when sessions get crossed is a nightmare (clearly, I'd like a way to treat this, because session state is also useful, but that's not the here and now...) Reordering attributes totally backends-side, though, would be a wonderful feature, and already has plenty of work just to realize the physical/logical decoupling in the backend, so I have a lot of enthusiasm for the project in general. -- Fdr ------------------------------- Hi All, First of all, thanks a million for all the wonderful feedbacks. It has really taught me a lot in just 8 hours. I am understanding the scenario now. I will take a little of your time to modify my original idea: The "middle layer" still exists, but NOT on the individual client side , rather , on the server side. That is, we maintain the middle layer on the server, and it is same for all the users. We can mutate the ordering, and changes would be reflected in all the clients, since they are all accessing the same middle layer, present on the server. The mapping structure, according to me, should still remain the same. Please let me know your feedback and corrections. Atri
-----Original Message----- From: jcasanov@systemguards.com.ec [mailto:jcasanov@systemguards.com.ec] On Behalf Of Jaime Casanova Sent: 18 March 2012 01:04 To: Atri Sharma Cc: Dave Page; Alvaro Herrera; Pg Hackers Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 On Sat, Mar 17, 2012 at 2:16 PM, Atri Sharma <atri.jiit@gmail.com> wrote: > Hi Dave, > > I believe,with the ALTER command,we plan to give the user power to modify > ordering . Dont we? > And how is that different from a view that orders the columns as the user wishes? -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -------------------------------------- Hi Jaime , Well, I believe, my idea is more towards the layers already present in database systems, rather than views. 1) Views are just a window to some(or all) part of a database. There are many restriction on views, none of which are applicable to our middle layer. This is so because, even though the middle layer is encapsulating the addressing and mapping details from the user , it still provides complete access and rights to the database according to the user's privilege level. 2) Views are optional, wherever our middle layer is built right in the database. It is not optional. User can only see the middle layer. It cannot directly interact with the underlying layers. Please let me know your feedback and comments. Atri
Excerpts from Atri Sharma's message of dom mar 18 01:05:23 -0300 2012: > I am understanding the scenario now. I will take a little of your time to > modify my original idea: > > The "middle layer" still exists, but NOT on the individual client side , > rather , on the server side. That is, we maintain the middle layer on the > server, and it is same for all the users. We can mutate the ordering, and > changes would be reflected in all the clients, since they are all accessing > the same middle layer, present on the server. I will take a little of your time to ask whether you read the older threads on the topic -- the one that Andrew Dunstan linked to, and the older ones, to which that email links to? This whole area has been extensively discussed before. In any case, I repeat what I said before: this is way too complex a topic for it to make a good GSoC project (unless you're already intimately familiar with the parser and executor code.) You need to pick something smaller, more localized. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Mon, Mar 19, 2012 at 11:51 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > > Excerpts from Atri Sharma's message of dom mar 18 01:05:23 -0300 2012: > >> I am understanding the scenario now. I will take a little of your time to >> modify my original idea: >> >> The "middle layer" still exists, but NOT on the individual client side , >> rather , on the server side. That is, we maintain the middle layer on the >> server, and it is same for all the users. We can mutate the ordering, and >> changes would be reflected in all the clients, since they are all accessing >> the same middle layer, present on the server. > > I will take a little of your time to ask whether you read the older > threads on the topic -- the one that Andrew Dunstan linked to, and the > older ones, to which that email links to? This whole area has been > extensively discussed before. > > In any case, I repeat what I said before: this is way too complex a > topic for it to make a good GSoC project (unless you're already > intimately familiar with the parser and executor code.) You need to > pick something smaller, more localized. > > -- > Álvaro Herrera <alvherre@commandprompt.com> > The PostgreSQL Company - Command Prompt, Inc. > PostgreSQL Replication, Consulting, Custom Development, 24x7 support ------------------------------ Hi Alvaro, Thanks for your reply.Yes,I have read the earlier posts. I am familiar with database systems.I have worked on them before and I have done designing of some very basic databases. Please guide me to some area where I can research for some gsoc project. Thanks, Atri -- Regards, Atri l'apprenant
On Tue, Mar 20, 2012 at 12:08 AM, Atri Sharma <atri.jiit@gmail.com> wrote: > On Mon, Mar 19, 2012 at 11:51 PM, Alvaro Herrera > <alvherre@commandprompt.com> wrote: >> >> Excerpts from Atri Sharma's message of dom mar 18 01:05:23 -0300 2012: >> >>> I am understanding the scenario now. I will take a little of your time to >>> modify my original idea: >>> >>> The "middle layer" still exists, but NOT on the individual client side , >>> rather , on the server side. That is, we maintain the middle layer on the >>> server, and it is same for all the users. We can mutate the ordering, and >>> changes would be reflected in all the clients, since they are all accessing >>> the same middle layer, present on the server. >> >> I will take a little of your time to ask whether you read the older >> threads on the topic -- the one that Andrew Dunstan linked to, and the >> older ones, to which that email links to? This whole area has been >> extensively discussed before. >> >> In any case, I repeat what I said before: this is way too complex a >> topic for it to make a good GSoC project (unless you're already >> intimately familiar with the parser and executor code.) You need to >> pick something smaller, more localized. >> >> -- >> Álvaro Herrera <alvherre@commandprompt.com> >> The PostgreSQL Company - Command Prompt, Inc. >> PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > > ------------------------------ > > Hi Alvaro, > > Thanks for your reply.Yes,I have read the earlier posts. > > I am familiar with database systems.I have worked on them before and I > have done designing of some very basic databases. > > Please guide me to some area where I can research for some gsoc project. > > Thanks, > > Atri > > > -- > Regards, > > Atri > l'apprenant ---------------------- Hi Alvaro, 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. -- Regards, Atri l'apprenant
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. But maybe I'm wrong and somebody can mentor you to do something useful with it -- there are so many advancements in PLs that I'm sure there must be something that can be done with PL/Java. Did you have a look at the TODO list? http://wiki.postgresql.org/wiki/Todo -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
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
-----Original Message----- From: Claes Jakobsson [mailto:claes@versed.se] Sent: 20 March 2012 14:40 To: Merlin Moncure Cc: Heikki Linnakangas; Alvaro Herrera; Atri Sharma; Daniel Farina; Andrew Dunstan; Dave Page; Pg Hackers Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 On 19 mar 2012, at 22.45, Merlin Moncure wrote: > 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. Personally I'd love a type 2 JDBC driver for PostgreSQL. It should be feasible as a summer project. It's somewhere deep down on my TODO list so I'd be happy to help. Cheers, Claes ------------------ Hi Claes, Thank you for taking time to reply to my query. I would simply love to implement a type 2 JDBC driver.It would be simply wonderful to add this feature to PL/Java (IMHO). Please let me know how to proceed further.Also,I would request you to please mentor me in this pet project of yours. Thanks a lot for giving me this project idea. Waiting for your reply, Atri
-----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. Atri
On 20.03.2012 11:10, Claes Jakobsson wrote: > On 19 mar 2012, at 22.45, Merlin Moncure wrote: >> 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. > > Personally I'd love a type 2 JDBC driver for PostgreSQL. Why? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
<p dir="LTR"><span lang="en-in"></span><span lang="en-us"><font face="Consolas">-----Original Message-----<br /> From: HeikkiLinnakangas [<a href="mailto:heikki.linnakangas@enterprisedb.com">mailto:heikki.linnakangas@enterprisedb.com</a>]<br/> Sent: 20 March 201217:39<br /> To: Claes Jakobsson<br /> Cc: Merlin Moncure; Alvaro Herrera; Atri Sharma; Daniel Farina; Andrew Dunstan;Dave Page; Pg Hackers<br /> Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012</font></span><spanlang="en-in"></span><p dir="LTR"><span lang="en-in"><font face="Consolas">On 20.03.2012 11:10, ClaesJakobsson wrote:</font></span><p dir="LTR"><span lang="en-in"><font face="Consolas">> On 19 mar 2012, at 22.45, MerlinMoncure wrote:</font></span><p dir="LTR"><span lang="en-in"><font face="Consolas">>> one pl/java based projectthat IMO is just screaming to be done is a</font></span><p dir="LTR"><span lang="en-in"><font face="Consolas">>>pl/java based FDW (wrapping JDBC) that would approximately reproduce</font></span><p dir="LTR"><spanlang="en-in"><font face="Consolas">>> dblink...maybe with some extra features geared towards ETL typejobs</font></span><p dir="LTR"><span lang="en-in"><font face="Consolas">>> like a row based callback for transformationsin java.</font></span><p dir="LTR"><span lang="en-in"><font face="Consolas">></font></span><p dir="LTR"><spanlang="en-in"><font face="Consolas">> Personally I'd love a type 2 JDBC driver for PostgreSQL.</font></span><pdir="LTR"><span lang="en-in"><font face="Consolas">Why?</font></span><p dir="LTR"><span lang="en-in"><fontface="Consolas">-- </font></span><p dir="LTR"><span lang="en-in"><font face="Consolas"> Heikki Linnakangas</font></span><pdir="LTR"><span lang="en-in"><font face="Consolas"> EnterpriseDB </font></span><span lang="en-in"></span><a href="http://www.enterprisedb.com"><span lang="en-in"><u><font color="#0000FF" face="Consolas">http://www.enterprisedb.com</font></u></span><spanlang="en-in"></span></a><span lang="en-in"></span><p dir="LTR"><spanlang="en-in"></span><p dir="LTR"><span lang="en-in"><font color="#000000" face="Consolas">---------------------------------------</font></span><pdir="LTR"><span lang="en-in"><font face="Consolas">HiHeikki,</font></span><p dir="LTR"><span lang="en-in"><b></b></span><p dir="LTR"><span lang="en-in"><b><fontface="Consolas">one pl/java based project that IMO is just screaming to be done is a</font></b></span><pdir="LTR"><span lang="en-in"><b><font face="Consolas">pl/java based FDW (wrapping JDBC) that would approximatelyreproduce</font></b></span><p dir="LTR"><span lang="en-in"><b><font face="Consolas">dblink...maybe with someextra features geared towards ETL type jobs</font></b></span><p dir="LTR"><span lang="en-in"><b><font face="Consolas">likea row based callback for transformations in java.</font></b></span><span lang="en-in"><b></b></span><pdir="LTR"><span lang="en-in"><font face="Consolas">The project will be wrapping JDBC in a wrapperclass and add</font></span><span lang="en-in"><font face="Consolas">ing ETL features?</font></span><p dir="LTR"><spanlang="en-in"><font face="Consolas">Please elaborate a bit more.I am not too sure if I understand it completely.</font></span><pdir="LTR"><span lang="en-in"><font face="Consolas">Waiting for your reply,</font></span><p dir="LTR"><spanlang="en-in"><font face="Consolas">Atri</font></span><span lang="en-in"></span><p dir="LTR"><span lang="en-in"></span>
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
On Mon, Mar 19, 2012 at 12:29 PM, Alvaro Herrera <alvherre@commandprompt.com> 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. > But maybe I'm wrong and somebody can mentor you to do something useful > with it -- there are so many advancements in PLs that I'm sure there > must be something that can be done with PL/Java. > > Did you have a look at the TODO list? http://wiki.postgresql.org/wiki/Todo However, the new user should be aware that many of the things in that list are better described as "to finish discussing/arguing whether we want it or not, and what trade offs we are willing to make" rather than "to do". That was my experience, anyway. Cheers, Jeff
Excerpts from Jeff Janes's message of mar mar 20 12:54:55 -0300 2012: > On Mon, Mar 19, 2012 at 12:29 PM, Alvaro Herrera > <alvherre@commandprompt.com> wrote: > > Did you have a look at the TODO list? http://wiki.postgresql.org/wiki/Todo > > However, the new user should be aware that many of the things in that > list are better described as "to finish discussing/arguing whether we > want it or not, and what trade offs we are willing to make" rather > than "to do". > > That was my experience, anyway. Feel free to edit the commentary at the top :-) -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
-----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. Please let me know how to proceed further. Waiting for your reply, Atri
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
-----Original Message----- From: Merlin Moncure [mailto:mmoncure@gmail.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.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 --------------- 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, Atri
On Tue, Mar 20, 2012 at 12:34 PM, Atri Sharma <atri.jiit@gmail.com> wrote: > > > -----Original Message----- > From: Merlin Moncure [mailto:mmoncure@gmail.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.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 > > --------------- > 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
On Wed, Mar 21, 2012 at 12:42 AM, Merlin Moncure <mmoncure@gmail.com> wrote: > On Tue, Mar 20, 2012 at 12:34 PM, Atri Sharma <atri.jiit@gmail.com> wrote: >> >> >> -----Original Message----- >> From: Merlin Moncure [mailto:mmoncure@gmail.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.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 >> >> --------------- >> 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
On 19 mar 2012, at 22.45, Merlin Moncure wrote: > 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. Personally I'd love a type 2 JDBC driver for PostgreSQL. It should be feasible as a summer project. It's somewhere deep downon my TODO list so I'd be happy to help. Cheers, Claes
On 20 mar 2012, at 13.08, Heikki Linnakangas wrote: > On 20.03.2012 11:10, Claes Jakobsson wrote: >> >> Personally I'd love a type 2 JDBC driver for PostgreSQL. > > Why? listen/notify over SSL for example unless that's been fixed in the JDBC driver recently. And I'm sure there are other thingsin libpq that would be nice to have. As mainly a Perl dude which uses libpq via DBD::Pg I find it odd that the Java people doesn't do the same instead of reimplementingeverything. Cheers, /Claes
On Tue, Mar 20, 2012 at 01:25:15PM +0100, Claes Jakobsson wrote: > > On 20 mar 2012, at 13.08, Heikki Linnakangas wrote: > > On 20.03.2012 11:10, Claes Jakobsson wrote: > >> > >> Personally I'd love a type 2 JDBC driver for PostgreSQL. > > > > Why? > > listen/notify over SSL for example unless that's been fixed in the > JDBC driver recently. And I'm sure there are other things in libpq that > would be nice to have. > > As mainly a Perl dude which uses libpq via > DBD::Pg I find it odd that the Java people doesn't do the same instead > of reimplementing everything. Well, I assume they reimplemented libpq so that java would not rely on a platform-specific library like libpq. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Mon, Apr 9, 2012 at 1:14 PM, Bruce Momjian <bruce@momjian.us> wrote: > Well, I assume they reimplemented libpq so that java would not rely on a > platform-specific library like libpq. yes, that is correct. jdbc for postgres is a complete implementation of the client side protocol. this has some good and bad points -- on the good side you have some features libpq is only about to get, like row level result processing, but on them minus side you are missing some features libpq has, like gssapi authentication (but you can still get that with jdbc->odbc bridge). but generally speaking jdbc is displacing odbc as the 'go to' library for connection between different kinds of database systems, especially on non-windows environments. jdbc is to java as fdw is to postgres basically. so a fdw exposed jdbc driver should be able to connect and gather data from just about anything -- even something like sql server so that you could bypass the freetds dependency which is quite nice. there's an odbc-fdw project that does something pretty similar and might be a more natural choice for windows coders. merlin
On 04/09/2012 02:14 PM, Bruce Momjian wrote: > On Tue, Mar 20, 2012 at 01:25:15PM +0100, Claes Jakobsson wrote: >> On 20 mar 2012, at 13.08, Heikki Linnakangas wrote: >>> On 20.03.2012 11:10, Claes Jakobsson wrote: >>>> Personally I'd love a type 2 JDBC driver for PostgreSQL. >>> Why? >> listen/notify over SSL for example unless that's been fixed in the >> JDBC driver recently. And I'm sure there are other things in libpq that >> would be nice to have.> > As mainly a Perl dude which uses libpq via >> DBD::Pg I find it odd that the Java people doesn't do the same instead >> of reimplementing everything. > Well, I assume they reimplemented libpq so that java would not rely on a > platform-specific library like libpq. Type 4 drivers are the norm in the Java world. You would find it much more difficult to get traction among Java users, in my experience, with a driver that's not pure Java. And in any case, I think it's a good thing to have two significant independent implementations of the wire protocol out there. Note too that the maintainer of the Perl DBD driver has opined in my hearing that he would like to be able to move from relying on libpq to having a pure Perl driver (although personally speaking I'm glad he hasn't.) cheers andrew
On Mon, Apr 09, 2012 at 01:29:46PM -0500, Merlin Moncure wrote: > but generally speaking jdbc is displacing odbc as the 'go to' library > for connection between different kinds of database systems, especially > on non-windows environments. jdbc is to java as fdw is to postgres > basically. so a fdw exposed jdbc driver should be able to connect and > gather data from just about anything -- even something like sql server > so that you could bypass the freetds dependency which is quite nice. Yes, I can see jdbc-fdw being very powerful. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +