Thread: Re: [PATCHES] Continue transactions after errors in psql

Re: [PATCHES] Continue transactions after errors in psql

From
Bruce Momjian
Date:
Greg Sabino Mullane wrote:
>  > The SQL-Standard itself says that errors inside transactions should only
> > rollback the last statement, if possible. So why is that not implemented in
> > PostgreSQL? What I read from past discussions here, is because it's just
> > unsave and will lead to data-garbage if you aren't very careful.
>   
> That's a good point: if that is indeed what the standard says, we should
> probably see about following it. Rolling back to the last savepoint seems
> a reasonable behavior to me.

OK, so we need to make a decision.  Right now I have Greg's patch that
is enabled by "\set ON_ERROR_ROLLBACK on":test=> \set ON_ERROR_ROLLBACK ontest=> BEGIN;BEGINtest=> lkjasdf;ERROR:
syntaxerror at or near "lkjasdf" at character 1LINE 1: lkjasdf;        ^test=> SELECT 1; ?column?----------        1(1
row)test=>COMMIT;COMMIT
 

The question is what to make the default:
disable it by default for all sessions (current patch)enable it by default only for interactive sessions, like
AUTOCOMMITenableit by default for all sessions (breaks too many apps)add a third mode called 'ttyonly' and figure out a
default

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: [PATCHES] Continue transactions after errors in psql

From
"Michael Paesold"
Date:
Bruce Momjian wrote:

> Greg Sabino Mullane wrote:
>>  > The SQL-Standard itself says that errors inside transactions should 
>> only
>> > rollback the last statement, if possible. So why is that not 
>> > implemented in
>> > PostgreSQL? What I read from past discussions here, is because it's 
>> > just
>> > unsave and will lead to data-garbage if you aren't very careful.
>>
>> That's a good point: if that is indeed what the standard says, we should
>> probably see about following it. Rolling back to the last savepoint seems
>> a reasonable behavior to me.
>
> OK, so we need to make a decision.  Right now I have Greg's patch that
> is enabled by "\set ON_ERROR_ROLLBACK on":
>
> test=> \set ON_ERROR_ROLLBACK on
> test=> BEGIN;
> BEGIN
> test=> lkjasdf;
> ERROR:  syntax error at or near "lkjasdf" at character 1
> LINE 1: lkjasdf;
>         ^
> test=> SELECT 1;
> ?column?
> ----------
>         1
> (1 row)
>
> test=> COMMIT;
> COMMIT
>
> The question is what to make the default:
>
> disable it by default for all sessions (current patch)
> enable it by default only for interactive sessions, like AUTOCOMMIT
> enable it by default for all sessions (breaks too many apps)
> add a third mode called 'ttyonly' and figure out a default

My vote:
1) disable it by default for all sessions

2) enable it with \set (can be set in .psqlrc), but provide a way so it only 
works with interactive commands, either always, or something like
\set ON_ERROR_ROLLBACK interactive

Best Regards,
Michael Paesold 



Re: [PATCHES] Continue transactions after errors in psql

From
Christopher Kings-Lynne
Date:
>     disable it by default for all sessions (current patch)

That is the most backwards non-surprising solution.

>     enable it by default only for interactive sessions, like AUTOCOMMIT

And that is what other dbms' do.

Chris


Re: [PATCHES] Continue transactions after errors in psql

From
Bruce Momjian
Date:
Christopher Kings-Lynne wrote:
> >     disable it by default for all sessions (current patch)
> 
> That is the most backwards non-surprising solution.
> 
> >     enable it by default only for interactive sessions, like AUTOCOMMIT
> 
> And that is what other dbms' do.

The current version controls only interactive sessions, and is off by
default.  I am willing to change that, but I need to hear from more
people on this to change it.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: [PATCHES] Continue transactions after errors in psql

From
Christopher Kings-Lynne
Date:
>>>    enable it by default only for interactive sessions, like AUTOCOMMIT
>>
>>And that is what other dbms' do.
> 
> 
> The current version controls only interactive sessions, and is off by
> default.  I am willing to change that, but I need to hear from more
> people on this to change it.

It's good with me.

Chris


Re: [PATCHES] Continue transactions after errors in psql

From
Mark Kirkwood
Date:
Bruce Momjian wrote:
> 
>     disable it by default for all sessions (current patch)
>     enable it by default only for interactive sessions, like AUTOCOMMIT

These two seem like the best contenders.

The choice comes down to whether we should:

1) Be like most other dbms's (e.g. Oracle, Firebird) + follow the spec.
2) Minimize psql behavior changes for current postgresql users.

Personally I favor 1), so would prefer:

enable it by default only for interactive sessions, like AUTOCOMMIT

Mark


Re: [PATCHES] Continue transactions after errors in psql

From
Bruce Momjian
Date:
Mark Kirkwood wrote:
> Bruce Momjian wrote:
> > 
> >     disable it by default for all sessions (current patch)
> >     enable it by default only for interactive sessions, like AUTOCOMMIT
> 
> These two seem like the best contenders.
> 
> The choice comes down to whether we should:
> 
> 1) Be like most other dbms's (e.g. Oracle, Firebird) + follow the spec.
> 2) Minimize psql behavior changes for current postgresql users.
> 
> Personally I favor 1), so would prefer:
> 
> enable it by default only for interactive sessions, like AUTOCOMMIT

We are going for 'off' by default and only interactive.  The fact that
interactive and non-interactive behavior is different is something that
suggests that turning it on by default might cause confusion.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: [PATCHES] Continue transactions after errors in psql

From
Mark Kirkwood
Date:
Bruce Momjian wrote:
> Mark Kirkwood wrote:
> 
>
>>Personally I favor 1), so would prefer:
>>
>>enable it by default only for interactive sessions, like AUTOCOMMIT
> 
> 
> We are going for 'off' by default and only interactive. 

Sweet.

> The fact that
> interactive and non-interactive behavior is different is something that
> suggests that turning it on by default might cause confusion.
> 

True, but reasonably explained by suggesting that non-interactive 
scripts should not be expected to keep going when there are typos or 
similar. In fact, I seem to recall being burned by that very thing in my 
early Oracle days...

Mark


Re: [PATCHES] Continue transactions after errors in psql

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

     

     
To reiterate my opinion, I think the behavior should be the same
for interactive and non-interactive sessions. Not only will it
prevent nasty surprises, but unless we make a third 'setting',
there will be no way to enable this in non-interactive scripts,
which is something that I would want to be able to do.

     
I don't buy the "but what if I set it in .psqlrc and forget" argument.
That could be applied to a lot of things you could put in there. This
setting defaults to "off" and must be explicitly enabled. I'd be okay
with a "smart" mode that explicitly enables the interactive/non-interactive
split.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200504260737
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFCbilxvJuQZxSWSsgRAgf8AJ9/NcsU/5A0V9isGvQy4sjba/aukgCgoFbp
otSb0vVLfnL7mIt99rA4Piw=
=1vVP
-----END PGP SIGNATURE-----



Re: [PATCHES] Continue transactions after errors in psql

From
"Michael Paesold"
Date:
Greg Sabino Mullane wrote:
> To reiterate my opinion, I think the behavior should be the same
> for interactive and non-interactive sessions. Not only will it
> prevent nasty surprises, but unless we make a third 'setting',
> there will be no way to enable this in non-interactive scripts,
> which is something that I would want to be able to do.
>
>  > I don't buy the "but what if I set it in .psqlrc and forget" argument.
> That could be applied to a lot of things you could put in there. This
> setting defaults to "off" and must be explicitly enabled. I'd be okay
> with a "smart" mode that explicitly enables the
> interactive/non-interactive
> split.

But people (like me for example) will want to enable this behaviour by
default. So they (me too) will put the option in .psqlrc. It is then enabled
"by default". But then many of my scripts will destroy data instead of just
erroring out.
I just don't see why non-interactive mode does need such a switch because
there is no way to check if there was an error. So just put two queries
there and hope one will work?

