Thread: 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 couldsnap 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/ > 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
On Mon, 2007-01-22 at 16:11 +0000, Gregory Stark wrote: > "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. OK, understood. ON_ERROR_ROLLBACK is what we need, for psql only. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
On 1/22/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > 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? * the ability to span the savepoint across multiple statements. * the ability to get what you want without wastefully creating a savepoint before every statement. * losing some behavior which (IMO) is general and beneficial. how do psql tricks help proper stored procedures should we aver get them? That being said, some simple extensions to the psql rollback feature would get the job done I guess. I'm still not happy with it but I knew it was a tough go from the beginning...I appreciate everyone's comments. merlin
On Mon, Jan 22, 2007 at 11:21:12AM -0500, Merlin Moncure wrote: > >\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 Sorry, "rollback conditionally on failure" isn't parsing for me. Can you give some example of what you mean? > 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. I don't understand this either. Everything you can do with nested transactions you can also do with savepoints, so I'm really not understand what the limitations are? Actually, looking at the savepoint documentation, it looks like there is no way to say: if transaction_state ok then release X else rollback to X Which is what a normal COMMIT does (sort of). This is very irritating for scripting, so maybe a "COMMIT TO X" command would be auseful addition? Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
On 1/22/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > 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?
I think the problem is with doing something like this:
BEGIN;
INSERT INTO foo VALUES ('1'); UPDATE status SET updated=now() WHERE tab='foo';
INSERT INTO bar VALUES ('2'); UPDATE status SET updated=now() WHERE tab='bar';
INSERT INTO baz VALUES ('3'); UPDATE status SET updated=now() WHERE tab='baz';
COMMIT;
This will issue three savepoints (if I understand how things wok correctly),
one for each INSERT+UPDATE block. This way eiher both of them succeed
or fail, within one transaction.
Now, I think the problem the OP wanted to solve was that keeping command
on one line just to have them "inside" one savepoint, and depending on psql(1)
to issue rollbacks for us. I think OPs idea was to be able to rollback if error
occured:
BEGIN;
SAVEPOINT s1;
INSERT...
UPDATE...
ROLLBACK TO s1 ON ERROR;
INSERT..
UPDATE...
ROLLBACK TO s2 ON ERROR;
UPDATE job SET ts = now(); -- OK
COMMIT; -- notice lack of rollback -- whole transaction will fail on error
One solution would be a psql command which
would fire given command on error condition, like:
BEGIN;
SAVEPOINT s1;
INSERT...
UPDATE...
\on_error ROLLBACK TO s1; INSERT INTO errors ....
SAVEPOINT s2;
....
COMMIT;
Regards, Dawid
On 1/22/07, Martijn van Oosterhout <kleptog@svana.org> wrote: > I don't understand this either. Everything you can do with nested > transactions you can also do with savepoints, so I'm really not > understand what the limitations are? > > Actually, looking at the savepoint documentation, it looks like there > is no way to say: > > if transaction_state ok then > release X > else > rollback to X exactly. > Which is what a normal COMMIT does (sort of). This is very irritating > for scripting, so maybe a "COMMIT TO X" command would be auseful > addition? right. thats exactly what I want (more or less, there are a couple of different ways to do it, but this is perfectly acceptable). The on errors bit was just a froofy addition that distracted from the core problem. merlin
On 1/22/07, Dawid Kuroczko <qnex42@gmail.com> wrote:
> On 1/22/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > 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?
>
> I think the problem is with doing something like this:
>
> BEGIN;
> INSERT INTO foo VALUES ('1'); UPDATE status SET updated=now() WHERE tab='foo';
> INSERT INTO bar VALUES ('2'); UPDATE status SET updated=now() WHERE tab='bar';
> INSERT INTO baz VALUES ('3'); UPDATE status SET updated=now() WHERE tab='baz';
> COMMIT;
>
> This will issue three savepoints (if I understand how things wok correctly),
yes
> one for each INSERT+UPDATE block. This way eiher both of them succeed
> or fail, within one transaction.
i think so...Martijn said it best: you can 'rollback' to, but you
can't 'commit' to. The 'commit to' would be the arguably much more
useful way of disposing of a savepoint. But that should be taken up
with sql standards committee :(.
> One solution would be a psql command which
> would fire given command on error condition, like:
yes, psql can handle this. while (IMO) a hack, it addresses the
shortcoming (scripting) specifically not handled by savepoints..
merlin
Merlin Moncure wrote: > On 1/22/07, Dawid Kuroczko <qnex42@gmail.com> wrote: > >one for each INSERT+UPDATE block. This way eiher both of them succeed > >or fail, within one transaction. > > i think so...Martijn said it best: you can 'rollback' to, but you > can't 'commit' to. The 'commit to' would be the arguably much more > useful way of disposing of a savepoint. But that should be taken up > with sql standards committee :(. You can RELEASE a savepoint though. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On 1/22/07, Alvaro Herrera <alvherre@commandprompt.com> wrote: > > i think so...Martijn said it best: you can 'rollback' to, but you > > can't 'commit' to. The 'commit to' would be the arguably much more > > useful way of disposing of a savepoint. But that should be taken up > > with sql standards committee :(. > > You can RELEASE a savepoint though. not following an error. RELEASE serves absolutely no purpose whatsoever. it's like the sql equivalent of an assembly NOP...wasts cpu cycles for no reason. merlin