Re: 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;

On Tue, Sep 27, 2011 at 7:57 PM, Will Leinweber <will(at)heroku(dot)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

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

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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:
> Will Leinweber <will(at)heroku(dot)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

Alvaro Herrera <alvherre(at)commandprompt(dot)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

On Wed, Sep 28, 2011 at 10:39 AM, Marko Tiikkaja
<marko(dot)tiikkaja(at)2ndquadrant(dot)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

On Wed, Sep 28, 2011 at 1:51 PM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> Alvaro Herrera <alvherre(at)commandprompt(dot)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

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(dot)Grittner(at)wicourts(dot)gov
> > wrote:
>
> > Alvaro Herrera <alvherre(at)commandprompt(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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

* Kevin Grittner (Kevin(dot)Grittner(at)wicourts(dot)gov) wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)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

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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)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

On Wed, Sep 28, 2011 at 1:02 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)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

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(dot)Grittner(at)wicourts(dot)gov
> > > wrote:
> >
> > > Alvaro Herrera <alvherre(at)commandprompt(dot)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(at)rice(dot)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

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(at)rice(dot)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(dot)Grittner(at)wicourts(dot)gov
> > > > wrote:
> > >
> > > > Alvaro Herrera <alvherre(at)commandprompt(dot)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(at)rice(dot)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
>

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(at)rice(dot)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

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(dot)Grittner(at)wicourts(dot)gov
> > > > wrote:
> > >
> > > > Alvaro Herrera <alvherre(at)commandprompt(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Mon, Nov 14, 2011 at 3:05 PM, Ross Reedstrom <reedstrm(at)rice(dot)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

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(dot)Grittner(at)wicourts(dot)gov
> > > > > wrote:
> > > >
> > > > > Alvaro Herrera <alvherre(at)commandprompt(dot)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(at)rice(dot)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(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>