Thread: Re: [PATCHES] Continue transactions after errors in psql
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
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
> 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
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
>>> 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
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
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
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
-----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-----
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
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
"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
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
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
> 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.
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.
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 |/
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
"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
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
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
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
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
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
-----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-----
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
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
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
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