Re: [HACKERS] PL/pgSQL 1.2
On 2014-09-06 06:59, Pavel Stehule wrote: People can prepare a simple functions like you did: ... And then use it in mass operations: BEGIN FOR company IN SELECT * FROM company_list() LOOP FOR id IN SELECT * FROM user_list(company) LOOP update_user(id); END LOOP; Or use it in application same style. Yes, someone *could* do that, people are dumb. But that's sort of *exactly* why we do it. We wrap these things into (sometimes) simple-looking function so that none of the application developers ever run any SQL. We define an interface between the application and the database, and that interface is implemented using PL/PgSQL functions. Sure, sometimes one function will just fire off a single UPDATE .. RETURNING, or a SELECT, but that doesn't matter. The trick is to be consistent everywhere. But further, even if we did follow every single one of the above points perfectly, it wouldn't change the point we're trying to make. What we're doing is following what the book dedicated an entire chapter to: Defensive Programming. Enforcing that that UPDATE affected exactly one row? Defensive Programming. Your strategy is defensive. 100%. But then I don't understand to your resistant to verbosity. It is one basic stone of Ada design The problem of defensive strategy in stored procedures is possibility to block optimizer and result can be terrible slow. On the end, it needs a complex clustering solution, complex HA24 solution and higher complexity ~ less safety. This is not problem on low load or low data applications. Banking applications are safe (and I accept, so there it is necessary), but they are not famous by speed. Right. We deal with money. In general, I'll take slow over buggy any day. .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 1.2
(Forgot to answer to this part) On 2014-09-06 06:59, Pavel Stehule wrote: Your strategy is defensive. 100%. But then I don't understand to your resistant to verbosity. It is one basic stone of Ada design I've never programmed in Ada, but I don't necessarily see why more verbose would unconditionally mean more defensive. My primary reason for objecting to some of the syntax suggestions that have been thrown around previously and during the last couple of days is that once you increase verbosity enough, the specialized syntax starts to be less and less desirable compared to what you can already do today. And even that I only try to apply to the parts of the syntax I find verbose just for the sake of being verbose, i.e. without any additional functionality, disambiguity or clarity. For example, having something like a CONSTRAINT CHECK (row_count = 1); is not really significantly better than RETURNING TRUE INTO STRICT _OK. It's better because the intent is more clear, and because you don't need a special _OK variable, but it still has 90% of the pain of the syntax you can use today. That being the useless verbosity. .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 1.2
On Sat, Sep 6, 2014 at 6:59 AM, Pavel Stehule pavel.steh...@gmail.com wrote: People can prepare a simple functions like you did: ... CREATE OR REPLACE FUNCTION user_list () RETURNS SETOF id AS $$ BEGIN RETURN QUERY SELECT id FROM user WHERE .. some = $1 END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION update_user(int) RETURNS void AS $$ BEGIN UPDATE user SET .. WHERE id = $1 END; $$ LANGUAGE; And then use it in mass operations: BEGIN FOR company IN SELECT * FROM company_list() LOOP FOR id IN SELECT * FROM user_list(company) LOOP update_user(id); END LOOP; Or use it in application same style. It is safe .. sure, and I accept it. But It is terrible slow. The above is horrible and ugly. That's not how I write code. Only for top-level functions, i.e. API-functions, is it motivated to encapsulate even simple queries like that, but *never* in other PL-functions, as that doesn't fulfil any purpose, putting simple queries inside functions only make it less obvious what the code does where you have a function call instead of a SQL-query. -- 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 1.2
2014-09-06 15:12 GMT+02:00 Joel Jacobson j...@trustly.com: On Sat, Sep 6, 2014 at 6:59 AM, Pavel Stehule pavel.steh...@gmail.com wrote: People can prepare a simple functions like you did: ... CREATE OR REPLACE FUNCTION user_list () RETURNS SETOF id AS $$ BEGIN RETURN QUERY SELECT id FROM user WHERE .. some = $1 END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION update_user(int) RETURNS void AS $$ BEGIN UPDATE user SET .. WHERE id = $1 END; $$ LANGUAGE; And then use it in mass operations: BEGIN FOR company IN SELECT * FROM company_list() LOOP FOR id IN SELECT * FROM user_list(company) LOOP update_user(id); END LOOP; Or use it in application same style. It is safe .. sure, and I accept it. But It is terrible slow. The above is horrible and ugly. That's not how I write code. Only for top-level functions, i.e. API-functions, is it motivated to encapsulate even simple queries like that, but *never* in other PL-functions, as that doesn't fulfil any purpose, putting simple queries inside functions only make it less obvious what the code does where you have a function call instead of a SQL-query. It is ugly, but I meet it. Its nothing special. Pavel
Re: [HACKERS] PL/pgSQL 1.2
On 09/06/2014 04:21 AM, Marko Tiikkaja wrote: We wrap these things into (sometimes) simple-looking function so that none of the application developers ever run any SQL. We define an interface between the application and the database, and that interface is implemented using PL/PgSQL functions. Sure, sometimes one function will just fire off a single UPDATE .. RETURNING, or a SELECT, but that doesn't matter. The trick is to be consistent everywhere. There is precisely your root problem. Instead of educating your application developers on how to properly use a relational database system, you try to make it foolproof. Guess what, the second you made something foolproof, evolution will create a dumber fool. This is a race you cannot win. Regards, Jan -- Jan Wieck Senior Software Engineer http://slony.info -- 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 1.2
On 2014-09-06 6:12 PM, Jan Wieck wrote: On 09/06/2014 04:21 AM, Marko Tiikkaja wrote: We wrap these things into (sometimes) simple-looking function so that none of the application developers ever run any SQL. We define an interface between the application and the database, and that interface is implemented using PL/PgSQL functions. Sure, sometimes one function will just fire off a single UPDATE .. RETURNING, or a SELECT, but that doesn't matter. The trick is to be consistent everywhere. There is precisely your root problem. Instead of educating your application developers on how to properly use a relational database system, you try to make it foolproof. Foolproofing is just one thing that's good about this solution. The other one would be that the application *doesn't need to know* what's going on behind the scenes. The app deals with a consistent API, and we make that API happen with PL/PgSQL. Guess what, the second you made something foolproof, evolution will create a dumber fool. This is a race you cannot win. You're completely missing the point. .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 1.2
06.09.2014 19:12, Jan Wieck kirjoitti: On 09/06/2014 04:21 AM, Marko Tiikkaja wrote: We wrap these things into (sometimes) simple-looking function so that none of the application developers ever run any SQL. We define an interface between the application and the database, and that interface is implemented using PL/PgSQL functions. Sure, sometimes one function will just fire off a single UPDATE .. RETURNING, or a SELECT, but that doesn't matter. The trick is to be consistent everywhere. There is precisely your root problem. Instead of educating your application developers on how to properly use a relational database system, you try to make it foolproof. There are also other reasons to wrap everything in functions, for example sharding using pl/proxy which by the way always throws an error if a SELECT didn't match exactly one row and the function wasn't declared returning 'SETOF' (although it currently doesn't set any sqlstate for these errors making it a bit difficult to properly catch them.) Anyway, I think the discussed feature to make select, update and delete throw an error if they returned or modified 1 row would be more useful as an extension of the basic sql statements instead of a plpgsql (2) only feature to make it possible to use it from other languages and outside functions. / Oskari -- 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 1.2
On 2014-09-06 7:34 PM, Oskari Saarenmaa wrote: Anyway, I think the discussed feature to make select, update and delete throw an error if they returned or modified 1 row would be more useful as an extension of the basic sql statements instead of a plpgsql (2) only feature to make it possible to use it from other languages and outside functions. I can't really say I object to this, but doing it in the PL allows the parameters to be printed as well, akin to the plpgsql.print_strict_params setting added in 9.4. Though I wonder if that would still be possible if PL/PgSQL peeked inside the parse tree a bit to pull out these constraints or something *waves hands*. Or perhaps there's a better way to attach a helpful DETAIL line to the error. .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 1.2
On 2014-09-04 2:28 PM, I wrote: On 9/4/14 2:04 PM, Pavel Stehule wrote: for example best practices for PL/SQL by Steven Feuerstein I'll spend some time with that book to have a better idea on where you're coming from. I've read through this book twice now. Some observations on things we don't follow: - We don't use the exact hungarian notation -ish convention for naming stuff. I don't see that as a bad thing. - Granted, we could be using the myfield tablename.columnname%TYPE; probably more. On the other hand, sometimes you would prefer to not have all your types in your functions change transparently after an ALTER TABLE. - The book takes the single exit point thinking to an extreme. I don't agree with that, regardless of the language (and thus I might not necessarily always follow it). - The book says Encapsulate INSERT, UPDATE, and DELETE statements behind procedure calls, which quite directly contradicts what you said earlier. The rest of the stuff we follow in our codebase as far as I can tell (except the Oracle-specific stuff, obviously). But further, even if we did follow every single one of the above points perfectly, it wouldn't change the point we're trying to make. What we're doing is following what the book dedicated an entire chapter to: Defensive Programming. Enforcing that that UPDATE affected exactly one row? Defensive Programming. .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 1.2
2014-09-06 4:25 GMT+02:00 Marko Tiikkaja ma...@joh.to: On 2014-09-04 2:28 PM, I wrote: On 9/4/14 2:04 PM, Pavel Stehule wrote: for example best practices for PL/SQL by Steven Feuerstein I'll spend some time with that book to have a better idea on where you're coming from. I've read through this book twice now. Some observations on things we don't follow: - We don't use the exact hungarian notation -ish convention for naming stuff. I don't see that as a bad thing. - Granted, we could be using the myfield tablename.columnname%TYPE; probably more. On the other hand, sometimes you would prefer to not have all your types in your functions change transparently after an ALTER TABLE. - The book takes the single exit point thinking to an extreme. I don't agree with that, regardless of the language (and thus I might not necessarily always follow it). - The book says Encapsulate INSERT, UPDATE, and DELETE statements behind procedure calls, which quite directly contradicts what you said earlier. Not necessary -- It say -- complex SQL should not be used more times in code, but there is not specified, so they must by stored in trivial functions. Complex queries should be wrapped by views instead - it doesn't block a optimizer There is a strong warning to not break optimizer. The rest of the stuff we follow in our codebase as far as I can tell (except the Oracle-specific stuff, obviously). Ten years ago I wrote article http://postgres.cz/wiki/PL/pgSQL_%28en%29#Recommendation_for_design_of_stored_procedures_in_PL.2FpqSQL_language based on Steve F, Joe Celko and others presentations and books http://postgres.cz/wiki/PL/pgSQL_%28en%29#Recommendation_for_design_of_stored_procedures_in_PL.2FpqSQL_language There is point: Don't enclose SQL commands to simply functions uselessly. Where is a problem. People can prepare a simple functions like you did: ... CREATE OR REPLACE FUNCTION user_list () RETURNS SETOF id AS $$ BEGIN RETURN QUERY SELECT id FROM user WHERE .. some = $1 END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION update_user(int) RETURNS void AS $$ BEGIN UPDATE user SET .. WHERE id = $1 END; $$ LANGUAGE; And then use it in mass operations: BEGIN FOR company IN SELECT * FROM company_list() LOOP FOR id IN SELECT * FROM user_list(company) LOOP update_user(id); END LOOP; Or use it in application same style. It is safe .. sure, and I accept it. But It is terrible slow. If you are lucky and have some knowledges, you can use a SQL function in Postgres. It is a macros, so it is not a black bock for optimizer, but I am not sure, if postgres optimizer can do well work in this case too. This is Joe Celko lovely theme. But further, even if we did follow every single one of the above points perfectly, it wouldn't change the point we're trying to make. What we're doing is following what the book dedicated an entire chapter to: Defensive Programming. Enforcing that that UPDATE affected exactly one row? Defensive Programming. Your strategy is defensive. 100%. But then I don't understand to your resistant to verbosity. It is one basic stone of Ada design The problem of defensive strategy in stored procedures is possibility to block optimizer and result can be terrible slow. On the end, it needs a complex clustering solution, complex HA24 solution and higher complexity ~ less safety. This is not problem on low load or low data applications. Banking applications are safe (and I accept, so there it is necessary), but they are not famous by speed. Pavel .marko
Re: [HACKERS] PL/pgSQL 1.2
On Wed, Sep 3, 2014 at 11:19 PM, Hannu Krosing ha...@2ndquadrant.com wrote: SELECT[1] - select exactly one row, anything else raises error SELECT[0:1] - select zero or one rows, anything else raises error SELECT[1:] - select one or more rows plain SELECT is equivalent to SELECT[0:] same syntax could be used for enforcing sane affected row counts for INSERT and DELETE +1 for a new fresh creative idea! My mind was stuck in a looking for keywords state. The only suggestion I think is slightly better is the STRICT UPDATE, but only if I'm right assuming the one row use-case is much more common than the zero or one rows and one or more rows use-cases. If all use-cases are equally important to support in a nice way, then the suggested syntax is brilliant, as it supports all of them. Bonus points for the extremely condensed syntax. 2. Substitute for EXECUTE with string manipulation using backticks `` for value/command substitution in SQL as an alternative to EXECUTE string +1 for being backwards compatible, but I have no strong opinion. 3. A way to tell pl/pggsql not to cache plans fro normal queries --- OR we could do it in SQL-ish way using like this: SELECT ... USING FRESH PLAN; +1 for clean syntax -- 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 1.2
2014-09-04 9:37 GMT+02:00 Joel Jacobson j...@trustly.com: On Wed, Sep 3, 2014 at 11:19 PM, Hannu Krosing ha...@2ndquadrant.com wrote: SELECT[1] - select exactly one row, anything else raises error SELECT[0:1] - select zero or one rows, anything else raises error SELECT[1:] - select one or more rows plain SELECT is equivalent to SELECT[0:] same syntax could be used for enforcing sane affected row counts for INSERT and DELETE +1 for a new fresh creative idea! My mind was stuck in a looking for keywords state. The only suggestion I think is slightly better is the STRICT UPDATE, but only if I'm right assuming the one row use-case is much more common than the zero or one rows and one or more rows use-cases. If all use-cases are equally important to support in a nice way, then the suggested syntax is brilliant, as it supports all of them. Bonus points for the extremely condensed syntax. 2. Substitute for EXECUTE with string manipulation using backticks `` for value/command substitution in SQL as an alternative to EXECUTE string +1 for being backwards compatible, but I have no strong opinion. 3. A way to tell pl/pggsql not to cache plans fro normal queries --- OR we could do it in SQL-ish way using like this: SELECT ... USING FRESH PLAN; +1 for clean syntax we have totally different opinion what is good Regards Pavel
Re: [HACKERS] PL/pgSQL 1.2
On Thu, Sep 4, 2014 at 9:39 AM, Pavel Stehule pavel.steh...@gmail.com wrote: we have totally different opinion what is good Can you elaborate on that? Your ASSERT CHECK ROWCOUNT = 1; is lengthly, which is why I don't like it. Imagine if having to type my $var === 'foo'; instead of my $var = 'foo'; on every single line of could where you want to assign a variable, that would just be ridiculous. If you have a typical CRUD application and decide to do *all* data operations via PL functions, which is a design pattern advocated by many*, then you will end up with a lot of very simple short PL functions, to do things like update_worker_status(), set_notification_response(), etc, in which you always pass something which is a primary key in some table, and want to update exactly one row. Having to type 27 extra characters for every single line of code, instead of the suggested 3 extra characters, is a big difference, for anyone who designs a CRUD application which relies on the usage of PL functions. For me, it would be useful to understand if you are developing CRUD applications, or if your main usage for PL/pgSQL functions are other things? If the latter, then maybe that could explain why you don't feel strongly about simplifying and condensing the syntax for the most common use-case of them all. *) but there are probably equally who prefer to handle business logics outside the database -- 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 1.2
On 9/4/14 2:10 AM, Hannu Krosing wrote: On 09/04/2014 12:17 AM, Marko Tiikkaja wrote: I'm not sure how much I like that syntax in cases like: WITH t AS ( -- multi-line query here ) SELECT[0:] foo, bar INTO _bat, _man FROM foo JOIN .. JOIN .. WHERE .. -- etc. It seems quite well hidden compared to a single keyword at the beginning of the query. What do you have in mind ? I'm just comparing it to the STRICT UPDATE ..; and ONE ROW UPDATE ..; syntaxes proposed earlier. Is your wiki page already available somewhere ? I'm working on getting it up ASAP; hopefully it will be today around the UTC noon. .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 1.2
2014-09-04 10:06 GMT+02:00 Joel Jacobson j...@trustly.com: On Thu, Sep 4, 2014 at 9:39 AM, Pavel Stehule pavel.steh...@gmail.com wrote: we have totally different opinion what is good Can you elaborate on that? I would to elaborate on enhancing plpgsql - but my primary target is readability without necessity of special special statements, types. I am strong against to create some shortcuts for relative too special use case. Your ASSERT CHECK ROWCOUNT = 1; is lengthly, which is why I don't like it. Imagine if having to type my $var === 'foo'; instead of my $var = 'foo'; on every single line of could where you want to assign a variable, that would just be ridiculous. If you have a typical CRUD application and decide to do *all* data operations via PL functions, which is a design pattern advocated by many*, then you will end up with a lot of very simple short PL functions, to do things like update_worker_status(), set_notification_response(), etc, in which you always pass something which is a primary key in some table, and want to update exactly one row. Having to type 27 extra characters for every single line of code, instead of the suggested 3 extra characters, is a big difference, for anyone who designs a CRUD application which relies on the usage of PL functions. Is not better to design special PL for this usage? I understand to your motivation, but it is not acceptable for me in plpgsql. Ten years ago, we had to solve similar problem - and we designed metalanguage that was translated to plpgsql. For me, it would be useful to understand if you are developing CRUD applications, or if your main usage for PL/pgSQL functions are other things? I am strong in opinion so PLpgSQL is targeted primary for implementation business logic in server side. CRUD is only one from possible use cases - and without any special importance to others. If the latter, then maybe that could explain why you don't feel strongly about simplifying and condensing the syntax for the most common use-case of them all. I don't agree so what you propose, it is common use case. And I don't think so it can be used in synergy with current design *) but there are probably equally who prefer to handle business logics outside the database It is maybe main difference between me and you. Usually I don't write CRUD applications, and I am not sure if plpgsql is good for CRUD. Mainly I would not to optimize plpgsql primary for CRUD.
Re: [HACKERS] PL/pgSQL 1.2
On 9/4/14 10:42 AM, Pavel Stehule wrote: 2014-09-04 10:06 GMT+02:00 Joel Jacobson j...@trustly.com: *) but there are probably equally who prefer to handle business logics outside the database It is maybe main difference between me and you. Usually I don't write CRUD applications, and I am not sure if plpgsql is good for CRUD. Mainly I would not to optimize plpgsql primary for CRUD. I don't think providing syntax to support the CRUD-like use case would be optimizing it primarily for CRUD. Changing how UPDATE and DELETE work by default would be, but that's not being suggested here (anymore). .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 1.2
On Thu, Sep 4, 2014 at 10:42 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I am strong in opinion so PLpgSQL is targeted primary for implementation business logic in server side. CRUD is only one from possible use cases - and without any special importance to others. Just curious, what kind of business logic do you write, where one row is not more special to you than two rows or five rows? Think about for a second what PL functions are able to return. Compare, RETURNS RECORD vs RETURNS SETOF RECORD When you return from a function, you get exactly 1 row, unless you explicitly use the SETOF RECORD syntax, where 0...n rows are possible. To add to that, we have the STRICT keyword, which also recognize the fact 1 row is special. So, we already *have* special handling for the 1 row case in many areas. I cannot see how you can fail to agree it would be a good thing to make it simple also for UPDATE/DELETE/INSERT. On Thu, Sep 4, 2014 at 10:42 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2014-09-04 10:06 GMT+02:00 Joel Jacobson j...@trustly.com: On Thu, Sep 4, 2014 at 9:39 AM, Pavel Stehule pavel.steh...@gmail.com wrote: we have totally different opinion what is good Can you elaborate on that? I would to elaborate on enhancing plpgsql - but my primary target is readability without necessity of special special statements, types. I am strong against to create some shortcuts for relative too special use case. Your ASSERT CHECK ROWCOUNT = 1; is lengthly, which is why I don't like it. Imagine if having to type my $var === 'foo'; instead of my $var = 'foo'; on every single line of could where you want to assign a variable, that would just be ridiculous. If you have a typical CRUD application and decide to do *all* data operations via PL functions, which is a design pattern advocated by many*, then you will end up with a lot of very simple short PL functions, to do things like update_worker_status(), set_notification_response(), etc, in which you always pass something which is a primary key in some table, and want to update exactly one row. Having to type 27 extra characters for every single line of code, instead of the suggested 3 extra characters, is a big difference, for anyone who designs a CRUD application which relies on the usage of PL functions. Is not better to design special PL for this usage? I understand to your motivation, but it is not acceptable for me in plpgsql. Ten years ago, we had to solve similar problem - and we designed metalanguage that was translated to plpgsql. For me, it would be useful to understand if you are developing CRUD applications, or if your main usage for PL/pgSQL functions are other things? I am strong in opinion so PLpgSQL is targeted primary for implementation business logic in server side. CRUD is only one from possible use cases - and without any special importance to others. If the latter, then maybe that could explain why you don't feel strongly about simplifying and condensing the syntax for the most common use-case of them all. I don't agree so what you propose, it is common use case. And I don't think so it can be used in synergy with current design *) but there are probably equally who prefer to handle business logics outside the database It is maybe main difference between me and you. Usually I don't write CRUD applications, and I am not sure if plpgsql is good for CRUD. Mainly I would not to optimize plpgsql primary for CRUD. -- 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 1.2
2014-09-04 10:53 GMT+02:00 Marko Tiikkaja ma...@joh.to: On 9/4/14 10:42 AM, Pavel Stehule wrote: 2014-09-04 10:06 GMT+02:00 Joel Jacobson j...@trustly.com: *) but there are probably equally who prefer to handle business logics outside the database It is maybe main difference between me and you. Usually I don't write CRUD applications, and I am not sure if plpgsql is good for CRUD. Mainly I would not to optimize plpgsql primary for CRUD. I don't think providing syntax to support the CRUD-like use case would be optimizing it primarily for CRUD. Changing how UPDATE and DELETE work by default would be, but that's not being suggested here (anymore). I am strong in opinion so safe stored procedures should be verbose. It is in contradiction to Joel direction. I wrote a proposal, how to do more friendly but still enough verbose Pavel .marko
Re: [HACKERS] PL/pgSQL 1.2
2014-09-04 10:57 GMT+02:00 Joel Jacobson j...@trustly.com: On Thu, Sep 4, 2014 at 10:42 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I am strong in opinion so PLpgSQL is targeted primary for implementation business logic in server side. CRUD is only one from possible use cases - and without any special importance to others. Just curious, what kind of business logic do you write, where one row is not more special to you than two rows or five rows? Think about for a second what PL functions are able to return. Compare, RETURNS RECORD vs RETURNS SETOF RECORD it is different semantic - returns composite or set of composites --- it is not row or rows Actually BL is usually processed oriented, so PL functions coverages changes in data, and for queries you use SELECT Returning SET from function is less often - and usually it is not in preferred patterns because you can very simple block a optimizer. When you return from a function, you get exactly 1 row, unless you explicitly use the SETOF RECORD syntax, where 0...n rows are possible. To add to that, we have the STRICT keyword, which also recognize the fact 1 row is special. So, we already *have* special handling for the 1 row case in many areas. I cannot see how you can fail to agree it would be a good thing to make it simple also for UPDATE/DELETE/INSERT. On Thu, Sep 4, 2014 at 10:42 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2014-09-04 10:06 GMT+02:00 Joel Jacobson j...@trustly.com: On Thu, Sep 4, 2014 at 9:39 AM, Pavel Stehule pavel.steh...@gmail.com wrote: we have totally different opinion what is good Can you elaborate on that? I would to elaborate on enhancing plpgsql - but my primary target is readability without necessity of special special statements, types. I am strong against to create some shortcuts for relative too special use case. Your ASSERT CHECK ROWCOUNT = 1; is lengthly, which is why I don't like it. Imagine if having to type my $var === 'foo'; instead of my $var = 'foo'; on every single line of could where you want to assign a variable, that would just be ridiculous. If you have a typical CRUD application and decide to do *all* data operations via PL functions, which is a design pattern advocated by many*, then you will end up with a lot of very simple short PL functions, to do things like update_worker_status(), set_notification_response(), etc, in which you always pass something which is a primary key in some table, and want to update exactly one row. Having to type 27 extra characters for every single line of code, instead of the suggested 3 extra characters, is a big difference, for anyone who designs a CRUD application which relies on the usage of PL functions. Is not better to design special PL for this usage? I understand to your motivation, but it is not acceptable for me in plpgsql. Ten years ago, we had to solve similar problem - and we designed metalanguage that was translated to plpgsql. For me, it would be useful to understand if you are developing CRUD applications, or if your main usage for PL/pgSQL functions are other things? I am strong in opinion so PLpgSQL is targeted primary for implementation business logic in server side. CRUD is only one from possible use cases - and without any special importance to others. If the latter, then maybe that could explain why you don't feel strongly about simplifying and condensing the syntax for the most common use-case of them all. I don't agree so what you propose, it is common use case. And I don't think so it can be used in synergy with current design *) but there are probably equally who prefer to handle business logics outside the database It is maybe main difference between me and you. Usually I don't write CRUD applications, and I am not sure if plpgsql is good for CRUD. Mainly I would not to optimize plpgsql primary for CRUD.
Re: [HACKERS] PL/pgSQL 1.2
On Thu, Sep 4, 2014 at 11:07 AM, Pavel Stehule pavel.steh...@gmail.com wrote: it is different semantic - returns composite or set of composites --- it is not row or rows The point was, RETURNS returns 1 while RETURNS SETOF returns 0 .. n. Actually BL is usually processed oriented, so PL functions coverages changes in data, and for queries you use SELECT OK, so you SELECT directly from tables? And in the PLs you change a lot of rows in the same txn? Returning SET from function is less often - and usually it is not in preferred patterns because you can very simple block a optimizer. Not if you do all access, also SELECT via PLs, then you might want to returns lists of things based on some input. But that's a different topic. What I wanted to examplify is the fact we *already* have a lot of syntax which handles the 1 row case in a special way. -- 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 1.2
2014-09-04 11:22 GMT+02:00 Joel Jacobson j...@trustly.com: On Thu, Sep 4, 2014 at 11:07 AM, Pavel Stehule pavel.steh...@gmail.com wrote: it is different semantic - returns composite or set of composites --- it is not row or rows The point was, RETURNS returns 1 while RETURNS SETOF returns 0 .. n. no RETURNS return VALUE (it is not a row) .. and in combination with SELECT - value will be a row. RETURNS SETOF returns rows Set Returning Functions is interesting concept, but with some issues too - when you use it in target part instead FROM part. Actually BL is usually processed oriented, so PL functions coverages changes in data, and for queries you use SELECT OK, so you SELECT directly from tables? And in the PLs you change a lot of rows in the same txn? depends - if you be more strict, then direct access to tables is prohibited and only access to views is enables. There is simple rules: reading - selects to tables or views, writing PL -- data are changes inside some process and any process should be covered by one or more PL hard to say, how often you are change only one row maybe 50/50% -- when you need fix some stored data. Insert or delete will be different Returning SET from function is less often - and usually it is not in preferred patterns because you can very simple block a optimizer. Not if you do all access, also SELECT via PLs, then you might want to returns lists of things based on some input. But that's a different topic. What I wanted to examplify is the fact we *already* have a lot of syntax which handles the 1 row case in a special way. I know what is CRUD, and I looked to your functions from github and I understand to your motivation. Just have different opinion about benefits of some your proposal, because I use plpgsql little bit different. Using PL only for CRUD is stopping in 1/10 way :). Pavel
Re: [HACKERS] PL/pgSQL 1.2
Everyone, I've started a wiki page with the list of the things I could think of at this very moment. I probably got the most annoying ones in there, but I also might have forgotten about some things. I invite discussion of every suggestion on -HACKERS. https://wiki.postgresql.org/wiki/Improving_PL/PgSQL_(September_2014) I'm sure other people's lists would look different; perhaps they should be added to the same page? Should we divide it based on who's suggesting the change to keep a better track? Anyway, you can start shooting now that we have at least one list of concrete proposals. .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 1.2
2014-09-04 13:37 GMT+02:00 Marko Tiikkaja ma...@joh.to: Everyone, I've started a wiki page with the list of the things I could think of at this very moment. I probably got the most annoying ones in there, but I also might have forgotten about some things. I invite discussion of every suggestion on -HACKERS. https://wiki.postgresql.org/wiki/Improving_PL/PgSQL_(September_2014) I'm sure other people's lists would look different; perhaps they should be added to the same page? Should we divide it based on who's suggesting the change to keep a better track? Anyway, you can start shooting now that we have at least one list of concrete proposals. where I can wrote comments? Usually I wrote them but I have to repeat it. I am sorry, It is difficult - your usage of plpgsql is sometimes less, some times more against best practices :( PL functions should not be only envelope to SQL statement Pavel .marko
Re: [HACKERS] PL/pgSQL 1.2
On 9/4/14 1:47 PM, Pavel Stehule wrote: 2014-09-04 13:37 GMT+02:00 Marko Tiikkaja ma...@joh.to: I've started a wiki page with the list of the things I could think of at this very moment. I probably got the most annoying ones in there, but I also might have forgotten about some things. I invite discussion of every suggestion on -HACKERS. where I can wrote comments? I guess comments could be added as a subsection if you feel -HACKERS doesn't work. I am sorry, It is difficult - your usage of plpgsql is sometimes less, some times more against best practices :( Best practices according to whom? And which parts, exactly? There's quite a lot of stuff in there, both explicitly stated and implicitly assumed. PL functions should not be only envelope to SQL statement I disagree, to some extent. Our external applications interface with the database only by calling functions (which are often written in PL/PgSQL). If that function has no more work to do than to run a single query, then yes, the function will just serve as an envelope to a single query. But that also allows the function to be changed in the future without having to change the application. Similarly to how you would expose an API when communicating with others instead of giving them a postgres user account and saying update these tables and after a month saying oh, you need to remember to INSERT into this table as well or we won't have logs of what you did. .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 1.2
2014-09-04 13:54 GMT+02:00 Marko Tiikkaja ma...@joh.to: On 9/4/14 1:47 PM, Pavel Stehule wrote: 2014-09-04 13:37 GMT+02:00 Marko Tiikkaja ma...@joh.to: I've started a wiki page with the list of the things I could think of at this very moment. I probably got the most annoying ones in there, but I also might have forgotten about some things. I invite discussion of every suggestion on -HACKERS. where I can wrote comments? I guess comments could be added as a subsection if you feel -HACKERS doesn't work. ok I am sorry, It is difficult - your usage of plpgsql is sometimes less, some times more against best practices :( Best practices according to whom? And which parts, exactly? There's quite a lot of stuff in there, both explicitly stated and implicitly assumed. PL functions should not be only envelope to SQL statement I disagree, to some extent. Our external applications interface with the database only by calling functions (which are often written in PL/PgSQL). If that function has no more work to do than to run a single query, then yes, the function will just serve as an envelope to a single query. But that also allows the function to be changed in the future without having to change the application. Similarly to how you would expose an API when communicating with others instead of giving them a postgres user account and saying update these tables and after a month saying oh, you need to remember to INSERT into this table as well or we won't have logs of what you did. for example best practices for PL/SQL by Steven Feuerstein We can talk about it - it subjective and I know so there are not only one style. For example, you can use a ALIAS to parameters if you have a problem with parameter variables postgres=# create or replace function foo(out a int) as $$ declare _x alias for a; begin _x := 10; end $$ language plpgsql; CREATE FUNCTION postgres=# select foo(); foo - 10 (1 row) or if you can be more secure use a block label postgres=# create or replace function foo(out a int) as $$ main declare _x alias for a; begin main._x := 10; end $$ language plpgsql; CREATE FUNCTION postgres=# select foo(); foo - 10 (1 row) The language has these possibilities. Why to add new redundant? Pavel .marko
Re: [HACKERS] PL/pgSQL 1.2
On Thu, Sep 4, 2014 at 4:06 AM, Joel Jacobson j...@trustly.com wrote: Your ASSERT CHECK ROWCOUNT = 1; is lengthly, which is why I don't like it. Imagine if having to type my $var === 'foo'; instead of my $var = 'foo'; on every single line of could where you want to assign a variable, that would just be ridiculous. This is really the core of the problem. PL/pgsql is an incredibly verbose language, and things that ought to be expressable in a small number of characters often take a large number of characters. The reason this problem is hard to solve is that PL/pgsql is based on SQL, and SQL is inherently pretty verbose. When we try to extend PL/pgsql, we can either pick syntax that looks like the existing language (which sucks because it's verbose) or we can pick syntax that is compact (which sucks because it looks nothing like the rest of the language). This is a deep and fundamental conflict that is not going away. So I'm with the crowd of people who says there's really nothing that can be done to improve PL/pgsql much. Yeah, we could make certain cases a little better at the expense of certain other cases (which is how this thread got started), but it's not really clear that we'd end up ahead at all (since a lot of people objected to the proposed changes) and if we did we wouldn't end up very far ahead (since the original proposal consisted of three minor items which are not going to radically transform anyone's experience). Even Oracle, who has a vastly larger RD budget than the PostgreSQL community, frankly hasn't done all that much with it. They have some nice conveniences which we lack, but it's still a clunky language. So, I think the right solution here is to work on improving the user experience people have with other procedural languages. Perl, Python, and Javascript are all notable for being terse, sometimes to excess. I remember trying to use PL/perl many years ago and giving up on it because of various problems, like not being able to get require to work, and not being able to create helper functions that could be called directly from Perl without going back through the SQL layer. But some of those kinds of things may have been fixed between then and now, and whichever ones haven't probably can be if people are willing to put in some work. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 1.2
On 9/4/14 2:04 PM, Pavel Stehule wrote: for example best practices for PL/SQL by Steven Feuerstein I'll spend some time with that book to have a better idea on where you're coming from. Also, *please* don't try and extrapolate what I do based on the code examples on the wiki page; they're all crap just to point out the issues. We can talk about it - it subjective and I know so there are not only one style. The language has these possibilities. Why to add new redundant? Adding a new alias for every single OUT parameter for every single function seems like a waste of time. It also doesn't improve readability in the way that OUT.foo := 1; does (though I guess you could add an out_ prefix to all of them). .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 1.2
On 4 sep 2014, at 11:42, Pavel Stehule pavel.steh...@gmail.com wrote: 2014-09-04 11:22 GMT+02:00 Joel Jacobson j...@trustly.com: The point was, RETURNS returns 1 while RETURNS SETOF returns 0 .. n. no RETURNS return VALUE (it is not a row) .. and in combination with SELECT - value will be a row. RETURNS SETOF returns rows I intentionally excluded the data type of what is returned. 1 VALUE vs 0...n VALUES Do you still fail to see the point 1 VALUE is special in the context of what a function returns?
Re: [HACKERS] PL/pgSQL 1.2
2014-09-04 14:37 GMT+02:00 Joel Jacobson j...@trustly.com: On 4 sep 2014, at 11:42, Pavel Stehule pavel.steh...@gmail.com wrote: 2014-09-04 11:22 GMT+02:00 Joel Jacobson j...@trustly.com: The point was, RETURNS returns 1 while RETURNS SETOF returns 0 .. n. no RETURNS return VALUE (it is not a row) .. and in combination with SELECT - value will be a row. RETURNS SETOF returns rows I intentionally excluded the data type of what is returned. 1 VALUE vs 0...n VALUES Do you still fail to see the point 1 VALUE is special in the context of what a function returns? sorry, I don't understand .. for me SRF functions are absolutly different monsters than scalar, array or composite function - so its impossible to compare it. Pavel
Re: [HACKERS] PL/pgSQL 1.2
On 09/04/2014 01:14 AM, Pavel Stehule wrote: 2014-09-03 23:19 GMT+02:00 Hannu Krosing ha...@2ndquadrant.com A more SQL-ish way of doing the same could probably be called COMMAND CONSTRAINTS and look something like this SELECT ... CHECK (ROWCOUNT BETWEEN 0 AND 1); It is very near to my proposed ASSERT Only if the ASSERT syntax would become part of the original statement, it is supposed to check. In Hannu's command constraint example above, the statement that causes the error, and thus will be logged and become identified by the error message, is the actual SELECT (or other DML statement). I think I like the COMMAND CONSTRAINT the best so far. Regards, Jan -- Jan Wieck Senior Software Engineer http://slony.info -- 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 1.2
2014-09-04 15:24 GMT+02:00 Jan Wieck j...@wi3ck.info: On 09/04/2014 01:14 AM, Pavel Stehule wrote: 2014-09-03 23:19 GMT+02:00 Hannu Krosing ha...@2ndquadrant.com A more SQL-ish way of doing the same could probably be called COMMAND CONSTRAINTS and look something like this SELECT ... CHECK (ROWCOUNT BETWEEN 0 AND 1); It is very near to my proposed ASSERT Only if the ASSERT syntax would become part of the original statement, it is supposed to check. In Hannu's command constraint example above, the statement that causes the error, and thus will be logged and become identified by the error message, is the actual SELECT (or other DML statement). this is valid argument. On second hand, I proposed a ASSERT that was not based on expressions only. There is not a technical issue to write assert with knowledge of related statement. I think I like the COMMAND CONSTRAINT the best so far. I not, because when it will not be part of SQL, than parser in plpgsql will be more complex. You have to inject SELECT, UPDATE, INSERT, DELETE Pavel Regards, Jan -- Jan Wieck Senior Software Engineer http://slony.info
Re: [HACKERS] PL/pgSQL 1.2
On 09/04/2014 09:31 AM, Pavel Stehule wrote: 2014-09-04 15:24 GMT+02:00 Jan Wieck j...@wi3ck.info I think I like the COMMAND CONSTRAINT the best so far. I not, because when it will not be part of SQL, than parser in plpgsql will be more complex. You have to inject SELECT, UPDATE, INSERT, DELETE Making the COMMAND CONSTRAINT part of the core SQL parser was how I understood Hannu's idea. It would be horrible to tuck that feature away inside of a PL, rather than making it available to all PLs as well as applications, that use SQL directly (I think there still are two or three applications that do). Regards, Jan -- Jan Wieck Senior Software Engineer http://slony.info -- 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 1.2
2014-09-04 15:38 GMT+02:00 Jan Wieck j...@wi3ck.info: On 09/04/2014 09:31 AM, Pavel Stehule wrote: 2014-09-04 15:24 GMT+02:00 Jan Wieck j...@wi3ck.info I think I like the COMMAND CONSTRAINT the best so far. I not, because when it will not be part of SQL, than parser in plpgsql will be more complex. You have to inject SELECT, UPDATE, INSERT, DELETE Making the COMMAND CONSTRAINT part of the core SQL parser was how I understood Hannu's idea. It would be horrible to tuck that feature away inside of a PL, rather than making it available to all PLs as well as applications, that use SQL directly (I think there still are two or three applications that do). So I am happy so we have agreement, so implementation on PL level can be terrible. Pavel Regards, Jan -- Jan Wieck Senior Software Engineer http://slony.info
Re: [HACKERS] PL/pgSQL 1.2
On 09/03/2014 04:19 PM, Hannu Krosing wrote: 1. Conditions for number of rows returned by SELECT or touched by UPDATE or DELETE Now that I think upon this... don't we already have it? SELECT ... LIMIT 1 That already solves the purported problem of multiple results in SELECT INTO as well. Could we possibly extend that to UPDATE and DELETE syntax too? -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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 1.2
On 9/4/14 4:09 PM, Shaun Thomas wrote: On 09/03/2014 04:19 PM, Hannu Krosing wrote: 1. Conditions for number of rows returned by SELECT or touched by UPDATE or DELETE Now that I think upon this... don't we already have it? SELECT ... LIMIT 1 No, that just hides any bugs. We want the opposite: any bugs or problems should be obvious. If the query returns or touches more than one row, that should raise an error, not just give you a random one and call it a day. That already solves the purported problem of multiple results in SELECT INTO as well. Could we possibly extend that to UPDATE and DELETE syntax too? Again, this is a different problem, but LIMIT syntax for UPDATE and DELETE has been proposed, see: http://www.postgresql.org/message-id/CADB9FDf-Vh6RnKAMZ4Rrg_YP9p3THdPbji8qe4qkxRuiOwm=m...@mail.gmail.com .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 1.2
On 09/04/2014 02:40 PM, Pavel Stehule wrote: 2014-09-04 14:37 GMT+02:00 Joel Jacobson j...@trustly.com mailto:j...@trustly.com: On 4 sep 2014, at 11:42, Pavel Stehule pavel.steh...@gmail.com mailto:pavel.steh...@gmail.com wrote: 2014-09-04 11:22 GMT+02:00 Joel Jacobson j...@trustly.com mailto:j...@trustly.com: The point was, RETURNS returns 1 while RETURNS SETOF returns 0 .. n. no RETURNS return VALUE (it is not a row) .. and in combination with SELECT - value will be a row. RETURNS SETOF returns rows I intentionally excluded the data type of what is returned. 1 VALUE vs 0...n VALUES Do you still fail to see the point 1 VALUE is special in the context of what a function returns? sorry, I don't understand .. for me SRF functions are absolutly different monsters than scalar, array or composite function - so its impossible to compare it. When looking from the other end of the problem, we are using SELECT/INSERT/UPDATE/DELETE *SET statements* in pl/pgsql when we really want scalars. My understanding is that one main drivers of starting this thread was wanting also guaranteed SCALAR versions of these. And wanting them in a way that is easy to use. Cheers -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ
Re: [HACKERS] PL/pgSQL 1.2
On 4 sep 2014, at 15:32, Pavel Stehule pavel.steh...@gmail.com wrote: 2014-09-04 15:24 GMT+02:00 Jan Wieck j...@wi3ck.info: On 09/04/2014 01:14 AM, Pavel Stehule wrote: 2014-09-03 23:19 GMT+02:00 Hannu Krosing ha...@2ndquadrant.com A more SQL-ish way of doing the same could probably be called COMMAND CONSTRAINTS and look something like this SELECT ... CHECK (ROWCOUNT BETWEEN 0 AND 1); It is very near to my proposed ASSERT Only if the ASSERT syntax would become part of the original statement, it is supposed to check. In Hannu's command constraint example above, the statement that causes the error, and thus will be logged and become identified by the error message, is the actual SELECT (or other DML statement). this is valid argument. On second hand, I proposed a ASSERT that was not based on expressions only. There is not a technical issue to write assert with knowledge of related statement. I think I like the COMMAND CONSTRAINT the best so far. I not, because when it will not be part of SQL, than parser in plpgsql will be more complex. You have to inject SELECT, UPDATE, INSERT, DELETE This is what I suspected. You are against the best syntax because they are more complex to implement. I think that's coming into the discussion from the wrong direction. First agree on the best syntax, then worry about the implementation. I also understand the syntax changes will mean a lot of trouble for your plpgsql_check_function() project, but that cannot hold us back, we must aim for the best possible syntax with plpgsql2. Your work with plpgsql_check_function() btw saved me hundreds of hours of work, when we upgraded from 8.4 a few years ago, many thanks Pavel! Pavel Regards, Jan -- Jan Wieck Senior Software Engineer http://slony.info
Re: [HACKERS] PL/pgSQL 1.2
On 4 sep 2014, at 16:45, Hannu Krosing ha...@2ndquadrant.com wrote: When looking from the other end of the problem, we are using SELECT/INSERT/UPDATE/DELETE *SET statements* in pl/pgsql when we really want scalars. My understanding is that one main drivers of starting this thread was wanting also guaranteed SCALAR versions of these. And wanting them in a way that is easy to use. +1 Thank you! I have been trying to explain this in multiple cryptic ways but failed. You just nailed it! That's *exactly* what I mean! Thanks for clarifying! Cheers -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- 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 1.2
2014-09-04 17:16 GMT+02:00 Joel Jacobson j...@trustly.com: On 4 sep 2014, at 16:45, Hannu Krosing ha...@2ndquadrant.com wrote: When looking from the other end of the problem, we are using SELECT/INSERT/UPDATE/DELETE *SET statements* in pl/pgsql when we really want scalars. My understanding is that one main drivers of starting this thread was wanting also guaranteed SCALAR versions of these. And wanting them in a way that is easy to use. +1 Thank you! I have been trying to explain this in multiple cryptic ways but failed. You just nailed it! That's *exactly* what I mean! You just need a ISAM API for Postgres, That is all. Pavel Thanks for clarifying! Cheers -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ
Re: [HACKERS] PL/pgSQL 1.2
2014-09-04 17:10 GMT+02:00 Joel Jacobson j...@trustly.com: On 4 sep 2014, at 15:32, Pavel Stehule pavel.steh...@gmail.com wrote: 2014-09-04 15:24 GMT+02:00 Jan Wieck j...@wi3ck.info: On 09/04/2014 01:14 AM, Pavel Stehule wrote: 2014-09-03 23:19 GMT+02:00 Hannu Krosing ha...@2ndquadrant.com A more SQL-ish way of doing the same could probably be called COMMAND CONSTRAINTS and look something like this SELECT ... CHECK (ROWCOUNT BETWEEN 0 AND 1); It is very near to my proposed ASSERT Only if the ASSERT syntax would become part of the original statement, it is supposed to check. In Hannu's command constraint example above, the statement that causes the error, and thus will be logged and become identified by the error message, is the actual SELECT (or other DML statement). this is valid argument. On second hand, I proposed a ASSERT that was not based on expressions only. There is not a technical issue to write assert with knowledge of related statement. I think I like the COMMAND CONSTRAINT the best so far. I not, because when it will not be part of SQL, than parser in plpgsql will be more complex. You have to inject SELECT, UPDATE, INSERT, DELETE This is what I suspected. You are against the best syntax because they are more complex to implement. I think that's coming into the discussion from the wrong direction. First agree on the best syntax, then worry about the implementation. Nobody say here, so it is best syntax. It is request of proprietary enhancing of SQL and lot of people say strongly no. But you don't listen. I also understand the syntax changes will mean a lot of trouble for your plpgsql_check_function() project, but that cannot hold us back, we must aim for the best possible syntax with plpgsql2. Your work with plpgsql_check_function() btw saved me hundreds of hours of work, when we upgraded from 8.4 a few years ago, many thanks Pavel! I have no problem with plpgsql_check_function management. I remember well how issues is related to support plpgsql specific STRICT or INTO clauses. Pavel Pavel Regards, Jan -- Jan Wieck Senior Software Engineer http://slony.info
Re: [HACKERS] PL/pgSQL 1.2
On 4 sep 2014, at 17:18, Pavel Stehule pavel.steh...@gmail.com wrote: You just need a ISAM API for Postgres, That is all. Now you are being ironic, and I would prefer to keep the discussion on a serious level. You know that's not applicable in my case, you know what I do for work and what kind of system we already have. I *love* plpgsql and our development method. I just want it to get slightly more convenient and secure. When you suggest ISAM, that's like saying demolish your house and build a new one when all I want is to make small but important changes to what I already do as a professional on a daily basis. -- 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 1.2
On 09/04/2014 11:16 AM, Joel Jacobson wrote: On 4 sep 2014, at 16:45, Hannu Krosing ha...@2ndquadrant.com wrote: When looking from the other end of the problem, we are using SELECT/INSERT/UPDATE/DELETE *SET statements* in pl/pgsql when we really want scalars. My understanding is that one main drivers of starting this thread was wanting also guaranteed SCALAR versions of these. And wanting them in a way that is easy to use. +1 Thank you! I have been trying to explain this in multiple cryptic ways but failed. You just nailed it! That's *exactly* what I mean! I believe we all agree that the availability of most of the proposed functionality is desirable. I think the main difference between your point of view and that of a few others (me included) is that you prefer a language that is easy and fast to type, with as few key strokes as possible, while we prefer a language that is similar to SQL, which is rather verbose to the reader. At least when the discussion is about the default procedural language installed with the core database system. Such a language should be as similar as possible to SQL. Which is the reason why I believe that the CHECK clause belongs into the main parser, not into the PL. Regards, Jan -- Jan Wieck Senior Software Engineer http://slony.info -- 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 1.2
On Thu, Sep 4, 2014 at 11:32 AM, Joel Jacobson j...@trustly.com wrote: On 4 sep 2014, at 17:18, Pavel Stehule pavel.steh...@gmail.com wrote: You just need a ISAM API for Postgres, That is all. Now you are being ironic, and I would prefer to keep the discussion on a serious level. You know that's not applicable in my case, you know what I do for work and what kind of system we already have. I *love* plpgsql and our development method. I just want it to get slightly more convenient and secure. When you suggest ISAM, that's like saying demolish your house and build a new one when all I want is to make small but important changes to what I already do as a professional on a daily basis. Go right ahead: this is an open source project, after all, and with an extremely permissive license to boot. You can modify your copy of PL/pgsql, or clone it and make PL/joelsql and then change whatever you like. Optionally, you could then publish that on PGXN for others to use and contribute to. On the other hand, if what you want is for other people to make changes to the official versions of PostgreSQL that are supported and maintained by the community, then that's a different thing altogether. It entails two challenges: first, to persuade the community that those changes will be good for everyone, not just you; and second, convincing them that they (rather than you) should be the ones to do the work. So far I'd say you're losing the first argument, and I expect you'll lose the second one, too (barring a financial transaction, of course). I'm not trying to brush you off here - I understand your concerns, and they're not stupid. But, like most of the people who have commented, I don't agree that your proposals would be an improvement for the majority of people. There are several ways to deal with that, but if your goal is to get those changes made in the PostgreSQL community then you have to acknowledge the competing concerns to be just as valid as your own and come up with a proposal everyone can live with. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 1.2
Pavel Stehule pavel.steh...@gmail.com wrote: You just need a ISAM API for Postgres, That is all. Joel sure hasn't *shown* us anything to suggest that wouldn't answer his needs better than any PL, or explained why that wouldn't be a better solution for him. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 1.2
2014-09-04 18:02 GMT+02:00 Kevin Grittner kgri...@ymail.com: Pavel Stehule pavel.steh...@gmail.com wrote: You just need a ISAM API for Postgres, That is all. Joel sure hasn't *shown* us anything to suggest that wouldn't answer his needs better than any PL, or explained why that wouldn't be a better solution for him. I understand what Joel does. And there is a space for improvement of plpgsql - on syntax level, on internal level. But we can start with some less controversial. And some controversial points we can coverage by extensions. It is in conformance with Postgres community politics - where is not agreement, use extensions. We have to be able to write these extensions. Extensibility of plpgsql is on the begin. But for some special use cases, these extensions can be perfect. From this long discuss I am thinking so there is perfect agreement on plpgsql asserts. We needed. And now we know where assertations can be used. There is agreement on using binary casting instead IO casting every where where it is possible. And I am not against to ensuring consistent behave of assigning, returning from fce for composite types. There is small differences between rows, records, .. But should not be too hurry. There are only few people who would to changes in this area. Almost users are happy. Personally I would to see a discussion about enhancing SPI much more -- because it is base of all PL and some performance limits and some internal complexity of plpgsql (and plpgsql_check too) is based on missing some interface between SPI and PL. Regards Pavel -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] PL/pgSQL 1.2
On Thu, Sep 4, 2014 at 5:51 PM, Robert Haas robertmh...@gmail.com wrote: When you suggest ISAM, that's like saying demolish your house and build a new one when all I want is to make small but important changes to what I already do as a professional on a daily basis. Go right ahead: this is an open source project, after all, and with an extremely permissive license to boot. You can modify your copy of PL/pgsql, or clone it and make PL/joelsql and then change whatever you like. Optionally, you could then publish that on PGXN for others to use and contribute to. On the other hand, if what you want is for other people to make changes to the official versions of PostgreSQL that are supported and maintained by the community, then that's a different thing altogether. It entails two challenges: first, to persuade the community that those changes will be good for everyone, not just you; and second, convincing them that they (rather than you) should be the ones to do the work. So far I'd say you're losing the first argument, and I expect you'll lose the second one, too (barring a financial transaction, of course). I'm not trying to brush you off here - I understand your concerns, and they're not stupid. But, like most of the people who have commented, I don't agree that your proposals would be an improvement for the majority of people. There are several ways to deal with that, but if your goal is to get those changes made in the PostgreSQL community then you have to acknowledge the competing concerns to be just as valid as your own and come up with a proposal everyone can live with. If my company would write code in PL/joelsql, I think I would have a hard time through any technical due diligence in the future. :-) The main reason why I'm so eager of finding a support from you, the majority of other readers on this list, is of course because I think we as a group can come up with a much better solution to the problem than what I could on my own. And for me it's better if we can agree on *something* which improves my and others life to *some* extent, rather than to just sitting here silent waiting another 16 years for PL/pgSQL 2 to develop itself. I can certainly live with a more SQLish syntax than the one I had in mind. I'm less concerned about the verbosity of the language, if I wanted a condensed language I should have opted for some other language in the first place, so that's not my problem. -- 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 1.2
On 09/03/2014 05:09 PM, Marko Tiikkaja wrote: On 9/3/14 5:05 PM, Bruce Momjian wrote: On Wed, Sep 3, 2014 at 07:54:09AM +0200, Pavel Stehule wrote: I am not against to improve a PL/pgSQL. And I repeat, what can be done and can be done early: a) ASSERT clause -- with some other modification to allow better static analyze of DML statements, and enforces checks in runtime. b) #option or PRAGMA clause with GUC with function scope that enforce check on processed rows after any DML statement c) maybe introduction automatic variable ROW_COUNT as shortcut for GET DIAGNOSTICS rc = ROW_COUNT All these ideas are being captured somewhere, right? Where? I'm working on a wiki page with all these ideas. Some of them break backwards compatibility somewhat blatantly, some of them could be added into PL/PgSQL if we're okay with reserving a keyword for the feature. All of them we think are necessary. Ok, here are my 0.5 cents worth of proposals for some features discussed in this thread They should be backwards compatible, but perhaps they are not very ADA/SQL-kosher ;) They also could be implemented as macros first with possible optimisations in the future 1. Conditions for number of rows returned by SELECT or touched by UPDATE or DELETE - Enforcing number of rows returned/affected could be done using the following syntax which is concise and clear (and should be in no way backwards incompatible) SELECT[1] - select exactly one row, anything else raises error SELECT[0:1] - select zero or one rows, anything else raises error SELECT[1:] - select one or more rows plain SELECT is equivalent to SELECT[0:] same syntax could be used for enforcing sane affected row counts for INSERT and DELETE A more SQL-ish way of doing the same could probably be called COMMAND CONSTRAINTS and look something like this SELECT ... CHECK (ROWCOUNT BETWEEN 0 AND 1); 2. Substitute for EXECUTE with string manipulation using backticks `` for value/command substitution in SQL as an alternative to EXECUTE string Again it should be backwards compatible as , as currently `` are not allowed inside pl/pgsql functions Sample 1: ALTER USER `current_user` PASSWORD newpassword; would be expanded to EXECUTE 'ALTER USER ' || current_user || ' PASSWORD = $1' USING newpassword; Sample2: SELECT * FROM `tablename` WHERE `idcolumn` = idvalue; this could be expanded to EXECUTE 'SELECT * FROM ' || tablename || ' WHERE quote_ident(idcolumn) = $1' USING idvalue; Notice that the use of around `` forced use of quote_ident() 3. A way to tell pl/pggsql not to cache plans fro normal queries --- This could be done using a #pragma or special /* NOPLANCACHE */ comment as suggested by Pavel Or we could expand the [] descriptor from 1. to allow more options OR we could do it in SQL-ish way using like this: SELECT ... USING FRESH PLAN; Best Regards -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- 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 1.2
Hannu Krosing ha...@2ndquadrant.com wrote: [suggested syntax] Interesting. The only one that really offends me is: SELECT * FROM `tablename` WHERE `idcolumn` = idvalue; I think that should be: SELECT * FROM `tablename` WHERE `idcolumn` = idvalue; i.e., I think the backticks belong on the outside. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 1.2
On 2014-09-03 23:19, Hannu Krosing wrote: 1. Conditions for number of rows returned by SELECT or touched by UPDATE or DELETE - Enforcing number of rows returned/affected could be done using the following syntax which is concise and clear (and should be in no way backwards incompatible) SELECT[1] - select exactly one row, anything else raises error SELECT[0:1] - select zero or one rows, anything else raises error SELECT[1:] - select one or more rows plain SELECT is equivalent to SELECT[0:] same syntax could be used for enforcing sane affected row counts for INSERT and DELETE I'm not sure how much I like that syntax in cases like: WITH t AS ( -- multi-line query here ) SELECT[0:] foo, bar INTO _bat, _man FROM foo JOIN .. JOIN .. WHERE .. -- etc. It seems quite well hidden compared to a single keyword at the beginning of the query. It's also not clear whether all of this flexibility is required. Enforcing exactly one conveniently is my main priority. Supporting the at most one case could be nice, too, but anything else feels like overkill. Though if the syntax is based on numbers (and not a keyword), then I guess we get the flexibility for free anyway. I also have my doubts about how easy it would be to implement this syntax given that we're using the real SQL parser. .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 1.2
On 09/04/2014 12:17 AM, Marko Tiikkaja wrote: On 2014-09-03 23:19, Hannu Krosing wrote: 1. Conditions for number of rows returned by SELECT or touched by UPDATE or DELETE - Enforcing number of rows returned/affected could be done using the following syntax which is concise and clear (and should be in no way backwards incompatible) SELECT[1] - select exactly one row, anything else raises error SELECT[0:1] - select zero or one rows, anything else raises error SELECT[1:] - select one or more rows plain SELECT is equivalent to SELECT[0:] same syntax could be used for enforcing sane affected row counts for INSERT and DELETE I'm not sure how much I like that syntax in cases like: WITH t AS ( -- multi-line query here ) SELECT[0:] foo, bar INTO _bat, _man FROM foo JOIN .. JOIN .. WHERE .. -- etc. It seems quite well hidden compared to a single keyword at the beginning of the query. What do you have in mind ? Is your wiki page already available somewhere ? It's also not clear whether all of this flexibility is required. Enforcing exactly one conveniently is my main priority. What do you want here on top of SELECT ... INTO STRICT ... ? Supporting the at most one case could be nice, too, but anything else feels like overkill. Though if the syntax is based on numbers (and not a keyword), then I guess we get the flexibility for free anyway. I also have my doubts about how easy it would be to implement this syntax given that we're using the real SQL parser. Definitely not trivial, but at least doable :) Finding and processing SELECT[...] could probably even be done with a (regex-based ?) pre-parser . -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- 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 1.2
2014-09-03 23:19 GMT+02:00 Hannu Krosing ha...@2ndquadrant.com: On 09/03/2014 05:09 PM, Marko Tiikkaja wrote: On 9/3/14 5:05 PM, Bruce Momjian wrote: On Wed, Sep 3, 2014 at 07:54:09AM +0200, Pavel Stehule wrote: I am not against to improve a PL/pgSQL. And I repeat, what can be done and can be done early: a) ASSERT clause -- with some other modification to allow better static analyze of DML statements, and enforces checks in runtime. b) #option or PRAGMA clause with GUC with function scope that enforce check on processed rows after any DML statement c) maybe introduction automatic variable ROW_COUNT as shortcut for GET DIAGNOSTICS rc = ROW_COUNT All these ideas are being captured somewhere, right? Where? I'm working on a wiki page with all these ideas. Some of them break backwards compatibility somewhat blatantly, some of them could be added into PL/PgSQL if we're okay with reserving a keyword for the feature. All of them we think are necessary. Ok, here are my 0.5 cents worth of proposals for some features discussed in this thread They should be backwards compatible, but perhaps they are not very ADA/SQL-kosher ;) They also could be implemented as macros first with possible optimisations in the future 1. Conditions for number of rows returned by SELECT or touched by UPDATE or DELETE - Enforcing number of rows returned/affected could be done using the following syntax which is concise and clear (and should be in no way backwards incompatible) SELECT[1] - select exactly one row, anything else raises error SELECT[0:1] - select zero or one rows, anything else raises error SELECT[1:] - select one or more rows It has zero verbosity and I don't like plain SELECT is equivalent to SELECT[0:] same syntax could be used for enforcing sane affected row counts for INSERT and DELETE A more SQL-ish way of doing the same could probably be called COMMAND CONSTRAINTS and look something like this SELECT ... CHECK (ROWCOUNT BETWEEN 0 AND 1); It is very near to my proposed ASSERT There is disadvantage of enhancing SQL syntax, because you have to handle ugly in PLpgSQL parser or you have to push it to SQL parser. SELECT ...; ASSERT CHECK ROWCOUNT BETWEEN 0 AND 1 .. solve it. There is only one difference - ; and we don't need to modify SQL and we have total general solution I don't like a design where is necessary to read documentation to language with all small details first. 2. Substitute for EXECUTE with string manipulation using backticks `` for value/command substitution in SQL as an alternative to EXECUTE string Again it should be backwards compatible as , as currently `` are not allowed inside pl/pgsql functions Sample 1: ALTER USER `current_user` PASSWORD newpassword; would be expanded to EXECUTE 'ALTER USER ' || current_user || ' PASSWORD = $1' USING newpassword; Sample2: SELECT * FROM `tablename` WHERE `idcolumn` = idvalue; this could be expanded to EXECUTE 'SELECT * FROM ' || tablename || ' WHERE quote_ident(idcolumn) = $1' USING idvalue; Notice that the use of around `` forced use of quote_ident() I am sorry - it is less readable than format function, and I afraid so there is mental collision with MySQL wide used syntax. Mainly - it is not natural solution that any beginner can do without reading documentation. It is only shortcut, but not clear. 3. A way to tell pl/pggsql not to cache plans fro normal queries --- This could be done using a #pragma or special /* NOPLANCACHE */ comment as suggested by Pavel In my experience - these special use cases can be wrapped well by function. So we can use #option probably well Or we could expand the [] descriptor from 1. to allow more options OR we could do it in SQL-ish way using like this: SELECT ... USING FRESH PLAN; Regards Pavel Best Regards -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ
