Re: [HACKERS] feature request: auto savepoint for interactive psql when in transaction.
On Wed, Aug 15, 2012 at 10:26:55PM -0400, Bruce Momjian wrote: On Mon, Nov 14, 2011 at 04:19:30PM -0600, Ross Reedstrom wrote: On Wed, Sep 28, 2011 at 11:47:51AM -0700, David Fetter wrote: On Wed, Sep 28, 2011 at 02:25:44PM -0400, Gurjeet Singh wrote: On Wed, Sep 28, 2011 at 1:51 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Alvaro Herrera alvhe...@commandprompt.com wrote: See ON_ERROR_ROLLBACK http://www.postgresql.org/docs/9.0/static/app-psql.html I had missed that. Dang, this database product is rich with nice features! :-) +1 I would like it to be on/interactive by default, though. You can have it by putting it in your .psqlrc. If we were just starting out, I'd be all for changing the defaults, but we're not. We'd break things unnecessarily if we changed this default. This discussion died out with a plea for better documentation, and perhaps some form of discoverability. I've scanned ahead and see no further discussion. However, I'm wondering, what use-cases would be broken by setting the default to 'interactive'? Running a non-interactive script by piping it to psql? Reading the code, I see that case is covered: the definition of 'interactive' includes both stdin and stdout are a tty, and the source of commands is stdin. Seems this functionality appeared in version 8.1. Was there discussion re: making it the default at that time? I'm all for backward compatibility, but I'm having trouble seeing what would break. I see that Peter blogged about this from a different angle over a year ago (http://petereisentraut.blogspot.com/2010/03/running-sql-scripts-with-psql.html) which drew a comment from Tom Lane that perhaps we need a better/different tool for running scripts. That would argue the defaults for psql proper should favor safe interactive use (autocommit off, anyone?) Peter mentioned the traditional method unix shells use to handle this: different config files are read for interactive vs. non-interactive startup. Seems we have that, just for the one setting ON_ERROR_ROLLBACK. What documentation improvement are you suggesting? The docs seem clear to me. Wow, that's a blast from the past: November. I think I wasn't looking for docs changes, just suggested that the thread ended with a plea from others for docs. I was wondering what supposed breakage would occur by changing the default psql ON_ERROR_ROLLBACK behavior to 'interactive', since the code guards that pretty hard to make sure it's a human in a terminal, not a redirect or script. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] feature request: auto savepoint for interactive psql when in transaction.
On Mon, Nov 14, 2011 at 3:05 PM, Ross Reedstrom reeds...@rice.edu wrote: On Mon, Nov 14, 2011 at 02:45:04PM -0800, Will Leinweber wrote: My coworker Dan suggested that some people copy and paste scripts. However I feel that that is an orthogonal problem and if there is a very high rate of input psql should detect that and turn interactive off. And I still strongly feel that on_error_rollback=interactive should be the default. Hmm, I think that falls under the don't so that, then usecase. I've been known to cp the occasional script - I guess the concern here would be not seeing failed steps that scrolled off the terminal. (I set my scrollback to basically infinity and actaully use it, but then I'm strange that way :-) ) I do this and have done this all the time. Because emacs. On the other hand, I only really do it in line-buffered modes. I also feel there is something of a development/production parity that is broken by this, but then again, so are backslash commands interpreted by psql, and that has never proven to be a practical problem. That I know of. I wouldn't let my particular use case (M-x shell) get in the way of changing the default if that was the consensus because I think this would help a lot more people than hurt. In the discoverability department, one can not-hack the server error message by having psql emit its own hint when it receives an error while in a transaction block (vs auto-commit). This is knowable via the ReadyForQuery message, which can tell you idle in transaction. -- fdr -- 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] feature request: auto savepoint for interactive psql when in transaction.
On Mon, Nov 14, 2011 at 04:19:30PM -0600, Ross Reedstrom wrote: On Wed, Sep 28, 2011 at 11:47:51AM -0700, David Fetter wrote: On Wed, Sep 28, 2011 at 02:25:44PM -0400, Gurjeet Singh wrote: On Wed, Sep 28, 2011 at 1:51 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Alvaro Herrera alvhe...@commandprompt.com wrote: See ON_ERROR_ROLLBACK http://www.postgresql.org/docs/9.0/static/app-psql.html I had missed that. Dang, this database product is rich with nice features! :-) +1 I would like it to be on/interactive by default, though. You can have it by putting it in your .psqlrc. If we were just starting out, I'd be all for changing the defaults, but we're not. We'd break things unnecessarily if we changed this default. This discussion died out with a plea for better documentation, and perhaps some form of discoverability. I've scanned ahead and see no further discussion. However, I'm wondering, what use-cases would be broken by setting the default to 'interactive'? Running a non-interactive script by piping it to psql? Reading the code, I see that case is covered: the definition of 'interactive' includes both stdin and stdout are a tty, and the source of commands is stdin. Seems this functionality appeared in version 8.1. Was there discussion re: making it the default at that time? I'm all for backward compatibility, but I'm having trouble seeing what would break. I see that Peter blogged about this from a different angle over a year ago (http://petereisentraut.blogspot.com/2010/03/running-sql-scripts-with-psql.html) which drew a comment from Tom Lane that perhaps we need a better/different tool for running scripts. That would argue the defaults for psql proper should favor safe interactive use (autocommit off, anyone?) Peter mentioned the traditional method unix shells use to handle this: different config files are read for interactive vs. non-interactive startup. Seems we have that, just for the one setting ON_ERROR_ROLLBACK. What documentation improvement are you suggesting? The docs seem clear to me. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] feature request: auto savepoint for interactive psql when in transaction.
On Wed, Sep 28, 2011 at 11:47:51AM -0700, David Fetter wrote: On Wed, Sep 28, 2011 at 02:25:44PM -0400, Gurjeet Singh wrote: On Wed, Sep 28, 2011 at 1:51 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Alvaro Herrera alvhe...@commandprompt.com wrote: See ON_ERROR_ROLLBACK http://www.postgresql.org/docs/9.0/static/app-psql.html I had missed that. Dang, this database product is rich with nice features! :-) +1 I would like it to be on/interactive by default, though. You can have it by putting it in your .psqlrc. If we were just starting out, I'd be all for changing the defaults, but we're not. We'd break things unnecessarily if we changed this default. This discussion died out with a plea for better documentation, and perhaps some form of discoverability. I've scanned ahead and see no further discussion. However, I'm wondering, what use-cases would be broken by setting the default to 'interactive'? Running a non-interactive script by piping it to psql? Reading the code, I see that case is covered: the definition of 'interactive' includes both stdin and stdout are a tty, and the source of commands is stdin. Seems this functionality appeared in version 8.1. Was there discussion re: making it the default at that time? I'm all for backward compatibility, but I'm having trouble seeing what would break. I see that Peter blogged about this from a different angle over a year ago (http://petereisentraut.blogspot.com/2010/03/running-sql-scripts-with-psql.html) which drew a comment from Tom Lane that perhaps we need a better/different tool for running scripts. That would argue the defaults for psql proper should favor safe interactive use (autocommit off, anyone?) Peter mentioned the traditional method unix shells use to handle this: different config files are read for interactive vs. non-interactive startup. Seems we have that, just for the one setting ON_ERROR_ROLLBACK. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- 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] feature request: auto savepoint for interactive psql when in transaction.
On Mon, Nov 14, 2011 at 02:45:04PM -0800, Will Leinweber wrote: My coworker Dan suggested that some people copy and paste scripts. However I feel that that is an orthogonal problem and if there is a very high rate of input psql should detect that and turn interactive off. And I still strongly feel that on_error_rollback=interactive should be the default. Hmm, I think that falls under the don't so that, then usecase. I've been known to cp the occasional script - I guess the concern here would be not seeing failed steps that scrolled off the terminal. (I set my scrollback to basically infinity and actaully use it, but then I'm strange that way :-) ) Trying to autodetect 'high rate of input' seems ... problematic. The code as is does handle detecting interactivity at startup, and for the current command - switching mid-stream ... catching repeated auto-rollbacks might be a possibility, then switching the transaction into 'failed' state. That should catch most of the possible cases where an early set of steps failed, but scrolled off, so there's no visible error at the end of paste. Until then, I've included this as a PSA at the start of any postgres talks I've given, because it's simply not widely known. Good man. (That's a Postgres Service Announcement, then?) Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- 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] feature request: auto savepoint for interactive psql when in transaction.
My coworker Dan suggested that some people copy and paste scripts. However I feel that that is an orthogonal problem and if there is a very high rate of input psql should detect that and turn interactive off. And I still strongly feel that on_error_rollback=interactive should be the default. Until then, I've included this as a PSA at the start of any postgres talks I've given, because it's simply not widely known. On Mon, Nov 14, 2011 at 2:19 PM, Ross Reedstrom reeds...@rice.edu wrote: On Wed, Sep 28, 2011 at 11:47:51AM -0700, David Fetter wrote: On Wed, Sep 28, 2011 at 02:25:44PM -0400, Gurjeet Singh wrote: On Wed, Sep 28, 2011 at 1:51 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Alvaro Herrera alvhe...@commandprompt.com wrote: See ON_ERROR_ROLLBACK http://www.postgresql.org/docs/9.0/static/app-psql.html I had missed that. Dang, this database product is rich with nice features! :-) +1 I would like it to be on/interactive by default, though. You can have it by putting it in your .psqlrc. If we were just starting out, I'd be all for changing the defaults, but we're not. We'd break things unnecessarily if we changed this default. This discussion died out with a plea for better documentation, and perhaps some form of discoverability. I've scanned ahead and see no further discussion. However, I'm wondering, what use-cases would be broken by setting the default to 'interactive'? Running a non-interactive script by piping it to psql? Reading the code, I see that case is covered: the definition of 'interactive' includes both stdin and stdout are a tty, and the source of commands is stdin. Seems this functionality appeared in version 8.1. Was there discussion re: making it the default at that time? I'm all for backward compatibility, but I'm having trouble seeing what would break. I see that Peter blogged about this from a different angle over a year ago ( http://petereisentraut.blogspot.com/2010/03/running-sql-scripts-with-psql.html ) which drew a comment from Tom Lane that perhaps we need a better/different tool for running scripts. That would argue the defaults for psql proper should favor safe interactive use (autocommit off, anyone?) Peter mentioned the traditional method unix shells use to handle this: different config files are read for interactive vs. non-interactive startup. Seems we have that, just for the one setting ON_ERROR_ROLLBACK. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
[HACKERS] feature request: auto savepoint for interactive psql when in transaction.
I ruined a 5 hour UPDATE by typoing a table name on a SELECT to verify the update worked. I suppose I have no one else to blame, but it was really frustrating, to say the least. I assume this has happened to others as well. I only later found out about SAVEPOINT, which I immediately ran the next time I attempted the huge update. psql console, while in a transaction, and while in interactive mode, should savepoint for me. —Will bqjezaraxa=# select count(*) from transactions where log=''; ERROR: relation transactions does not exist bqjezaraxa=# select count(*) from transfers where log=''; ERROR: current transaction is aborted, commands ignored until end of transaction block bqjezaraxa=# oh damn it ; ERROR: syntax error at or near oh LINE 1: oh damn 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] feature request: auto savepoint for interactive psql when in transaction.
On Tue, Sep 27, 2011 at 7:57 PM, Will Leinweber w...@heroku.com wrote: I ruined a 5 hour UPDATE by typoing a table name on a SELECT to verify the update worked. I suppose I have no one else to blame, but it was really frustrating, to say the least. I assume this has happened to others as well. I only later found out about SAVEPOINT, which I immediately ran the next time I attempted the huge update. psql console, while in a transaction, and while in interactive mode, should savepoint for me. I guess it would be a neat feature to have this in Postgres rather than in psql. That is, if running in an explicit transaction (one started with BEGIN), issue a savepoint after/before every command and emit the savepoint name in a NOTICE. I *think* savepoints are detrimental to performance, maybe under certain pre-conditions, so it might be desirable to control it using a user-settable parameter. If there's no perceivable performance difference in using savepoints even under large transactions, then we might want to make it all automatic and transparent. So Postgres issues a savepoint before every command, and if the command fails, rollback to that savepoint, else release that savepoint. Regards, -- Gurjeet Singh EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] feature request: auto savepoint for interactive psql when in transaction.
On 28/09/2011 02:57, Will Leinweber wrote: psql console, while in a transaction, and while in interactive mode, should savepoint for me. You are lucky, since that feature has been in psql for some time already. Simply add this to your .psqlrc: \set ON_ERROR_ROLLBACK on -- Marko Tiikkajahttp://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] feature request: auto savepoint for interactive psql when in transaction.
Excerpts from Will Leinweber's message of mar sep 27 20:57:52 -0300 2011: I ruined a 5 hour UPDATE by typoing a table name on a SELECT to verify the update worked. I suppose I have no one else to blame, but it was really frustrating, to say the least. I assume this has happened to others as well. I only later found out about SAVEPOINT, which I immediately ran the next time I attempted the huge update. psql console, while in a transaction, and while in interactive mode, should savepoint for me. See ON_ERROR_ROLLBACK http://www.postgresql.org/docs/9.0/static/app-psql.html -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] feature request: auto savepoint for interactive psql when in transaction.
Gurjeet Singh singh.gurj...@gmail.com wrote: Will Leinweber w...@heroku.com wrote: I ruined a 5 hour UPDATE by typoing a table name on a SELECT to verify the update worked. Ouch! I normally use tab-completion or copy/paste to save myself from myself in such situations. I only later found out about SAVEPOINT, which I immediately ran the next time I attempted the huge update. That could work, too. Of course you have to remember too execute the SAVEPOINT statement *after* the big UPDATE, and you could have a typo in entering the SAVEPOINT command. psql console, while in a transaction, and while in interactive mode, should savepoint for me. I could potentially see a psql backslash command for that. One concern I would have about it is that sometimes people paste a series of commands into an interactive psql session as one big paste. What happens then? I guess it would be a neat feature to have this in Postgres rather than in psql. That is, if running in an explicit transaction (one started with BEGIN), issue a savepoint after/before every command and emit the savepoint name in a NOTICE. You certainly wouldn't want to do that all the time, and I'm very skeptical about the idea of putting it in the backend code. For one thing, I don't think it makes sense to do this except in an interactive session. (At least, I can't think of a use-case without a human on the other end of the connection.) If there's no perceivable performance difference in using savepoints even under large transactions, then we might want to make it all automatic and transparent. So Postgres issues a savepoint before every command, and if the command fails, rollback to that savepoint, else release that savepoint. No. While I haven't seen the failed transaction concept in other databases, I'm inclined to think it's a good idea. If you have a series of statements to run, and one of them fails, continuing to execute later statements could cause data loss. (Picture CREATE TABLE AS SELECT followed by DROP TABLE on the original.) -Kevin -- 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] feature request: auto savepoint for interactive psql when in transaction.
Alvaro Herrera alvhe...@commandprompt.com wrote: See ON_ERROR_ROLLBACK http://www.postgresql.org/docs/9.0/static/app-psql.html I had missed that. Dang, this database product is rich with nice features! :-) -Kevin -- 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] feature request: auto savepoint for interactive psql when in transaction.
On Wed, Sep 28, 2011 at 1:51 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Alvaro Herrera alvhe...@commandprompt.com wrote: See ON_ERROR_ROLLBACK http://www.postgresql.org/docs/9.0/static/app-psql.html I had missed that. Dang, this database product is rich with nice features! :-) +1 I would like it to be on/interactive by default, though. -- Gurjeet Singh EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] feature request: auto savepoint for interactive psql when in transaction.
On Wed, Sep 28, 2011 at 02:25:44PM -0400, Gurjeet Singh wrote: On Wed, Sep 28, 2011 at 1:51 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Alvaro Herrera alvhe...@commandprompt.com wrote: See ON_ERROR_ROLLBACK http://www.postgresql.org/docs/9.0/static/app-psql.html I had missed that. Dang, this database product is rich with nice features! :-) +1 I would like it to be on/interactive by default, though. You can have it by putting it in your .psqlrc. If we were just starting out, I'd be all for changing the defaults, but we're not. We'd break things unnecessarily if we changed this default. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] feature request: auto savepoint for interactive psql when in transaction.
* Kevin Grittner (kevin.gritt...@wicourts.gov) wrote: Alvaro Herrera alvhe...@commandprompt.com wrote: See ON_ERROR_ROLLBACK http://www.postgresql.org/docs/9.0/static/app-psql.html I had missed that. Dang, this database product is rich with nice features! :-) Be careful when running scripts, however.. Any invocation of psql will read you .psqlrc and if you've got ON_ERROR_ROLLBACK set there then psql -f blah ; will pick up on that and you'll end up running every command in the script (including the commit at the end...), even if some of the commands in it failed. So, if you put it in your .psqlrc, be VERY sure that you UN-set it on the command-line whenever running SQL scripts which *should* rollback on failures.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] feature request: auto savepoint for interactive psql when in transaction.
Excerpts from Stephen Frost's message of mié sep 28 16:22:58 -0300 2011: Be careful when running scripts, however.. Any invocation of psql will read you .psqlrc and if you've got ON_ERROR_ROLLBACK set there then psql -f blah ; will pick up on that and you'll end up running every command in the script (including the commit at the end...), even if some of the commands in it failed. So set it to interactive. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] feature request: auto savepoint for interactive psql when in transaction.
Alvaro Herrera alvhe...@commandprompt.com wrote: ON_ERROR_ROLLBACK [on can be a problem in a script file] So set it to interactive. I think we have an opportunity for a documentation enhancement there. -Kevin -- 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] feature request: auto savepoint for interactive psql when in transaction.
On Wed, Sep 28, 2011 at 1:02 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Alvaro Herrera alvhe...@commandprompt.com wrote: ON_ERROR_ROLLBACK [on can be a problem in a script file] So set it to interactive. I think we have an opportunity for a documentation enhancement there. In the same vein, I think there may also be some room for discoverability enhancements here in psql, too: I've been using psql for years and just compensated for want of this feature by being very careful or annoyed (when that didn't work out). I never knew about this. Something along the lines of the HINT message emitted from the server, but I do not know if client-side psql has any prior art for that. -- fdr -- 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] feature request: auto savepoint for interactive psql when in transaction.
On Wed, Sep 28, 2011 at 10:39 AM, Marko Tiikkaja marko.tiikk...@2ndquadrant.com wrote: Simply add this to your .psqlrc: \set ON_ERROR_ROLLBACK on Thank you Marko and Alvaro for pointing me in the right direction. I set it to 'interactive', which I think makes the most sense. I do wish this behavior was a little more discoverable, even though it is in the manual. —Will -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