If you really want this for scripts, there must be two options:
* one to put savely into .psqlrc (what some people will want, I have \set
AUTOCOMMIT off in my .psqlrc file, too, and I know I am not the only one)
* another one that will also work in scripts

I hope you understand and accept the issue here.

Best Regards,
Michael Paesold


Re: [PATCHES] Continue transactions after errors in psql

From
Richard Huxton
Date:
Michael Paesold wrote:
>
> But people (like me for example) will want to enable this behaviour by
> default. So they (me too) will put the option in .psqlrc. It is then
> enabled "by default". But then many of my scripts will destroy data
> instead of just erroring out.
> I just don't see why non-interactive mode does need such a switch
> because there is no way to check if there was an error. So just put two
> queries there and hope one will work?

DROP TABLE foo;
CREATE TABLE foo...

--
   Richard Huxton
   Archonet Ltd

Re: [PATCHES] Continue transactions after errors in psql

From
Tom Lane
Date:
"Greg Sabino Mullane" <greg@turnstep.com> writes:
> To reiterate my opinion, I think the behavior should be the same
> for interactive and non-interactive sessions. Not only will it
> prevent nasty surprises, but unless we make a third 'setting',
> there will be no way to enable this in non-interactive scripts,
> which is something that I would want to be able to do.

I'm finding it hard to visualize a non-interactive script making
any good use of such a setting.  Without a way to test whether
you got an error or not, it would amount to an "ignore errors
within transactions" mode, which seems a pretty bad idea.

Can you show a plausible use-case for such a thing?

            regards, tom lane

Re: [PATCHES] Continue transactions after errors in psql

From
"Michael Paesold"
Date:
Richard Huxton wrote:

> Michael Paesold wrote:
>>
>> But people (like me for example) will want to enable this behaviour by
>> default. So they (me too) will put the option in .psqlrc. It is then
>> enabled "by default". But then many of my scripts will destroy data
>> instead of just erroring out.
>> I just don't see why non-interactive mode does need such a switch because
>> there is no way to check if there was an error. So just put two queries
>> there and hope one will work?
>
> DROP TABLE foo;
> CREATE TABLE foo...

This would be:

\set AUTOCOMMIT off
DROP TABLE foo; -- error, rolled back
CREATE TABLE foo ...
COMMIT;

You could as well do:

\set AUTOCOMMIT on -- default
DROP TABLE foo; -- print error message
CREATE TABLE foo ...

There is not much difference, except for locking, ok. I see your point, but
I don't think this makes enabling it by default (even in .psqlrc) any safer.

Best Regards,
Michael Paesold



Re: [PATCHES] Continue transactions after errors in psql

From
Tom Lane
Date:
Richard Huxton <dev@archonet.com> writes:
> Michael Paesold wrote:
>> I just don't see why non-interactive mode does need such a switch
>> because there is no way to check if there was an error. So just put two
>> queries there and hope one will work?

> DROP TABLE foo;
> CREATE TABLE foo...

Unconvincing.  What if the drop fails for permission reasons, rather
than because the table's not there?  Then the CREATE will fail too
... but now the script bulls ahead regardless, with who knows what
bad consequences.

I would far rather see people code explicit markers around statements
whose failure can be ignored.  That is, a script that needs this
behavior ought to look like

    BEGIN;
    \begin_ignore_error
    DROP TABLE foo;
    \end_ignore_error
    CREATE ...
    ...
    COMMIT;

where I'm supposing that we invent psql backslash commands to cue
the sending of SAVEPOINT and RELEASE-or-ROLLBACK commands.  (Anyone
got a better idea for the names than that?)

Once you've got such an infrastructure, it makes sense to allow an
interactive mode that automatically puts such things around each
statement.  But I can't really see the argument for using such a
behavior in a script.  Scripts are too stupid.

            regards, tom lane

Re: [PATCHES] Continue transactions after errors in psql

