Re: [HACKERS] PL/pgSQL 2
On Mon, 1 Sep 2014 15:19:41 +0200 Joel Jacobson j...@trustly.com wrote: The fatal problems with Python3 and Perl6 was the inability to mix code between Python2/3 and Perl5/6. We don't have that problem with pl-languages in postgres, so please don't make that comparison, as it's incorrect. Actually Perl6 can include Perl5 code allows you to use v5.6 or use v6.0 to regulate how the code in any one block is compiled w/in the program. Even Perl 5 allows mixing blocks/modules with different version syntax w/in the same compiler. The mistake Python made was not allowing the Python 3 compiler to gracefully handle Pythin 2 input. -- Steven Lembark 3646 Flora Pl Workhorse Computing St Louis, MO 63110 lemb...@wrkhors.com +1 888 359 3508 -- 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-04 18:29 GMT-03:00 Robert Haas robertmh...@gmail.com: On Thu, Sep 4, 2014 at 2:31 PM, Josh Berkus j...@agliodbs.com wrote: Sadly, what's prevented us from having packages already has been the insistence of potential feature sponsors that they work *exactly* like PL/SQL's packages, which is incompatible with Postgres namespacing. Also, we'd want any package concept to be usable with external PLs as well as PL/pgSQL, which necessitates other Oracle-incompatible changes. This is not a fun area in which to try to be exactly like Oracle. Just to take one example, the whole package is created and dumped as a single object, with all of its contained functions *and their comments*, including the exact position of those comments, such as inside the argument list to document what particular arguments are supposed to do. We've worked out a (partial) solution to that problem in Advanced Server, but it's not perfect, and it limits the ability to implement other features that PostgreSQL users would probably expect, like being able to add a function to a package after-the-fact. PostgreSQL has a certain cleanliness of design that comes from doing things in a way that makes sense from first principles, rather than the way that other people may have done it. I'm not prepared to say that a $184B company made a bad design decision here - it certainly seems to have worked out for them - but it's not what I would have picked, and it's not a very good fit for other design decisions we've made in PostgreSQL already. All-in-all, I'm pretty happy with our EXTENSION system as a way of loading code (and SQL function definitions) in a modular way. It's not perfect, but it's definitely made my life as a developer easier. There are some things you can do with an Oracle package but not a PostgreSQL extension, but there is an awful lot of overlap, too. I doubt we'd want to duplicate all that machinery just for compatibility reasons. If it were possible to mark a function as private for its extension that would be awesome (the opposite would work too, i.e. a way to specify a public API, meaning the rest is private). For big extensions it's not clear which functions can be used directly by users of the extension and which ones are just implementation details.
Re: [HACKERS] PL/pgSQL 2
On Tue, Oct 7, 2014 at 12:42 PM, Steven Lembark lemb...@wrkhors.com wrote: On Mon, 1 Sep 2014 15:19:41 +0200 Joel Jacobson j...@trustly.com wrote: The fatal problems with Python3 and Perl6 was the inability to mix code between Python2/3 and Perl5/6. We don't have that problem with pl-languages in postgres, so please don't make that comparison, as it's incorrect. Actually Perl6 can include Perl5 code allows you to use v5.6 or use v6.0 to regulate how the code in any one block is compiled w/in the program. Even Perl 5 allows mixing blocks/modules with different version syntax w/in the same compiler. I don't think that really helps very much at the end of the day; Perl 6 was a disaster for Perl. Breaking compatibility of a language is a good way to kill it off. Compiler support is only one example of a very broad set of problems it causes. Hiding that compatibility breaking under language 2.0 doesn't solve anything either. merlin -- 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 10/7/14, 1:08 PM, Rodolfo Campero wrote: If it were possible to mark a function as private for its extension that would be awesome (the opposite would work too, i.e. a way to specify a public API, meaning the rest is private). For big extensions it's not clear which functions can be used directly by users of the extension and which ones are just implementation details. I would love to have that both for extensions as well as outside of extensions. If you're doing sophisticated things in your database you'll end up wanting private objects, and right now the only reasonable way to do that is to throw them in a _blah schema and try to further hide them with permissions games. :( -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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 Mon, 01 Sep 2014 12:00:48 +0200 Marko Tiikkaja ma...@joh.to wrote: create a new language. There are enough problems with SQL in general, enough alternatives proposed over time that it might be worth coming up with something that Just Works. -- Steven Lembark 3646 Flora Pl Workhorse Computing St Louis, MO 63110 lemb...@wrkhors.com +1 888 359 3508 -- 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
Python2 - Python3 would've been a lot less painful if you could mark, on a module-by-module basis, whether a module was python2 or python3 code. It wasn't very practical for Python because python code can reach deep into the guts of unrelated objects discovered at runtime - it can add/replace member functions, even hot-patch bytecode. That's not something we allow in PL/PgSQL, though; from the outside a PL/PgSQL function is pretty opaque to callers. Perl does this with use version. Currently this guarantees that the compiler is a minimum version and also turns OFF later version's keywords. At that point someone could turn on/off the appropriate syntax with by module or code block. If you never turn on v2.0 you never get the new behavior; after that people can adjust the amount and location of later code to their own taste. -- Steven Lembark 3646 Flora Pl Workhorse Computing St Louis, MO 63110 lemb...@wrkhors.com +1 888 359 3508 -- 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 04/09/14 18:02, Craig Ringer wrote: On 09/04/2014 06:48 AM, Joshua D. Drake wrote: On 09/03/2014 11:48 AM, Robert Haas wrote: Anyway, to get back around to the topic of PL/SQL compatibility specifically, if you care about that issue, pick one thing that exists in PL/SQL but not in PL/pgsql and try to do something about it. Maybe it'll be something that EnterpiseDB has already done something about, in which case, if your patch gets committed, Advanced Server will lose a bit of distinction as compared with PostgreSQL. Or maybe it'll be something that EnterpriseDB hasn't done anything about, and then everybody comes out strictly ahead. What I think you shouldn't do (although you're free to ignore me) is continue thinking of Oracle compatibility as one monolithic thing, because it isn't, or to pursue of a course of trying to get the PostgreSQL community to slavishly follow Oracle, because I think you'll fail, and even if you succeed I don't think the results will actually be positive for PostgreSQL. Well put Robert. Indeed, especially with reference to the size and scope of Oracle. Its XML library alone is huge. At best it's reasonable to hope for compatibility with a limited subset of PL/SQL - and really, we're a good way there already, with most of what's missing being down to missing core server features or things PostgreSQL just does differently. True Oracle compatibility (for procedures) pretty much requires an embedded JVM with a rich class library. Since PL/Java seems to be dying a slow death by neglect and disinterest I don't think it's likely anyone would be tackling compatibility with the embedded JVM features anytime soon. There are a few things I would like to see, like secure session variables in PL/PgSQL. Mostly, though, I think talk of Oracle compatibility seems to be something that comes up before the speaker has really understood what that would mean, and the sheer scope of the endeavour. It's not going from 50% compatible to 80% compatible, it's going from 5% compatible to 7% compatible. The most used 5% maybe, but still... Getting that 5% of what is most used, would be a great gain. Maybe the speaker is mislead in the size of the endeavour, but quite sure about what that market needs are ;) Cheers, Á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 03/09/14 20:48, Robert Haas wrote: On Tue, Sep 2, 2014 at 5:47 PM, Álvaro Hernández Tortosa a...@nosys.es wrote: Yeah, we differ there. I think having an Oracle compatibility layer in PostgreSQL would be the-next-big-thing we could have. Oracle is has orders of magnitude bigger user base than postgres has; and having the ability to attract them would bring us many many more users which, in turn, would benefit us all very significantly. It would be my #1 priority to do in postgres (but yes, I know -guess- how hard and what resources that would require). But dreaming is free :) There are a number of reasons why this isn't really practical. First, Oracle compatibility isn't one feature. The compatibility layer that exists in EnterpriseDB's Advanced Server product consists of many different changes to many different parts of the system. A few of those changes are simple syntax compatibility, where we do the exact same thing PostgreSQL does but with different syntax, but a lot of them are functional enhancements. Even within SPL, there's a whole bunch of different changes to a whole bunch of different areas, and most of those are functional enhancements rather than just tweaking syntax. So, if you tried to implement a new, Oracle-compatible PL, you'd find that you don't have one or a small number of changes to make, but a long series of features ranging from small to very large. You'd also find that adding a new PL, without changing any other parts of the server, only bridges a small part of the compatibility gap. Hi Robert, thanks for the insights here. Understood it is not a single thing the compatibility layer. And it's sure a very long and involved task to build such compatibility parts. However, I don't see anything bad in having one or some parts of it. For example, having a pl that is similar -maybe only syntax- is a good thing. Sure, there are surely lot of things that can't be done simply, tons of functions not available and so on, but that alone would mean Oracle users would feel both more comfortable and making their current code easier to port. That would already be a lot. Second, if you did manage to develop something which was significantly more compatible with Oracle than PostgreSQL or PL/pgsql is today, you'd probably find that the community wouldn't accept it. It's almost misleading to think of Oracle as a database; it's an enormous software ecosystem with facilities for doing just about everything under the sun, and many of those things more than one way. For example, in 9.4, EnterpriseDB will be releasing a UTL_HTTP package that contains many of the same interfaces that are present in Oracle. The interface decisions made by Oracle Corporation are reasonable in view of their architecture, but I am quite sure that this community would not want, for example, to return long text values as SETOF VARCHAR(2000) rather than TEXT, just because Oracle does that. And rightly so: I wouldn't want PostgreSQL to follow any other product that slavishly whether I worked at EnterpriseDB or not. This kind of thing crops up over and over again, and it only works to say that PostgreSQL should choose the Oracle behavior in every case if you believe that the primary mission of PostgreSQL should be to copy Oracle, and I don't. I also don't think it's a bad thing that Advanced Server makes different decisions than PostgreSQL in some cases. A further problem is that, in this particular case, you'd probably here the argument from PostgreSQL hackers that they really don't want to be burdened with maintaining an HTTP client in the core server when the same thing could be done from an extension, and that's a valid argument, too. It is also valid for EnterpriseDB to make a different decision for itself, based on business priorities. I wouldn't follow those routes just for doing perfect compatibility. I agree, and I'd never push for those. In the light of all these things, I'd never expect perfect, scientific compatibility, but a best, but well documented, effort. Now, none of that is to say that we wouldn't do well to give a little more thought to Oracle compatibility than we do. We've either made or narrowly avoided a number of decisions over the years which introduced - or threatened to introduce - minor, pointless incompatibilities with other database products, Oracle included. That really doesn't benefit anyone. To take another example, I've been complaining about the fact that PostgreSQL 8.3+ requires far more typecasts in stored procedures than any other database I'm aware of for years, probably since before I joined EnterpriseDB. And I still think we're kidding ourselves to think that we've got that right when nobody else is doing something similar. I don't think the community should reverse that decision to benefit EnterpriseDB, or to be compatible with Oracle: I think the community should reverse that decision because it's stupid, and the
Re: [HACKERS] PL/pgSQL 2
On Fri, Sep 5, 2014 at 6:18 PM, Andrew Dunstan and...@dunslane.net wrote: On 09/05/2014 12:37 PM, Merlin Moncure wrote: On Thu, Sep 4, 2014 at 6:40 PM, Florian Pflug f...@phlo.org wrote: Cost of hidden IO cast is negative too. If we can change it, then we can increase a sped. But the whole power of PL/pgSQL comes from the fact that it allows you to use the full set of postgres data types and operatores, and that it seamlessly integrated with SQL. Without that, PL/pgSQL is about as appealing as BASIC as a programming language... Right, and it's exactly those types and operators that are the cause of the performance issues. A compiled pl/pgsql would only get serious benefit for scenarios involving tons of heavy iteration or funky local data structure manipulation. Those scenarios are somewhat rare in practice for database applications and often better handled in a another pl should they happen. plv8 is emerging as the best non-sql it's JIT compiled by the plv8 runtime, the javascript language is designed for embedding. and the json data structure has nice similarities with postgres's arrays and types. In fact, if I *were* to attempt pl/pgsql compiling, I'd probably translate the code to plv8 and hand it off to the llvm engine. You'd still have to let postgres handle most of the operator and cast operations but you could pull some things into the plv8 engine. Probably, this would be a net loser since plv8 (unlike plpgsql) has to run everything through SPI. plpgsql makes extensive use of SPI. Just look at the source code if you don't believe me. oh, certainly. pl/pgsql also has the ability to bypass SPI for many simple expressions. Other pls generally don't do this because they can't if they want to guarantee SQL semanticsthat's ok then because they don't have to as the language runtime handles operations local to the function and everything runs under that language's rules. In a nutshell, my thinking here is to translate pl/pgsql to pl/v8 javascript and then let the optimizing v8 runtime take it from there. This is IMNSHO a tiny challenge relative to writing an optimization engine for pl/pgsql by hand. Think of it as coffeescript for databases. It's a nice thought, but there's a lot of roadblocks to making it happen -- starting with the lack of a javascript library that would wrap the C postgres datatype routines so you wouldn't have to call in to SPI for every little thing; as you know even i := i + 1; can't be handled by native javascript operations. plv8 also has a nice find_function gadget that lets you find and call another plv8 function directly instead of having to use an SPI call. Yeah -- this is another reason why pl/v8 is a nice as a compilation target. javascript as we all know is a language with a long list of pros and cons but it's designed for embedding. merlin -- 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/05/2014 10:32 PM, Marko Tiikkaja wrote: On 2014-09-02 8:52 PM, Kevin Grittner wrote: Marko Tiikkaja ma...@joh.to wrote: Sounds like in this case you'd only use set-oriented programming at the end of the transaction, no? I guess -- more properly I would say in the final database transaction for that financial transaction. Yes, I should have said financial transaction, but I hit send a bit too early. And no, that never made me wish that plpgsql functions defaulted to throwing errors for DML statements that affected more than one row. Fine. But you should still be able to see the point we're trying to make. The number one is special, and it's present everywhere. If you want to program defensively, you have to go through a lot of pain right now. We're looking for a way to alleviate that pain. Defaulting to throwing errors would be one way to do it, but that's not what's being suggested here anymore. You can dismiss what we're doing by saying that it doesn't follow the best practices or we just want an interface for a key-value store or whatever. And yes, to some extent, a simple interface for a key-value store would come in handy. But we still have the 5-15% (big part of it being the reporting we need to do) of the code that *doesn't* want that, *and* we want to use all of the Postgres features where applicable. The point isn't about best practices. The point is that if you want to ensure that at maximum one row is affected, then qualify it by a unique set of columns. Making PL/pgSQL behave different on UPDATE than SQL to enforce that by default was simply a misguided design idea. 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 2
On 2014-09-06 6:06 PM, Jan Wieck wrote: You can dismiss what we're doing by saying that it doesn't follow the best practices or we just want an interface for a key-value store or whatever. And yes, to some extent, a simple interface for a key-value store would come in handy. But we still have the 5-15% (big part of it being the reporting we need to do) of the code that *doesn't* want that, *and* we want to use all of the Postgres features where applicable. The point isn't about best practices. It got to that point upthread. The point is that if you want to ensure that at maximum one row is affected, then qualify it by a unique set of columns. And what if you get the set of columns wrong (also consider the presence of joins)? What if someone changes that set of columns? What if your unique indexes have been violated because of a bug in postgres or hardware malfunction? Wouldn't you want the problem to be obvious? Making PL/pgSQL behave different on UPDATE than SQL to enforce that by default was simply a misguided design idea. OK, fine. But that's not what I suggested on the wiki page, and is also not what I'm arguing for here right now. What the message you referred to was about was the condescending attitude where we were told to think in terms of sets (paraphrased), without considering whether that's even possible to do *all the time*. .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
On 09/06/2014 12:17 PM, Marko Tiikkaja wrote: OK, fine. But that's not what I suggested on the wiki page, and is also not what I'm arguing for here right now. What the message you referred to was about was the condescending attitude where we were told to think in terms of sets (paraphrased), without considering whether that's even possible to do *all the time*. SQL is, by definition, a set oriented language. The name Procedural Language / pgSQL was supposed to suggest that this language adds some procedural elements to the PostgreSQL database. I never intended to create a 100% procedural language. It was from the very beginning, 16 years ago, intended to keep the set orientation when it comes to DML statements inside of functions. That means that you will have to think in sets *all the time*. The empty set and a set with one element are still sets. No matter how hard you try to make them special, in my mind they are not. 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 2
On 2014-09-06 6:31 PM, Jan Wieck wrote: On 09/06/2014 12:17 PM, Marko Tiikkaja wrote: OK, fine. But that's not what I suggested on the wiki page, and is also not what I'm arguing for here right now. What the message you referred to was about was the condescending attitude where we were told to think in terms of sets (paraphrased), without considering whether that's even possible to do *all the time*. SQL is, by definition, a set oriented language. The name Procedural Language / pgSQL was supposed to suggest that this language adds some procedural elements to the PostgreSQL database. I never intended to create a 100% procedural language. It was from the very beginning, 16 years ago, intended to keep the set orientation when it comes to DML statements inside of functions. No matter how hard you try to make them special, in my mind they are not. Of course they are. That's why you have PRIMARY KEYs and UNIQUE constraints. .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
On 09/06/2014 12:33 PM, Marko Tiikkaja wrote: On 2014-09-06 6:31 PM, Jan Wieck wrote: On 09/06/2014 12:17 PM, Marko Tiikkaja wrote: OK, fine. But that's not what I suggested on the wiki page, and is also not what I'm arguing for here right now. What the message you referred to was about was the condescending attitude where we were told to think in terms of sets (paraphrased), without considering whether that's even possible to do *all the time*. SQL is, by definition, a set oriented language. The name Procedural Language / pgSQL was supposed to suggest that this language adds some procedural elements to the PostgreSQL database. I never intended to create a 100% procedural language. It was from the very beginning, 16 years ago, intended to keep the set orientation when it comes to DML statements inside of functions. No matter how hard you try to make them special, in my mind they are not. Of course they are. That's why you have PRIMARY KEYs and UNIQUE constraints. Then please use those features instead of crippling the language. 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 2
On Sat, Sep 6, 2014 at 12:38 PM, Jan Wieck-3 [via PostgreSQL] ml-node+s1045698n5818047...@n5.nabble.com wrote: On 09/06/2014 12:33 PM, Marko Tiikkaja wrote: On 2014-09-06 6:31 PM, Jan Wieck wrote: On 09/06/2014 12:17 PM, Marko Tiikkaja wrote: OK, fine. But that's not what I suggested on the wiki page, and is also not what I'm arguing for here right now. What the message you referred to was about was the condescending attitude where we were told to think in terms of sets (paraphrased), without considering whether that's even possible to do *all the time*. SQL is, by definition, a set oriented language. The name Procedural Language / pgSQL was supposed to suggest that this language adds some procedural elements to the PostgreSQL database. I never intended to create a 100% procedural language. It was from the very beginning, 16 years ago, intended to keep the set orientation when it comes to DML statements inside of functions. No matter how hard you try to make them special, in my mind they are not. Of course they are. That's why you have PRIMARY KEYs and UNIQUE constraints. Then please use those features instead of crippling the language. If the language, and the system as a whole, was only used by perfectionists that do not make errors - and with perfectly clean data - this adherence to purity would be acceptable. But the real world is not that clean and so enhancing the language to meet the needs of the real world is not crippling the language. Begin able to state explicitly that the cardinality of the set I get back must be 1, no more and no less, doesn't remove the fact that I know I am dealing with a set and that I simply want to make an assertion as to its properties so that if a bug 3 layers deep into the application causes something other than 1 row to be affected I know immediately and can invoke the appropriate action - throw an error. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PL-pgSQL-2-tp5817121p5818051.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Re: [HACKERS] PL/pgSQL 2
On 09/06/2014 12:47 PM, David G Johnston wrote: If the language, and the system as a whole, was only used by perfectionists that do not make errors - and with perfectly clean data - this adherence to purity would be acceptable. But the real world is not that clean and so enhancing the language to meet the needs of the real world is not crippling the language. Begin able to state explicitly that the cardinality of the set I get back must be 1, no more and no less, doesn't remove the fact that I know I am dealing with a set and that I simply want to make an assertion as to its properties so that if a bug 3 layers deep into the application causes something other than 1 row to be affected I know immediately and can invoke the appropriate action - throw an error. As I already mentioned in the other thread, those assertions or checks do not belong into the PL. If they are desired they should be added to the main SQL syntax as COMMAND CONSTRAINT like suggested by Hannu. Your statement is not limited to PL functions. It is just as valid for NORMAL applications. However, this would be a proprietary extension that is not covered by any SQL standard and for that reason alone cannot be the default. 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 2
On Thu, Sep 4, 2014 at 6:40 PM, Florian Pflug f...@phlo.org wrote: Cost of hidden IO cast is negative too. If we can change it, then we can increase a sped. But the whole power of PL/pgSQL comes from the fact that it allows you to use the full set of postgres data types and operatores, and that it seamlessly integrated with SQL. Without that, PL/pgSQL is about as appealing as BASIC as a programming language... Right, and it's exactly those types and operators that are the cause of the performance issues. A compiled pl/pgsql would only get serious benefit for scenarios involving tons of heavy iteration or funky local data structure manipulation. Those scenarios are somewhat rare in practice for database applications and often better handled in a another pl should they happen. plv8 is emerging as the best non-sql it's JIT compiled by the plv8 runtime, the javascript language is designed for embedding. and the json data structure has nice similarities with postgres's arrays and types. In fact, if I *were* to attempt pl/pgsql compiling, I'd probably translate the code to plv8 and hand it off to the llvm engine. You'd still have to let postgres handle most of the operator and cast operations but you could pull some things into the plv8 engine. Probably, this would be a net loser since plv8 (unlike plpgsql) has to run everything through SPI. IMO, what needs to happen first would be for the data type routines to be pulled out of main library so that client side applications and pls could link against it allowing for guaranteed sql semantics without having to call into the backend -- at least the standard types. merlin -- 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/05/2014 12:37 PM, Merlin Moncure wrote:
On Thu, Sep 4, 2014 at 6:40 PM, Florian Pflug f...@phlo.org wrote:
Cost of hidden IO cast is negative too. If we can change it, then we can
increase a sped.
But the whole power of PL/pgSQL comes from the fact that it allows you to
use the full set of postgres data types and operatores, and that it seamlessly
integrated with SQL. Without that, PL/pgSQL is about as appealing as BASIC
as a programming language...
Right, and it's exactly those types and operators that are the cause
of the performance issues. A compiled pl/pgsql would only get serious
benefit for scenarios involving tons of heavy iteration or funky local
data structure manipulation. Those scenarios are somewhat rare in
practice for database applications and often better handled in a
another pl should they happen.
plv8 is emerging as the best non-sql it's JIT compiled by the plv8
runtime, the javascript language is designed for embedding. and the
json data structure has nice similarities with postgres's arrays and
types. In fact, if I *were* to attempt pl/pgsql compiling, I'd
probably translate the code to plv8 and hand it off to the llvm
engine. You'd still have to let postgres handle most of the operator
and cast operations but you could pull some things into the plv8
engine. Probably, this would be a net loser since plv8 (unlike
plpgsql) has to run everything through SPI.
plpgsql makes extensive use of SPI. Just look at the source code if you
don't believe me.
plv8 also has a nice find_function gadget that lets you find and call
another plv8 function directly instead of having to use an SPI call.
It has two serious defects in my view, that it inherits from v8.
First, and foremost, it has the old really really horrible Javascript
scoping rules for variables. This makes it totally unsuitable for
anything except trivially short functions. There is good news and bad
news on this front: modern versions of v8 have code to allow proper
lexical scoping as provided for in the draft ECMASCRIPT6 standard (the
feature is named harmony scoping). Example of command line use:
andrew@vpncli plv8js]$ d8 --use-strict --harmony
V8 version 3.14.5.10 [console: readline]
d8 var i = 10; for (let i = 0; i 3; i++) { let j = i; for (let i
= 4; i 6; i++) { print (j + j + i + i); } }
j 0 i 4
j 0 i 5
j 1 i 4
j 1 i 5
j 2 i 4
j 2 i 5
d8 print(i);
10
d8
The bad news is that neither Hitosho nor I (yet) know how to allow
setting these flags for the plv8 embedded engine.
The other defect is that its string handling is just awful. It has
neither multiline strings, not interpolation into strings.
The good news is that the new draft standard addresses these issues too,
with something called template strings. The bad news is that V8 doesn't
yet have code to support the feature, AFAICT. The Mozilla people are a
bit ahead here, and this feature is due in a release of their rhino
javascript library that will be in Mozilla 34, due out in November,
AIUI. Let's hope that the V8 guys get their act together on this.
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 2014-09-02 8:52 PM, Kevin Grittner wrote: Marko Tiikkaja ma...@joh.to wrote: Sounds like in this case you'd only use set-oriented programming at the end of the transaction, no? I guess -- more properly I would say in the final database transaction for that financial transaction. Yes, I should have said financial transaction, but I hit send a bit too early. And no, that never made me wish that plpgsql functions defaulted to throwing errors for DML statements that affected more than one row. Fine. But you should still be able to see the point we're trying to make. The number one is special, and it's present everywhere. If you want to program defensively, you have to go through a lot of pain right now. We're looking for a way to alleviate that pain. Defaulting to throwing errors would be one way to do it, but that's not what's being suggested here anymore. You can dismiss what we're doing by saying that it doesn't follow the best practices or we just want an interface for a key-value store or whatever. And yes, to some extent, a simple interface for a key-value store would come in handy. But we still have the 5-15% (big part of it being the reporting we need to do) of the code that *doesn't* want that, *and* we want to use all of the Postgres features where applicable. .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
On 09/01/2014 04:04 AM, Joel Jacobson wrote: + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1 row, as that's the most common use-case, and provide alternative syntax to modify multiple or zero rows. What? No. The whole point of SQL is that it's set-based and can modify multiple rows at once. Hobbling it specifically for functions seems fundamentally flawed. Especially for what we purport to be a core PostgreSQL language. + Change all warnings into errors I... what? I could see coming up with a better exception handling mechanism for escalating messages. But you're talking about taking a core element of PostgreSQL (warnings) and simply ripping them out so plpgsql2 loses even that small functionality. I'm sure you've put a lot of thought into this, but you're not the only person using plpgsql or any, however ambitious, potential replacement. -- 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 2
On 4 sep 2014, at 15:09, Shaun Thomas stho...@optionshouse.com wrote: On 09/01/2014 04:04 AM, Joel Jacobson wrote: + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1 row, as that's the most common use-case, and provide alternative syntax to modify multiple or zero rows. What? No. The whole point of SQL is that it's set-based and can modify multiple rows at once. Hobbling it specifically for functions seems fundamentally flawed. Especially for what we purport to be a core PostgreSQL language. I've dropped that suggestion and is instead in favour of a keyword like STRICT, ONE ROW, SINGLETON or [1] like suggested by others. Any keyword or syntax will do fine, but I would prefer STRICT. + Change all warnings into errors I... what? I could see coming up with a better exception handling mechanism for escalating messages. But you're talking about taking a core element of PostgreSQL (warnings) and simply ripping them out so plpgsql2 loses even that small functionality. You misunderstood, I meant plpgsql warnings, that you currently can turn into errors by setting things in the config file. Such as shadowing of variables. I'm sure you've put a lot of thought into this, but you're not the only person using plpgsql or any, however ambitious, potential replacement. -- 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 2
On 09/04/2014 02:48 AM, Robert Haas wrote: To take another example, I've been complaining about the fact that PostgreSQL 8.3+ requires far more typecasts in stored procedures than any other database I'm aware of for years, probably since before I joined EnterpriseDB. +10 This still drives me nuts, and it's a serious problem for ORM users too. The idea that we won't accept a 'text' typed input for an 'xml' or 'json' field is IMO absurdly and needlessly pedantic. I've not yet seen an argument for what problems this solves. I know why the changes in 8.3 were made, and they're clearly beneficial overall, but we need to start putting some more implicit casts from text to text-like types in, especially where there's no SQL-standard type that users of JDBC etc can easily use in mappings. -- 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 9/4/14 5:54 PM, Craig Ringer wrote: On 09/04/2014 02:48 AM, Robert Haas wrote: To take another example, I've been complaining about the fact that PostgreSQL 8.3+ requires far more typecasts in stored procedures than any other database I'm aware of for years, probably since before I joined EnterpriseDB. +10 This still drives me nuts, and it's a serious problem for ORM users too. The idea that we won't accept a 'text' typed input for an 'xml' or 'json' field is IMO absurdly and needlessly pedantic. I've not yet seen an argument for what problems this solves. In what context? Are we talking about parameters which have been cast to text, or what? I don't remember ever having an issue with this, though I remember the lack of implicit cast from text to json (or the other way round) making a bug more obvious a couple of times. .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
On 09/04/2014 06:48 AM, Joshua D. Drake wrote: On 09/03/2014 11:48 AM, Robert Haas wrote: Anyway, to get back around to the topic of PL/SQL compatibility specifically, if you care about that issue, pick one thing that exists in PL/SQL but not in PL/pgsql and try to do something about it. Maybe it'll be something that EnterpiseDB has already done something about, in which case, if your patch gets committed, Advanced Server will lose a bit of distinction as compared with PostgreSQL. Or maybe it'll be something that EnterpriseDB hasn't done anything about, and then everybody comes out strictly ahead. What I think you shouldn't do (although you're free to ignore me) is continue thinking of Oracle compatibility as one monolithic thing, because it isn't, or to pursue of a course of trying to get the PostgreSQL community to slavishly follow Oracle, because I think you'll fail, and even if you succeed I don't think the results will actually be positive for PostgreSQL. Well put Robert. Indeed, especially with reference to the size and scope of Oracle. Its XML library alone is huge. At best it's reasonable to hope for compatibility with a limited subset of PL/SQL - and really, we're a good way there already, with most of what's missing being down to missing core server features or things PostgreSQL just does differently. True Oracle compatibility (for procedures) pretty much requires an embedded JVM with a rich class library. Since PL/Java seems to be dying a slow death by neglect and disinterest I don't think it's likely anyone would be tackling compatibility with the embedded JVM features anytime soon. There are a few things I would like to see, like secure session variables in PL/PgSQL. Mostly, though, I think talk of Oracle compatibility seems to be something that comes up before the speaker has really understood what that would mean, and the sheer scope of the endeavour. It's not going from 50% compatible to 80% compatible, it's going from 5% compatible to 7% compatible. The most used 5% maybe, but still... -- 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
Hi Craig 2014-09-04 17:54 GMT+02:00 Craig Ringer cr...@2ndquadrant.com: On 09/04/2014 02:48 AM, Robert Haas wrote: To take another example, I've been complaining about the fact that PostgreSQL 8.3+ requires far more typecasts in stored procedures than any other database I'm aware of for years, probably since before I joined EnterpriseDB. +10 This still drives me nuts, and it's a serious problem for ORM users too. The idea that we won't accept a 'text' typed input for an 'xml' or 'json' field is IMO absurdly and needlessly pedantic. I've not yet seen an argument for what problems this solves. I know why the changes in 8.3 were made, and they're clearly beneficial overall, but we need to start putting some more implicit casts from text to text-like types in, especially where there's no SQL-standard type that users of JDBC etc can easily use in mappings. I don't see a problem in additional casts. But some missing casts are well - I found lot performance issues based on using wrong data types - integers, dates in text column. Pavel -- 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/04/2014 09:02 AM, Craig Ringer wrote: There are a few things I would like to see, like secure session variables in PL/PgSQL. Mostly, though, I think talk of Oracle compatibility seems to be something that comes up before the speaker has really understood what that would mean, and the sheer scope of the endeavour. It's not going from 50% compatible to 80% compatible, it's going from 5% compatible to 7% compatible. The most used 5% maybe, but still... However, there are users who want certain features from PL/SQL not for compatibility but because they're useful. For example: * A package concept for encapsulation of multiple procedures, session variables, etc. * The ability to compile functions/procedures for faster execution. * Autonomous transactions We'd also like to borrow stuff from other DBMSes, such as multisets. All of the above are worth implementing, even if it means implementing them with different syntax (and mechanics) than PL/SQL. Sadly, what's prevented us from having packages already has been the insistence of potential feature sponsors that they work *exactly* like PL/SQL's packages, which is incompatible with Postgres namespacing. Also, we'd want any package concept to be usable with external PLs as well as PL/pgSQL, which necessitates other Oracle-incompatible changes. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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
2014-09-04 20:31 GMT+02:00 Josh Berkus j...@agliodbs.com: On 09/04/2014 09:02 AM, Craig Ringer wrote: There are a few things I would like to see, like secure session variables in PL/PgSQL. Mostly, though, I think talk of Oracle compatibility seems to be something that comes up before the speaker has really understood what that would mean, and the sheer scope of the endeavour. It's not going from 50% compatible to 80% compatible, it's going from 5% compatible to 7% compatible. The most used 5% maybe, but still... However, there are users who want certain features from PL/SQL not for compatibility but because they're useful. For example: * A package concept for encapsulation of multiple procedures, session variables, etc. * The ability to compile functions/procedures for faster execution. This point is more complex, because bottleneck is not in plpgsql - it is terrible fast against noncompiled pcode interpreted PL/SQL and it is comparable with PL/SQL - due different design. A expression evaluation is slower, partially due using a SQL expression interpret, partially due our arrays and strings are immutable, and any composition are slow. Cost of hidden IO cast is negative too. If we can change it, then we can increase a sped. Almost all from these bottlenecks are out of plpgsql engine. So compilation of plpgsql is myth and it doesn't help and it doesn't need it. It doesn't help with speed. Pavel * Autonomous transactions We'd also like to borrow stuff from other DBMSes, such as multisets. All of the above are worth implementing, even if it means implementing them with different syntax (and mechanics) than PL/SQL. Sadly, what's prevented us from having packages already has been the insistence of potential feature sponsors that they work *exactly* like PL/SQL's packages, which is incompatible with Postgres namespacing. Also, we'd want any package concept to be usable with external PLs as well as PL/pgSQL, which necessitates other Oracle-incompatible changes. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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
* Robert Haas (robertmh...@gmail.com) wrote: Second, if you did manage to develop something which was significantly more compatible with Oracle than PostgreSQL or PL/pgsql is today, you'd probably find that the community wouldn't accept it. Agreed. Moving PostgreSQL forward is what the community is interested in- not duplicating what another database product has for the strict goal of easing migrations from those databases (be it Oracle or MSSQL or MySQL). To take another example, I've been complaining about the fact that PostgreSQL 8.3+ requires far more typecasts in stored procedures than any other database I'm aware of for years, probably since before I joined EnterpriseDB. And I still think we're kidding ourselves to think that we've got that right when nobody else is doing something similar. I don't think the community should reverse that decision to benefit EnterpriseDB, or to be compatible with Oracle: I think the community should reverse that decision because it's stupid, and the precedent of other systems demonstrates that it is possible to do better. Oracle's handling of reserved words also seems to be considerably less irritating than ours, and I'd propose that we improve that in PostgreSQL too, if I knew how to do it. Unfortunately, I suspect that requires jettisoning bison and rolling our own parser generator, and it's hard to argue that would be a good investment of effort for the benefit we'd get. Also agreed on this, though any serious discussion on this would deserve its own thread. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] PL/pgSQL 2
On Thu, Sep 4, 2014 at 2:31 PM, Josh Berkus j...@agliodbs.com wrote: Sadly, what's prevented us from having packages already has been the insistence of potential feature sponsors that they work *exactly* like PL/SQL's packages, which is incompatible with Postgres namespacing. Also, we'd want any package concept to be usable with external PLs as well as PL/pgSQL, which necessitates other Oracle-incompatible changes. This is not a fun area in which to try to be exactly like Oracle. Just to take one example, the whole package is created and dumped as a single object, with all of its contained functions *and their comments*, including the exact position of those comments, such as inside the argument list to document what particular arguments are supposed to do. We've worked out a (partial) solution to that problem in Advanced Server, but it's not perfect, and it limits the ability to implement other features that PostgreSQL users would probably expect, like being able to add a function to a package after-the-fact. PostgreSQL has a certain cleanliness of design that comes from doing things in a way that makes sense from first principles, rather than the way that other people may have done it. I'm not prepared to say that a $184B company made a bad design decision here - it certainly seems to have worked out for them - but it's not what I would have picked, and it's not a very good fit for other design decisions we've made in PostgreSQL already. All-in-all, I'm pretty happy with our EXTENSION system as a way of loading code (and SQL function definitions) in a modular way. It's not perfect, but it's definitely made my life as a developer easier. There are some things you can do with an Oracle package but not a PostgreSQL extension, but there is an awful lot of overlap, too. I doubt we'd want to duplicate all that machinery just for compatibility reasons. -- 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 2
On Sep4, 2014, at 20:50 , Pavel Stehule pavel.steh...@gmail.com wrote: 2014-09-04 20:31 GMT+02:00 Josh Berkus j...@agliodbs.com: * The ability to compile functions/procedures for faster execution. This point is more complex, because bottleneck is not in plpgsql - it is terrible fast against noncompiled pcode interpreted PL/SQL and it is comparable with PL/SQL - due different design. A expression evaluation is slower, partially due using a SQL expression interpret, partially due our arrays and strings are immutable, and any composition are slow. That, in principle, is just an inlining problem, though. Say we translate PL/pgSQL into LLVM bytecode in the simplest possible way by simply traversing the parse tree, and emitting calls to the functions that the interpreter calls now. Now, that alone wouldn't buy much, as you say. But if we additionally compile (at least parts of) the executor machinery to LLVM bytecode too (just once, while building postgres), the LLVM optimizer should in principle be able to inline at least some of these calls, which *could* have considerable benefit. The hard part would probably be to figure out how to inform the executor which parts it may consider to be *constant* (i.e. what constitues the execution *plan*) and which parts can change from one execution to the next (i.e. the executor state). In fact, such an approach would allow all expression evaluations to be JITed - not only those appearing in PL/pgSQL functions but also in plain SQL. Cost of hidden IO cast is negative too. If we can change it, then we can increase a sped. But the whole power of PL/pgSQL comes from the fact that it allows you to use the full set of postgres data types and operatores, and that it seamlessly integrated with SQL. Without that, PL/pgSQL is about as appealing as BASIC as a programming language... best regards, Florian Pflug -- 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 3 September 2014 01:08, Jan Wieck j...@wi3ck.info wrote: On 09/02/2014 06:56 PM, Andrew Dunstan wrote: People are free to do what they want, but to my mind that would be a massive waste of resources, and probably imposing a substantial extra maintenance burden on the core committers. I hear you and agree to some degree. But at the same time I remember that one of the strengths of Postgres used to be to be able to incorporate new ideas. This seems to be one of those cases. Instead of fork plpgsql2, what about designing a completely new PL/postgres from scratch? It will only take 3-10 years, but I bet it will be worth it after all. And I mean that. No sarcasm. And how it would be better then already existing plperl/plpython? - Szymon
Re: [HACKERS] PL/pgSQL 2
On Wed, Sep 3, 2014 at 7:54 AM, Pavel Stehule pavel.steh...@gmail.com 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 If you need more, and some users would more, then it job for new language really. You fail to illustrate *why* it's a job for a new language. All improvements suggested above are possible with plpgsql, and *should* be improved in plpgsql, that I agree with. But the 100% backwards-compatibiity ambition puts hard limits on what's possible, and if we can accept (100%-X) backwards compatibility where X is a small number, then so much more ideas are possible, and that's why plpgsql2 is a good idea. Hopefully, most of the plpgsql2 changes can be turned on/off in plpgsql with PRAGMA clause with GUC, but will be more messy than a good decent default behaviour. I'm in favour of Tom's idea. To merely make the plpgsql2 language a way of explicitly saying you want a specific exact combination of features/beaviour/settings which we can implemented in plpgsql's existing codebase. Since it was about 100 posts since Tom's post, maybe it's worth repeating for those who missed it: 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 I fully agree on this approach. It's maintainable and it will be useful from day 1. -- 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-03 9:14 GMT+02:00 Joel Jacobson j...@trustly.com: On Wed, Sep 3, 2014 at 7:54 AM, Pavel Stehule pavel.steh...@gmail.com 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 If you need more, and some users would more, then it job for new language really. You fail to illustrate *why* it's a job for a new language. All improvements suggested above are possible with plpgsql, and *should* be improved in plpgsql, that I agree with. ok, super But the 100% backwards-compatibiity ambition puts hard limits on what's possible, and if we can accept (100%-X) backwards compatibility where X is a small number, then so much more ideas are possible, and that's why plpgsql2 is a good idea. Hopefully, most of the plpgsql2 changes can be turned on/off in plpgsql with PRAGMA clause with GUC, but will be more messy than a good decent default behaviour. I'm in favour of Tom's idea. To merely make the plpgsql2 language a way of explicitly saying you want a specific exact combination of features/beaviour/settings which we can implemented in plpgsql's existing codebase. Since it was about 100 posts since Tom's post, maybe it's worth repeating for those who missed it: 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 I fully agree on this approach. It's maintainable and it will be useful from day 1. I can accept technical solution, but I have hard problem with your vision of plpgsql future. I afraid so it is too specific with your use case. When you use name plpgsql2 you say, so plpgsql2 is successor plpgsql. It is very hard to accept it. So any other name is not problem for me - like plpgsql-safe-subset or something else Pavel
Re: [HACKERS] PL/pgSQL 2
On Wed, Sep 3, 2014 at 10:07 AM, Pavel Stehule pavel.steh...@gmail.com wrote: When you use name plpgsql2 you say, so plpgsql2 is successor plpgsql. It is very hard to accept it. So any other name is not problem for me - like plpgsql-safe-subset or something else plpgsql2 *is* the successor of plpgsql, that's why it has a 2 in the name. Anything which is very different from plpgsql should instead get a different name. For all new users, having a convenient shorthand (plpgsql2) for enabling what ever the project think is the best-practice collection of pragmas, is a simple and efficient way of helping new users to get the best possible behaviour of the language, when starting from scratch. It also simplifies communication among developers, when they talk about code written in plpgsql2, they will all eventually know what they mean, instead of having to describe what collection of pragmas they use in their code. That also simplifies code examples, but most importantly, one does not have to declare all the pragmas for each function, or worry about the pragmas in the config file will ever change. Once we have agreed upon plpgsql2, then it will be a constant, and never break compatibility, and that's a good thing. Then we can all write new code according to the updated specs and hopefully we will not need a plpgsql3 until year 2030. -- 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 03:50 PM, Jan Wieck wrote: PL/pgSQL's syntax was modelled to look like PL/SQL. Which is a Ada/COBOL lookalike. Instead of trying to mimic what it was or a T-SQL thing instead ... maybe it is time to come up with a true PostgreSQL specific PL for a change? Just for the sake of being something new, and not a copy of some old opossum, that's rotting like road kill on the side of the highway for a decade already. Well, I don't think PostgreSQL needs its own PL. I mean we already have several (what other database has pl/javascript or pl/python?) Besides, the idea of this community trying to build its own programming language... oh lord ;) JD Jan -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc If we send our children to Caesar for their education, we should not be surprised when they come back as Romans. -- 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 04:01 PM, Álvaro Hernández Tortosa wrote: It's not copying. It's easying a path for others to migrate and come to Postgres. I'm interested why you are more interested in MSSQL. My reasons for being interested in Oracle are: - It has more users (biggest and above all, the main reason: we could attract more) - Postgres is perceived as similar to Oracle (so migration is likely to be easier) That's all I want. Grow postgres userbase, attracting Oracle users :) I find that we have more opportunity to replace MSSQL than Oracle. Obviously it depends on a lot of things but my goal is as yours, just with a different database. JD Álvaro -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc If we send our children to Caesar for their education, we should not be surprised when they come back as Romans. -- 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 Wed, Sep 3, 2014 at 3:17 PM, Joshua D. Drake j...@commandprompt.com wrote: Well, I don't think PostgreSQL needs its own PL. I mean we already have several (what other database has pl/javascript or pl/python?) PostgreSQL already *have* it's own PL, it's called PL/pgSQL. Besides, the idea of this community trying to build its own programming language... oh lord ;) I would agree it's too much of a challenge to invent a brand new programming language, I agree that's unrealistic, that's why I'm opting to do as much as possible in the existing language, and carefully think about what non-compatible important changes we simply cannot make to PL/pgSQL, as they by definition would break compatibility (which we all agree is not acceptable), but that *would* be possible with PL/pgSQL 2. -- 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/03/2014 03:14 AM, Joel Jacobson wrote: I'm in favour of Tom's idea. To merely make the plpgsql2 language a way of explicitly saying you want a specific exact combination of features/beaviour/settings which we can implemented in plpgsql's existing codebase. Since it was about 100 posts since Tom's post, maybe it's worth repeating for those who missed it: 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 I fully agree on this approach. It's maintainable and it will be useful from day 1. One can take that approach to another, more generic level. Like GUCs can be set on a ROLE base with ALTER USER or ALTER ROLE, PL specific GUCs could be set via ALTER LANGUAGE foo SET The possibility to CREATE LANGUAGE mybetterpl, pointing to the same PL handler function, exists already. And the same mechanism could be used by other languages, like PL/Python (for whatever such language might need such settings). This way an application can define the language settings, it needs, by simply creating its own language, based on all the possible permutations of those PRAGMA/GUC settings. 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 2
On Tue, Sep 2, 2014 at 08:46:36PM -0400, Christopher Browne wrote: 3. Is there anything to be learned from Tutorial D? That is, Date Darwen's would-be alternative to SQL of their Third Manifesto? What would a set-oriented-language PL look like, such as APL? I guess Perl has arrays, so it is kind-of set-oriented. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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 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? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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 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. .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
2014-09-03 17:05 GMT+02:00 Bruce Momjian br...@momjian.us: 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 these two yes c) maybe introduction automatic variable ROW_COUNT as shortcut for GET DIAGNOSTICS rc = ROW_COUNT this is my fresh some smarty designed asserts can be used for static analyses too. I am able to analyze plan of DML statements, and I can raise warning if expected rows is not 1 or if there are not filter over unique index some UPDATE ... WHERE id = 1; ASSERT(PROCESSED_ROW_COUNT = 1); And I can recheck in plpgsql_check, and it can enforce fast check in runtime Pavel All these ideas are being captured somewhere, right? Where? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Re: [HACKERS] PL/pgSQL 2
On 03/09/14 15:24, Joshua D. Drake wrote: On 09/02/2014 04:01 PM, Álvaro Hernández Tortosa wrote: It's not copying. It's easying a path for others to migrate and come to Postgres. I'm interested why you are more interested in MSSQL. My reasons for being interested in Oracle are: - It has more users (biggest and above all, the main reason: we could attract more) - Postgres is perceived as similar to Oracle (so migration is likely to be easier) That's all I want. Grow postgres userbase, attracting Oracle users :) I find that we have more opportunity to replace MSSQL than Oracle. Obviously it depends on a lot of things but my goal is as yours, just with a different database. Honestly, I don't care whether MSSQL or Oracle. What I want is to attract more users, get Postgres out of where it is and appeal even more users. With that regard, Oracle or MSSQL doesn't matter to me. That's why if you have some time, I'd love to listen to why do you think there is more opportunity to replace MSSQL. We may continue that privately as is a little bit off-topic. Thanks! Á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 Tue, Sep 2, 2014 at 5:47 PM, Álvaro Hernández Tortosa a...@nosys.es wrote: Yeah, we differ there. I think having an Oracle compatibility layer in PostgreSQL would be the-next-big-thing we could have. Oracle is has orders of magnitude bigger user base than postgres has; and having the ability to attract them would bring us many many more users which, in turn, would benefit us all very significantly. It would be my #1 priority to do in postgres (but yes, I know -guess- how hard and what resources that would require). But dreaming is free :) There are a number of reasons why this isn't really practical. First, Oracle compatibility isn't one feature. The compatibility layer that exists in EnterpriseDB's Advanced Server product consists of many different changes to many different parts of the system. A few of those changes are simple syntax compatibility, where we do the exact same thing PostgreSQL does but with different syntax, but a lot of them are functional enhancements. Even within SPL, there's a whole bunch of different changes to a whole bunch of different areas, and most of those are functional enhancements rather than just tweaking syntax. So, if you tried to implement a new, Oracle-compatible PL, you'd find that you don't have one or a small number of changes to make, but a long series of features ranging from small to very large. You'd also find that adding a new PL, without changing any other parts of the server, only bridges a small part of the compatibility gap. Second, if you did manage to develop something which was significantly more compatible with Oracle than PostgreSQL or PL/pgsql is today, you'd probably find that the community wouldn't accept it. It's almost misleading to think of Oracle as a database; it's an enormous software ecosystem with facilities for doing just about everything under the sun, and many of those things more than one way. For example, in 9.4, EnterpriseDB will be releasing a UTL_HTTP package that contains many of the same interfaces that are present in Oracle. The interface decisions made by Oracle Corporation are reasonable in view of their architecture, but I am quite sure that this community would not want, for example, to return long text values as SETOF VARCHAR(2000) rather than TEXT, just because Oracle does that. And rightly so: I wouldn't want PostgreSQL to follow any other product that slavishly whether I worked at EnterpriseDB or not. This kind of thing crops up over and over again, and it only works to say that PostgreSQL should choose the Oracle behavior in every case if you believe that the primary mission of PostgreSQL should be to copy Oracle, and I don't. I also don't think it's a bad thing that Advanced Server makes different decisions than PostgreSQL in some cases. A further problem is that, in this particular case, you'd probably here the argument from PostgreSQL hackers that they really don't want to be burdened with maintaining an HTTP client in the core server when the same thing could be done from an extension, and that's a valid argument, too. It is also valid for EnterpriseDB to make a different decision for itself, based on business priorities. Now, none of that is to say that we wouldn't do well to give a little more thought to Oracle compatibility than we do. We've either made or narrowly avoided a number of decisions over the years which introduced - or threatened to introduce - minor, pointless incompatibilities with other database products, Oracle included. That really doesn't benefit anyone. To take another example, I've been complaining about the fact that PostgreSQL 8.3+ requires far more typecasts in stored procedures than any other database I'm aware of for years, probably since before I joined EnterpriseDB. And I still think we're kidding ourselves to think that we've got that right when nobody else is doing something similar. I don't think the community should reverse that decision to benefit EnterpriseDB, or to be compatible with Oracle: I think the community should reverse that decision because it's stupid, and the precedent of other systems demonstrates that it is possible to do better. Oracle's handling of reserved words also seems to be considerably less irritating than ours, and I'd propose that we improve that in PostgreSQL too, if I knew how to do it. Unfortunately, I suspect that requires jettisoning bison and rolling our own parser generator, and it's hard to argue that would be a good investment of effort for the benefit we'd get. Anyway, to get back around to the topic of PL/SQL compatibility specifically, if you care about that issue, pick one thing that exists in PL/SQL but not in PL/pgsql and try to do something about it. Maybe it'll be something that EnterpiseDB has already done something about, in which case, if your patch gets committed, Advanced Server will lose a bit of distinction as compared with PostgreSQL. Or maybe it'll be something that EnterpriseDB hasn't done
Re: [HACKERS] PL/pgSQL 2
This is more of an SQL request the pl/pgsql but is/has there been thought to adding the ternary if/then opeator? Something like: boolean_exp ? val_if_true : val_if_false using ? by itself would be OK but not ideal - and the addition of the doesn't seem hateful... Sorry if this is deemed off-topic but I just went to write CASE WHEN boolean_exp THEN val_if_true ELSE val_if_false END And the fact there is as much standard code as there is custom bothered me just as is being discussed on this thread. I'm going to go write a ifthen(bool, anyelement, anyelement) function now David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PL-pgSQL-2-tp5817121p5817608.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
2014-09-03 21:01 GMT+02:00 David G Johnston david.g.johns...@gmail.com:
This is more of an SQL request the pl/pgsql but is/has there been thought
to
adding the ternary if/then opeator? Something like:
boolean_exp ? val_if_true : val_if_false
using ? by itself would be OK but not ideal - and the addition of the
doesn't seem hateful...
Sorry if this is deemed off-topic but I just went to write
CASE WHEN boolean_exp THEN val_if_true ELSE val_if_false END
And the fact there is as much standard code as there is custom bothered me
just as is being discussed on this thread.
I'm going to go write a ifthen(bool, anyelement, anyelement) function
now
if you use a SQL (SQL macro, then it can be effective)
postgres=# CREATE OR REPLACE FUNCTION if(bool, anyelement, anyelement)
RETURNS anyelement AS $$SELECT CASE WHEN $1 THEN $2 ELSE $3 END $$ LANGUAGE
sql;
CREATE FUNCTION
postgres=# CREATE OR REPLACE FUNCTION fx(text) RETURNS text AS $$ BEGIN
RAISE NOTICE '%', $1; RETURN $1; END$$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# SELECT if(false, fx('msg1'), fx('msg2'));
NOTICE: msg2
if
--
msg2
(1 row)
postgres=# SELECT if(true, fx('msg1'), fx('msg2'));
NOTICE: msg1
if
--
msg1
(1 row)
Only necessary parameters are evaluated
Pavel
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/PL-pgSQL-2-tp5817121p5817608.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 09/03/2014 11:48 AM, Robert Haas wrote: Anyway, to get back around to the topic of PL/SQL compatibility specifically, if you care about that issue, pick one thing that exists in PL/SQL but not in PL/pgsql and try to do something about it. Maybe it'll be something that EnterpiseDB has already done something about, in which case, if your patch gets committed, Advanced Server will lose a bit of distinction as compared with PostgreSQL. Or maybe it'll be something that EnterpriseDB hasn't done anything about, and then everybody comes out strictly ahead. What I think you shouldn't do (although you're free to ignore me) is continue thinking of Oracle compatibility as one monolithic thing, because it isn't, or to pursue of a course of trying to get the PostgreSQL community to slavishly follow Oracle, because I think you'll fail, and even if you succeed I don't think the results will actually be positive for PostgreSQL. Well put Robert. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc If we send our children to Caesar for their education, we should not be surprised when they come back as Romans. -- 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 Tue, Sep 2, 2014 at 5:46 AM, Craig Ringer cr...@2ndquadrant.com wrote: 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? I would guess about a year. 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. I've *never* said PL/pgSQL sucks. I *love* PL/pgSQL, seriously. I write code for many hours a day in the language. I don't even want to change much. My wishlist consists mostly of things which makes the language more secure. Currently it's a pain to verify your data operations do exactly what you requested. I would guess most novice developers don't understand this, and by mistake write insecure code. I don't want any OO. I don't want PL/SQL or PL/PSM. I'm a happy camper with PL/pgSQL. That said, *if* we now have a one-shot opportunity of possibly breaking a bit of compatibility for a minority of current code, motivated by the introduction of new important features not possible without plpgsql2, *then* let's make the best of that opportunity. I don't find myself selecting from a table which table name I don't know the name when writing the code, so I'm not pariticulary interested in prodiving a syntax for that use case, but I'm not against the feature if others need it, even if it would possibly increase the lines of code of existing plpgsql code which needs to be modified to remain compatible by X %. Given the needed diff between plpgsql and plpgsql2 for the changes I'm mostly interested in would probably be quite small, I'm in favour of Tom's suggestion of: c) plpgsql and plpgsql2 are the same code base, with a small number of places that act differently depending on the language version. That fits perfectly for my needs, as I don't want to change much. But even if we find we want to make larger mostly-compatible changes, maybe that also can be implemented using the same approach. For me, the most important is to not break *most* of existing plpgsql code, but it's OK to break *some*. And when breaking it, it should be trivial to rewrite it to become compatible. -- 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 09:06 AM, Joel Jacobson wrote: Given the needed diff between plpgsql and plpgsql2 for the changes I'm mostly interested in would probably be quite small, I'm in favour of Tom's suggestion of: c) plpgsql and plpgsql2 are the same code base, with a small number of places that act differently depending on the language version. That fits perfectly for my needs, as I don't want to change much. But even if we find we want to make larger mostly-compatible changes, maybe that also can be implemented using the same approach. For me, the most important is to not break *most* of existing plpgsql code, but it's OK to break *some*. And when breaking it, it should be trivial to rewrite it to become compatible. I think the next step would be to list all the things you don't like with current PL/pgSQL, and write down how you would want them to work if you were starting with a clean slate. Let's see how wide the consensus is that the new syntax/behavior is better than what we have now. We can then start thinking how to best adapt them to the current PL/pgSQL syntax and codebase. Maybe with pragmas, or new commands, or deprecating the old behavior; the best approach depends on the details, and how widely desired the new behavior is, so we need to see that first. I'd suggest collecting the ideas on a wiki page, and once you have some concrete set of features and syntax there, start a new thread to discuss them. Others will probably have other features they want, like the simpler DROP TABLE ? thing. - Heikki -- 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 Tue, Sep 2, 2014 at 8:26 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 09/02/2014 09:06 AM, Joel Jacobson wrote: For me, the most important is to not break *most* of existing plpgsql code, but it's OK to break *some*. And when breaking it, it should be trivial to rewrite it to become compatible. I think the next step would be to list all the things you don't like with current PL/pgSQL, and write down how you would want them to work if you were starting with a clean slate. Let's see how wide the consensus is that the new syntax/behavior is better than what we have now. We can then start thinking how to best adapt them to the current PL/pgSQL syntax and codebase. Maybe with pragmas, or new commands, or deprecating the old behavior; the best approach depends on the details, and how widely desired the new behavior is, so we need to see that first. I'd suggest collecting the ideas on a wiki page, and once you have some concrete set of features and syntax there, start a new thread to discuss them. Others will probably have other features they want, like the simpler DROP TABLE ? thing. Excellent idea, I'm on it! -- 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 11:04 GMT+02:00 Joel Jacobson j...@trustly.com: Hi, For those of you who use PL/pgSQL every day, I'm quite certain you all feel there are a number of things you would like to change in the language, but realize it cannot be achieved without possibly breaking compatibility, at least in theory. Even though you own code would survive the change, there might be code somewhere in the world which would break. This is of course not acceptable and that's why we have the current status quo of development, or at least not far away from a status quo. So instead of continue to adding optional settings to the config file, and instead of killing discussions around what can be done by bringing up the backwards-compatibility argument, let's instead fork the language and call it plpgsql2. Since no code is yet written in plpgsql2, we can start of from a clean sheet, and no good ideas need to be killed due to backwards-compatibility concerns. The interest for such a project is probably limited to a small number of companies/people around the world, as most users are probably perfectly happy with the current version of plpgsql, as they only use it occasionally and not every day like we do at my company. Just like with plpgsql, once released, plpgsql2 cannot break compatibility with future versions, so we only have one chance to carefully think though what we would like to change in the language. From the top of my head, these are Things I personally would want to see in plpgsql2: + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1 row, as that's the most common use-case, and provide alternative syntax to modify multiple or zero rows. + Make SELECT .. INTO .. throw an error if it selects more than 1 row. INTO STRICT only works if no rows should be an error, but there is currently no nice way if no rows OR exactly 1 row should be found by the query. + Change all warnings into errors last paragraph is some what I dislike on your proposal. Why: plpgsql is relative good mix of simplified ADA -- there are no too complex statement, no too much keywords, it is language that is simple to learn. Second part of mix is PostgreSQL SQL. It does same things what does in plan SQL. Your proposal change it. It is not good idea. Exactly clean solution is possible now DELETE FROM tab WHERE xx = somevar; GET DIAGNOSTICS rc = ROW_COUNT; IF rc 1 THEN RAISE EXCEPTION END IF; It is absolutely clean, absolutely readable. But it is verbose - yes, agree, maybe too much. But verbosity is basic stone of ADA and plpgsql too. It is what I like on plpgsql. What we can do better? 1. we can implement a conditional RAISE DELETE FROM tab WHERE xx = somevar; GET DIAGNOSTICS rc = ROW_COUNT; RAISE EXCEPTION 'some' WHEN rc 0; It is relatively natural and we use similar construct in CONTINUE statement. 2. What can be next? We can implement some idiom (shortcut) for GET DIAGNOSTICS DELETE FROM tab WHERE xx = somevar; RAISE EXCEPTION 'some' WHEN AFFECTED_ROW_COUNT 1; 3. What next? Maybe some notations - -- ** ensure_exact_one_row DELETE FROM tab WHERE xx = somevar; But default will be same as in plain SQL. Regards Pavel p.s. I dislike some flags to SQL statements .. like STRICT it increase a complexity of PL parser, and it increase a distance between SQL and PLPGSQL SQL. These are small changes, probably possible with just a few hundred lines of code in total, which also should be the ambition, as larger changes would never survive during time as it would require too much efforts to keep up with the main project. Secondly, I trust plpgsql mainly because it's being used by a lot of people in a lot of production systems, the same would not hold true for plpgsql2 for the first years of existence, so we who would use it in production systems must understand every single line of code changed and feel the risk of possible bugs and their impact are within acceptable boundaries. I can probably think of a few more things, but these are the major annoyances. Please share your wish list of things you would want in plpgsql2 which are not possible to implement in plpgsql because they could possibly break compatibility. Regards, Joel
Re: [HACKERS] PL/pgSQL 2
On 02/09/14 05:24, Craig Ringer wrote: 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. So we'd choose a bizarre and quirky language instead of anything better just because it's standard. I'm sure current and prospective users will surely prefer a bizarre and quirky language that is standard approved, rather than a modern, comfortable, easy-to-use, that is not embodied by the ISO. No doubt ^_^ 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. That is in my opinion a way more sensible choice. To bless PL/JavaScript as an in-core language would be a very wise choice. Á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-02 11:25 GMT+02:00 Álvaro Hernández Tortosa a...@nosys.es: On 02/09/14 05:24, Craig Ringer wrote: 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. So we'd choose a bizarre and quirky language instead of anything better just because it's standard. I'm sure current and prospective users will surely prefer a bizarre and quirky language that is standard approved, rather than a modern, comfortable, easy-to-use, that is not embodied by the ISO. No doubt ^_^ SQL/PSM is used in DB2, Sybase Anywhere, MySQL, 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. That is in my opinion a way more sensible choice. To bless PL/JavaScript as an in-core language would be a very wise choice. Á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 9/2/14 11:04 AM, Pavel Stehule wrote: It is relatively natural and we use similar construct in CONTINUE statement. 2. What can be next? We can implement some idiom (shortcut) for GET DIAGNOSTICS DELETE FROM tab WHERE xx = somevar; RAISE EXCEPTION 'some' WHEN AFFECTED_ROW_COUNT 1; Yes, a special variable would be closer to how I would prefer to access the row count. 3. What next? Maybe some notations - -- ** ensure_exact_one_row DELETE FROM tab WHERE xx = somevar; I really, really don't like the idea of turning regular SQL statements into something slightly different based on comments around (or inside) the query. .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
On 02/09/14 21:25, Álvaro Hernández Tortosa wrote: On 02/09/14 05:24, Craig Ringer wrote: 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. So we'd choose a bizarre and quirky language instead of anything better just because it's standard. I'm sure current and prospective users will surely prefer a bizarre and quirky language that is standard approved, rather than a modern, comfortable, easy-to-use, that is not embodied by the ISO. No doubt ^_^ Well there is the risk that by randomly adding new syntax to PL/pgSQL we turn it in a bizarre and quirky *non standard* language. Part of the attraction of PL/pgsql is that it is Ada like - if we break that too much then...well...that would be bad. So I think a careful balance is needed, to add new features that keep the spirit of the original language. Regards 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 02/09/14 06:40, Tom Lane wrote: 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 OK, so this compatibility layer is tough. Knew that already ;) But on the other side, the syntax is similar to plpgsql, right? So what about just having a compatible syntax? It would be the first step to that compatibility layer, which could -or could not- be a long-term goal for postgres (having the whole layer). I don't buy that having that would cut EDB's air supply. They're doing great, and they know how to take care of themselves, I'm sure ;) Besides that, competition is always positive, and I'm sure they'd be more benefited than harmed by postgres having that layer. If we are to have another plpgsql-like language (like plpgsql2) and we could design it so it would attract many many users (let's not forget that Oracle may have around two orders of magnitude more users than pg), that would benefit us all greatly. Even if not perfect. Even if it is a longer project which spans more than one release. But just having the syntax (or most of it, maybe avoiding some complex unimplemented postgres features, if that required a huge effort) is a big win. For 9.4, we have the media already saying Postgres has NoSQL capabilities (which is only partially true). For x.y we could have the media saying Postgres adds Oracle compatibility (which would be only partially true). But that brings a lot of users to postgres, and that helps us all. And also it could serve as a motivation point to implement those in-core missing features, too, that Oracle has. If on the other hand we resign from attracting Oracle users, in a moment where non-Oracle databases are fighting for them. and we lose here well, let's at least have a very compelling, attractive, in-core, blessed, language. Even disliking it myself, PL/JavaScript would be my #1 candidate there. My 4 (already) cents, Á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-02 11:34 GMT+02:00 Marko Tiikkaja ma...@joh.to: On 9/2/14 11:04 AM, Pavel Stehule wrote: It is relatively natural and we use similar construct in CONTINUE statement. 2. What can be next? We can implement some idiom (shortcut) for GET DIAGNOSTICS DELETE FROM tab WHERE xx = somevar; RAISE EXCEPTION 'some' WHEN AFFECTED_ROW_COUNT 1; Yes, a special variable would be closer to how I would prefer to access the row count. I am not against. We have FOUND, we can have AFFECTED_ROW_COUNT or something else. ROW_COUNT is probably wide used as variable. This style can be simply implemented. Pavel 3. What next? Maybe some notations - -- ** ensure_exact_one_row DELETE FROM tab WHERE xx = somevar; I really, really don't like the idea of turning regular SQL statements into something slightly different based on comments around (or inside) the query. it can be something else than comment. For me, it is really futuristic, but it has more potential than using some specialized keywords inside SQL statement. More, we can mix it with #option - be global for function. .marko
Re: [HACKERS] PL/pgSQL 2
On 02/09/14 11:34, Mark Kirkwood wrote: On 02/09/14 21:25, Álvaro Hernández Tortosa wrote: On 02/09/14 05:24, Craig Ringer wrote: 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. So we'd choose a bizarre and quirky language instead of anything better just because it's standard. I'm sure current and prospective users will surely prefer a bizarre and quirky language that is standard approved, rather than a modern, comfortable, easy-to-use, that is not embodied by the ISO. No doubt ^_^ Well there is the risk that by randomly adding new syntax to PL/pgSQL we turn it in a bizarre and quirky *non standard* language. Part of the attraction of PL/pgsql is that it is Ada like - if we break that too much then...well...that would be bad. So I think a careful balance is needed, to add new features that keep the spirit of the original language. I agree. I think I haven't suggested adding new syntax to pl/pgsql. But having its syntax similar to ADA is IMHO not something good. I'm sure few prospective postgres users would be compelled to that. They are compelled about JavaScript, python, Scala or Ruby, to name a few, but definitely not ADA. 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-02 11:40 GMT+02:00 Álvaro Hernández Tortosa a...@nosys.es: On 02/09/14 06:40, Tom Lane wrote: 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 OK, so this compatibility layer is tough. Knew that already ;) But on the other side, the syntax is similar to plpgsql, right? So what about just having a compatible syntax? It would be the first step to that compatibility layer, which could -or could not- be a long-term goal for postgres (having the whole layer). I don't buy that having that would cut EDB's air supply. They're doing great, and they know how to take care of themselves, I'm sure ;) Besides that, competition is always positive, and I'm sure they'd be more benefited than harmed by postgres having that layer. If we are to have another plpgsql-like language (like plpgsql2) and we could design it so it would attract many many users (let's not forget that Oracle may have around two orders of magnitude more users than pg), that would benefit us all greatly. Even if not perfect. Even if it is a longer project which spans more than one release. But just having the syntax (or most of it, maybe avoiding some complex unimplemented postgres features, if that required a huge effort) is a big win. For 9.4, we have the media already saying Postgres has NoSQL capabilities (which is only partially true). For x.y we could have the media saying Postgres adds Oracle compatibility (which would be only partially true). But that brings a lot of users to postgres, and that helps us all. Partial true can enforce so lot of people will hate postgres too. False promises are wrong And also it could serve as a motivation point to implement those in-core missing features, too, that Oracle has. If on the other hand we resign from attracting Oracle users, in a moment where non-Oracle databases are fighting for them. and we lose here well, let's at least have a very compelling, attractive, in-core, blessed, language. Even disliking it myself, PL/JavaScript would be my #1 candidate there. My 4 (already) cents, Á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 02/09/14 11:31, Pavel Stehule wrote: 2014-09-02 11:25 GMT+02:00 Álvaro Hernández Tortosa a...@nosys.es mailto:a...@nosys.es: On 02/09/14 05:24, Craig Ringer wrote: 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. So we'd choose a bizarre and quirky language instead of anything better just because it's standard. I'm sure current and prospective users will surely prefer a bizarre and quirky language that is standard approved, rather than a modern, comfortable, easy-to-use, that is not embodied by the ISO. No doubt ^_^ SQL/PSM is used in DB2, Sybase Anywhere, MySQL, That's a way better argument that it's standard :))) Still, I think postgres is in the position of attracting more Oracle than DB2+Sybase+MySQL users Álvaro
Re: [HACKERS] PL/pgSQL 2
2014-09-02 11:44 GMT+02:00 Álvaro Hernández Tortosa a...@nosys.es: On 02/09/14 11:34, Mark Kirkwood wrote: On 02/09/14 21:25, Álvaro Hernández Tortosa wrote: On 02/09/14 05:24, Craig Ringer wrote: 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. So we'd choose a bizarre and quirky language instead of anything better just because it's standard. I'm sure current and prospective users will surely prefer a bizarre and quirky language that is standard approved, rather than a modern, comfortable, easy-to-use, that is not embodied by the ISO. No doubt ^_^ Well there is the risk that by randomly adding new syntax to PL/pgSQL we turn it in a bizarre and quirky *non standard* language. Part of the attraction of PL/pgsql is that it is Ada like - if we break that too much then...well...that would be bad. So I think a careful balance is needed, to add new features that keep the spirit of the original language. I agree. I think I haven't suggested adding new syntax to pl/pgsql. But having its syntax similar to ADA is IMHO not something good. I'm sure few prospective postgres users would be compelled to that. They are compelled about JavaScript, python, Scala or Ruby, to name a few, but definitely not ADA. SQL/PSM is mix near Modula -- like Lua But integrated JavaScript can be good idea And Lua too - it is faster than Javascript with less overhead, but with significantly less community. 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 02/09/14 11:44, Pavel Stehule wrote: For 9.4, we have the media already saying Postgres has NoSQL capabilities (which is only partially true). For x.y we could have the media saying Postgres adds Oracle compatibility (which would be only partially true). But that brings a lot of users to postgres, and that helps us all. Partial true can enforce so lot of people will hate postgres too. False promises are wrong Then let's stop talking about postgres being NoSQL. NoSQL is basically schema-less (really bad name) plus infinite scalability (which basically means transparent sharding). We fail to provide the latter very clearly... Álvaro
Re: [HACKERS] PL/pgSQL 2
2014-09-02 11:50 GMT+02:00 Álvaro Hernández Tortosa a...@nosys.es: On 02/09/14 11:31, Pavel Stehule wrote: 2014-09-02 11:25 GMT+02:00 Álvaro Hernández Tortosa a...@nosys.es: On 02/09/14 05:24, Craig Ringer wrote: 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. So we'd choose a bizarre and quirky language instead of anything better just because it's standard. I'm sure current and prospective users will surely prefer a bizarre and quirky language that is standard approved, rather than a modern, comfortable, easy-to-use, that is not embodied by the ISO. No doubt ^_^ SQL/PSM is used in DB2, Sybase Anywhere, MySQL, That's a way better argument that it's standard :))) Still, I think postgres is in the position of attracting more Oracle than DB2+Sybase+MySQL users Not all can be happy :) We can implement SQL/PSM in conformity with ANSI SQL. But we cannot to implement PL/SQL be in 20% compatible with oracle - Aggegates, pipe functions, collections, without rewriting lot code. I remember lot of projects that promises compatibility with Oracle based on Firebird -- all are dead. Now situation is little bit different - there are big press for migration from Oracle, but Oracle is too big monster. Pavel Álvaro
Re: [HACKERS] PL/pgSQL 2
On 02/09/14 11:56, Pavel Stehule wrote: 2014-09-02 11:50 GMT+02:00 Álvaro Hernández Tortosa a...@nosys.es mailto:a...@nosys.es: On 02/09/14 11:31, Pavel Stehule wrote: 2014-09-02 11:25 GMT+02:00 Álvaro Hernández Tortosa a...@nosys.es mailto:a...@nosys.es: On 02/09/14 05:24, Craig Ringer wrote: 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. So we'd choose a bizarre and quirky language instead of anything better just because it's standard. I'm sure current and prospective users will surely prefer a bizarre and quirky language that is standard approved, rather than a modern, comfortable, easy-to-use, that is not embodied by the ISO. No doubt ^_^ SQL/PSM is used in DB2, Sybase Anywhere, MySQL, That's a way better argument that it's standard :))) Still, I think postgres is in the position of attracting more Oracle than DB2+Sybase+MySQL users Not all can be happy :) We can implement SQL/PSM in conformity with ANSI SQL. But we cannot to implement PL/SQL be in 20% compatible with oracle - Aggegates, pipe functions, collections, without rewriting lot code. I remember lot of projects that promises compatibility with Oracle based on Firebird -- all are dead. Now situation is little bit different - there are big press for migration from Oracle, but Oracle is too big monster. OK. Thanks for all the info I was missing about this complexity, I see that it goes well beyond the syntax thing. However, I'd insist that this should be IMHO a big priority, and I'd set it as a long-term goal. Even better if it could have a phased approach, that would make a lot of people happier (targeting the most used functionality). I'm sure pushing us to implement those missing features would also be really good, too. In the meantime, having another language (probably not plpgsql2) that is modern and appealing to many users would be a very nice win. Regards, Álvaro
Re: [HACKERS] PL/pgSQL 2
On 9/2/14 11:40 AM, Álvaro Hernández Tortosa wrote: If we are to have another plpgsql-like language (like plpgsql2) and we could design it so it would attract many many users (let's not forget that Oracle may have around two orders of magnitude more users than pg), that would benefit us all greatly. Even if not perfect. Even if it is a longer project which spans more than one release. But just having the syntax (or most of it, maybe avoiding some complex unimplemented postgres features, if that required a huge effort) is a big win. Have you looked at http://www.postgresql.org/docs/9.3/static/plpgsql-porting.html already? As far as I can tell, that already *is* the case as far as the language goes. It seems to me that most of the stuff that's different between the two are things that are out of the control of the language (no autonomous transactions, function source code in a literal etc.) For 9.4, we have the media already saying Postgres has NoSQL capabilities (which is only partially true). For x.y we could have the media saying Postgres adds Oracle compatibility (which would be only partially true). But that brings a lot of users to postgres, and that helps us all. This would be a horrible, horrible lie. If on the other hand we resign from attracting Oracle users, in a moment where non-Oracle databases are fighting for them. and we lose here well, let's at least have a very compelling, attractive, in-core, blessed, language. Even disliking it myself, PL/JavaScript would be my #1 candidate there. The best part about PL/PgSQL is the seamless integration with SQL. You can put an SQL expression pretty much anywhere. How well would that work if the framework was Javascript instead of the ADA-like body that both PL/SQL and PL/PgSQL implement? .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
On 02/09/14 12:46, Marko Tiikkaja wrote: On 9/2/14 11:40 AM, Álvaro Hernández Tortosa wrote: If we are to have another plpgsql-like language (like plpgsql2) and we could design it so it would attract many many users (let's not forget that Oracle may have around two orders of magnitude more users than pg), that would benefit us all greatly. Even if not perfect. Even if it is a longer project which spans more than one release. But just having the syntax (or most of it, maybe avoiding some complex unimplemented postgres features, if that required a huge effort) is a big win. Have you looked at http://www.postgresql.org/docs/9.3/static/plpgsql-porting.html already? Precisely this page shows some indications of examples of things that could be done at a language level that would make it way easier to port from PL/SQL (if you don't use that unsupported stuff). At least for that, if the syntax is exactly the same, it could make things much more comfortable (I'm not aiming for a 0-effort port, at least in first place, but to get the 80% or 60% easier than now). As far as I can tell, that already *is* the case as far as the language goes. It seems to me that most of the stuff that's different between the two are things that are out of the control of the language (no autonomous transactions, function source code in a literal etc.) Maybe it would be interesting to analyze: - What it's impossible to have right now in postgres - What can be implemented in a different way, but that would work in postgres - What could be somehow emulated And adapt the syntax as much as possible to aim for the biggest compatibility possible. For 9.4, we have the media already saying Postgres has NoSQL capabilities (which is only partially true). For x.y we could have the media saying Postgres adds Oracle compatibility (which would be only partially true). But that brings a lot of users to postgres, and that helps us all. This would be a horrible, horrible lie. Certainly not more horrible than today's PostgreSQL has NoSQL. Despite that, I'm not saying I'd lie. I'd say what the media would say, which is completely different. If on the other hand we resign from attracting Oracle users, in a moment where non-Oracle databases are fighting for them. and we lose here well, let's at least have a very compelling, attractive, in-core, blessed, language. Even disliking it myself, PL/JavaScript would be my #1 candidate there. The best part about PL/PgSQL is the seamless integration with SQL. You can put an SQL expression pretty much anywhere. How well would that work if the framework was Javascript instead of the ADA-like body that both PL/SQL and PL/PgSQL implement? SQL integration is a must in a PL/* language, that's for sure. But leveraging a well known language, tooling, and, specially, external libraries/ecosystem is a much bigger win. Specially if all the languages that I know of are capable (with more or less effort) to integrate SQL. So maybe JavaScript with a way of integrating SQL would be preferable IMO. 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 Tue, Sep 2, 2014 at 11:04 AM, Pavel Stehule pavel.steh...@gmail.com wrote: What we can do better? 1. we can implement a conditional RAISE DELETE FROM tab WHERE xx = somevar; GET DIAGNOSTICS rc = ROW_COUNT; RAISE EXCEPTION 'some' WHEN rc 0; It is relatively natural and we use similar construct in CONTINUE statement. 2. What can be next? We can implement some idiom (shortcut) for GET DIAGNOSTICS DELETE FROM tab WHERE xx = somevar; RAISE EXCEPTION 'some' WHEN AFFECTED_ROW_COUNT 1; 3. What next? Maybe some notations - -- ** ensure_exact_one_row DELETE FROM tab WHERE xx = somevar; But default will be same as in plain SQL. All three suggestions are either too verbose, ugly or hackish. I write too much code every day in PL/pgSQL to find any other solution than the cleanest and simplest to be acceptable. I reckon there are those who mostly use the language to create aggregated reports or to run some kind of batch jobs. But I use it almost exlusively for OLTP, and then you most often update a single row, and if 0 or 1 rows are affected, it's an error. Therefore, I wish the syntax for the most common use case to be as clean as possible, and there is nothing cleaner than plain UPDATE. Also, when showing a beginner the power of PL/pgSQL, it cannot be acceptable to have to write two rows to do something as simple as an update. All the suggestions above range between 2-3 rows (for DELETE, but I guess the syntax would be the same for UPDATE). For an in-depth discussion on this subject, please see http://joelonsql.com/2013/05/03/plpgsql-1-annoyance/ I have no good ideas though on what the syntax would look like to allow zero rows or multiple rows for an UPDATE though. It's much harder to come up with things to *add* to a syntax than what obvious ugliness you want to *remove*. If I had to guess though, I would think something in the end of the UPDATE command like a new keyword, could work. It wouldn't mess up the syntax too much, and wouldn't require an extra line of code. I strongly feel we should give a plain UPDATE without any extra lines of code or special syntax a default behaviour, which is different from accept any number of affected rows. My definitive vote is to throw an error if not exactly 1 row was affected, and to provide a nice syntax to allow the other use cases. Right now it's the other way around, we never throw an error, and *always* have to check how many rows were affected. That means we *always* get both more lines of code and also uglier code in our applications, than we would if we optimized for the most common use case. -- 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 03:16 PM, Joel Jacobson wrote: On Tue, Sep 2, 2014 at 11:04 AM, Pavel Stehule pavel.steh...@gmail.com wrote: What we can do better? 1. we can implement a conditional RAISE DELETE FROM tab WHERE xx = somevar; GET DIAGNOSTICS rc = ROW_COUNT; RAISE EXCEPTION 'some' WHEN rc 0; It is relatively natural and we use similar construct in CONTINUE statement. 2. What can be next? We can implement some idiom (shortcut) for GET DIAGNOSTICS DELETE FROM tab WHERE xx = somevar; RAISE EXCEPTION 'some' WHEN AFFECTED_ROW_COUNT 1; 3. What next? Maybe some notations - -- ** ensure_exact_one_row DELETE FROM tab WHERE xx = somevar; But default will be same as in plain SQL. All three suggestions are either too verbose, ugly or hackish. I write too much code every day in PL/pgSQL to find any other solution than the cleanest and simplest to be acceptable. I reckon there are those who mostly use the language to create aggregated reports or to run some kind of batch jobs. But I use it almost exlusively for OLTP, and then you most often update a single row, and if 0 or 1 rows are affected, it's an error. Therefore, I wish the syntax for the most common use case to be as clean as possible, and there is nothing cleaner than plain UPDATE. Also, when showing a beginner the power of PL/pgSQL, it cannot be acceptable to have to write two rows to do something as simple as an update. All the suggestions above range between 2-3 rows (for DELETE, but I guess the syntax would be the same for UPDATE). For an in-depth discussion on this subject, please see http://joelonsql.com/2013/05/03/plpgsql-1-annoyance/ In the mailing list thread that you linked there, Tom suggested using STRICT UPDATE ... to mean that updating 0 or 1 rows is an error (http://www.postgresql.org/message-id/16397.1356106...@sss.pgh.pa.us). What happened to that proposal? - Heikki -- 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-02 14:16 GMT+02:00 Joel Jacobson j...@trustly.com: On Tue, Sep 2, 2014 at 11:04 AM, Pavel Stehule pavel.steh...@gmail.com wrote: What we can do better? 1. we can implement a conditional RAISE DELETE FROM tab WHERE xx = somevar; GET DIAGNOSTICS rc = ROW_COUNT; RAISE EXCEPTION 'some' WHEN rc 0; It is relatively natural and we use similar construct in CONTINUE statement. 2. What can be next? We can implement some idiom (shortcut) for GET DIAGNOSTICS DELETE FROM tab WHERE xx = somevar; RAISE EXCEPTION 'some' WHEN AFFECTED_ROW_COUNT 1; 3. What next? Maybe some notations - -- ** ensure_exact_one_row DELETE FROM tab WHERE xx = somevar; But default will be same as in plain SQL. All three suggestions are either too verbose, ugly or hackish. It is main problem for me. I am thinking so verbosity is important. If it is ugly, cannot to say. It is subjective. I write too much code every day in PL/pgSQL to find any other solution than the cleanest and simplest to be acceptable. I reckon there are those who mostly use the language to create aggregated reports or to run some kind of batch jobs. But I use it almost exlusively for OLTP, and then you most often update a single row, and if 0 or 1 rows are affected, it's an error. It is valid only for UPDATE, not for DELETE. You can delete with FK and it is common operation. Therefore, I wish the syntax for the most common use case to be as clean as possible, and there is nothing cleaner than plain UPDATE. Also, when showing a beginner the power of PL/pgSQL, it cannot be acceptable to have to write two rows to do something as simple as an update. All the suggestions above range between 2-3 rows (for DELETE, but I guess the syntax would be the same for UPDATE). For an in-depth discussion on this subject, please see http://joelonsql.com/2013/05/03/plpgsql-1-annoyance/ It is way how to do COBOL from plpgsql. I am against it. Start to develop new language what will support fast development, but it is wrong way for plpgsql - and It is out my interest I have no good ideas though on what the syntax would look like to allow zero rows or multiple rows for an UPDATE though. It's much harder to come up with things to *add* to a syntax than what obvious ugliness you want to *remove*. If I had to guess though, I would think something in the end of the UPDATE command like a new keyword, could work. It wouldn't mess up the syntax too much, and wouldn't require an extra line of code. I strongly feel we should give a plain UPDATE without any extra lines of code or special syntax a default behaviour, which is different from accept any number of affected rows. My definitive vote is to throw an error if not exactly 1 row was affected, and to provide a nice syntax to allow the other use cases. Right now it's the other way around, we never throw an error, and *always* have to check how many rows were affected. That means we *always* get both more lines of code and also uglier code in our applications, than we would if we optimized for the most common use case.
Re: [HACKERS] PL/pgSQL 2
On 09/02/2014 05:44 AM, Álvaro Hernández Tortosa wrote:
On 02/09/14 11:34, Mark Kirkwood wrote:
On 02/09/14 21:25, Álvaro Hernández Tortosa wrote:
On 02/09/14 05:24, Craig Ringer wrote:
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.
So we'd choose a bizarre and quirky language instead of anything
better just because it's standard. I'm sure current and prospective
users will surely prefer a bizarre and quirky language that is standard
approved, rather than a modern, comfortable, easy-to-use, that is not
embodied by the ISO. No doubt ^_^
Well there is the risk that by randomly adding new syntax to PL/pgSQL
we turn it in a bizarre and quirky *non standard* language. Part of
the attraction of PL/pgsql is that it is Ada like - if we break that
too much then...well...that would be bad. So I think a careful
balance is needed, to add new features that keep the spirit of the
original language.
I agree. I think I haven't suggested adding new syntax to
pl/pgsql. But having its syntax similar to ADA is IMHO not something
good. I'm sure few prospective postgres users would be compelled to
that. They are compelled about JavaScript, python, Scala or Ruby, to
name a few, but definitely not ADA.
Just as a small nit pick - the name of the language is not ADA, but Ada.
It isn't an acronym. The language is named after Ada Lovelace, arguably
the world's first programmer. If you're not familiar with modern Ada,
let me recommend the newly published Programming in Ada 2012 by John
Barnes. But I digress.
JavaScript would actually be quite a good alternative. However, using it
involves something others have objected to, namely calling SQL via a
function call. It's true that plpgsql lets you call SQL commands without
explicitly invoking SPI. OTOH, it actually relies on SPI under the hood
a lot more that other PLs, which I have little doubt is responsible for
timings like this:
andrew=# do $$ declare x int = 1; i int = 1; begin while i
1000 loop i := i + 1; x := x + 46; end loop; raise notice ' x =
%',x; end; $$;
NOTICE: x = 45955
DO
Time: 13222.195 ms
andrew=# do $$ var x = 1; var i = 1; while (i 1000) { i += 1;
x += 46; } plv8.elog(NOTICE, x = + x); $$ language plv8;
NOTICE: x = 45955
DO
Time: 27.976 ms
But I'm not suggesting we should implement a Javascript PL in core either.
Finally, +1 to Tom's suggestion upthread that we implement different
behaviours via pragmas rather than some new offshoot language. Maybe a
GUC could specify a default set of such pragmas, so you wouldn't need to
decorate every function with them.
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 9/2/14 2:29 PM, Heikki Linnakangas wrote: In the mailing list thread that you linked there, Tom suggested using STRICT UPDATE ... to mean that updating 0 or 1 rows is an error (http://www.postgresql.org/message-id/16397.1356106...@sss.pgh.pa.us). What happened to that proposal? http://www.postgresql.org/message-id/27477.1361916...@sss.pgh.pa.us I can't find Peter's email right now, but basically nobody liked the suggestion 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
If PL/Javascript is a serious consideration, how will int64 and numeric be
handled?
Thanks,
Ryan Pedela
Datalanche CEO, co-founder
www.datalanche.com
rped...@datalanche.com
513-571-6837
On Tue, Sep 2, 2014 at 6:38 AM, Andrew Dunstan and...@dunslane.net wrote:
On 09/02/2014 05:44 AM, Álvaro Hernández Tortosa wrote:
On 02/09/14 11:34, Mark Kirkwood wrote:
On 02/09/14 21:25, Álvaro Hernández Tortosa wrote:
On 02/09/14 05:24, Craig Ringer wrote:
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.
So we'd choose a bizarre and quirky language instead of anything
better just because it's standard. I'm sure current and prospective
users will surely prefer a bizarre and quirky language that is standard
approved, rather than a modern, comfortable, easy-to-use, that is not
embodied by the ISO. No doubt ^_^
Well there is the risk that by randomly adding new syntax to PL/pgSQL we
turn it in a bizarre and quirky *non standard* language. Part of the
attraction of PL/pgsql is that it is Ada like - if we break that too much
then...well...that would be bad. So I think a careful balance is needed, to
add new features that keep the spirit of the original language.
I agree. I think I haven't suggested adding new syntax to pl/pgsql.
But having its syntax similar to ADA is IMHO not something good. I'm sure
few prospective postgres users would be compelled to that. They are
compelled about JavaScript, python, Scala or Ruby, to name a few, but
definitely not ADA.
Just as a small nit pick - the name of the language is not ADA, but Ada.
It isn't an acronym. The language is named after Ada Lovelace, arguably the
world's first programmer. If you're not familiar with modern Ada, let me
recommend the newly published Programming in Ada 2012 by John Barnes. But
I digress.
JavaScript would actually be quite a good alternative. However, using it
involves something others have objected to, namely calling SQL via a
function call. It's true that plpgsql lets you call SQL commands without
explicitly invoking SPI. OTOH, it actually relies on SPI under the hood a
lot more that other PLs, which I have little doubt is responsible for
timings like this:
andrew=# do $$ declare x int = 1; i int = 1; begin while i
1000 loop i := i + 1; x := x + 46; end loop; raise notice ' x =
%',x; end; $$;
NOTICE: x = 45955
DO
Time: 13222.195 ms
andrew=# do $$ var x = 1; var i = 1; while (i 1000) { i += 1;
x += 46; } plv8.elog(NOTICE, x = + x); $$ language plv8;
NOTICE: x = 45955
DO
Time: 27.976 ms
But I'm not suggesting we should implement a Javascript PL in core either.
Finally, +1 to Tom's suggestion upthread that we implement different
behaviours via pragmas rather than some new offshoot language. Maybe a GUC
could specify a default set of such pragmas, so you wouldn't need to
decorate every function with them.
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 09/02/2014 08:41 AM, Ryan Pedela wrote: If PL/Javascript is a serious consideration, how will int64 and numeric be handled? Please don't top-post on the PostgreSQL lists. See http://idallen.com/topposting.html Unfortunately, I think the short answer is not very well. In theory we cauld add in new types to a Javascript interpreter to handle them, but that would still leave you scrambling to handle user defined types. One of the advantages of plpgsql is that it can handle any Postgres data type without having to do anything special. The truth is that different PLs meet different needs and have different strengths and weaknesses. 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
2014-09-02 14:38 GMT+02:00 Andrew Dunstan and...@dunslane.net:
On 09/02/2014 05:44 AM, Álvaro Hernández Tortosa wrote:
On 02/09/14 11:34, Mark Kirkwood wrote:
On 02/09/14 21:25, Álvaro Hernández Tortosa wrote:
On 02/09/14 05:24, Craig Ringer wrote:
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.
So we'd choose a bizarre and quirky language instead of anything
better just because it's standard. I'm sure current and prospective
users will surely prefer a bizarre and quirky language that is standard
approved, rather than a modern, comfortable, easy-to-use, that is not
embodied by the ISO. No doubt ^_^
Well there is the risk that by randomly adding new syntax to PL/pgSQL we
turn it in a bizarre and quirky *non standard* language. Part of the
attraction of PL/pgsql is that it is Ada like - if we break that too much
then...well...that would be bad. So I think a careful balance is needed, to
add new features that keep the spirit of the original language.
I agree. I think I haven't suggested adding new syntax to pl/pgsql.
But having its syntax similar to ADA is IMHO not something good. I'm sure
few prospective postgres users would be compelled to that. They are
compelled about JavaScript, python, Scala or Ruby, to name a few, but
definitely not ADA.
Just as a small nit pick - the name of the language is not ADA, but Ada.
It isn't an acronym. The language is named after Ada Lovelace, arguably the
world's first programmer. If you're not familiar with modern Ada, let me
recommend the newly published Programming in Ada 2012 by John Barnes. But
I digress.
JavaScript would actually be quite a good alternative. However, using it
involves something others have objected to, namely calling SQL via a
function call. It's true that plpgsql lets you call SQL commands without
explicitly invoking SPI. OTOH, it actually relies on SPI under the hood a
lot more that other PLs, which I have little doubt is responsible for
timings like this:
andrew=# do $$ declare x int = 1; i int = 1; begin while i
1000 loop i := i + 1; x := x + 46; end loop; raise notice ' x =
%',x; end; $$;
NOTICE: x = 45955
DO
Time: 13222.195 ms
andrew=# do $$ var x = 1; var i = 1; while (i 1000) { i += 1;
x += 46; } plv8.elog(NOTICE, x = + x); $$ language plv8;
NOTICE: x = 45955
DO
Time: 27.976 ms
this test is unfair to plpgsql, and you know it well :)
any operations over native types will be faster than in plpgsql, although
this difference is maybe too much. Doesn't use --enable-cassert ?
But I'm not suggesting we should implement a Javascript PL in core either.
Finally, +1 to Tom's suggestion upthread that we implement different
behaviours via pragmas rather than some new offshoot language. Maybe a GUC
could specify a default set of such pragmas, so you wouldn't need to
decorate every function with them.
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 2014-09-02 14:41:03 +0200, Marko Tiikkaja wrote: On 9/2/14 2:29 PM, Heikki Linnakangas wrote: In the mailing list thread that you linked there, Tom suggested using STRICT UPDATE ... to mean that updating 0 or 1 rows is an error (http://www.postgresql.org/message-id/16397.1356106...@sss.pgh.pa.us). What happened to that proposal? http://www.postgresql.org/message-id/27477.1361916...@sss.pgh.pa.us I can't find Peter's email right now, but basically nobody liked the suggestion in the end. Perhaps we need the ONE ROW operatation ;) ONE ROW UPDATE ...; Greetings, Andres Freund -- Andres Freund 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
Joel Jacobson j...@trustly.com wrote: + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1 row, as that's the most common use-case, and provide alternative syntax to modify multiple or zero rows. I just embarked on wading through the 99 messages (so far) on this thread, so my apologies if this has already been addressed -- but I wanted to register a strong objection to making this the default in any rewrite. If we want to support a setting or a statement option for it, fine; but in my personal experience in a production environment with thousands of plpgsql functions, most functions written to deal with one row at a time were orders of magnitude slower than they needed to be -- I spent a lot of my time rewriting them to use set logic so that they could benefit from the optimizer's attention. Getting people to write things in a declarative style in the first place was difficult because so many of the programmers were so attached to the imperative style of coding; making it more difficult for people to Do The Right Thing is a bad idea IMO. As a side note, of the many times I rewrote long functions which looped through individual rows, I would estimate that 80% of them had subtle bugs which were fixed by changing them to set logic. Sure, some of those would have caused run-time errors rather than plausible-but-incorrect results with the change you suggest, but far from all of them. -- 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 2
On 09/02/2014 09:08 AM, Pavel Stehule wrote:
JavaScript would actually be quite a good alternative. However,
using it involves something others have objected to, namely
calling SQL via a function call. It's true that plpgsql lets you
call SQL commands without explicitly invoking SPI. OTOH, it
actually relies on SPI under the hood a lot more that other PLs,
which I have little doubt is responsible for timings like this:
andrew=# do $$ declare x int = 1; i int = 1; begin while i
1000 loop i := i + 1; x := x + 46; end loop; raise notice ' x =
%',x; end; $$;
NOTICE: x = 45955
DO
Time: 13222.195 ms
andrew=# do $$ var x = 1; var i = 1; while (i 1000) { i += 1;
x += 46; } plv8.elog(NOTICE, x = + x); $$ language plv8;
NOTICE: x = 45955
DO
Time: 27.976 ms
this test is unfair to plpgsql, and you know it well :)
any operations over native types will be faster than in plpgsql,
although this difference is maybe too much. Doesn't use
--enable-cassert ?
It's not unfair, and no it isn't using cassert. This was from a
production grade server.
PLV8 has its own issues (see discussion elsewhere in this thread re
int64 and numeric). It's just that speed isn't one of them :-)
Please note that I'm not unhappy with plpgsql. I have my own small list
of things that I would like improved, but there isn't very much that
bugs me about it.
A few years ago I was largely instrumental in building an entire billing
system, including some very complex tax rating, for a small Telco, using
plpgsql plus a tiny bit of plperlu glue where we needed unsafe
operations. It was quite fast enough - see my talk at pgopen a few years
back.
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 Tue, Sep 2, 2014 at 3:12 PM, Kevin Grittner kgri...@ymail.com wrote: Joel Jacobson j...@trustly.com wrote: + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1 row, as that's the most common use-case, and provide alternative syntax to modify multiple or zero rows. I just embarked on wading through the 99 messages (so far) on this thread, so my apologies if this has already been addressed -- but I wanted to register a strong objection to making this the default in any rewrite. If we want to support a setting or a statement option for it, fine; but in my personal experience in a production environment with thousands of plpgsql functions, most functions written to deal with one row at a time were orders of magnitude slower than they needed to be -- I spent a lot of my time rewriting them to use set logic so that they could benefit from the optimizer's attention. Getting people to write things in a declarative style in the first place was difficult because so many of the programmers were so attached to the imperative style of coding; making it more difficult for people to Do The Right Thing is a bad idea IMO. The common use-case I have in mind is when you have a function which takes some kind of ID as an input param, which maps to a primary key in some table, which you want to update. If the where-clause would be incorrect and the update would update all rows in the table, that would be a disaster, which is what I want to prevent. I think the benefit of a secure and convenient way of updating exactly 1 row outweights the reduced convenience of updating multiple rows when you really want to update multiple rows. Compare this to the normal psql prompt. How many million dollars would you say the total cost would be for mistakes where someone forgets the WHERE-clause of an UPDATE or a DELETE? :-) It's the same type of mistake I want to prevent from in a convenient way, and there is nothing more convenient than the default behavour. That also means *all* users will get that behaviour even if they don't explicitly request it, which is a good thing, because then they are protected against the danger of not knowing how to make sure it updated/deleted only one row. -- 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 Tue, Sep 2, 2014 at 2:29 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: In the mailing list thread that you linked there, Tom suggested using STRICT UPDATE ... to mean that updating 0 or 1 rows is an error (http://www.postgresql.org/message-id/16397.1356106...@sss.pgh.pa.us). What happened to that proposal? From the STRICT mail thread, this was the last post: Marko Tiikkaja ma...@joh.to writes: If I'm counting correctly, we have four votes for this patch and two votes against it. Any other opinions? FWIW, I share Peter's poor opinion of this syntax. I can see the appeal of not having to write an explicit check of the rowcount afterwards, but that appeal is greatly weakened by the strange syntax. (IOW, if you were counting me as a + vote, that was only a vote for the concept --- on reflection I don't much like this implementation.) regards, tom lane I think it's much better to make it the default behaviour in plpgsql2 than to add a new syntax to plpgsql, because then we don't have to argue what to call the keyword or where to put it. -- 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 Tue, Sep 2, 2014 at 2:30 PM, Pavel Stehule pavel.steh...@gmail.com wrote: It is way how to do COBOL from plpgsql. I am against it. Start to develop new language what will support fast development, but it is wrong way for plpgsql - and It is out my interest Are you saying COBOL by default update's one row and throws an error otherwise? In what way could *not* changing the syntax of a standard UPDATE command, but changing the *behaviour*, in plpgsql2, be deemed to be a step in the COBOL direction? I don't want a new language, I love plpgsql, I just want to love it a bit more, I don't think I have to clarify on that any more. -- 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 04:32 PM, Joel Jacobson wrote: On Tue, Sep 2, 2014 at 2:29 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: In the mailing list thread that you linked there, Tom suggested using STRICT UPDATE ... to mean that updating 0 or 1 rows is an error (http://www.postgresql.org/message-id/16397.1356106...@sss.pgh.pa.us). What happened to that proposal? From the STRICT mail thread, this was the last post: Marko Tiikkaja ma...@joh.to writes: If I'm counting correctly, we have four votes for this patch and two votes against it. Any other opinions? FWIW, I share Peter's poor opinion of this syntax. I can see the appeal of not having to write an explicit check of the rowcount afterwards, but that appeal is greatly weakened by the strange syntax. (IOW, if you were counting me as a + vote, that was only a vote for the concept --- on reflection I don't much like this implementation.) regards, tom lane I think it's much better to make it the default behaviour in plpgsql2 than to add a new syntax to plpgsql, because then we don't have to argue what to call the keyword or where to put it. Then you'll have to argue what the *other* syntax should look like. And not everyone agrees on the default either, see Kevin's email. Designing a new language is going to be an uphill battle, even more so than enhancing current plpgsql. - Heikki -- 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 Tue, Sep 2, 2014 at 3:41 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 09/02/2014 04:32 PM, Joel Jacobson wrote: I think it's much better to make it the default behaviour in plpgsql2 than to add a new syntax to plpgsql, because then we don't have to argue what to call the keyword or where to put it. Then you'll have to argue what the *other* syntax should look like. And not everyone agrees on the default either, see Kevin's email. Designing a new language is going to be an uphill battle, even more so than enhancing current plpgsql. Any ideas on what the *other* syntax could look like? -- 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 04:52 PM, Joel Jacobson wrote: On Tue, Sep 2, 2014 at 3:41 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 09/02/2014 04:32 PM, Joel Jacobson wrote: I think it's much better to make it the default behaviour in plpgsql2 than to add a new syntax to plpgsql, because then we don't have to argue what to call the keyword or where to put it. Then you'll have to argue what the *other* syntax should look like. And not everyone agrees on the default either, see Kevin's email. Designing a new language is going to be an uphill battle, even more so than enhancing current plpgsql. Any ideas on what the *other* syntax could look like? Well, I'm in the camp that the current default is fine... - Heikki -- 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 9/2/14 3:52 PM, Joel Jacobson wrote: On Tue, Sep 2, 2014 at 3:41 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 09/02/2014 04:32 PM, Joel Jacobson wrote: I think it's much better to make it the default behaviour in plpgsql2 than to add a new syntax to plpgsql, because then we don't have to argue what to call the keyword or where to put it. Then you'll have to argue what the *other* syntax should look like. And not everyone agrees on the default either, see Kevin's email. Designing a new language is going to be an uphill battle, even more so than enhancing current plpgsql. Any ideas on what the *other* syntax could look like? When I've played around with the idea of fixing PL/PgSQL in my head, what I had in mind is that UPDATE and DELETE not affecting exactly one row raises an exception, unless PERFORM is used. PERFORM would set a special variable (e.g. ROW_COUNT) which can be consulted after the operation. For example: UPDATE foo WHERE bar = 1; -- must affect exactly one row PERFORM UPDATE foo WHERE bar = 1; -- can affect any number of rows IF row_count 1 THEN RAISE EXCEPTION 'oh no'; END IF; This, obviously, requires us to get rid of the requirement for PERFORM today, which I see as a win as well. .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
On Tue, Sep 2, 2014 at 3:58 PM, Marko Tiikkaja ma...@joh.to wrote: When I've played around with the idea of fixing PL/PgSQL in my head, what I had in mind is that UPDATE and DELETE not affecting exactly one row raises an exception, unless PERFORM is used. PERFORM would set a special variable (e.g. ROW_COUNT) which can be consulted after the operation. For example: UPDATE foo WHERE bar = 1; -- must affect exactly one row PERFORM UPDATE foo WHERE bar = 1; -- can affect any number of rows IF row_count 1 THEN RAISE EXCEPTION 'oh no'; END IF; This, obviously, requires us to get rid of the requirement for PERFORM today, which I see as a win as well. I don't like rebranding the PERFORM command, as that would require all existing code with PERFORM commands to be changed. That also still requires 4 rows for some all other use-cases than 1 row affected, if all you want is a general error in case your expectations of rows affected were not met. I think with a single line of UPDATE command, you should be forced to indicate you want something else than 1 row affected, and if your expectations are not met, you should get the error on the UPDATE command, not having to check a variable on the next line of code. I therefore think, since we don't have to be 100% backwards compatible, it's OK and a good thing to introduce some new keyword to UPDATE (and DELETE). I have no ideas on what keyword(s) though. -- 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
Marko Tiikkaja ma...@joh.to writes: For example: UPDATE foo WHERE bar = 1; -- must affect exactly one row PERFORM UPDATE foo WHERE bar = 1; -- can affect any number of rows FWIW, I agree with the position that this would be a completely wrong thing to do. UPDATE should work like it does in plain SQL. If you want a restriction to exactly one row, that needs to be a modifier. I take no position on how the modifier should be spelled, though. 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 2014-09-02 10:21:50 -0400, Tom Lane wrote: Marko Tiikkaja ma...@joh.to writes: For example: UPDATE foo WHERE bar = 1; -- must affect exactly one row PERFORM UPDATE foo WHERE bar = 1; -- can affect any number of rows FWIW, I agree with the position that this would be a completely wrong thing to do. UPDATE should work like it does in plain SQL. If you want a restriction to exactly one row, that needs to be a modifier. I take no position on how the modifier should be spelled, though. Personally I think ONE ROW UPDATE ... reads nicely and SQL-ish. But it's not very expandable to other numbers. Greetings, Andres Freund -- Andres Freund 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
Joel Jacobson j...@trustly.com wrote: On Tue, Sep 2, 2014 at 3:12 PM, Kevin Grittner kgri...@ymail.com wrote: Joel Jacobson j...@trustly.com wrote: + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1 row, as that's the most common use-case, and provide alternative syntax to modify multiple or zero rows. Getting people to write things in a declarative style in the first place was difficult because so many of the programmers were so attached to the imperative style of coding; making it more difficult for people to Do The Right Thing is a bad idea IMO. The common use-case I have in mind is when you have a function which takes some kind of ID as an input param, which maps to a primary key in some table, which you want to update. In that case FOUND works just fine. A primary key value can't have more than one matching row. If the where-clause would be incorrect and the update would update all rows in the table, that would be a disaster, which is what I want to prevent. By the time you find out that the number of rows affected is every row in the table, you have horribly bloated the table and all its indexes. Causing a DML statement to abort when it sees a second row is a completely different issue than what I (and I suspect most others on the list) thought we were talking about, and would need to affect far more than the PL. I think the benefit of a secure and convenient way of updating exactly 1 row outweights the reduced convenience of updating multiple rows when you really want to update multiple rows. I don't. Compare this to the normal psql prompt. How many million dollars would you say the total cost would be for mistakes where someone forgets the WHERE-clause of an UPDATE or a DELETE? :-) Dunno, but that also tends to suggest a solution that isn't limited to a PL would be beneficial. It's the same type of mistake I want to prevent from in a convenient way, and there is nothing more convenient than the default behavour. That also means *all* users will get that behaviour even if they don't explicitly request it, which is a good thing, because then they are protected against the danger of not knowing how to make sure it updated/deleted only one row. I think that changing the default behavior of SQL from set oriented to something else is a horrible idea. I absolutely, unequivocally oppose that at the SQL or plpgsql level as harmful. I understand the need to check for this in various cases, and in fact the application framework I designed at my previous job had Java methods for doing DML with such a check included, named InsertOneRow(), UpdateOneRow(), and DeleteOneRow(). Very useful. If we can agree on a way to allow users to do the same in plpgsql, fine -- but certainly not as the default default (word intentionally repeated). -- 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 2
On 9/2/14 4:15 PM, Joel Jacobson wrote: I don't like rebranding the PERFORM command, as that would require all existing code with PERFORM commands to be changed. I'm not saying the suggested syntax is perfect, but PERFORM should be euthanized anyway. Or at least the need for it; perhaps there's no need to break all the current uses of PERFORM. .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
On 9/2/14 4:26 PM, Kevin Grittner wrote: Joel Jacobson j...@trustly.com wrote: The common use-case I have in mind is when you have a function which takes some kind of ID as an input param, which maps to a primary key in some table, which you want to update. In that case FOUND works just fine. A primary key value can't have more than one matching row. No, but your code can have a bug. INTO rejecting any queries returning more than one row helps, though, but having to write RETURNING TRUE INTO _OK; is not pretty either. If the where-clause would be incorrect and the update would update all rows in the table, that would be a disaster, which is what I want to prevent. By the time you find out that the number of rows affected is every row in the table, you have horribly bloated the table and all its indexes. Causing a DML statement to abort when it sees a second row is a completely different issue than what I (and I suspect most others on the list) thought we were talking about, and would need to affect far more than the PL. Updating even two rows instead of one can have catastrophic effects. It's the same type of mistake I want to prevent from in a convenient way, and there is nothing more convenient than the default behavour. That also means *all* users will get that behaviour even if they don't explicitly request it, which is a good thing, because then they are protected against the danger of not knowing how to make sure it updated/deleted only one row. I think that changing the default behavior of SQL from set oriented to something else is a horrible idea. I absolutely, unequivocally oppose that at the SQL or plpgsql level as harmful. I understand the need to check for this in various cases, and in fact the application framework I designed at my previous job had Java methods for doing DML with such a check included, named InsertOneRow(), UpdateOneRow(), and DeleteOneRow(). Very useful. If we can agree on a way to allow users to do the same in plpgsql, fine -- but certainly not as the default default (word intentionally repeated). Yeah, it doesn't necessarily need to be the default default (and I see a lot of people saying it shouldn't be). Even having a per-query modifier would be better than the current behaviour. .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
On 09/02/2014 11:52 AM, Álvaro Hernández Tortosa wrote: On 02/09/14 11:44, Pavel Stehule wrote: For 9.4, we have the media already saying Postgres has NoSQL capabilities (which is only partially true). For x.y we could have the media saying Postgres adds Oracle compatibility (which would be only partially true). But that brings a lot of users to postgres, and that helps us all. Partial true can enforce so lot of people will hate postgres too. False promises are wrong Then let's stop talking about postgres being NoSQL. NoSQL is basically schema-less (really bad name) plus infinite scalability (which basically means transparent sharding). We fail to provide the latter very clearly... Have you ever tried any of the real NoSQL products version of infinite scalability ? We are no worse than most if you use just the unstructured part (which is what the NoSQL crowd provides) and something like pl/proxy for scaling. Cheers -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ
Re: [HACKERS] PL/pgSQL 2
Marko Tiikkaja ma...@joh.to wrote: On 9/2/14 4:26 PM, Kevin Grittner wrote: Joel Jacobson j...@trustly.com wrote: The common use-case I have in mind is when you have a function which takes some kind of ID as an input param, which maps to a primary key in some table, which you want to update. In that case FOUND works just fine. A primary key value can't have more than one matching row. No, but your code can have a bug. So the main use case is to allow buggy functions which are deployed to production without adequate testing to be detected? Bugs like not getting the primary key column(s) right? I think it would be great to have some way to generate an error if a given statement doesn't affect exactly one row, but the above is a pretty weak argument for making it a default behavior. INTO rejecting any queries returning more than one row helps, though, but having to write RETURNING TRUE INTO _OK; is not pretty either. No, that sure would not be. If the where-clause would be incorrect and the update would update all rows in the table, that would be a disaster, which is what I want to prevent. By the time you find out that the number of rows affected is every row in the table, you have horribly bloated the table and all its indexes. Causing a DML statement to abort when it sees a second row is a completely different issue than what I (and I suspect most others on the list) thought we were talking about, and would need to affect far more than the PL. Updating even two rows instead of one can have catastrophic effects. That's a different problem than Joel just said was his main concern. I was pointing out that the solution he was proposing was a very poor solution to the problem he said he was trying to solve. Can you imagine the damage if a function that updated every row in a table whenever anyone tried to update a single row by primary key made it past testing and staging phases into production? Depending on the table, it might not need to run more than a few times before the bloat ate all disk space and your production environment was totally hosed to the point of needing to delete everything from $PGDATA and restore from your last known good backup. Accidentally updating a single unintended row is a whole different class of problem, with potentially completely different solutions. We can talk about both, but let's not conflate them. The proposed new behavior seems like it would only detect a small percentage of ways you can accidentally update unintended rows, but I agree it would catch enough of them to be a potentially useful option. If it were a new option on the DML statement syntax, once could certainly have code review or some sort of lint software to look for omissions. If you don't have a code review process before things hit production, well, mechanical solutions like this can only be expected to catch a small percentage of the damage from application bugs deployed to production. It's the same type of mistake I want to prevent from in a convenient way, and there is nothing more convenient than the default behavour. That also means *all* users will get that behaviour even if they don't explicitly request it, which is a good thing, because then they are protected against the danger of not knowing how to make sure it updated/deleted only one row. I think that changing the default behavior of SQL from set oriented to something else is a horrible idea. I absolutely, unequivocally oppose that at the SQL or plpgsql level as harmful. I understand the need to check for this in various cases, and in fact the application framework I designed at my previous job had Java methods for doing DML with such a check included, named InsertOneRow(), UpdateOneRow(), and DeleteOneRow(). Very useful. If we can agree on a way to allow users to do the same in plpgsql, fine -- but certainly not as the default default (word intentionally repeated). Yeah, it doesn't necessarily need to be the default default (and I see a lot of people saying it shouldn't be). Even having a per-query modifier would be better than the current behaviour. There we seem to agree. I definitely think it is a useful option if we can sort out a good way to allow it. -- 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 2
On Sep 1, 2014, at 10:24 PM, Craig Ringer cr...@2ndquadrant.com wrote: 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. Good to know. I used to do a lot of perl and will revisit the language. 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. I’d be happy with PL/Javascript, PL/Lua or ?? as long as creating dynamic SQL queries was simple, i.e. no goofball 6 or 10 level quotes to make it work. So instead of (from the docs, 40.6.4. Looping Through Query Results) EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name); EXECUTE 'INSERT INTO ' || quote_ident(mviews.mv_name) || ' ' || mviews.mv_query; should be something like: EXECUTE ‘TRUNCATE TABLE $$mviews.mv_name’; EXECUTE ‘INSERT INTO $$mviews.mv_name $$mviews.mv_query’; Wow, so after I wrote the above, I went back to review the docs and lo and behold the format function was added in 9.1 (I think). It turns out it can already be written as (not tested) EXECUTE format( ‘TRUNCATE TABLE %I’, mviews.mv_name); EXECUTE format( ‘INSERT INTO %I %L’, mviews.mv_name, mviews.mv_query); That’s not so bad and very similar to how it would have to be done in many other languages. However the first three examples in the docs for PL/pgSQL for dynamic queries and many, many other places don’t show this approach. And the format syntax is only listed 4 lines from the bottom of the section as a ‘you can also do this’. From the position and wording I would interpret that something must be wrong with using the format function to construct dynamic queries, but, who knew, I never scrolled down that far in the docs. Thank you to whomever added the format() function. So what’s wrong with using format() for dynamic queries and why is the approach not more prominent or recommended? And the format function option is not even listed in the section on quoting (40.11.1. Handling of Quotation Marks) 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 9/2/14 5:08 PM, Kevin Grittner wrote: Marko Tiikkaja ma...@joh.to wrote: On 9/2/14 4:26 PM, Kevin Grittner wrote: Joel Jacobson j...@trustly.com wrote: The common use-case I have in mind is when you have a function which takes some kind of ID as an input param, which maps to a primary key in some table, which you want to update. In that case FOUND works just fine. A primary key value can't have more than one matching row. No, but your code can have a bug. So the main use case is to allow buggy functions which are deployed to production without adequate testing to be detected? Bugs like not getting the primary key column(s) right? The main use case is making it more clear *during testing* that the code is broken. It doesn't hurt that it would also not trash your data if someone deployed bad code into production, but I think it's more important to have good tools for testing your code. I think it would be great to have some way to generate an error if a given statement doesn't affect exactly one row, but the above is a pretty weak argument for making it a default behavior. Perhaps. .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
On Tue, Sep 2, 2014 at 5:08 PM, Kevin Grittner kgri...@ymail.com wrote: Marko Tiikkaja ma...@joh.to wrote: No, but your code can have a bug. So the main use case is to allow buggy functions which are deployed to production without adequate testing to be detected? Bugs like not getting the primary key column(s) right? I think it would be great to have some way to generate an error if a given statement doesn't affect exactly one row, but the above is a pretty weak argument for making it a default behavior. Instead of writing unit tests for such trivial things as updating one row and testing if it got updated, it's better to make such unit tests asserts instead, which is exactly what we achieve if we provide a syntax to throw an error if not exactly 1 row was affected. Updating even two rows instead of one can have catastrophic effects. That's a different problem than Joel just said was his main concern. I was pointing out that the solution he was proposing was a very poor solution to the problem he said he was trying to solve. Can you imagine the damage if a function that updated every row in a table whenever anyone tried to update a single row by primary key made it past testing and staging phases into production? Depending on the table, it might not need to run more than a few times before the bloat ate all disk space and your production environment was totally hosed to the point of needing to delete everything from $PGDATA and restore from your last known good backup. Sorry for being unclear, I didn't mean to suggest the main concern is updating *all* rows. The main concern is when you have a rather complex UPDATE WHERE clause, aiming to update exactly one row. Some of the expressions might be assertions, to just double-verify the values and to make it stand-out you are checking those expressions. -- 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:10 PM, Neil Tiffin wrote: I’d be happy with PL/Javascript, PL/Lua or ?? as long as creating dynamic SQL queries was simple, i.e. no goofball 6 or 10 level quotes to make it work. So instead of (from the docs, 40.6.4. Looping Through Query Results) EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name); EXECUTE 'INSERT INTO ' || quote_ident(mviews.mv_name) || ' ' || mviews.mv_query; should be something like: EXECUTE ‘TRUNCATE TABLE $$mviews.mv_name’; EXECUTE ‘INSERT INTO $$mviews.mv_name $$mviews.mv_query’; I think we need to remove those sections entirely from the docs, in favour of using only format(...) with EXECUTE ... USING . Too many people seem to see that, and not format(...). So what’s wrong with using format() for dynamic queries and why is the approach not more prominent or recommended? Historical, really. And the format function option is not even listed in the section on quoting (40.11.1. Handling of Quotation Marks) That's a real oversight that needs fixing. Thanks. -- 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 06:44 PM, Joel Jacobson wrote: On Tue, Sep 2, 2014 at 5:08 PM, Kevin Grittner kgri...@ymail.com wrote: Marko Tiikkaja ma...@joh.to wrote: No, but your code can have a bug. So the main use case is to allow buggy functions which are deployed to production without adequate testing to be detected? Bugs like not getting the primary key column(s) right? I think it would be great to have some way to generate an error if a given statement doesn't affect exactly one row, but the above is a pretty weak argument for making it a default behavior. Instead of writing unit tests for such trivial things as updating one row and testing if it got updated, it's better to make such unit tests asserts instead, which is exactly what we achieve if we provide a syntax to throw an error if not exactly 1 row was affected. Marko posted a patch to add assertions to PL/pgSQL last year, see http://www.postgresql.org/message-id/5234af3f.4000...@joh.to. It was a long thread, but in the end I think everyone was more or less OK with the syntax ASSERT condition;. I also think that syntax is fine, and it would be a nice feature, assuming we can avoid reserving the ASSERT keyword. I think that would actually be a good way to enforce the rule that an UPDATE only updates a single row. Just put a ASSERT ROW_COUNT=1; after the update. - Heikki -- 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
Joel Jacobson j...@trustly.com wrote: The common use-case I have in mind is when you have a function which takes some kind of ID as an input param, which maps to a primary key in some table, which you want to update. If the where-clause would be incorrect and the update would update all rows in the table, that would be a disaster, which is what I want to prevent. Joel Jacobson j...@trustly.com wrote: Sorry for being unclear, I didn't mean to suggest the main concern is updating *all* rows. The main concern is when you have a rather complex UPDATE WHERE clause, aiming to update exactly one row. Some of the expressions might be assertions, to just double-verify the values and to make it stand-out you are checking those expressions. These are two different problems which probably need two different solutions. Making the default behavior of a set-based command that it throw an error if the resulting set is not exactly one row doesn't seem like the right solution to either one of them. -- 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 2
On Tue, Sep 2, 2014 at 6:03 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: I think that would actually be a good way to enforce the rule that an UPDATE only updates a single row. Just put a ASSERT ROW_COUNT=1; after the update. So instead of one line of code, I would need to write two lines of code at almost *all* places where a currently have an UPDATE. :-( In that case, I think RETURNING TRUE INTO STRICT _OK is less ugly. I think the problem with my perspective is my ambitions. I use PL/pgSQL not as a secondary language, but it's my primary language for developing applications. For me, updating a row, is like setting a variable in a normal language. No normal language would require two rows to set a variable. It would be like having to do: my $var = 10; die unless $var == 10; in Perl to set a variable. -- 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 17:03, Hannu Krosing wrote: On 09/02/2014 11:52 AM, Álvaro Hernández Tortosa wrote: On 02/09/14 11:44, Pavel Stehule wrote: For 9.4, we have the media already saying Postgres has NoSQL capabilities (which is only partially true). For x.y we could have the media saying Postgres adds Oracle compatibility (which would be only partially true). But that brings a lot of users to postgres, and that helps us all. Partial true can enforce so lot of people will hate postgres too. False promises are wrong Then let's stop talking about postgres being NoSQL. NoSQL is basically schema-less (really bad name) plus infinite scalability (which basically means transparent sharding). We fail to provide the latter very clearly... Have you ever tried any of the real NoSQL products version of infinite scalability ? Yes, and they are absolutely not infinite, and they suck in many other places. But they scale beyond one node, transparently, something that postgres doesn't. And regardless, this is what people is buying, we like it or not. We are no worse than most if you use just the unstructured part (which is what the NoSQL crowd provides) and something like pl/proxy for scaling. We are definitely worse. This is the problem, we only look to our own belly bottom (if this expression exists in English). All NoSQL scale *easily*, *transparently* beyond one node. Postgres doesn't. I'm not saying they don't suck at many many other things, or that some of them may be worse solution than the problem. But despite JSON/JSONB in pg is awesome, it's far far away from what we need to compete agains NoSQL in these regards. Ask anyone not in the postgres world to use pl/proxy for scaling and they will run away to mongo/whatever. Talk about HA... and the discussion is over :( I know how hard these problems are in the general, transactional approach that postgres takes, and that NoSQL does this for very simple, non-ACID cases, but they do. Hence, we cannot claim NoSQL compliance, just because we have jsonb. Unfortunately :( (Surely we do have many other values, but let's not say that we have NoSQL capabilities, because we don't while others -better or worse- do). Regards, Álvaro
