-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Attached is a patch that takes advantage of savepoints to enable
transactions to continue even after errors in psql. The name of it
is \reseterror, and it is off by default. It's backwards compatible,
and allows things like this to work on 8.0 and up servers:

\reseterror
BEGIN;
DELETE FROM foobar;
INSERT INTO foobar(a) VALUES(1);
ISNER INTO foobar(a) VALUES(2);
INSERT INTO foobar(a) VALUES(3);
COMMIT;

Doing a SELECT(a) FROM foobar will show two values, 1 and 3. This
is a great help for those of us that tend to type typos into our
psql session, and end up cursing as we have to restart our current
transaction.

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

iD8DBQFB9wlpvJuQZxSWSsgRAsAzAKCxQ/JtR6/RXgV39uDTm9FIxCIp8QCeKC6T
2l10ef5DHkmFC2dSMQLNHjg=
=HKv9
-----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

On Tuesday 25 January 2005 22:07, Greg Sabino Mullane wrote:
> Attached is a patch that takes advantage of savepoints to enable
> transactions to continue even after errors in psql. The name of it
> is \reseterror, and it is off by default. It's backwards compatible,
> and allows things like this to work on 8.0 and up servers:
>
> \reseterror
> BEGIN;
> DELETE FROM foobar;
> INSERT INTO foobar(a) VALUES(1);
> ISNER INTO foobar(a) VALUES(2);
> INSERT INTO foobar(a) VALUES(3);
> COMMIT;
>
> Doing a SELECT(a) FROM foobar will show two values, 1 and 3. This
> is a great help for those of us that tend to type typos into our
> psql session, and end up cursing as we have to restart our current
> transaction.

I've been testing this patch and found the following bug:
test=# \reseterror
Reset error is on.
test=# begin;
BEGIN
test=# select * from t;
c
---
1
(1 row)
test=# delete from t;
DELETE 1
test=# select * from tt;
ERROR: relation "tt" does not exist
ERROR: relation "tt" does not exist
test=# select * from t;
c
---
(0 rows)
test=# commit;
COMMIT
ERROR: RELEASE SAVEPOINT may only be used in transaction blocks
ERROR: RELEASE SAVEPOINT may only be used in transaction blocks


I've attached a revised patch which fixes the problem, however I'm sure there
is a better way. Thanks to Neil for putting up with me on irc :-)

--
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

Robert Treat wrote:

> I've attached a revised patch which fixes the problem, however I'm sure
> there
> is a better way. Thanks to Neil for putting up with me on irc :-)

In September 2004 I had already sent a patch to implement this behaviour,
the patch, still in the archives, is here:
http://archives.postgresql.org/pgsql...9/bin00040.bin
(savepoints.patch)

There are some issues it addressed:

Assuming you put this option in your .psqlrc file, you will still probably
not want this to be active when you execute commands from a file
(non-interactive). So pset.notty must be checked.
Again, when using \i, resetting errors seems dangerous. Using \i should also
temporarily disable those savepoints.

The real problem with my patch was, that it did not release the savepoints.
Why? Look at this example (with the current patch reseterrors patch):

template1=# \reseterror
Reset error is on.
template1=# BEGIN;
BEGIN
template1=# SAVEPOINT a;
SAVEPOINT
template1=# CREATE TABLE TEST ( a integer);
CREATE TABLE
template1=# ROLLBACK TO a;
ERROR: no such savepoint

So to get this right, you have to track savepoints created by the user and
only release psql savepoints when there is no user savepoint "sitting on top
of" your savepoint.

Two ways come to my mind:
1) Parse SQL for savepoint and rollback to and create a stack of all
savepoints. Then you can always release all savepoints as long as they are
your own.
2) Implement a server-side function to get the savepoints from the server
and query that before every release.

What do you think?

Best Regards,
Michael Paesold


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

> I've attached a revised patch which fixes the problem, however I'm sure there
> is a better way. Thanks to Neil for putting up with me on irc :-)

How about calling the savepoint pg_psql_savepoint instead, that way it
follows our 'don't begin things with pg_' philosophy.

Chris

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Fri, 2005-01-28 at 04:46, Christopher Kings-Lynne wrote:
> > I've attached a revised patch which fixes the problem, however I'm sure there
> > is a better way. Thanks to Neil for putting up with me on irc :-)
>
> How about calling the savepoint pg_psql_savepoint instead, that way it
> follows our 'don't begin things with pg_' philosophy.
>

I was actually thinking of calling it something like
pg_<xact-start-time> thinking that would be pretty unique within a
transaction, though having a specific documented name seemed ok too.

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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Michael Paesold wrote:
> 2) Implement a server-side function to get the savepoints from the server
> and query that before every release.

I could not find a way to do this. Is there any interface to the list?
</aside>

I looked over the patch from Michael Paesold, and talked extensively with
Robert Treat about this, and here is the solution Robert and I came up with:
(thanks to both for their work)

First, I'm not of the opinion that it should automatically be turned off
when running non-interactively. That's too much assuming of what the user
wants, when this is a settable flag. However, it should be settable via
a script to a definite state. So \reseterror will take an optional argument,
"off" or "on", which sets it rather than toggles it.

The patch Robert provided shold catch the problem of "good command-commit".
The other problem is not stepping on other people's savepoints. The best
solution we came up with was to check for savepoint commands ourselves,
similar to the way psql already checks for transaction affecting commands,
and handle things appropriately. Specifically, if someone issues a savepoint
while in \reseterror mode, it switches off automatically*. Since the
implementation of reseterror is pretty much a lazy shortcut to issuing savepoints
yourself, it should be safe to say that you do not want to mix manual and
automatic ones, and we'll back off (with a message) if you issue your own.
Plus there will be a warning in the docs to be careful about mixing savepoints
and the \reseterror method.

* We could also switch it back on after rollback or release, but this would
entail a little more tracking.

Comments?

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200501282306
http://biglumber.com/x/web?pk=2529DF...9B906714964AC8

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

iD8DBQFB+wzovJuQZxSWSsgRAt5eAJ9BVMtYZ9H+A76cNdUuhv 4GpXeCwQCdFVsi
+mgg6ZzMylgHgdfiVn4yI5o=
=CpZQ
-----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

Greg Sabino Mullane wrote:

> Michael Paesold wrote:
>> 2) Implement a server-side function to get the savepoints from the server
>> and query that before every release.
>
> I could not find a way to do this. Is there any interface to the list?

Alvaro suggested to implement such a function. It is not there yet. I think
you would have to access the sub xact stack, but I have not looked into that
code for quite some time.
http://archives.postgresql.org/pgsql...0/msg00370.php


> First, I'm not of the opinion that it should automatically be turned off
> when running non-interactively. That's too much assuming of what the user
> wants, when this is a settable flag. However, it should be settable via
> a script to a definite state. So \reseterror will take an optional
> argument,
> "off" or "on", which sets it rather than toggles it.

Discussion here last year showed some concern from people that this feature
could bite people and is not really safe. Perhaps the best way would be to
create three states:
\reseterrors (on|off|auto)
where auto means it's only active for interactive queries.
(auto could be named interactive)

> The other problem is not stepping on other people's savepoints. The best
> solution we came up with was to check for savepoint commands ourselves,
> similar to the way psql already checks for transaction affecting commands,
> and handle things appropriately. Specifically, if someone issues a
> savepoint
> while in \reseterror mode, it switches off automatically*. Since the
> implementation of reseterror is pretty much a lazy shortcut to issuing
> savepoints
> yourself, it should be safe to say that you do not want to mix manual and
> automatic ones, and we'll back off (with a message) if you issue your own.
> Plus there will be a warning in the docs to be careful about mixing
> savepoints
> and the \reseterror method.
>
> * We could also switch it back on after rollback or release, but this
> would
> entail a little more tracking.
>
> Comments?

I would prefer a solution, where the feature is not disabled as soon as I
use my own savepoints. I like \reseterror because it prevents making typos
from aborting my transaction. Explicit savepoints I rather use to try a
whole bunch of statements and then decide if I want to commit so far. I can
still make typos.

If you don't want to, I can implement such a savepoint stack. I don't think
it's that hard. The parsing, as you mentioned, should also not be too hard,
because the infrastructure (removing white space) is already there.

