Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Applied.
---
pgman wrote:
pgman wrote:
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.
OK, updated patch that allows for 'on/interactive/off'. Seems there are
enough use cases to add an 'interactive' option.
--
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
Index: doc/src/sgml/ref/psql-ref.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.134
diff -c -c -r1.134 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml14 Mar 2005 06:19:01 - 1.134
--- doc/src/sgml/ref/psql-ref.sgml28 Apr 2005 03:35:00 -
***
*** 2050,2055
--- 2050,2077
/varlistentry
varlistentry
+ indexterm
+primaryrollback/primary
+secondarypsql/secondary
+ /indexterm
+ termvarnameON_ERROR_ROLLBACK/varname/term
+ listitem
+ para
+ When literalon/, if a statement in a transaction block
+ generates an error, the error is ignored and the transaction
+ continues. When literalinteractive/, such errors are only
+ ignored in interactive sessions, and not when reading script
+ files. When literaloff/ (the default), a statement in a
+ transaction block that generates an error aborts the entire
+ transaction. The on_error_rollback-on mode works by issuing an
+ implicit commandSAVEPONT/ for you, just before each command
+ that is in a transaction block, and rolls back to the savepoint
+ on error.
+ /para
+ /listitem
+ /varlistentry
+
+ varlistentry
termvarnameON_ERROR_STOP/varname/term
listitem
para
Index: src/bin/psql/common.c
===
RCS file: /cvsroot/pgsql/src/bin/psql/common.c,v
retrieving revision 1.96
diff -c -c -r1.96 common.c
*** src/bin/psql/common.c 22 Feb 2005 04:40:52 - 1.96
--- src/bin/psql/common.c 28 Apr 2005 03:35:01 -
***
*** 941,951
bool
SendQuery(const char *query)
{
! PGresult *results;
! TimevalStruct before,
! after;
! boolOK;
!
if (!pset.db)
{
psql_error(You are currently not connected to a database.\n);
--- 941,953
bool
SendQuery(const char *query)
{
! PGresult*results;
! TimevalStruct before, after;
! bool OK, on_error_rollback_savepoint = false;
! PGTransactionStatusType transaction_status;
! static bool on_error_rollback_warning = false;
! const char *rollback_str;
!
if (!pset.db)
{
psql_error(You are currently not connected to a database.\n);
***
*** 973,979
SetCancelConn();
! if (PQtransactionStatus(pset.db) == PQTRANS_IDLE
!GetVariableBool(pset.vars, AUTOCOMMIT)
!command_no_begin(query))
{
--- 975,983
SetCancelConn();
! transaction_status = PQtransactionStatus(pset.db);
!
! if (transaction_status == PQTRANS_IDLE
!GetVariableBool(pset.vars, AUTOCOMMIT)
!command_no_begin(query))
{
***
*** 987,992
--- 991,1023
}
PQclear(results);
}
+ else if (transaction_status == PQTRANS_INTRANS
+ (rollback_str = GetVariable(pset.vars,
ON_ERROR_ROLLBACK)) != NULL
+ /* !off and !interactive is 'on' */
+ pg_strcasecmp(rollback_str, off) != 0
+
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
-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 [EMAIL PROTECTED] PGP Key: 0x14964AC8 200504270754 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCb33NvJuQZxSWSsgRAvdfAJwMqysSpVI2BDh9wENT2jxMZnspagCfRlHJ 9ElhNydsz2FsCc1JgI5R+gU= =h9AW -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
On Tue, 2005-04-26 at 10:28, Tom Lane wrote:
Greg Sabino Mullane [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
pgman wrote:
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.
OK, updated patch that allows for 'on/interactive/off'. Seems there are
enough use cases to add an 'interactive' option.
--
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
Index: doc/src/sgml/ref/psql-ref.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.134
diff -c -c -r1.134 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml 14 Mar 2005 06:19:01 - 1.134
--- doc/src/sgml/ref/psql-ref.sgml 28 Apr 2005 03:35:00 -
***
*** 2050,2055
--- 2050,2077
/varlistentry
varlistentry
+ indexterm
+primaryrollback/primary
+secondarypsql/secondary
+ /indexterm
+ termvarnameON_ERROR_ROLLBACK/varname/term
+ listitem
+ para
+ When literalon/, if a statement in a transaction block
+ generates an error, the error is ignored and the transaction
+ continues. When literalinteractive/, such errors are only
+ ignored in interactive sessions, and not when reading script
+ files. When literaloff/ (the default), a statement in a
+ transaction block that generates an error aborts the entire
+ transaction. The on_error_rollback-on mode works by issuing an
+ implicit commandSAVEPONT/ for you, just before each command
+ that is in a transaction block, and rolls back to the savepoint
+ on error.
+ /para
+ /listitem
+ /varlistentry
+
+ varlistentry
termvarnameON_ERROR_STOP/varname/term
listitem
para
Index: src/bin/psql/common.c
===
RCS file: /cvsroot/pgsql/src/bin/psql/common.c,v
retrieving revision 1.96
diff -c -c -r1.96 common.c
*** src/bin/psql/common.c 22 Feb 2005 04:40:52 - 1.96
--- src/bin/psql/common.c 28 Apr 2005 03:35:01 -
***
*** 941,951
bool
SendQuery(const char *query)
{
! PGresult *results;
! TimevalStruct before,
! after;
! boolOK;
!
if (!pset.db)
{
psql_error(You are currently not connected to a database.\n);
--- 941,953
bool
SendQuery(const char *query)
{
! PGresult*results;
! TimevalStruct before, after;
! bool OK, on_error_rollback_savepoint = false;
! PGTransactionStatusType transaction_status;
! static bool on_error_rollback_warning = false;
! const char *rollback_str;
!
if (!pset.db)
{
psql_error(You are currently not connected to a database.\n);
***
*** 973,979
SetCancelConn();
! if (PQtransactionStatus(pset.db) == PQTRANS_IDLE
!GetVariableBool(pset.vars, AUTOCOMMIT)
!command_no_begin(query))
{
--- 975,983
SetCancelConn();
! transaction_status = PQtransactionStatus(pset.db);
!
! if (transaction_status == PQTRANS_IDLE
!GetVariableBool(pset.vars, AUTOCOMMIT)
!command_no_begin(query))
{
***
*** 987,992
--- 991,1023
}
PQclear(results);
}
+ else if (transaction_status == PQTRANS_INTRANS
+(rollback_str = GetVariable(pset.vars,
ON_ERROR_ROLLBACK)) != NULL
+/* !off and !interactive is 'on' */
+pg_strcasecmp(rollback_str, off) != 0
+(pset.cur_cmd_interactive ||
+ pg_strcasecmp(rollback_str, interactive) != 0))
+ {
+ if (on_error_rollback_warning == false pset.sversion 8)
+
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
-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 [EMAIL PROTECTED] PGP Key: 0x14964AC8 200504260737 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCbilxvJuQZxSWSsgRAgf8AJ9/NcsU/5A0V9isGvQy4sjba/aukgCgoFbp otSb0vVLfnL7mIt99rA4Piw= =1vVP -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Greg Sabino Mullane [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Philip Warner [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Andrew Dunstan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Andrew Dunstan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
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.
The question is what to make the default:
o disable it by default for all sessions (current patch)
o enable it by default only for interactive sessions, like AUTOCOMMIT
o enable it by default for all sessions (breaks too many apps)
o add a third mode called 'ttyonly' and figure out a default
Based on the comments I received, and the mention that ignoring errors
is part of the SQL standard, I chose the second option, patch attached:
$ psql test
Welcome to psql 8.1devel, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
test= BEGIN;
BEGIN
test= asdf;
ERROR: syntax error at or near asdf at character 1
LINE 1: asdf;
^
test= SELECT 1;
?column?
--
1
(1 row)
test= COMMIT;
COMMIT
Can someone confirm that this is the way Oracle works as well? I
checked on IRC and isql does it. I am uncertain how applications
behave.
--
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
Index: doc/src/sgml/ref/psql-ref.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.134
diff -c -c -r1.134 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml 14 Mar 2005 06:19:01 - 1.134
--- doc/src/sgml/ref/psql-ref.sgml 25 Apr 2005 20:01:05 -
***
*** 2050,2055
--- 2050,2075
/varlistentry
varlistentry
+ indexterm
+primaryrollback/primary
+secondarypsql/secondary
+ /indexterm
+ termvarnameON_ERROR_ROLLBACK/varname/term
+ listitem
+ para
+ When literalon/ (the default), in interactive mode,
+ ignore errors generated by commands in a transaction block,
+ rather than aborting the transaction. Ignoring errors never
+ happens in non-interactive mode or if the value is
+ literaloff/. The on_error_rollback-on mode works by issuing
+ an implicit commandSAVEPONT/ for you, just before each
+ command that is in a transaction block, and rolls back to the
+ savepoint on error.
+ /para
+ /listitem
+ /varlistentry
+
+ varlistentry
termvarnameON_ERROR_STOP/varname/term
listitem
para
Index: src/bin/psql/common.c
===
RCS file: /cvsroot/pgsql/src/bin/psql/common.c,v
retrieving revision 1.96
diff -c -c -r1.96 common.c
*** src/bin/psql/common.c 22 Feb 2005 04:40:52 - 1.96
--- src/bin/psql/common.c 25 Apr 2005 20:01:08 -
***
*** 941,951
bool
SendQuery(const char *query)
{
! PGresult *results;
! TimevalStruct before,
! after;
! boolOK;
!
if (!pset.db)
{
psql_error(You are currently not connected to a database.\n);
--- 941,952
bool
SendQuery(const char *query)
{
! PGresult*results;
! TimevalStruct before, after;
! bool OK, on_error_rollback_savepoint = false;
! PGTransactionStatusType transaction_status;
! static bool on_error_rollback_warning = false;
!
if (!pset.db)
{
psql_error(You are currently not connected to a database.\n);
***
*** 973,979
SetCancelConn();
! if (PQtransactionStatus(pset.db) == PQTRANS_IDLE
!GetVariableBool(pset.vars, AUTOCOMMIT)
!command_no_begin(query))
{
--- 974,982
SetCancelConn();
! transaction_status = PQtransactionStatus(pset.db);
!
! if (transaction_status == PQTRANS_IDLE
!GetVariableBool(pset.vars, AUTOCOMMIT)
!command_no_begin(query))
{
***
*** 987,992
--- 990,1019
}
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Tom Lane wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
I think everyone agrees this should only work in interactive mode. I
think the only unknown is if it should be 'on' by default in interactive
mode? Does it make sense to follow the standard in interactive mode if
we don't follow it in non-interative mode?
I doubt it's a good idea to change the default for this at all; in
particular, making the default interactive behavior different from
the noninteractive behavior seems like a recipe for problems.
Agreed. New patch attached. I will apply tomorrow.
--
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
Index: doc/src/sgml/ref/psql-ref.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.134
diff -c -c -r1.134 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml 14 Mar 2005 06:19:01 - 1.134
--- doc/src/sgml/ref/psql-ref.sgml 26 Apr 2005 00:35:48 -
***
*** 2050,2055
--- 2050,2075
/varlistentry
varlistentry
+ indexterm
+primaryrollback/primary
+secondarypsql/secondary
+ /indexterm
+ termvarnameON_ERROR_ROLLBACK/varname/term
+ listitem
+ para
+ When literalon/, only in interactive mode, if a statement in
+ a transaction block generates an error, the error is ignored and
+ the transaction continues. When literaloff/ (the default), a
+ statement in a transaction block that generates an error aborts
+ the entire transaction. The on_error_rollback-on mode works by
+ issuing an implicit commandSAVEPONT/ for you, just before
+ each command that is in a transaction block, and rolls back to
+ the savepoint on error.
+ /para
+ /listitem
+ /varlistentry
+
+ varlistentry
termvarnameON_ERROR_STOP/varname/term
listitem
para
Index: src/bin/psql/common.c
===
RCS file: /cvsroot/pgsql/src/bin/psql/common.c,v
retrieving revision 1.96
diff -c -c -r1.96 common.c
*** src/bin/psql/common.c 22 Feb 2005 04:40:52 - 1.96
--- src/bin/psql/common.c 26 Apr 2005 00:35:50 -
***
*** 941,951
bool
SendQuery(const char *query)
{
! PGresult *results;
! TimevalStruct before,
! after;
! boolOK;
!
if (!pset.db)
{
psql_error(You are currently not connected to a database.\n);
--- 941,952
bool
SendQuery(const char *query)
{
! PGresult*results;
! TimevalStruct before, after;
! bool OK, on_error_rollback_savepoint = false;
! PGTransactionStatusType transaction_status;
! static bool on_error_rollback_warning = false;
!
if (!pset.db)
{
psql_error(You are currently not connected to a database.\n);
***
*** 973,979
SetCancelConn();
! if (PQtransactionStatus(pset.db) == PQTRANS_IDLE
!GetVariableBool(pset.vars, AUTOCOMMIT)
!command_no_begin(query))
{
--- 974,982
SetCancelConn();
! transaction_status = PQtransactionStatus(pset.db);
!
! if (transaction_status == PQTRANS_IDLE
!GetVariableBool(pset.vars, AUTOCOMMIT)
!command_no_begin(query))
{
***
*** 987,992
--- 990,1019
}
PQclear(results);
}
+ else if (transaction_status == PQTRANS_INTRANS
+pset.cur_cmd_interactive
+GetVariableBool(pset.vars, ON_ERROR_ROLLBACK))
+ {
+ if (on_error_rollback_warning == false pset.sversion 8)
+ {
+ fprintf(stderr, _(The server version (%d) does not
support savepoints for ON_ERROR_ROLLBACK.\n),
+ pset.sversion);
+ on_error_rollback_warning = true;
+ }
+ else
+ {
+ results = PQexec(pset.db, SAVEPOINT
pg_psql_temporary_savepoint);
+ if (PQresultStatus(results) != PGRES_COMMAND_OK)
+ {
+ psql_error(%s, PQerrorMessage(pset.db));
+ PQclear(results);
+ ResetCancelConn();
+ return false;
+ }
+ PQclear(results);
+ on_error_rollback_savepoint = true;
+