From
"Joshua D. Drake"
Date:
> I would far rather see people code explicit markers around statements
> whose failure can be ignored.  That is, a script that needs this
> behavior ought to look like
>
>     BEGIN;
>     \begin_ignore_error
>     DROP TABLE foo;
>     \end_ignore_error
>     CREATE ...
>     ...
>     COMMIT;

That seems awful noisy. Why not just:

       BEGIN:
       DROP TABLE foo;
       ERROR: table foo does not exist;
       CONTINUE;
       etc....

Sincerely,

Joshua D. Drake
Command Prompt, Inc.




Re: Continue transactions after errors in psql

From
Harald Fuchs
Date:
In article <426E4AC1.4070706@archonet.com>,
Richard Huxton <dev@archonet.com> writes:

>> I just don't see why non-interactive mode does need such a switch
>> because there is no way to check if there was an error. So just put
>> two queries there and hope one will work?

> DROP TABLE foo;
> CREATE TABLE foo...

Ah, my pet peeve!  "DROP TABLE IF EXISTS name" is the only thing I
really miss from MySQL.



Re: [PATCHES] Continue transactions after errors in

From
Philip Warner
Date:
At 12:28 AM 27/04/2005, Tom Lane wrote:
>Can you show a plausible use-case for such a thing?

A not-uncommon case in other DBs is to handle insert/update code where
insert is the most likely result. Not sure if this is relevant to scripts:

Begin;
...do stuff...
insert into....
<trap duplicate index error and do update instead>
update...
...more stuff...
commit;


Also, the blunder-on-regardless approach is popular in pg_dump, or so I'm
told ;-).


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp.mit.edu:11371       |/


Re: [PATCHES] Continue transactions after errors in psql

From
Tom Lane
Date:
Philip Warner <pjw@rhyme.com.au> writes:
> Also, the blunder-on-regardless approach is popular in pg_dump, or so I'm
> told ;-).

Sure, but pg_dump scripts don't try to execute as a single transaction.
None of this discussion applies to the behavior outside an explicit
transaction block.

            regards, tom lane

Re: [PATCHES] Continue transactions after errors in psql

From
Tom Lane
Date:
"Joshua D. Drake" <jd@commandprompt.com> writes:
>> BEGIN;
>> \begin_ignore_error
>> DROP TABLE foo;
>> \end_ignore_error
>> CREATE ...
>> ...
>> COMMIT;

> That seems awful noisy. Why not just:

>        BEGIN:
>        DROP TABLE foo;
>        ERROR: table foo does not exist;
>        CONTINUE;
>        etc....

Well, ignoring questions of how we choose to spell the commands, the
thing I'd not like about the second alternative is that it doesn't
afford any control over the number of statements rolled back upon
error.

            regards, tom lane

Re: [PATCHES] Continue transactions after errors in psql

From
Andrew Dunstan
Date:

Tom Lane wrote:

>Richard Huxton <dev@archonet.com> writes:
>
>
>>Michael Paesold wrote:
>>
>>
>>>I just don't see why non-interactive mode does need such a switch
>>>because there is no way to check if there was an error. So just put two
>>>queries there and hope one will work?
>>>
>>>
>
>
>
>>DROP TABLE foo;
>>CREATE TABLE foo...
>>
>>
>
>Unconvincing.  What if the drop fails for permission reasons, rather
>than because the table's not there?  Then the CREATE will fail too
>... but now the script bulls ahead regardless, with who knows what
>bad consequences.
>
>I would far rather see people code explicit markers around statements
>whose failure can be ignored.  That is, a script that needs this
>behavior ought to look like
>
>    BEGIN;
>    \begin_ignore_error
>    DROP TABLE foo;
>    \end_ignore_error
>    CREATE ...
>    ...
>    COMMIT;
>
>
>
>

That's a lot of work. In this particular case I would actually like to
see us provide "DROP IF EXISTS ..." or some such.

My instinct on this facility is that distinguishing between interactive
and noninteractive use is likely to be highly confusing. So I would
favor behaviour that is consistent and defaults to off.

cheers

andrew



Re: [PATCHES] Continue transactions after errors in psql