Best Regards,
Michael Paesold


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Sat, Jan 29, 2005 at 01:04:36PM +0100, Michael Paesold wrote:
> Greg Sabino Mullane wrote:
>
> >Michael Paesold wrote:
> >>2) Implement a server-side function to get the savepoints from the server
> >>and query that before every release.
> >
> >I could not find a way to do this. Is there any interface to the list?
>
> Alvaro suggested to implement such a function. It is not there yet. I think
> you would have to access the sub xact stack, but I have not looked into
> that code for quite some time.
> http://archives.postgresql.org/pgsql...0/msg00370.php

The only problem with this idea is that the function cannot be run when
the transaction is in aborted state. Not sure if that is a problem or
not. What happens if the user does

SAVEPOINT foo; SLECT 1; ROLLBACK TO foo;

all in one command in psql?

I know psql sends that as three commands, so maybe it's not an issue.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Doing what he did amounts to sticking his fingers under the hood of the
implementation; if he gets his fingers burnt, it's his problem." (Tom Lane)

---------------------------(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

Alvaro Herrera wrote:


>> >Michael Paesold wrote:
>> Alvaro suggested to implement such a function. It is not there yet. I
>> think
>> you would have to access the sub xact stack, but I have not looked into
>> that code for quite some time.
>> http://archives.postgresql.org/pgsql...0/msg00370.php
>
> The only problem with this idea is that the function cannot be run when
> the transaction is in aborted state. Not sure if that is a problem or
> not. What happens if the user does

I don't think there is a problem. If the transaction is in aborted state, we
only care if the last statement has aborted the transaction. Otherwise we
would not issue our savepoint at all. In that case, i.e. if the last
statement aborted the transaction, we can roll it back anyway, can't we?
There can't be a savepoint on top of us, because that would have failed
right now.
Is my logic wrong?

> SAVEPOINT foo; SLECT 1; ROLLBACK TO foo;
>
> all in one command in psql?
>
> I know psql sends that as three commands, so maybe it's not an issue.

As far as I remember psql splits the three commands, so there would be an
implicit savepoint for each individual statement:

* SAVEPOINT pg_psql_savepoint; -- [1]
SAVEPOINT foo;
* SAVEPOINT pg_psql_savepoint; -- [2]
SLECT 1;
* ROLLBACK TO pg_psql_savepoint; -- [2]
* SAVEPOINT pg_psql_savepoint; -- [3]
ROLLBACK TO foo;
* RELEASE pg_psql_savepoint; -- [3]
* RELEASE pg_psql_savepoint; -- [1], because pg_psql_savepoint is on top of
the stack now again

I hope you get the point. ;-)

Do you think it's better to create a server-side function or handle that in
the client? How hard would it be to implement such a function? And what
should it return? Only the name of the current top savepoint?

Best Regards,
Michael Paesold



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

This thread has been saved for the 8.1 release:

http://momjian.postgresql.org/cgi-bin/pgpatches2

---------------------------------------------------------------------------

Greg Sabino Mullane wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> Attached is a patch that takes advantage of savepoints to enable
> transactions to continue even after errors in psql. The name of it
> is \reseterror, and it is off by default. It's backwards compatible,
> and allows things like this to work on 8.0 and up servers:
>
> \reseterror
> BEGIN;
> DELETE FROM foobar;
> INSERT INTO foobar(a) VALUES(1);
> ISNER INTO foobar(a) VALUES(2);
> INSERT INTO foobar(a) VALUES(3);
> COMMIT;
>
> Doing a SELECT(a) FROM foobar will show two values, 1 and 3. This
> is a great help for those of us that tend to type typos into our
> psql session, and end up cursing as we have to restart our current
> transaction.
>
> - --
> Greg Sabino Mullane greg@turnstep.com
> PGP Key: 0x14964AC8 200501252203
> http://biglumber.com/x/web?pk=2529DF...9B906714964AC8
> -----BEGIN PGP SIGNATURE-----
>
> iD8DBQFB9wlpvJuQZxSWSsgRAsAzAKCxQ/JtR6/RXgV39uDTm9FIxCIp8QCeKC6T
> 2l10ef5DHkmFC2dSMQLNHjg=
> =HKv9
> -----END PGP SIGNATURE-----
>

[ Attachment, skipping... ]

>
> ---------------------------(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

--
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 majordomo@postgresql.org so that your
message can get through to the mailing list cleanly