Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On Sat, 2012-06-23 at 12:18 +, Jasen Betts wrote: On 2012-06-19, Rafal Pietrak ra...@zorro.isa-geek.com wrote: And we are talking about interractive psql breaking transaction because of syntax error - almost always this is a one time typo. I'd prefere it to be a bit more sloopy, then deployed SQL application (e.g. non-interactive session). possibly you could program keyboard macros to handle savepoints to have an easy way to recover from these errors, but if you're working on a Yes, but again. In my own psql usage, it goes like this: this is a simple and easy SQL, most of it was cut/paste anyway what could possibly go wrong ups. But it goes wrong in less then every 20th or 100th time, less then once in a few months. So i don't realy feel like pushing somebody into a development effort, that woud just slightly enhance psql comfort of usage. I most certainly want even be cooking any macros, as I would forget to use it when it could be of some help. My comment on this thread was mearly to object, that a request to allow maintaining transaction state despite syntax error is obviusly wrong. busy database keeping a transaction open whislt you think about syntax is going to cost perfromance for the other users. And this is a really good point - although I do know my schemas and I can choose appropriate moment for long hand-opened transation, mistakes happen (well, this whole thread is about mistakes :) Anyway, I personaly feel that psql would be more comfortable if one could request explicit rollback despite errors (like by BEGIN INTERACTIVE). -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On Sun, Jun 24, 2012 at 1:37 PM, Rafal Pietrak ra...@zorro.isa-geek.comwrote: On Sat, 2012-06-23 at 12:18 +, Jasen Betts wrote: On 2012-06-19, Rafal Pietrak ra...@zorro.isa-geek.com wrote: And we are talking about interractive psql breaking transaction because of syntax error - almost always this is a one time typo. I'd prefere it to be a bit more sloopy, then deployed SQL application (e.g. non-interactive session). possibly you could program keyboard macros to handle savepoints to have an easy way to recover from these errors, but if you're working on a Yes, but again. In my own psql usage, it goes like this: this is a simple and easy SQL, most of it was cut/paste anyway what could possibly go wrong ups. But it goes wrong in less then every 20th or 100th time, less then once in a few months. So i don't realy feel like pushing somebody into a development effort, that woud just slightly enhance psql comfort of usage. I most certainly want even be cooking any macros, as I would forget to use it when it could be of some help. My comment on this thread was mearly to object, that a request to allow maintaining transaction state despite syntax error is obviusly wrong. busy database keeping a transaction open whislt you think about syntax is going to cost perfromance for the other users. And this is a really good point - although I do know my schemas and I can choose appropriate moment for long hand-opened transation, mistakes happen (well, this whole thread is about mistakes :) Additionally, I would point out that the times I have gotten into trouble with long-running transactions delaying other users it has been functioning queries which updated lots of rows unexpectedly slowly, not syntax errors ;-) Best Wishes, Chris Travers
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On 2012-06-19, Rafal Pietrak ra...@zorro.isa-geek.com wrote: And we are talking about interractive psql breaking transaction because of syntax error - almost always this is a one time typo. I'd prefere it to be a bit more sloopy, then deployed SQL application (e.g. non-interactive session). possibly you could program keyboard macros to handle savepoints to have an easy way to recover from these errors, but if you're working on a busy database keeping a transaction open whislt you think about syntax is going to cost perfromance for the other users. -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On Wed, 2012-06-20 at 00:24 -0700, Chris Travers wrote: I guess it seems to me that I would not object to a new option for transaction behavior where one could do something like SET TRANSACTION INTERACTIVE; and have no errors abort the transaction at all (explicit commit or rollback required) but I would complain loudly if this were to be the default, and I don't see a real need for it. It's already available in psql. See ON_ERROR_ROLLBACK: http://www.postgresql.org/docs/9.2/static/app-psql.html Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On 19 June 2012 22:07, Tom Lane t...@sss.pgh.pa.us wrote: Rafal Pietrak ra...@zorro.isa-geek.com writes: The point is, that SQL syntax errors are so obviusly different from execution errors, that noting this distinction should not raise any ambiguity. I beg to disagree. Typos can manifest themselves as execution errors just as well as syntax errors. The arguments for the current behaviour are clear and rational. I see no challenge possible on that basis. However, PostgreSQL is one of the only databases to behave in this way. This causes some database applications to have subtle problems when we migrate/port them to work with us. Some, though few, programs actually rely on run-time errors in order to execute correctly. I don't condone or encourage that but I do recognise that there is substantial legacy code out there, and much of that needs to run on multiple DBMS. So it would be useful to have a non-default option of statement-level abort for those cases, as an ease of use feature. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
It seems to me there is one very simple reason not to change current behavior which those in favor are glossing over. Most interactions with a database are not occurring over an interface like psql with one person typing on one side and the db executing on the other.If that were the case I would understand the concern that a typo should give the user an opportunity to pick up the statement where he/she left off. However most interactions with the database are purely through intermediary software. Adding a lot of do what I mean or give me a chance to retry that adds a great deal of complexity to the job of the software in trapping and handling errors. It is far, far more simple to say syntax errors abort transactions and leave it at that. I know as a developer I don't want that behavior to change. I guess it seems to me that I would not object to a new option for transaction behavior where one could do something like SET TRANSACTION INTERACTIVE; and have no errors abort the transaction at all (explicit commit or rollback required) but I would complain loudly if this were to be the default, and I don't see a real need for it. Best Wishes, Chris Travers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On 20/06/2012 08:24, Chris Travers wrote: It seems to me there is one very simple reason not to change current behavior which those in favor are glossing over. Most interactions with a database are not occurring over an interface like psql with one person typing on one side and the db executing on the other.If that were the case I would understand the concern that a typo should give the user an opportunity to pick up the statement where he/she left off. However most interactions with the database are purely through intermediary software. Adding a lot of do what I mean or give me a chance to retry that adds a great deal of complexity to the job of the software in trapping and handling errors. It is far, far more simple to say syntax errors abort transactions and leave it at that. I know as a developer I don't want that behavior to change. I guess it seems to me that I would not object to a new option for transaction behavior where one could do something like SET TRANSACTION INTERACTIVE; and have no errors abort the transaction at all (explicit commit or rollback required) but I would complain loudly if this were to be the default, and I don't see a real need for it. Best Wishes, Chris Travers It would be very nice to turn this feature off completely as a property of your session. I generally see it as necessary to do everything inside a transaction when working in the DB manually. It adds greater protection against forgotten WHERE clauses etc. I've seen too many DBs mashed because of a careless typo. The current behavior encourages admins not to use transactions because any error (typo or not) forces them to re-do all their work so far or put in a lot of extra typing to wrap everything. On the idea of different error behavior between bad syntax and pragmatics... Splitting hairs between a syntax error and other errors is dangerous. There are too many cases where the division can not be clear. And any implementation would find it difficult not to fall foul of the principle of least astonishment. http://en.wikipedia.org/wiki/Principle_of_least_astonishment For example pg/plsql executing dynamic SQL. An error may have been caused by faulty arguments. However one of the arguments may have been a SQL statement in part or full. How should PostgreSQL behave? See the argument as bad (data error) or the SQL it contains as a syntax error. You can always find an answer to this that works, but will that answer be obvious to every developer? Regards Phil -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On Wed, 2012-06-20 at 00:24 -0700, Chris Travers wrote: [--] I guess it seems to me that I would not object to a new option for transaction behavior where one could do something like SET TRANSACTION INTERACTIVE; and have no errors abort the transaction at all (explicit commit or rollback required) but I would complain loudly if this were to be the default, and I don't see a real need for it. Awesome! Or rather: BEGIN [INTERACTIVE]; (mind the TAB) for a one shot interaction. -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On Tue, Jun 19, 2012 at 11:25:24AM -0600, Scott Marlowe wrote: On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter edsonrich...@hotmail.com wrote: There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I would never want half job done. Thia is the purpose of transactions: or all or nothing... This this this, and again, this. Imagine: begin; insert into tableb selcet * from tableb; truncate tableb; commit; What should happen when we get to the error on the second line? Keep going? Boom, data gone because of a syntax error. I've been lurking, and maybe I should go back to that :-) but I think you misunderstand. The idea is not to ignore or second-guess typoes, but to report them without affecting the transaction, and only do this in interactive sessions. Personally, I like the idea of BEGIN INTERACTIVE, but note I do not offer to do the work. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman rocket surgeon / fe...@crowfix.com GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Simon Riggs wrote: So it would be useful to have a non-default option of statement-level abort for those cases, as an ease of use feature. I think you misspelled foot gun Chris Travers wrote: Most interactions with a database are not occurring over an interface like psql with one person typing on one side and the db executing on the other. If that were the case I would understand the concern that a typo should give the user an opportunity to pick up the statement where he/she left off. Well, that's really up to the users/authors of other tools, if they feel the need to scratch that itch. I guess it seems to me that I would not object to a new option for transaction behavior where one could do something like SET TRANSACTION INTERACTIVE; and have no errors abort the transaction at all (explicit commit or rollback required) but I would complain loudly if this were to be the default, and I don't see a real need for it. I would object. That's a recipe for disaster, and goes against our philosophy of being safe, careful, and correct. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201206200945 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk/h1BQACgkQvJuQZxSWSshZ6QCfaGG1y0d76aTMKiXTU8Gy8i2G MjUAnAiAbf53qL3MOXUEiqKARhm2mezx =wbJw -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On Wed, Jun 20, 2012 at 06:36:09AM -0700, fe...@crowfix.com wrote: On Tue, Jun 19, 2012 at 11:25:24AM -0600, Scott Marlowe wrote: On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter edsonrich...@hotmail.com wrote: There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I would never want half job done. Thia is the purpose of transactions: or all or nothing... This this this, and again, this. Imagine: begin; insert into tableb selcet * from tableb; truncate tableb; commit; What should happen when we get to the error on the second line? Keep going? Boom, data gone because of a syntax error. I've been lurking, and maybe I should go back to that :-) but I think you misunderstand. The idea is not to ignore or second-guess typoes, but to report them without affecting the transaction, and only do this in interactive sessions. Personally, I like the idea of BEGIN INTERACTIVE, but note I do not offer to do the work. Looks like I should go back to lurking, and do better at it :-( The discussion began about differentiatng typoes and other errors, which is clearly not easy or obvious, and something that has always frustrated me when I find programs which try to do so. Then I saw the idea of BEGIN INTERACTIVE and lost sight of the discussion. My apoligies for stepping in so badly. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman rocket surgeon / fe...@crowfix.com GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
Em 20/06/2012 11:00, fe...@crowfix.com escreveu: On Wed, Jun 20, 2012 at 06:36:09AM -0700, fe...@crowfix.com wrote: On Tue, Jun 19, 2012 at 11:25:24AM -0600, Scott Marlowe wrote: On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter edsonrich...@hotmail.com wrote: There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I would never want half job done. Thia is the purpose of transactions: or all or nothing... This this this, and again, this. Imagine: begin; insert into tableb selcet * from tableb; truncate tableb; commit; What should happen when we get to the error on the second line? Keep going? Boom, data gone because of a syntax error. I've been lurking, and maybe I should go back to that :-) but I think you misunderstand. The idea is not to ignore or second-guess typoes, but to report them without affecting the transaction, and only do this in interactive sessions. Personally, I like the idea of BEGIN INTERACTIVE, but note I do not offer to do the work. Looks like I should go back to lurking, and do better at it :-( The discussion began about differentiatng typoes and other errors, which is clearly not easy or obvious, and something that has always frustrated me when I find programs which try to do so. Then I saw the idea of BEGIN INTERACTIVE and lost sight of the discussion. My apoligies for stepping in so badly. Don't be sorry, your question arrived a great discussion. Nobody is mad about that, is just a matter to have great minds having great discussions! Thansk for bringing that. Does not mean I agree with your point, but the debate is bigger than my personal opinion. I'm certain that something good will arrive from this thread. In time: I believe that Begin Interactive is not a good idea. Such behavior shall be built in the interface application, not in database backend... As I stated before, being tolerant or not, or trying to guess what user means is a task for the interface. The database must keep the always safe, always good policy with data. Regards, Edson. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
Craig Ringer cr...@postnewspapers.com.au writes: I've been working in psql a lot recently, and have started to wonder why statements with syntax errors or other problems that render them unexecutable terminate the transaction. Well, the obvious reason is that it's hard to tell what the user meant, so bailing is the safest response. I understand why statements that raise errors during their execution terminate a transaction, So you're suggesting that SELECT 1/0; should terminate a transaction, but SELECT 1//0; should not? How about ROLBACK;? It gets pretty squishy pretty fast when you try to decide which sorts of errors are more important than others. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On Tue, Jun 19, 2012 at 02:20:57AM -0400, Tom Lane wrote: Craig Ringer cr...@postnewspapers.com.au writes: I've been working in psql a lot recently, and have started to wonder why statements with syntax errors or other problems that render them unexecutable terminate the transaction. Well, the obvious reason is that it's hard to tell what the user meant, so bailing is the safest response. I understand why statements that raise errors during their execution terminate a transaction, So you're suggesting that SELECT 1/0; should terminate a transaction, but SELECT 1//0; should not? How about ROLBACK;? It gets pretty squishy pretty fast when you try to decide which sorts of errors are more important than others. +1. I hate tools that try to read your mind. They invariably fail at that. The current behaviour is 100% correct and unambiguous. Cheers, Peter -- http://sjamaan.ath.cx -- The process of preparing programs for a digital computer is especially attractive, not only because it can be economically and scientifically rewarding, but also because it can be an aesthetic experience much like composing poetry or music. -- Donald Knuth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On 06/19/2012 02:20 PM, Tom Lane wrote: So you're suggesting that SELECT 1/0; should terminate a transaction, but SELECT 1//0; should not? How about ROLBACK;? It gets pretty squishy pretty fast when you try to decide which sorts of errors are more important than others. When put that way, it seems blindingly obvious. You have a talent for making a devastating point very succinctly. -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On Tue, 2012-06-19 at 19:06 +0800, Craig Ringer wrote: On 06/19/2012 02:20 PM, Tom Lane wrote: So you're suggesting that SELECT 1/0; should terminate a transaction, but SELECT 1//0; should not? How about ROLBACK;? It gets pretty squishy pretty fast when you try to decide which sorts of errors are more important than others. When put that way, it seems blindingly obvious. You have a talent for making a devastating point very succinctly. I'd humbly disagree. Not to drag this discussiong any further, just to make a point that the other approach is also blindingly obvious. Only the other way around. The point is, that SQL syntax errors are so obviusly different from execution errors, that noting this distinction should not raise any ambiguity. In Tom's example ROLBACK: 1. should not break the transaction 2. should only raise NOTICE: syntax error 2.1. in case this was issued from command line - user can always ROLTAB to see what's next. 2.2. in case of a compiled program sending a ROLBACK to the backend hack, the programmer should know better. 3. and BTW: what about rolling back a tediously cooked sequence of statements finished by COMINT? Things are not so obvious. And frankly, if not for the TAB I'd have case (3) so often, that it would have driven me crasy. -R -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On Tue, Jun 19, 2012 at 03:35:19PM +0200, Rafal Pietrak wrote: The point is, that SQL syntax errors are so obviusly different from execution errors, that noting this distinction should not raise any ambiguity. Good. One looks forward to your fully-worked-out AI/ESP patch that gets this right every time. While you're at it, I suggest fixing these obvious mistakes: SELECT SELECT 'text'; SELECT 'text; SELECT INSERT 'text' INTO column; INSERT 'text' INTO 'column'; And so on. Every one of these is a boiled down example of a stupid think-o I have made more than once. This is what the command buffer is for. If you really want your input system to provide fairly complete syntax checking for you, however, I will point out that psql's \e command will happily drop you into the editor of your choice. If you want an editor that knows more about what you want than you do, I think you will find it is spelled emacs. Best, A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
Rafal Pietrak ra...@zorro.isa-geek.com writes: The point is, that SQL syntax errors are so obviusly different from execution errors, that noting this distinction should not raise any ambiguity. I beg to disagree. Typos can manifest themselves as execution errors just as well as syntax errors. You are probably thinking that we could behave differently if the error was detected by the lexer, or perhaps the lexer + grammar, rather than later on. But those boundaries are purely implementation artifacts, and the division of labor isn't always obvious, especially to people not steeped in the innards of PG. Users are going to be confused (and unhappy) if some errors roll back their transaction while other not-obviously-different ones don't. As an example, suppose you fat-finger '-' for '=' in UPDATE: UPDATE tab SET col - 42 WHERE ... This is going to draw a grammar error. But make the same mistake a few tokens later: UPDATE tab SET col = 42 WHERE key - 42; and now you will get a pretty late-stage parse analysis failure, since it'll bleat that the argument of WHERE isn't boolean. Users are definitely not going to understand why the former doesn't kill their transaction but the latter does. Or, if we solve that problem by saying that no parse-analysis failure kills the transaction, where does that stop? The boundaries between parse analysis, planning, and execution are even squishier and more arbitrary (from a naive user's standpoint) than the ones earlier in the process. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
Hm, sorry but I still can not get into that argument. Take your example 3 (COMINT in place of COMMIT) How should the DB know that (and how) to safely recover from such error? You need to tell - and there are tools to do so right available. In an interactive session: - use autocommit=on to indicate that any statement surely will not invalidate any previous one Then the problem is non-existent - if you need transactional grouping of statements: you may envelope each statement with transactional sub structure (e.g. SAVEPOINTRELEASE) to indicate to the DB that only the inner most level of transaction is at stake and the environment outside that statement may cope with errors. Agreed, this is unexpected if coming from a DB that treats syntax errors differently. (May be sometimes there will be a mode with interactive tools that provide such enveloping implicitly (if requested by user)) In a non-interactive session it is more obvious. What should happen when after the failed COMMIT above the session is to be terminated? The pending transaction is to be terminated anyway. Moreover, of a syntax error happens with a statement (e.g. some update) and a later statement is assuming it had succeeded and will ruin your data if not, would you still appreciate the DB to simply ignore the error (logging a message of course) and later on happily commit inconsistent data? I'm sure, there will be loud outcry if such would be possible by mere syntax error handling. If your application is prepared to handle syntax errors during run, then use available tools, if not (and most application likely will not provide such logic), accept the need for testing your applications. Any reaction for a transactional system has to guarantee consistency even for the price of convenience. Thus, convenience may cost some extra effort. At the end, I read the complaint as a suggestion to maintainers of interactive tools to build such interactive convenience into their tools. But do not detect evidence for this to be a feature of the DB in the first place. Rainer On 19.06.2012 15:35, Rafal Pietrak wrote: On Tue, 2012-06-19 at 19:06 +0800, Craig Ringer wrote: On 06/19/2012 02:20 PM, Tom Lane wrote: So you're suggesting that SELECT 1/0; should terminate a transaction, but SELECT 1//0; should not? How about ROLBACK;? It gets pretty squishy pretty fast when you try to decide which sorts of errors are more important than others. When put that way, it seems blindingly obvious. You have a talent for making a devastating point very succinctly. I'd humbly disagree. Not to drag this discussiong any further, just to make a point that the other approach is also blindingly obvious. Only the other way around. The point is, that SQL syntax errors are so obviusly different from execution errors, that noting this distinction should not raise any ambiguity. In Tom's example ROLBACK: 1. should not break the transaction 2. should only raise NOTICE: syntax error 2.1. in case this was issued from command line - user can always ROLTAB to see what's next. 2.2. in case of a compiled program sending a ROLBACK to the backend hack, the programmer should know better. 3. and BTW: what about rolling back a tediously cooked sequence of statements finished by COMINT? Things are not so obvious. And frankly, if not for the TAB I'd have case (3) so often, that it would have driven me crasy. -R -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I would never want half job done. Thia is the purpose of transactions: or all or nothing... Tom Lane t...@sss.pgh.pa.us escreveu: Rafal Pietrak ra...@zorro.isa-geek.com writes: The point is, that SQL syntax errors are so obviusly different from execution errors, that noting this distinction should not raise any ambiguity. I beg to disagree. Typos can manifest themselves as execution errors just as well as syntax errors. You are probably thinking that we could behave differently if the error was detected by the lexer, or perhaps the lexer + grammar, rather than later on. But those boundaries are purely implementation artifacts, and the division of labor isn't always obvious, especially to people not steeped in the innards of PG. Users are going to be confused (and unhappy) if some errors roll back their transaction while other not-obviously-different ones don't. As an example, suppose you fat-finger '-' for '=' in UPDATE: UPDATE tab SET col - 42 WHERE ... This is going to draw a grammar error. But make the same mistake a few tokens later: UPDATE tab SET col = 42 WHERE key - 42; and now you will get a pretty late-stage parse analysis failure, since it'll bleat that the argument of WHERE isn't boolean. Users are definitely not going to understand why the former doesn't kill their transaction but the latter does. Or, if we solve that problem by saying that no parse-analysis failure kills the transaction, where does that stop? The boundaries between parse analysis, planning, and execution are even squishier and more arbitrary (from a naive user's standpoint) than the ones earlier in the process. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 ... ON_ERROR_ROLLBACK=interactive provides a helper for that in psql. Savepoints are overhead, though, and I don't understand why they're required for statements that don't even parse. Other have handled the latter part of the above already (short version: error is the only sane response to a non-parsing statement), but as to the first part, the overhead is really not that high. Yes, psql will create and remove a savepoint around each statement, but this is a very lightweight action, especially if you are using psql in interactive mode. In other words, we already have an elegant and lightweight approach to the described problem. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201206191146 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk/gn30ACgkQvJuQZxSWSsgekgCfcoBq2VjCitjrpK9CrSMFob0Y YF8An3Qp/OQjAcRsEBahE5OIbFzEEZX/ =hHAn -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter edsonrich...@hotmail.com wrote: There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I would never want half job done. Thia is the purpose of transactions: or all or nothing... This this this, and again, this. Imagine: begin; insert into tableb selcet * from tableb; truncate tableb; commit; What should happen when we get to the error on the second line? Keep going? Boom, data gone because of a syntax error. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
And I will be pleased that data is gone! I really did not expect anything but this. If I need such tolerant behavior, then this shall be a feature of my special app, not a feature of the database... If the developer does not know how to write sql, then is time to learn. If the problem is the dynamic generated Sql, then I must write more test cases to cover these new scenarios. But IMHO, database must fail always (syntax or not...). Regards, Edson Scott Marlowe scott.marl...@gmail.com escreveu: On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter edsonrich...@hotmail.com wrote: There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I would never want half job done. Thia is the purpose of transactions: or all or nothing... This this this, and again, this. Imagine: begin; insert into tableb selcet * from tableb; truncate tableb; commit; What should happen when we get to the error on the second line? Keep going? Boom, data gone because of a syntax error. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On Wed, 2012-06-20 at 08:27 +1200, Gavin Flower wrote: [] I would be be extremely concerned about any move to allow syntax errors not to abort a transaction. Me too. But it's a nuicence for interractive session when transactions breakes due to syntax error - still, may be as Rainer Pruy said earlier, this may be a suggestion to maintainers of interactive tools. Even minor syntax errors may indicate that something much more serious is wrong. No. We are talking about an interactive session - someone just have misstyped something; it's a one time event. PL/1 was designed to tolerate various errors and guess what the programmer intended, it would make assumptions and act on them – a good way to hide serious programming errors. A language that is too forgiving encourages sloppy thinking. This is dangerous grounds :) - without going too far I'd say, there is also ADA (rigorious) and perl (sloopy). statistically, anything I installed, that's written in perl is ways more stable, then enything else. But I'd also say, that I prefere tools (programming languages, operating systems, IDE, etc), that help me from makeing errors. [---] I would far rather a compiler pull me up for minor violations, than an obvious error not picked up until I came to test the program. The compiler is not perfect and some errors will slip through. However, the sooner errors are detected, the less likely an error will cause bad problems in production. On the other hand I find it more tedious then it pays off, when current CC force me to explicitly typecast every pointer I write, because: type don't match. But that's not the point here. The point is, that sometimes we need regorious, and sometimes we need sloopy. Like, when we start a project, we need to scetch, then we need to tighten the shoelaces. At least for me it works that way. And we are talking about interractive psql breaking transaction because of syntax error - almost always this is a one time typo. I'd prefere it to be a bit more sloopy, then deployed SQL application (e.g. non-interactive session). The greater the size and complexity of code, the more important this all becomes. Mind you, even very simple SQL SELECT's might have results used to make critical business decisions - so even then, sloppy habits should be discouraged. Hmmm, years ago I has told, that UNIX is sloopy (does not guarantee anything to a process: neither time to dysk when writing, nor CPU time, nor even IRQ response time), so it will not prevail. It did. And it runs critical systems. As postgres is my favourite database for its ease of use (to the point where I dont try applications which only run on its closest free-couterpart: mysql :), there is always room for improvements (my personal wishlist for postgres is currently 11 points long and keeping transaction on syntax errors is even beyond that list). -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
But that data was supposed to get transferred into another table first! Data shouldn't just disappear like that. If you want that kind of behaviour use a different db that likes to throw your data away when it shouldn't. On Tue, Jun 19, 2012 at 1:09 PM, Edson Richter edsonrich...@hotmail.com wrote: And I will be pleased that data is gone! I really did not expect anything but this. If I need such tolerant behavior, then this shall be a feature of my special app, not a feature of the database... If the developer does not know how to write sql, then is time to learn. If the problem is the dynamic generated Sql, then I must write more test cases to cover these new scenarios. But IMHO, database must fail always (syntax or not...). Regards, Edson Scott Marlowe scott.marl...@gmail.com escreveu: On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter edsonrich...@hotmail.com wrote: There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I would never want half job done. Thia is the purpose of transactions: or all or nothing... This this this, and again, this. Imagine: begin; insert into tableb selcet * from tableb; truncate tableb; commit; What should happen when we get to the error on the second line? Keep going? Boom, data gone because of a syntax error. -- To understand recursion, one must first understand recursion. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
I like the current behavior. Having been pleasantly surprised that this is how Pg operates, it is very helpful when I'm working on scripts or batches such as for creating or populating schemas. If it dies part way through, I know I can just fix the problem and rerun the whole thing, without having to first undo or skip the earlier portions. Also, rollback for everything is much more deterministic. -- Darren Duncan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On 20/06/12 01:35, Rafal Pietrak wrote: On Tue, 2012-06-19 at 19:06 +0800, Craig Ringer wrote: On 06/19/2012 02:20 PM, Tom Lane wrote: So you're suggesting that SELECT 1/0; should terminate a transaction, but SELECT 1//0; should not? How about ROLBACK;? It gets pretty squishy pretty fast when you try to decide which sorts of errors are more important than others. When put that way, it seems blindingly obvious. You have a talent for making a devastating point very succinctly. I'd humbly disagree. Not to drag this discussiong any further, just to make a point that the other approach is also blindingly obvious. Only the other way around. The point is, that SQL syntax errors are so obviusly different from execution errors, that noting this distinction should not raise any ambiguity. In Tom's example ROLBACK: 1. should not break the transaction 2. should only raise NOTICE: syntax error 2.1. in case this was issued from command line - user can always ROLTAB to see what's next. 2.2. in case of a compiled program sending a ROLBACK to the backend hack, the programmer should know better. 3. and BTW: what about rolling back a tediously cooked sequence of statements finished by COMINT? Things are not so obvious. And frankly, if not for the TAB I'd have case (3) so often, that it would have driven me crasy. -R -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ I would be be extremely concerned about any move to allow syntax errors not to abort a transaction. Even minor syntax errors may indicate that something much more serious is wrong. PL/1 was designed to tolerate various errors and guess what the programmer intended, it would make assumptions and act on them – a good way to hide serious programming errors. A language that is too forgiving encourages sloppy thinking. A bit like in chess, if you don't follow the dictum of 'touch a piece move it' in social play (it is the rule in match and tournament play), then your level of skill in Chess is unlikely to improve much. I coach Chess at my son's school and I used to be Director-of-Play for Chess tournaments. I remember learning C many years ago, very unforgiving. However, the discipline imposed was very beneficial to improving my programming skills. I would far rather a compiler pull me up for minor violations, than an obvious error not picked up until I came to test the program. The compiler is not perfect and some errors will slip through. However, the sooner errors are detected, the less likely an error will cause bad problems in production. The greater the size and complexity of code, the more important this all becomes. Mind you, even very simple SQL SELECT's might have results used to make critical business decisions - so even then, sloppy habits should be discouraged. I would be very reluctant to hire any developer who had the mind set of seriously wanting something like psql to be forgiving of any kind of error - as it suggests that they are more careless than normal, and lack the attitude to be reliably rigorous.
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
According to documentation, TRUNCATEis transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit. You will find this description at following page: http://www.postgresql.org/docs/9.0/static/sql-truncate.html So, when you have the syntax error on second line, then transaction is rolled back (cannot proceed: and that's why Syntax Errors should be treated as any other error) and your data is safe. Regards, Edson Richter. Em 19/06/2012 18:58, Scott Marlowe escreveu: But that data was supposed to get transferred into another table first! Data shouldn't just disappear like that. If you want that kind of behaviour use a different db that likes to throw your data away when it shouldn't. On Tue, Jun 19, 2012 at 1:09 PM, Edson Richter edsonrich...@hotmail.com wrote: And I will be pleased that data is gone! I really did not expect anything but this. If I need such tolerant behavior, then this shall be a feature of my special app, not a feature of the database... If the developer does not know how to write sql, then is time to learn. If the problem is the dynamic generated Sql, then I must write more test cases to cover these new scenarios. But IMHO, database must fail always (syntax or not...). Regards, Edson Scott Marlowe scott.marl...@gmail.com escreveu: On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter edsonrich...@hotmail.com wrote: There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I would never want half job done. Thia is the purpose of transactions: or all or nothing... This this this, and again, this. Imagine: begin; insert into tableb selcet * from tableb; truncate tableb; commit; What should happen when we get to the error on the second line? Keep going? Boom, data gone because of a syntax error.
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On Tue, Jun 19, 2012 at 6:19 PM, Edson Richter edsonrich...@hotmail.com wrote: According to documentation, TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit. You will find this description at following page: http://www.postgresql.org/docs/9.0/static/sql-truncate.html So, when you have the syntax error on second line, then transaction is rolled back (cannot proceed: and that's why Syntax Errors should be treated as any other error) and your data is safe. Yes but the discussion was that the syntax error SHOULDN'T cause a roll back, and I was giving an example of when a transaction should have rolled back but wouldn't have if syntax errors didn't cause rollback. In a different vein, the issue of interactive versus scripted is something I don't want to take chances on getting wrong. If I'm in the psql terminal and type \i /tmp/somesqlile.sql is that interactive or scripted? How can psql know? Should it know? Can I trust it to make the right decision of interactive versus scripted each time? I generally put more than two lines of sql in a text file, edit it, and throw at begin; on it. run it with \i and then commit or rollback as needed. It documents what you did so you can check it in somewhere, and makes it repeatable. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
Em 19/06/2012 22:26, Scott Marlowe escreveu: On Tue, Jun 19, 2012 at 6:19 PM, Edson Richter edsonrich...@hotmail.com wrote: According to documentation, TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit. You will find this description at following page: http://www.postgresql.org/docs/9.0/static/sql-truncate.html So, when you have the syntax error on second line, then transaction is rolled back (cannot proceed: and that's why Syntax Errors should be treated as any other error) and your data is safe. Yes but the discussion was that the syntax error SHOULDN'T cause a roll back, and I was giving an example of when a transaction should have rolled back but wouldn't have if syntax errors didn't cause rollback. In a different vein, the issue of interactive versus scripted is something I don't want to take chances on getting wrong. If I'm in the psql terminal and type \i /tmp/somesqlile.sql is that interactive or scripted? How can psql know? Should it know? Can I trust it to make the right decision of interactive versus scripted each time? I generally put more than two lines of sql in a text file, edit it, and throw at begin; on it. run it with \i and then commit or rollback as needed. It documents what you did so you can check it in somewhere, and makes it repeatable. AFAIK, psql open one connection to database - and the transaction is connection related (two different connections does not share a transaction). I really mean AFAIK. At this point, someone else with more internals knowledge can give some light here. My argument was pro syntax error should rollback to make things safe... :-). Assuming psql is working with only one connection, even in interactive mode, the transaction should remains valid. Regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Feature discussion: Should syntax errors abort a transaction?
Hi all I've been working in psql a lot recently, and have started to wonder why statements with syntax errors or other problems that render them unexecutable terminate the transaction. I understand why statements that raise errors during their execution terminate a transaction, and that explicit savepoints may be used if this is undesired. That's all good, and I know that ON_ERROR_ROLLBACK=interactive provides a helper for that in psql. Savepoints are overhead, though, and I don't understand why they're required for statements that don't even parse. If I typo a statement and run: SELETC blah FROM blah; why is a savepoint required to stop that from terminating the transaction? I know psql isn't parsing and validating the statements so bad statements still go to the backend, of course, but I don't get why the backend can't recognise an unparseable statement or statement that references non-existent database objects and report it without killing the transaction if it's talking to psql interactively. Is this just a nobody's cared enough to implement it thing, where it'd be possible but the simplest/safest/easiest path is to have the backend always kill the tx and nobody's wanted to add a communication channel to let psql tell the backend it's working interactively? -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
