savepoint improvements
I've never really been very happy with the decision early on in the
development of nested transactions to use savepoints in the way they
were implemented in the command structure. Savepoints are nearly
useless for sql scripting because there is no way to probe a
transaction and handle error conditions appropriately without dipping
into a function -- which puts severe limits how savepoints might be
utilized. I suspect the savepoint command is almost never used
outside of oracle compatibility efforts. [I'm not taking away from NT
here, begin...exception..end is incredibly useful and I'm sure widely
used]
The missing piece of the puzzle is the ability to recover a failed
transaction without issuing a full commit/rollback. This could be a
new flavor of the savepoint command, commit command, or a new command.
As a bonus, upon recovering the transaction you could snap an sql
statement...this would be great for scripting:
BEGIN;
SAVEPOINT X;
COMMIT ON ERRORS SELECT FOO();
--or--
BEGIN;
SAVEPOINT x;
SAVEPOINT y ON ERRORS SELECT FOO; -- (or ROLLBACK TO SAVEPOINT x);
COMMIT;
comments? fast track to todo list? :-)
merlin
"Merlin Moncure" <mmoncure@gmail.com> writes:
I suspect the savepoint command is almost never used
outside of oracle compatibility efforts.
Last I heard, we implemented it because it is in the SQL standard.
I have no idea (nor do I much care) whether it's oracle-compatible.
BEGIN;
SAVEPOINT X;
COMMIT ON ERRORS SELECT FOO();
--or--
BEGIN;
SAVEPOINT x;
SAVEPOINT y ON ERRORS SELECT FOO; -- (or ROLLBACK TO SAVEPOINT x);
COMMIT;
comments? fast track to todo list? :-)
Not exactly. You haven't even made clear what you think that means,
let alone how it would be implemented. What context is foo() supposed
to be executed in? What happens if it fails?
regards, tom lane
Merlin Moncure skrev:
The missing piece of the puzzle is the ability to recover a failed
transaction without issuing a full commit/rollback. This could be a
new flavor of the savepoint command, commit command, or a new command.
As a bonus, upon recovering the transaction you could snap an sql
statement...this would be great for scripting:BEGIN;
SAVEPOINT X;
COMMIT ON ERRORS SELECT FOO();--or--
BEGIN;
SAVEPOINT x;
SAVEPOINT y ON ERRORS SELECT FOO; -- (or ROLLBACK TO SAVEPOINT x);
COMMIT;comments? fast track to todo list? :-)
Isn't the problem that you try to use psql for scripting and it doesn't
have usual scripting power like branching (if) or looping (while,for)
that most scripting languages have. If there was say an \if command in
psql you could do things like this:
BEGIN;
INSERT INTO foo VALUES (42);
SAVEPOINT X;
INSERT INTO foo VALUES (NULL);
\if errorcode > 0
ROLLBACK TO SAVEPOINT X;
INSERT INTO foo VALUES (666);
\endif
COMMIT;
I'm not sure you want to extend psql to be a full scripting engine, but
maybe. It would be useful to me if it had an \if command like above. An
other alternative is to use some other language to write scripts in that
already have branching, looping, expression evaluation and what else.
/Dennis
On 1/19/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Merlin Moncure" <mmoncure@gmail.com> writes:
I suspect the savepoint command is almost never used
outside of oracle compatibility efforts.Last I heard, we implemented it because it is in the SQL standard.
I have no idea (nor do I much care) whether it's oracle-compatible.
Not exactly. You haven't even made clear what you think that means,
let alone how it would be implemented. What context is foo() supposed
to be executed in? What happens if it fails?
right. I understand this is a nonstandard extension so the bar is
pretty high here...well, my thought was that the subtransaction could
be rolled back and foo executed in the parent transaction. In the
very early implementation of NT you could push and pop transactions
from a stack via multiple begin/end. so, in those terms the
equivalent would be:
BEGIN;
BEGIN; -- savepoint x
COMMIT;
FOO(); -- called if x fails only
COMMIT;
if foo() fails, the whole transaction is failed because that pops the
outer transaction and with savepoints you can only be one level deep.
On 1/20/07, Dennis Bjorklund <db@zigo.dhs.org> wrote:
Isn't the problem that you try to use psql for scripting and it doesn't
have usual scripting power like branching (if) or looping (while,for)
that most scripting languages have. If there was say an \if command in
psql you could do things like this:
To be honest, I'm not a huge fan of psql tricks (error recovery being
another example) but this could provide a solution. in your opnion,
how would you use \if to query the transaction state?
merlin
On Fri, 2007-01-19 at 15:12 -0500, Merlin Moncure wrote:
The missing piece of the puzzle is the ability to recover a failed
transaction without issuing a full commit/rollback.
Agreed.
AFAIK all other RDBMS interpret the SQL Standard to mean that a
statement can fail with an ERROR, then further statements can then be
issued and yet still successfully commit. With PostgreSQL, a commit
cannot be successful following an ERROR.
My understanding is that subtransactions were implemented as a way of
implementing the above, if so desired, but it isn't realistic to
automatically wrap every statement in a subtransaction, just in case.
That can mean some pretty strange re-coding to get around that problem,
when it occurs. Most people don't write their programs to rely on that
behaviour, thankfully, but some do. Whether we care about compatibility
with other RDBMS or not, users do frequently need their software to
support multiple RDBMS.
I'd like to see a TODO item to allow an *option* to be set to choose
between these two transactional behaviours.
- abort on error
- continue on error i.e. COMMIT can/might succeed - though there are
still cases where it cannot, such as a serializable exception.
That's a major change I agree, but the first step to its implementation
is to agree that it might be desirable to allow it.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
On 1/21/07, Simon Riggs <simon@2ndquadrant.com> wrote:
On Fri, 2007-01-19 at 15:12 -0500, Merlin Moncure wrote:
The missing piece of the puzzle is the ability to recover a failed
transaction without issuing a full commit/rollback.Agreed.
I'd like to see a TODO item to allow an *option* to be set to choose
between these two transactional behaviours.
[...]
- continue on error i.e. COMMIT can/might succeed - though there are
still cases where it cannot, such as a serializable exception.
and what should be the behaviour of that? the same as rollback?
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
"Jaime Casanova" <systemguards@gmail.com> writes:
On 1/21/07, Simon Riggs <simon@2ndquadrant.com> wrote:
- continue on error i.e. COMMIT can/might succeed - though there are
still cases where it cannot, such as a serializable exception.
and what should be the behaviour of that? the same as rollback?
The only conceivable implementation is an implicit savepoint issued
before each statement. By and large that seems to me to be most easily
handled on the client side, and many of our client libraries already
have the ability to do it. (For instance, psql has ON_ERROR_ROLLBACK.)
If we tried to do it on the server side, we would break any client
software that wasn't prepared for the change of behavior --- see the 7.3
autocommit fiasco for an example.
So as far as the server is concerned, I see no TODO here.
regards, tom lane
I'd like to see a TODO item to allow an *option* to be set to choose
between these two transactional behaviours.
- abort on error
- continue on error i.e. COMMIT can/might succeed - though there are
still cases where it cannot, such as a serializable exception.That's a major change I agree, but the first step to its implementation
is to agree that it might be desirable to allow it.
At a minimum we need to stop forcing a rollback just because we have a
syntax error. It makes development a complete pain in the butt and is
one of the most, "WTF" looks I get when I am training.
postgres=# begin;
BEGIN
postgres=# create table foo (bar ints);
ERROR: type "ints" does not exist
postgres=# create table foo (bar int);
ERROR: current transaction is aborted, commands ignored until end of
transaction block
postgres=#
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
"Joshua D. Drake" <jd@commandprompt.com> writes:
At a minimum we need to stop forcing a rollback just because we have a
syntax error. It makes development a complete pain in the butt and is
one of the most, "WTF" looks I get when I am training.
postgres=# begin;
BEGIN
postgres=# create table foo (bar ints);
ERROR: type "ints" does not exist
postgres=# create table foo (bar int);
ERROR: current transaction is aborted, commands ignored until end of
transaction block
postgres=#
ON_ERROR_ROLLBACK is what you are looking for.
regression=# \set ON_ERROR_ROLLBACK on
regression=# begin;
BEGIN
regression=# create table foo (bar ints);
ERROR: type "ints" does not exist
LINE 1: create table foo (bar ints);
^
regression=# create table foo (bar int);
CREATE TABLE
regression=# commit;
COMMIT
regression=#
regards, tom lane
On Sun, 2007-01-21 at 13:28 -0500, Tom Lane wrote:
"Jaime Casanova" <systemguards@gmail.com> writes:
On 1/21/07, Simon Riggs <simon@2ndquadrant.com> wrote:
- continue on error i.e. COMMIT can/might succeed - though there are
still cases where it cannot, such as a serializable exception.and what should be the behaviour of that? the same as rollback?
No. The behaviour is to continue the transaction even though an error
has occurred, i.e.
BEGIN;
1. INSERT...
success
2. INSERT .... VALUES () () ()
--fails with error on 3rd VALUES statement
dynamically re-construct INSERT statement with remaining 2 VALUES
statements
3. INSERT VALUES () ();
success
COMMIT;
work done by 1 and 3 is committed
Behaviour needs to support any error at (2) except serializable
exceptions.
The only conceivable implementation is an implicit savepoint issued
before each statement.
Perhaps the only acceptable one.
By and large that seems to me to be most easily
handled on the client side, and many of our client libraries already
have the ability to do it.
PL/pgSQL supports EXCEPTIONs, but no other clients support it, AFAICS.
(For instance, psql has ON_ERROR_ROLLBACK.)
Thats not the same thing, regrettably.
If we tried to do it on the server side, we would break any client
software that wasn't prepared for the change of behavior --- see the 7.3
autocommit fiasco for an example.
Only if we changed the default behaviour, which I am not suggesting.
So as far as the server is concerned, I see no TODO here.
If the server team won't allow it, we must document that this behaviour
must be a client-side function in the *server* TODO, so that all the
various client projects can read the same TODO item and implement it.
"Implement continue-on-error transactional behaviour for each client
library".
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
On Sat, 2007-01-20 at 18:08, Merlin Moncure wrote:
[snip]
To be honest, I'm not a huge fan of psql tricks (error recovery being
another example) but this could provide a solution. in your opnion,
how would you use \if to query the transaction state?
Wouldn't it make sense to introduce instead something like:
\set language plpgsql
... and then redirect to plpgsql all you type ?
That would give you the possibility to execute things in your favorite
language directly from psql without creating a function.
Cheers,
Csaba.
On 1/21/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Jaime Casanova" <systemguards@gmail.com> writes:
On 1/21/07, Simon Riggs <simon@2ndquadrant.com> wrote:
- continue on error i.e. COMMIT can/might succeed - though there are
still cases where it cannot, such as a serializable exception.and what should be the behaviour of that? the same as rollback?
The only conceivable implementation is an implicit savepoint issued
before each statement.
I'm not sure I agree here...before the NT implementation was changed
over to savepoint syntax it was perfectly possible to recover from
errors inside a transaction...and is still possible in plpgsql
functions only. What I'm asking for is to reopen this behavior
somehow...in the production environments I've worked in application
update and maintenance relied heavily on scripting, and lack of this
functionality forces me to wrap the script launch with C code to work
around limitations of the savepoint system.
In pure SQL, we have a 'begin' statement equivalent but no 'end'
statement. Why not?
merlin
On 1/22/07, Csaba Nagy <nagy@ecircle-ag.com> wrote:
On Sat, 2007-01-20 at 18:08, Merlin Moncure wrote:
[snip]To be honest, I'm not a huge fan of psql tricks (error recovery being
another example) but this could provide a solution. in your opnion,
how would you use \if to query the transaction state?Wouldn't it make sense to introduce instead something like:
\set language plpgsql
... and then redirect to plpgsql all you type ?That would give you the possibility to execute things in your favorite
language directly from psql without creating a function.
The nature of pl/pgsql would make this impossible, or at least highly
complex and difficult...one reason is that the language has a much
more complex internal state than sql. Most other languages that I
think this would be worthwhile already their own immediate execution
interpreters.
merlin
On Mon, 2007-01-22 at 09:25 -0500, Merlin Moncure wrote:
On 1/21/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Jaime Casanova" <systemguards@gmail.com> writes:
On 1/21/07, Simon Riggs <simon@2ndquadrant.com> wrote:
- continue on error i.e. COMMIT can/might succeed - though there are
still cases where it cannot, such as a serializable exception.and what should be the behaviour of that? the same as rollback?
The only conceivable implementation is an implicit savepoint issued
before each statement.I'm not sure I agree here...before the NT implementation was changed
over to savepoint syntax it was perfectly possible to recover from
errors inside a transaction...and is still possible in plpgsql
functions only. What I'm asking for is to reopen this behavior
somehow...in the production environments I've worked in application
update and maintenance relied heavily on scripting, and lack of this
functionality forces me to wrap the script launch with C code to work
around limitations of the savepoint system.
Could you post an example, just so we're all clear what the problems
are? I thought I understood what you are requesting; I may not.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
On 1/22/07, Simon Riggs <simon@2ndquadrant.com> wrote:
Could you post an example, just so we're all clear what the problems
are? I thought I understood what you are requesting; I may not.
ok,
The short version is I would like the ability to run some sql commands
and recover the transaction if an error occurs.
We have the ability to do this with savepoint...rollback to
savepoint...but these are not useful without introducing an external
language (c,perl) that can catch the errors and do a rollback to a
savepoint conditionally on the sql error state.
How would this be useful?
Well when I update production systems I often do this from a master
script that loads smaller scripts from another place:
-- update_production.sql
begin;
\i update_foo.sql
\i update_bar.sql
commit;
any error updating foo or bar will blow up the whole thing. Maybe
this is desirable, but it is often nice to be able to do some error
handling here. In the pre-savepoint NT implementation I could:
-- update_production.sql
begin;
begin;
insert into log values ('foo');
\i update_foo.sql
commit;
begin;
insert into log values ('bar');
\i update_bar.sql
commit;
commit;
In between the inner transactions I could check 'log' to see if
everything went through and take appropriate action. Now client
applications have the luxury of being able to check the return code of
the query execution call, but SQL only scripts can't.
This would be perfectly acceptable:
-- update_production.sql
begin;
savepoint foo;
\i update_foo.sql
rollback to savepoint foo [if I failed only];
savepoint bar;
\i update_bar.sql
rollback to savepoint foo [if I failed only];
commit;
This would be just great for scripts but would also help client side
programming a bit by introducing more flexible error handling
behaviors without having to handle things via the returned sql error
code. The on errors bit I was talking about earlier is just syntax
sugar but the critical part is being able to recover transactions
partially without external handler...
merlin
"Merlin Moncure" <mmoncure@gmail.com> writes:
On 1/22/07, Simon Riggs <simon@2ndquadrant.com> wrote:
Could you post an example, just so we're all clear what the problems
are? I thought I understood what you are requesting; I may not.
ok,
The short version is I would like the ability to run some sql commands
and recover the transaction if an error occurs.
I'm getting tired of repeating this, but: neither of you have said
anything that doesn't appear to me to be handled by ON_ERROR_ROLLBACK.
What exactly is lacking in that feature?
regards, tom lane
On Mon, Jan 22, 2007 at 10:40:37AM -0500, Merlin Moncure wrote:
The short version is I would like the ability to run some sql commands
<snip>
any error updating foo or bar will blow up the whole thing. Maybe
this is desirable, but it is often nice to be able to do some error
handling here. In the pre-savepoint NT implementation I could:
<snip>
Nested transactions are trivially implemented on top of savepoints. If
we're talking about psql, maybe all we need to do is create the
commands in psql:
\begin_nest
\commit_nest
\rollback_nest
Would that suit your purpose?
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
On Mon, 2007-01-22 at 10:46 -0500, Tom Lane wrote:
"Merlin Moncure" <mmoncure@gmail.com> writes:
On 1/22/07, Simon Riggs <simon@2ndquadrant.com> wrote:
Could you post an example, just so we're all clear what the problems
are? I thought I understood what you are requesting; I may not.ok,
The short version is I would like the ability to run some sql commands
and recover the transaction if an error occurs.I'm getting tired of repeating this, but: neither of you have said
anything that doesn't appear to me to be handled by ON_ERROR_ROLLBACK.
What exactly is lacking in that feature?
Sorry for not replying to your other post.
ON_ERROR_ROLLBACK doesn't do the same thing, thats why. It shuts out the
noise messages, true, but it doesn't re-execute all of the commands in
the transaction that succeeded and so breaks the transaction, as
originally coded.
BEGIN;
stmt1;
stmt2; <-- error
stmt3;
COMMIT;
results in stmt3 completing successfully even though stmt1 and stmt2 do
not == broken script.
The behaviour we've been discussing is when stmt2 fails, to allow stmt3
to be submitted, so that at commit, stmt1 and stmt3 effects will be
successful *if* the user wishes this.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
"Simon Riggs" <simon@2ndquadrant.com> writes:
BEGIN;
stmt1;
stmt2; <-- error
stmt3;
COMMIT;results in stmt3 completing successfully even though stmt1 and stmt2 do
not == broken script.
stmt1 would still be completed successfully.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
On 1/22/07, Martijn van Oosterhout <kleptog@svana.org> wrote:
we're talking about psql, maybe all we need to do is create the
commands in psql:\begin_nest
\commit_nest
\rollback_nest
That would work if we could rollback conditionally on failure (like
on_error_rollback but with definable beginning and ending points). I
still think we are hacking around limitations of savepoints but it
would solve the scripting problem at least. A general implementation
on the server would benefit everybody.
merlin