From
Tom Lane
Date:
Andrew Dunstan <andrew@dunslane.net> writes:
> Tom Lane wrote:
>> I would far rather see people code explicit markers around statements
>> whose failure can be ignored.  That is, a script that needs this
>> behavior ought to look like
>>
>> BEGIN;
>> \begin_ignore_error
>> DROP TABLE foo;
>> \end_ignore_error
>> CREATE ...
>> ...
>> COMMIT;

> That's a lot of work.

How so?  It's a minuscule extension to the psql patch already coded:
just provide backslash commands to invoke the bits of code already
written.

> In this particular case I would actually like to
> see us provide "DROP IF EXISTS ..." or some such.

That's substantially more work, with substantially less scope of
applicability: it would only solve the issue for DROP.

            regards, tom lane

Re: [PATCHES] Continue transactions after errors in psql

From
Andrew Dunstan
Date:

Tom Lane wrote:

>Andrew Dunstan <andrew@dunslane.net> writes:
>
>
>>Tom Lane wrote:
>>
>>
>>>I would far rather see people code explicit markers around statements
>>>whose failure can be ignored.  That is, a script that needs this
>>>behavior ought to look like
>>>
>>>BEGIN;
>>>\begin_ignore_error
>>>DROP TABLE foo;
>>>\end_ignore_error
>>>CREATE ...
>>>...
>>>COMMIT;
>>>
>>>
>
>
>
>>That's a lot of work.
>>
>>
>
>How so?  It's a minuscule extension to the psql patch already coded:
>just provide backslash commands to invoke the bits of code already
>written.
>
>

I meant it's a lot to type ;-)

>
>
>>In this particular case I would actually like to
>>see us provide "DROP IF EXISTS ..." or some such.
>>
>>
>
>That's substantially more work, with substantially less scope of
>applicability: it would only solve the issue for DROP.
>
>
>
>

True. I wasn't suggesting it as an alternative in the general case. I
still think it's worth doing, though - I have often seen it requested
and can't think of a compelling reason not to provide it. But maybe
that's off topic ;-)

cheers

andrew

Re: [PATCHES] Continue transactions after errors in psql

From
Tom Lane
Date:
Andrew Dunstan <andrew@dunslane.net> writes:
>>> \begin_ignore_error
>>> DROP TABLE foo;
>>> \end_ignore_error

> I meant it's a lot to type ;-)

Well, that's just a matter of choosing good (ie short) names for the
backslash commands.  I was trying to be clear rather than proposing
names I would actually want to use ;-).  Any suggestions?

            regards, tom lane

Re: [PATCHES] Continue transactions after errors in psql

From
John DeSoi
Date:
On Apr 26, 2005, at 10:35 AM, Tom Lane wrote:

> Once you've got such an infrastructure, it makes sense to allow an
> interactive mode that automatically puts such things around each
> statement.  But I can't really see the argument for using such a
> behavior in a script.  Scripts are too stupid.


Would it be possible to have a command line switch and/or a psql
variable to control "interactive"? If I recall correctly, the setting
depends on tty and there are possible interactive uses of psql outside
of a terminal session. With so many things depending on this, it would
be nice to be able to override the default.

Thanks,

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


Re: [PATCHES] Continue transactions after errors in psql

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> I'm finding it hard to visualize a non-interactive script making
> any good use of such a setting.  Without a way to test whether
> you got an error or not, it would amount to an "ignore errors
> within transactions" mode, which seems a pretty bad idea.
>
> Can you show a plausible use-case for such a thing?

