Re: [HACKERS] PL/pgSQL 2
Joel Jacobson j...@trustly.com writes: I see two possible approaches of a plpgsql2 project, both aiming to require minimal/no changes of most existing best-practice plpgsql code: a) fork plpgsql code base and implement changes with as few lines of code as possible, making it easier to understand the changes, verify their correctness and apply future patches of the plpgsql code. b) fork plpgsql code and remove as much code as possible thanks to the reduced complexity possible thanks to the stricter behaviour achieved by removing settings and enforcing a stricter coding convention and killing obsolete quirks. You lost me at fork. Reality is that plpgsql development is not going to stop; wherever possible we are going to want to put new features into both languages, and bug fixes even more so. Moreover, the size of the changes you've suggested hardly merits a fork in the first place, and the notion that we're going to be able to radically simplify the code base after making them is laughable. What I would think about is c) plpgsql and plpgsql2 are the same code base, with a small number of places that act differently depending on the language version. We could alternatively get the result by inventing a bunch of pragma declarations, or some similar notation, that control the behavioral changes one-at-a-time. That might even be worth doing anyway, in case somebody likes some of the ideas and others not so much. But I'd see the language version as a convenient shorthand for enabling a specified collection of pretty-localized incompatible behavior changes. If they're not pretty localized, there's going to be a barrier to uptake, very comparable to the python3 analogy mentioned upthread. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
=?UTF-8?B?w4FsdmFybyBIZXJuw6FuZGV6IFRvcnRvc2E=?= a...@nosys.es writes: What I can add is that, if Postgres is to devote resources to a new language, I would plan it with a broader scope. What would attract most users? Would it bring non postgres users to Postgres? What could be one of the killer features of any next version? My trivial answer to most of these questions is: PL/SQL. By that I suppose you mean I wish it would act just like Oracle. The problem with such a wish is that a lot of the incompatibilities with Oracle are functions of the core SQL engine, not of the PL. plpgsql already is about as close to PL/SQL as it's possible to get without changing core Postgres behavior --- or at least, that was the original design desire, and I don't think that it's failed in any large degree. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 01/09/14 20:42, Tom Lane wrote: =?UTF-8?B?w4FsdmFybyBIZXJuw6FuZGV6IFRvcnRvc2E=?= a...@nosys.es writes: What I can add is that, if Postgres is to devote resources to a new language, I would plan it with a broader scope. What would attract most users? Would it bring non postgres users to Postgres? What could be one of the killer features of any next version? My trivial answer to most of these questions is: PL/SQL. By that I suppose you mean I wish it would act just like Oracle. The problem with such a wish is that a lot of the incompatibilities with Oracle are functions of the core SQL engine, not of the PL. plpgsql already is about as close to PL/SQL as it's possible to get without changing core Postgres behavior --- or at least, that was the original design desire, and I don't think that it's failed in any large degree. regards, tom lane It's true that some of the incompatibilities are the core engine, internal functions and so on, and that the plpgsql design goal was to achieve similarity. But similarity is not code compatibility, and afaik, plpgsql is not code compatible with PL/SQL. Having 1:1 code compatibility, if possible, is a very well first step, only followed by the core functionalities you mention. If postgres were going for a new language, why not implement one which, having the other suggested functionality, also has 1:1 PL/SQL code compatibility? I'm sure it's no trivial task, but one highly desirable. Regards, Álvaro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
2014-09-01 20:58 GMT+02:00 Álvaro Hernández Tortosa a...@nosys.es: On 01/09/14 20:42, Tom Lane wrote: =?UTF-8?B?w4FsdmFybyBIZXJuw6FuZGV6IFRvcnRvc2E=?= a...@nosys.es writes: What I can add is that, if Postgres is to devote resources to a new language, I would plan it with a broader scope. What would attract most users? Would it bring non postgres users to Postgres? What could be one of the killer features of any next version? My trivial answer to most of these questions is: PL/SQL. By that I suppose you mean I wish it would act just like Oracle. The problem with such a wish is that a lot of the incompatibilities with Oracle are functions of the core SQL engine, not of the PL. plpgsql already is about as close to PL/SQL as it's possible to get without changing core Postgres behavior --- or at least, that was the original design desire, and I don't think that it's failed in any large degree. regards, tom lane It's true that some of the incompatibilities are the core engine, internal functions and so on, and that the plpgsql design goal was to achieve similarity. But similarity is not code compatibility, and afaik, plpgsql is not code compatible with PL/SQL. Having 1:1 code compatibility, if possible, is a very well first step, only followed by the core functionalities you mention. If postgres were going for a new language, why not implement one which, having the other suggested functionality, also has 1:1 PL/SQL code compatibility? I'm sure it's no trivial task, but one highly desirable. It is false expectation - language is only one part .. and plpgsql isn't to far. There are different system of modules, different system of custom aggregates, mainly with PL/SQL is very complex library dbms_. This library is maybe more complex than current Postgres base. It is task for commercial project --- not all Postgres users need a Oracle compatibility layer. Next, I am sure, so it is in contradiction to Joel proposal. Regards Pavel Regards, Álvaro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Mon, Sep 1, 2014 at 8:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: c) plpgsql and plpgsql2 are the same code base, with a small number of places that act differently depending on the language version. +1 to the idea -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Mon, Sep 1, 2014 at 8:34 PM, Álvaro Hernández Tortosa a...@nosys.es wrote: What I can add is that, if Postgres is to devote resources to a new language, I would plan it with a broader scope. What would attract most users? Would it bring non postgres users to Postgres? What could be one of the killer features of any next version? My trivial answer to most of these questions is: PL/SQL. I don't know with detail how complex this is to get in Postgres (well, EDB probably knows), but if I had to chose a new language, this is it. So my questions would rather be: Interesting visionary ideas. For me personally, I have no Oracle functions to port to Postgres, so all differences between PL/SQL and PL/pgSQL would just be unnecessary extra amount of work in the process of porting existing code into a new language, be it PL/SQL or PL/pgSQL 2. That said, if PL/SQL wouldn't suffer from the problems I have with PL/pgSQL today, I wouldn't see a problem writing new code in the language, but then I would probably never manage to port all existing code to PL/SQL and I would be stuck with a mix of code in two languages instead of one. With PL/pgSQL 2 on the other hand, it would be feasible to eventually port all my existing code, as most of it would be compatible without changes and the rest would easy to make compatible. I guess it's a question of if it's more important to recruit Oracle users, or if it's more important to improve the life of existing PL/pgSQL Postgres users. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 01/09/14 21:08, Pavel Stehule wrote: 2014-09-01 20:58 GMT+02:00 Álvaro Hernández Tortosa a...@nosys.es mailto:a...@nosys.es: On 01/09/14 20:42, Tom Lane wrote: =?UTF-8?B?w4FsdmFybyBIZXJuw6FuZGV6IFRvcnRvc2E=?= a...@nosys.es mailto:a...@nosys.es writes: What I can add is that, if Postgres is to devote resources to a new language, I would plan it with a broader scope. What would attract most users? Would it bring non postgres users to Postgres? What could be one of the killer features of any next version? My trivial answer to most of these questions is: PL/SQL. By that I suppose you mean I wish it would act just like Oracle. The problem with such a wish is that a lot of the incompatibilities with Oracle are functions of the core SQL engine, not of the PL. plpgsql already is about as close to PL/SQL as it's possible to get without changing core Postgres behavior --- or at least, that was the original design desire, and I don't think that it's failed in any large degree. regards, tom lane It's true that some of the incompatibilities are the core engine, internal functions and so on, and that the plpgsql design goal was to achieve similarity. But similarity is not code compatibility, and afaik, plpgsql is not code compatible with PL/SQL. Having 1:1 code compatibility, if possible, is a very well first step, only followed by the core functionalities you mention. If postgres were going for a new language, why not implement one which, having the other suggested functionality, also has 1:1 PL/SQL code compatibility? I'm sure it's no trivial task, but one highly desirable. It is false expectation - language is only one part .. and plpgsql isn't to far. There are different system of modules, different system of custom aggregates, mainly with PL/SQL is very complex library dbms_. This library is maybe more complex than current Postgres base. OK. Understood. Full compatibility may be a longer-term goal. But why it's bad to have the same syntax at a language -not library- level? It is task for commercial project --- not all Postgres users need a Oracle compatibility layer. Certainly not all users need that layer. But I'm sure few would complain to have it. Besides that, why do you say it is meant for a commercial project? If it is because postgres should not listen to users willing to migrate from Oracle --then we're screwed, losing the biggest opportunity (of attracting a large crowd of users) of recent times. If it is because it's too complex... well, I don't think the postgres community (as a whole) have less resources than commercial projects. Next, I am sure, so it is in contradiction to Joel proposal. That's not my business ;P No, really: if there is a new version of a language, which modifies the current syntax of plpgsql; if plpgsql is already very similar to PL/SQL: why not rather than coming up with a new syntax use an already existing one? One that many, many more users than plpgsql, already know? Regards, Álvaro
Re: [HACKERS] PL/pgSQL 2
On 01/09/14 21:52, Joel Jacobson wrote: On Mon, Sep 1, 2014 at 8:34 PM, Álvaro Hernández Tortosa a...@nosys.es wrote: What I can add is that, if Postgres is to devote resources to a new language, I would plan it with a broader scope. What would attract most users? Would it bring non postgres users to Postgres? What could be one of the killer features of any next version? My trivial answer to most of these questions is: PL/SQL. I don't know with detail how complex this is to get in Postgres (well, EDB probably knows), but if I had to chose a new language, this is it. So my questions would rather be: Interesting visionary ideas. For me personally, I have no Oracle functions to port to Postgres, so all differences between PL/SQL and PL/pgSQL would just be unnecessary extra amount of work in the process of porting existing code into a new language, be it PL/SQL or PL/pgSQL 2. That said, if PL/SQL wouldn't suffer from the problems I have with PL/pgSQL today, I wouldn't see a problem writing new code in the language, but then I would probably never manage to port all existing code to PL/SQL and I would be stuck with a mix of code in two languages instead of one. With PL/pgSQL 2 on the other hand, it would be feasible to eventually port all my existing code, as most of it would be compatible without changes and the rest would easy to make compatible. I guess it's a question of if it's more important to recruit Oracle users, or if it's more important to improve the life of existing PL/pgSQL Postgres users. I agree that for you, unfortunately, plpgsql2 would be better than PL/SQL. However, I believe as a whole a bigger majority of users would be benefited from this. If anyone is willing to look at the market numbers, the number of PL/SQL users compared to those of plpgsql is really huge. That would surely attract many more users to postgres, which would ultimately greatly help us all (in detriment of you and a few others, sure, but on a way that I'm sure it's manageable). Of course, I'd +1 any attempt to build a super-set of PL/SQL that would try to implement, also, the request that you and others may have about this PL, should they make sense. Regards, Álvaro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 2014-09-01 11:11 PM, Álvaro Hernández Tortosa wrote: No, really: if there is a new version of a language, which modifies the current syntax of plpgsql; if plpgsql is already very similar to PL/SQL: why not rather than coming up with a new syntax use an already existing one? One that many, many more users than plpgsql, already know? The point isn't to create a new language just for the sake of creating a new one. It's to fix the problems PL/PgSQL has. If we're just going to trade the problems in PL/PgSQL with another set of problems implemented by PL/SQL, we're just worse off in the end. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
Álvaro Hernández Tortosa wrote On 01/09/14 21:52, Joel Jacobson wrote: On Mon, Sep 1, 2014 at 8:34 PM, Álvaro Hernández Tortosa lt; aht@ gt; wrote: What I can add is that, if Postgres is to devote resources to a new language, I would plan it with a broader scope. What would attract most users? Would it bring non postgres users to Postgres? What could be one of the killer features of any next version? My trivial answer to most of these questions is: PL/SQL. I don't know with detail how complex this is to get in Postgres (well, EDB probably knows), but if I had to chose a new language, this is it. So my questions would rather be: Interesting visionary ideas. For me personally, I have no Oracle functions to port to Postgres, so all differences between PL/SQL and PL/pgSQL would just be unnecessary extra amount of work in the process of porting existing code into a new language, be it PL/SQL or PL/pgSQL 2. That said, if PL/SQL wouldn't suffer from the problems I have with PL/pgSQL today, I wouldn't see a problem writing new code in the language, but then I would probably never manage to port all existing code to PL/SQL and I would be stuck with a mix of code in two languages instead of one. With PL/pgSQL 2 on the other hand, it would be feasible to eventually port all my existing code, as most of it would be compatible without changes and the rest would easy to make compatible. I guess it's a question of if it's more important to recruit Oracle users, or if it's more important to improve the life of existing PL/pgSQL Postgres users. I agree that for you, unfortunately, plpgsql2 would be better than PL/SQL. However, I believe as a whole a bigger majority of users would be benefited from this. Is it even legal for us to create PL/SQL? Beyond that in all likelihood having both a version two of the pl/pgsql language and the pl/SQL language would be a desireable outcome for, say, a 10.0 release. The former simply because languages by their very nature are evolutionary and at some point the lost productivity of suppressing such evolution in the name of backward compatibility will be deemed undesirable. It may be desirable to simply call the new language pl/elephant instead pl/pgsql2 but the fundamental reason for evolving a language in order to incorporate newly acquired knowledge is unarguable. Though in this case the entire language/extension mechanism should be considered and not just the specific procedural-SQL language we are dealing with here. The goal of adding PL/SQL would be to increase the user base of the project and hopefully attract new blood to the development team in order to maximize long-term survivability and increase the pace of innovation. We would be unable to introduce substantial evolution to this language for that very reason and so a different language is likely to be needed - eventually. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PL-pgSQL-2-tp5817121p5817251.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 01/09/14 23:31, Marko Tiikkaja wrote: On 2014-09-01 11:11 PM, Álvaro Hernández Tortosa wrote: No, really: if there is a new version of a language, which modifies the current syntax of plpgsql; if plpgsql is already very similar to PL/SQL: why not rather than coming up with a new syntax use an already existing one? One that many, many more users than plpgsql, already know? The point isn't to create a new language just for the sake of creating a new one. It's to fix the problems PL/PgSQL has. If we're just going to trade the problems in PL/PgSQL with another set of problems implemented by PL/SQL, we're just worse off in the end. Agreed. But if we can solve them --only if we could-- by leveraging a syntax that happens to be: - Similar to that of plpgsql (exactly the same as plpgsql2 would be similar to plpgsql) - Already known by a large, very large, group of users we would be way better off. If there are unresolved problems in the PL/SQL current implementation, doing a superset of it may make sense. Regards, Álvaro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 01/09/14 23:46, David G Johnston wrote: Álvaro Hernández Tortosa wrote On 01/09/14 21:52, Joel Jacobson wrote: On Mon, Sep 1, 2014 at 8:34 PM, Álvaro Hernández Tortosa lt; aht@ gt; wrote: What I can add is that, if Postgres is to devote resources to a new language, I would plan it with a broader scope. What would attract most users? Would it bring non postgres users to Postgres? What could be one of the killer features of any next version? My trivial answer to most of these questions is: PL/SQL. I don't know with detail how complex this is to get in Postgres (well, EDB probably knows), but if I had to chose a new language, this is it. So my questions would rather be: Interesting visionary ideas. For me personally, I have no Oracle functions to port to Postgres, so all differences between PL/SQL and PL/pgSQL would just be unnecessary extra amount of work in the process of porting existing code into a new language, be it PL/SQL or PL/pgSQL 2. That said, if PL/SQL wouldn't suffer from the problems I have with PL/pgSQL today, I wouldn't see a problem writing new code in the language, but then I would probably never manage to port all existing code to PL/SQL and I would be stuck with a mix of code in two languages instead of one. With PL/pgSQL 2 on the other hand, it would be feasible to eventually port all my existing code, as most of it would be compatible without changes and the rest would easy to make compatible. I guess it's a question of if it's more important to recruit Oracle users, or if it's more important to improve the life of existing PL/pgSQL Postgres users. I agree that for you, unfortunately, plpgsql2 would be better than PL/SQL. However, I believe as a whole a bigger majority of users would be benefited from this. Is it even legal for us to create PL/SQL? Honestly, I don't know. But I don't think so. And if it is, be it. Let's have Oracle sue us (who?) Beyond that in all likelihood having both a version two of the pl/pgsql language and the pl/SQL language would be a desireable outcome for, say, a 10.0 release. If 10.0 is to follow 9.5, I'd agree. Althouth I'd also agree for any earlier, if that would be possible. The former simply because languages by their very nature are evolutionary and at some point the lost productivity of suppressing such evolution in the name of backward compatibility will be deemed undesirable. It may be desirable to simply call the new language pl/elephant instead pl/pgsql2 but the fundamental reason for evolving a language in order to incorporate newly acquired knowledge is unarguable. Though in this case the entire language/extension mechanism should be considered and not just the specific procedural-SQL language we are dealing with here. The goal of adding PL/SQL would be to increase the user base of the project and hopefully attract new blood to the development team in order to maximize long-term survivability and increase the pace of innovation. We would be unable to introduce substantial evolution to this language for that very reason and so a different language is likely to be needed - eventually. That's what I think. Increasing the user base, in a moment where for many reasons we all know many want to migrate off of Oracle... let's bring them here before they move to something else, with also piles of money and great marketing capabilities Álvaro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Sep 1, 2014, at 10:45 AM, Tom Lane t...@sss.pgh.pa.us wrote: What is actually being proposed, AFAICS, is a one-shot fix for a bunch of unfortunate choices. That might be worth doing, but let's not fool ourselves about whether it’s one-shot or not. Well, one shot every 18 years is not so bad. I am only a casual user and as such probably do not merit much consideration from the experts here. I only work with PL/pgSQL occasionally, but never go weeks without doing it and sometimes it is all I do for weeks. That said and this being the internet, IMO working in PL/pgSQL is only slightly better than stabbing myself in the leg with a knife compared to other non-PL/pgSQL languages I work in. Mostly my hate is directed at the silly quoting. But it has lots of other quirks that are not all that obvious unless you work with it all day every day. Now I could use other languages as was suggested upstream. Lets see, I use R all the time, but R is not a first class language, not in core, and its slow. Python 3 would be acceptable to me, but its untrusted. tcl I don’t know and don’t want to learn as no one else seems to use it (in my world anyway). perl is the only possibility left and again, no one in my world is using Perl and it’s not clear if there is a performance penalty. The docs say the best language for performance is PL/pgSQL after pure SQL. Really, this is from the docs a_output := a_output || '' if v_'' || referrer_keys.kind || '' like '' || referrer_keys.key_string || '' then return '' || referrer_keys.referrer_type || ‘'; end if;''; That should be enough alone to suggest postgreSQL start working on a modern, in core, fast, fully supported language. Of course PL/pgSQL works, but so did one-line 5k perl programs that nobody likes today. Everything can be done in assembler, but no one suggests that today. Today, it is all about programmer productivity. PL/pgSQL has a lot of unnecessary stuff that sucks the life out of programmer productivity. And this should be very much a concern of the professionals that support PostgreSQL For example: DECLARE declarations BEGIN statements END This looks a lot like COBOL or Pascal, and today is mostly unnecessary. So my opinion would be to keep PL/pgSQL and adopt a new PL language that is fully supported, fast, and modern, that will over time replace PL/pgSQL. Neil -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/01/2014 08:09 PM, Neil Tiffin wrote: On Sep 1, 2014, at 10:45 AM, Tom Lane t...@sss.pgh.pa.us wrote: What is actually being proposed, AFAICS, is a one-shot fix for a bunch of unfortunate choices. That might be worth doing, but let's not fool ourselves about whether it’s one-shot or not. Well, one shot every 18 years is not so bad. I am only a casual user and as such probably do not merit much consideration from the experts here. I only work with PL/pgSQL occasionally, but never go weeks without doing it and sometimes it is all I do for weeks. That said and this being the internet, IMO working in PL/pgSQL is only slightly better than stabbing myself in the leg with a knife compared to other non-PL/pgSQL languages I work in. Mostly my hate is directed at the silly quoting. But it has lots of other quirks that are not all that obvious unless you work with it all day every day. Now I could use other languages as was suggested upstream. Lets see, I use R all the time, but R is not a first class language, not in core, and its slow. Python 3 would be acceptable to me, but its untrusted. tcl I don’t know and don’t want to learn as no one else seems to use it (in my world anyway). perl is the only possibility left and again, no one in my world is using Perl and it’s not clear if there is a performance penalty. The docs say the best language for performance is PL/pgSQL after pure SQL. Really, this is from the docs a_output := a_output || '' if v_'' || referrer_keys.kind || '' like '' || referrer_keys.key_string || '' then return '' || referrer_keys.referrer_type || ‘'; end if;''; The docs also tell you how to avoid having to do this, using dollar quoting. That should be enough alone to suggest postgreSQL start working on a modern, in core, fast, fully supported language. Of course PL/pgSQL works, but so did one-line 5k perl programs that nobody likes today. Everything can be done in assembler, but no one suggests that today. Today, it is all about programmer productivity. PL/pgSQL has a lot of unnecessary stuff that sucks the life out of programmer productivity. And this should be very much a concern of the professionals that support PostgreSQL For example: DECLARE declarations BEGIN statements END This looks a lot like COBOL or Pascal, and today is mostly unnecessary. It looks like Ada, and that's not an accident. (Nor is it a bad thing.) The very last thing we should be doing is to invent a new language. There are already plenty to choose from. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Mon, Sep 1, 2014 at 9:07 PM, Andrew Dunstan [via PostgreSQL] ml-node+s1045698n5817265...@n5.nabble.com wrote: On 09/01/2014 08:09 PM, Neil Tiffin wrote: That should be enough alone to suggest postgreSQL start working on a modern, in core, fast, fully supported language. Of course PL/pgSQL works, but so did one-line 5k perl programs that nobody likes today. Everything can be done in assembler, but no one suggests that today. Today, it is all about programmer productivity. PL/pgSQL has a lot of unnecessary stuff that sucks the life out of programmer productivity. And this should be very much a concern of the professionals that support PostgreSQL For example: DECLARE declarations BEGIN statements END This looks a lot like COBOL or Pascal, and today is mostly unnecessary. It looks like Ada, and that's not an accident. (Nor is it a bad thing.) The very last thing we should be doing is to invent a new language. There are already plenty to choose from. cheers andrew The extent of plenty narrows considerably if you factor in a requirement for SQL to be treated as a first-class construct... I would welcome the chance to evaluate an unencumbered language designed to mesh with PostgreSQL specifically and that would greatly ease the effort needed to write applications driven largely via in-database functions. Put differently - how much effort do we want to making PostgreSQL an irresistible platform that is difficult to leave? Now, of course, and salient to the point Andrew made, I'm not sure we actually have anyone with the talent AND desire to actually create such a language - we haven't need for a specialist language writer for a while now and I think you'd want a specialist if you were to try and write a language from scratch (over even adapt an existing language like what was apparently done with Ada). The question here is whether the resources are available, if it was deemed desirable, to even superficially overhaul pl/pgsql? Random thought as I wrote that: how about considering how pl/pgsql functionality can be generalize so that it is a database API that another language can call? In that way the server would drive the core functionality and the language would simply be an interpreter that enforces its specific notion of acceptable syntax. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PL-pgSQL-2-tp5817121p5817267.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Re: [HACKERS] PL/pgSQL 2
On 09/02/2014 09:40 AM, David G Johnston wrote: Random thought as I wrote that: how about considering how pl/pgsql functionality can be generalize so that it is a database API that another language can call? In that way the server would drive the core functionality and the language would simply be an interpreter that enforces its specific notion of acceptable syntax. That's pretty much what we already have with the SPI and procedural language handler infrastructure. PL/Perl, PL/Python, etc exist because we have this. What do you see as missing from the current infrastructure? What can't be done that should be able to be done in those languages? -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/02/2014 08:09 AM, Neil Tiffin wrote: Now I could use other languages as was suggested upstream. Lets see, I use R all the time, but R is not a first class language, not in core, and its slow. Python 3 would be acceptable to me, but its untrusted. tcl I don’t know and don’t want to learn as no one else seems to use it (in my world anyway). perl is the only possibility left and again, no one in my world is using Perl and it’s not clear if there is a performance penalty. The docs say the best language for performance is PL/pgSQL after pure SQL. PL/Perl is plenty fast, FWIW. I agree that it is unfortunate that we don't have an in-core trusted real language PL other than PL/Perl. I am personally hoping that PL/V8 will be in a position to be adopted as PL/JavaScript soon, as that would be an excellent fit with how the language fashion world is currently moving - JSON and JavaScript abound. More seriously, JavaScript is also a good fit for a trusted PL. I've long favoured Lua because of the excellent embeddable runtime and security-friendly design, but it's never really got the uptake required to make it a serious contender. I'd be quite happy to see PL/JavaScript in-core. (The other obvious candidate would be PL/Ruby, but it doesn't have an untrusted variant, and AFAIK Ruby is no better than Python when it comes to supporting a secure runtime: hopeless.) That should be enough alone to suggest postgreSQL start working on a modern, in core, fast, fully supported language. I couldn't disagree more. If we were to implement anything, it'd be PL/PSM (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and quirky as anything else the SQL committee has brought forth, but it's at least a standard(ish) language. Creating a new language when there are already many existing contenders is absolutely nonsensical. Other than PL/PSM the only thing that'd make any sense would be to *pick a suitable existing language* like Lua or JavaScript and bless it as a supported, always-available, in-core language runtime that's compiled in by default. Of course PL/pgSQL works, but so did one-line 5k perl programs that nobody likes today. Everything can be done in assembler, but no one suggests that today. Today, it is all about programmer productivity. PL/pgSQL has a lot of unnecessary stuff that sucks the life out of programmer productivity. And this should be very much a concern of the professionals that support PostgreSQL PL/PgSQL is how it is in part because of PL/SQL (http://en.wikipedia.org/wiki/PL/SQL) which in turn owes its heritage to Ada and Pascal. It serves an important role. I'm not going to pretend it's pretty, but -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/02/2014 05:46 AM, David G Johnston wrote: The goal of adding PL/SQL would be to increase the user base of the project and hopefully attract new blood to the development team in order to maximize long-term survivability and increase the pace of innovation. We would be unable to introduce substantial evolution to this language for that very reason and so a different language is likely to be needed - eventually. Sure - but it can be done out of core, with core patches only as required for making things that aren't currently possible work. You'd need to solve some really hard problems though. Autonomous transactions and top-level true procedures being the big ones. You'd also need to handle multiple result sets, TABLE-typed variables (backed with a tuplestore, maybe?), and lots more. Most of the differences between PL/SQL and PL/PgSQL stem from underlying differences in PostgresSQL and Oracle, as Tom has already pointed out. Often PostgreSQL limitations that don't exist in Oracle. So you'd have a lot of work to do in core to make a usefully-better-than-PL/PgSQL PL/SQL implementation possible. If someone came up with a convincing PL/SQL compatibility layer then it'd be worth considering adopting - when it was ready. But of course, anyone who does the work for that is quite likely to want to sell it to cashed-up Oracle users looking to save a few hundred grand on per-CPU licensing. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Mon, Sep 1, 2014 at 11:12 PM, Craig Ringer cr...@2ndquadrant.com wrote: On 09/02/2014 09:40 AM, David G Johnston wrote: Random thought as I wrote that: how about considering how pl/pgsql functionality can be generalize so that it is a database API that another language can call? In that way the server would drive the core functionality and the language would simply be an interpreter that enforces its specific notion of acceptable syntax. That's pretty much what we already have with the SPI and procedural language handler infrastructure. PL/Perl, PL/Python, etc exist because we have this. What do you see as missing from the current infrastructure? What can't be done that should be able to be done in those languages? Yet pl/pgsql does not have to use SPI-interface type calls to interact with PostgreSQL at the SQL level... I don't have an answer to your questions but the one I'm asking is whether a particular language could hide all of the SPI stuff behind some custom syntax so that it in effect looks similar to what pl/pgsql does today? Or, more to the point, does pl/pgsql use the same SPI interface behind the scenes as PL/Perl or does it have its own special interface? David J.
Re: [HACKERS] PL/pgSQL 2
On 09/01/2014 11:19 PM, Joel Jacobson wrote: On Mon, Sep 1, 2014 at 5:16 PM, Craig Ringer cr...@2ndquadrant.com wrote: On 09/01/2014 10:41 PM, Joel Jacobson wrote: This is exactly why we need a new language. All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in plpgsql2, with the most beautiful syntax we can come up with. I guess it's a question if we want to support things like this. If we want to, then we also want a new language. Given how much bike shedding occurs around trivial features, can you imagine how long that'd take? I wasn't aware of the expression bike shedding so I had to look it up. It apparently means spend the majority of its time on relatively unimportant but easy-to-grasp issues. If you feel the development of plpgsql falls into this category, that most time is spent on the smaller unimportant things, isn't that a clear sign we need plpgsql2, for there to be any hope of progress on the important things? Er, no. My point is that weeks can be spent just arguing about whether you should have a variable-delimiter ($variable) or not, how syntax should look, etc. Imagine how long it'd take to get a new language syntax agreed upon? You jumped in to say that you thought that: EXECUTE format(SELECT %I FROM %I WHERE $1, col, tbl) USING val; was is exactly why we need a new language and that All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in plpgsql2, with the most beautiful syntax we can come up with. But you haven't said HOW you propose to fix this one case. Show me. How do you want this to look? The user requirement is Execute a SELECT against a table whose name is provided at runtime, selecting a column or set of columns whose names are provided at runtime, with literals substituted as placement parameters. The above is ugly. Fine, not arguing. Show me what you want instead. You're happy to say how much you dislike PL/PgSQL, but I haven't seen a concrete proposal on how you want something new to look. That would be a useful and constructive start, as we could then examine, point-by-point, how/if those needs can be met in PL/PgSQL. If they can't then you'd have a more convincing argument for a new version than PL/PgSQL sucks. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/02/2014 11:42 AM, David Johnston wrote: Yet pl/pgsql does not have to use SPI-interface type calls to interact with PostgreSQL at the SQL level... That's right. I don't have an answer to your questions but the one I'm asking is whether a particular language could hide all of the SPI stuff behind some custom syntax so that it in effect looks similar to what pl/pgsql does today? Sure, it's possible. Have you seen Microsoft's LINQ for C# / .NET? One of the few really awesome things they've done in a long time. Do you see many other languages with good seamless SQL support, though? The only one I can think of off the top of my head is PROGRESS/4GL, and like PL/PgSQL it's designed around the idea of being a database-integrated language. The hard problem to solve here isn't how do I make the language talk to the database server backend. That's easy. The hard problem is how should the integrated SQL support look and work so it fits in with the language while remaining as powerful and expressive as SQL its self. It's *the same* problem as if you were using the same language via a network connection to the database, instead of embedding it. How do you make SQL syntax interoperate with your language's syntax, or produce a clean-ish adapter layer? Lots of people have tried. Most have failed dismally. ActiveRecord becomes gruesomely ugly hodgepodge of text snippets and code as soon as you try to do anything interesting/nontrivial with it. Hibernate Criteria Query - ever wanted to write 100 lines of code for a simple query? You'll love it. JOOQ? Django ORM? SQLAlchemy? iBatis/MyBatis? QueryDSL? They're all ugly in various ways. The underlying problem is that SQL, syntactically speaking, is a weird, clunky way to do things that sticks out like a sore thumb when you put it together with a language designed after 1975. Also, most of the modern languages people want to use are to some degree OO, not just procedural. That brings the OO/relational mismatch into play, and there's no pretty and easy solution for that. http://en.wikipedia.org/wiki/Object-relational_impedance_mismatch PL/PgSQL gets away with it by not being OO - it fits its model around that of SQL. If you can solve that problem - create a smooth, seamless language-integrated way to use SQL in any language of your choice - then please, please come talk about it here. Or, more to the point, does pl/pgsql use the same SPI interface behind the scenes as PL/Perl or does it have its own special interface? Take a look. cd src/pl/pgsql/src You'll see pl_handler.c, the PL/PgSQL procedural language handler. There's also pl_exec.c, containing the guts of the language runtime. SPI use is heavy throughout. PL/PgSQL uses SPI to execute queries and fetch results. See, for example, exec_stmt_execsql(...) in pl_exec.c . Any other language that can run embedded into the PostgreSQL backend can do the same. PL/Python does it: http://www.postgresql.org/docs/current/static/plpython-database.html (though unfortunately the PL/Python interface for SQL does't follow the Python DB-API). PL/V8, PL/Lua, PL/Ruby, they all use the SPI. Same as PL/PgSQL. The challenge isn't dispatching the SQL, the challenge is fitting SQL into the language seamlessly. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 02/09/14 15:46, Craig Ringer wrote: was is exactly why we need a new language and that All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in plpgsql2, with the most beautiful syntax we can come up with. But you haven't said HOW you propose to fix this one case. Unfortunately, there is likely to be a (large) variance of opinion concerning the details. In particular 'beautiful/elegant...'. Err - these things are mostly in the eye of the beholder. E.g: I might want this new shiny syntax to be lisp like, as that is beautiful (heh, kidding - but you should get the idea). Cheers Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 14/09/02 12:24, Craig Ringer wrote: On 09/02/2014 08:09 AM, Neil Tiffin wrote: (...) That should be enough alone to suggest postgreSQL start working on a modern, in core, fast, fully supported language. I couldn't disagree more. If we were to implement anything, it'd be PL/PSM (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and quirky as anything else the SQL committee has brought forth, but it's at least a standard(ish) language. For reference, and without wading into the general debate, there is an existing, albeit outdated and dormant PL/PSM implementation: http://pgfoundry.org/frs/?group_id=1000238 http://postgres.cz/wiki/SQL/PSM_Manual From my (limited) experience with the MySQL variant, it makes PL/pgSQL look positively concise and elegant. Though that's just my subjective opinion (possibly coloured by the particular implementation) and not necessarily a pro/contra argument ;). Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
Craig Ringer cr...@2ndquadrant.com writes: If someone came up with a convincing PL/SQL compatibility layer then it'd be worth considering adopting - when it was ready. But of course, anyone who does the work for that is quite likely to want to sell it to cashed-up Oracle users looking to save a few hundred grand on per-CPU licensing. As a case in point, EDB have spent quite a few man-years on their Oracle compatibility layer; and it's still not a terribly exact match, according to my colleagues who have looked at it. So that is a tarbaby I don't personally care to touch ... even ignoring the fact that cutting off EDB's air supply wouldn't be a good thing for the community to do. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/02/2014 12:40 PM, Tom Lane wrote: As a case in point, EDB have spent quite a few man-years on their Oracle compatibility layer; and it's still not a terribly exact match, according to my colleagues who have looked at it. So that is a tarbaby I don't personally care to touch ... even ignoring the fact that cutting off EDB's air supply wouldn't be a good thing for the community to do. Yep. Especially as PL/SQL is not a lovely language to work with anyway; if the goal was a better built-in PL then PL/SQL wouldn't be my first choice by any stretch. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
