Thread: 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;
Re: feature request: auto savepoint for interactive psql when in transaction.
From
Gurjeet Singh
Date:
<div dir="ltr"><div class="gmail_quote">On Tue, Sep 27, 2011 at 7:57 PM, Will Leinweber <span dir="ltr"><<a href="mailto:will@heroku.com">will@heroku.com</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin: 0pt0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"> I ruined a 5 hour UPDATE by typoing a tablename on a SELECT to verify<br /> the update worked. I suppose I have no one else to blame, but it was<br /> really frustrating,to say the least. I assume this has happened to<br /> others as well.<br /><br /> I only later found out aboutSAVEPOINT, which I immediately ran the<br /> next time I attempted the huge update.<br /><br /> psql console, whilein a transaction, and while in interactive mode,<br /> should savepoint for me.<br /><br clear="all" /></blockquote></div><br/>I guess it would be a neat feature to have this in Postgres rather than in psql. That is, if runningin an explicit transaction (one started with BEGIN), issue a savepoint after/before every command and emit the savepointname in a NOTICE.<br /><br /> 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.<br /><br /> If there's no perceivableperformance difference in using savepoints even under large transactions, then we might want to make it all automaticand transparent. So Postgres issues a savepoint before every command, and if the command fails, rollback to thatsavepoint, else release that savepoint.<br /><br /> Regards,<br />-- <br /><div dir="ltr">Gurjeet Singh<br />EnterpriseDBCorporation<br />The Enterprise PostgreSQL Company<br /></div><br /></div>
Re: feature request: auto savepoint for interactive psql when in transaction.
From
Marko Tiikkaja
Date:
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 Tiikkaja http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: feature request: auto savepoint for interactive psql when in transaction.
From
Alvaro Herrera
Date:
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 <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: feature request: auto savepoint for interactive psql when in transaction.
From
"Kevin Grittner"
Date:
Gurjeet Singh <singh.gurjeet@gmail.com> wrote: > Will Leinweber <will@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
Re: feature request: auto savepoint for interactive psql when in transaction.
From
"Kevin Grittner"
Date:
Alvaro Herrera <alvherre@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
Re: feature request: auto savepoint for interactive psql when in transaction.
From
Gurjeet Singh
Date:
On Wed, Sep 28, 2011 at 1:51 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
+1
I would like it to be on/interactive by default, though.
--
Alvaro Herrera <alvherre@commandprompt.com> wrote:I had missed that. Dang, this database product is rich with nice
> See ON_ERROR_ROLLBACK
> http://www.postgresql.org/docs/9.0/static/app-psql.html
features! :-)
+1
I would like it to be on/interactive by default, though.
--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Re: feature request: auto savepoint for interactive psql when in transaction.
From
David Fetter
Date:
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.Grittner@wicourts.gov > > wrote: > > > Alvaro Herrera <alvherre@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 <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@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
Re: feature request: auto savepoint for interactive psql when in transaction.
From
Stephen Frost
Date:
* Kevin Grittner (Kevin.Grittner@wicourts.gov) wrote: > Alvaro Herrera <alvherre@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
Re: feature request: auto savepoint for interactive psql when in transaction.
From
Alvaro Herrera
Date:
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 <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: feature request: auto savepoint for interactive psql when in transaction.
From
"Kevin Grittner"
Date:
Alvaro Herrera <alvherre@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
Re: feature request: auto savepoint for interactive psql when in transaction.
From
Daniel Farina
Date:
On Wed, Sep 28, 2011 at 1:02 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Alvaro Herrera <alvherre@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
Re: feature request: auto savepoint for interactive psql when in transaction.
From
Will Leinweber
Date:
On Wed, Sep 28, 2011 at 10:39 AM, Marko Tiikkaja <marko.tiikkaja@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
Re: feature request: auto savepoint for interactive psql when in transaction.
From
Ross Reedstrom
Date:
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.Grittner@wicourts.gov > > > wrote: > > > > > Alvaro Herrera <alvherre@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. reedstrm@rice.edu Systems Engineer & Admin, Research Scientist phone: 713-348-6166 Connexions http://cnx.org fax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
Re: feature request: auto savepoint for interactive psql when in transaction.
From
Ross Reedstrom
Date:
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 c&p 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. reedstrm@rice.edu Systems Engineer & Admin, Research Scientist phone: 713-348-6166 Connexions http://cnx.org fax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
Re: feature request: auto savepoint for interactive psql when in transaction.
From
Will Leinweber
Date:
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 <reedstrm@rice.edu> wrote:
On Wed, Sep 28, 2011 at 11:47:51AM -0700, David Fetter wrote:This discussion died out with a plea for better documentation, and perhaps some
> 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.Grittner@wicourts.gov
> > > wrote:
> >
> > > Alvaro Herrera <alvherre@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.
>
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. reedstrm@rice.edu
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
Connexions http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
Re: feature request: auto savepoint for interactive psql when in transaction.
From
Bruce Momjian
Date:
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.Grittner@wicourts.gov > > > > wrote: > > > > > > > Alvaro Herrera <alvherre@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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Re: feature request: auto savepoint for interactive psql when in transaction.
From
Daniel Farina
Date:
On Mon, Nov 14, 2011 at 3:05 PM, Ross Reedstrom <reedstrm@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 c&p 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
Re: feature request: auto savepoint for interactive psql when in transaction.
From
Ross Reedstrom
Date:
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.Grittner@wicourts.gov > > > > > wrote: > > > > > > > > > Alvaro Herrera <alvherre@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. reedstrm@rice.edu Systems Engineer & Admin, Research Scientist phone: 713-348-6166 Connexions http://cnx.org fax: 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 <bruce@momjian.us> http://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 >