I could have used this yesterday. I was populating a test table with
a primary key on two columns and needed to add a bunch of random rows.
I generated a 10_000 line file of one insert statement each. Rather than
worrying about collisions, I could simply \rollbackonerror (or whatever
we're calling it today :) and silently discard the handful that happen
to violate the primary key constraint and let the rest insert.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200504270754
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFCb33NvJuQZxSWSsgRAvdfAJwMqysSpVI2BDh9wENT2jxMZnspagCfRlHJ
9ElhNydsz2FsCc1JgI5R+gU=
=h9AW
-----END PGP SIGNATURE-----



Re: [PATCHES] Continue transactions after errors in psql

From
Robert Treat
Date:
On Tue, 2005-04-26 at 10:28, Tom Lane wrote:
> "Greg Sabino Mullane" <greg@turnstep.com> writes:
> > To reiterate my opinion, I think the behavior should be the same
> > for interactive and non-interactive sessions. Not only will it
> > prevent nasty surprises, but unless we make a third 'setting',
> > there will be no way to enable this in non-interactive scripts,
> > which is something that I would want to be able to do.
>
> I'm finding it hard to visualize a non-interactive script making
> any good use of such a setting.  Without a way to test whether
> you got an error or not, it would amount to an "ignore errors
> within transactions" mode, which seems a pretty bad idea.
>
> Can you show a plausible use-case for such a thing?
>

I plan to use it in scripts that push site meta-data out to our test
servers, where the list of sites are all different so any static data
dump is bound to fail on some foreign key checks (but I don't care which
ones fail as long as some go over).

I'm sure others can come up with different scenarios, but more
importantly is I don't see a good reason to treat this setting different
from all others and explicitly forbid this use from people, especially
when I can imagine people coming from other dbs where this behavior is
more common who might in fact expect it to work this way.


Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


Re: [PATCHES] Continue transactions after errors in psql

From
Bruce Momjian
Date:
Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
> >>> \begin_ignore_error
> >>> DROP TABLE foo;
> >>> \end_ignore_error
>
> > I meant it's a lot to type ;-)
>
> Well, that's just a matter of choosing good (ie short) names for the
> backslash commands.  I was trying to be clear rather than proposing
> names I would actually want to use ;-).  Any suggestions?

Well, if we allowed ON_ERROR_ROLLBACK to work in non-interactive
sessions we could just do:

    \set ON_ERROR_ROLLBACK on
    DROP TABLE foo;
    \set ON_ERROR_ROLLBACK off

No new syntax required.  Seems this variable is going to need an
'interactive' setting, which means it isn't boolean anymore.

Also, should we allow 'true/false' to work with these seetings?  We do
that with boolean columns in SQL.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: [PATCHES] Continue transactions after errors in psql

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Well, that's just a matter of choosing good (ie short) names for the
>> backslash commands.  I was trying to be clear rather than proposing
>> names I would actually want to use ;-).  Any suggestions?

> Well, if we allowed ON_ERROR_ROLLBACK to work in non-interactive
> sessions we could just do:

>     \set ON_ERROR_ROLLBACK on
>     DROP TABLE foo;
>     \set ON_ERROR_ROLLBACK off

That isn't the same thing at all.  The syntax I was proposing allows the
script writer to define a savepoint covering multiple statements,
whereas the above does not.

Maybe what we really need is a "rollback or release savepoint"
operation, defined as "ROLLBACK TO foo if in error state, RELEASE foo
if not in error state".  This is essentially the thing that a script
writer has to have and can't do for himself due to the lack of any
conditional ability in psql scripts.  We could imagine implementing
that either as a SQL command or as a psql backslash command ... I don't
have a strong feeling either way.

            regards, tom lane

Re: [PATCHES] Continue transactions after errors in psql

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> Well, that's just a matter of choosing good (ie short) names for the
> >> backslash commands.  I was trying to be clear rather than proposing
> >> names I would actually want to use ;-).  Any suggestions?
>
> > Well, if we allowed ON_ERROR_ROLLBACK to work in non-interactive
> > sessions we could just do:
>
> >     \set ON_ERROR_ROLLBACK on
> >     DROP TABLE foo;
> >     \set ON_ERROR_ROLLBACK off
>
> That isn't the same thing at all.  The syntax I was proposing allows the
> script writer to define a savepoint covering multiple statements,
> whereas the above does not.

Well, it fits the use case posted, that is to conditionally roll back a
_single_ failed query.  I don't see the need to add a new
infrastructure/command unless people have a use case for rolling back a
group of statements on failure.  I have no seen such a description yet.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073