Thread: Does psql use nested transactions?
In 8.0 beta 1 I just tried:
psql template1 > begin; > select * from pg_class; ... got stuff ... > select * from aaa; ERROR:
relation"zzz" does not exist > select * from pg_class; ERROR: current transaction is aborted....
Should psql run every statement in a nested transaction and only rollback
that TX? Or is that not possible/desirable. If possible, this would be a
*great* feature for those of use prone to mis-spellings.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \| | --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/
> In 8.0 beta 1 I just tried: > > psql template1 > > begin; > > select * from pg_class; > ... got stuff ... > > select * from aaa; > ERROR: relation "zzz" does not exist > > select * from pg_class; > ERROR: current transaction is aborted.... > > Should psql run every statement in a nested transaction and only rollback > that TX? Or is that not possible/desirable. If possible, this would be a > *great* feature for those of use prone to mis-spellings. Nested transactions are not designed to help you with spelling errors. They are designed to give you more flexible options with rolling back data to keep it in a valid state. psql is designed to follow the same logical statement progression as any other connection to the database. Your suggestion could muck up sql scripts piped through it to the database. If you make a lot of spelling errors, just macro 'savepoint x' and fire that when typing multiple related statements into psql after each statement. Merlin
At 01:47 AM 12/08/2004, Tom Lane wrote: >It might be reasonable to offer that behavior as an option, but I think >I'd only want it on for interactive input. My preference would be for something like: set savepoint_per_statement=true then interactive & scripts could choose to use either feature. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp.mit.edu:11371 |/
>> Should psql run every statement in a nested transaction and only rollback
>> that TX?
> psql is designed to follow the same logical statement progression as any
> other connection to the database. Your suggestion could muck up sql
> scripts piped through it to the database.
The other objection is that this would create very substantial overhead.
It might be reasonable to offer that behavior as an option, but I think
I'd only want it on for interactive input.
regards, tom lane
Philip Warner wrote: > At 01:47 AM 12/08/2004, Tom Lane wrote: > >It might be reasonable to offer that behavior as an option, but I think > >I'd only want it on for interactive input. > > My preference would be for something like: > > set savepoint_per_statement=true > > then interactive & scripts could choose to use either feature. Added to TODO list: * Add an option to automatically use savepoints for each statement in a multi-statement transaction. When enabled, this would allow errors in multi-statement transactions to be automatically ignored. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Added to TODO list:
> * Add an option to automatically use savepoints for each statement in a
> multi-statement transaction.
> When enabled, this would allow errors in multi-statement transactions
> to be automatically ignored.
Note that whoever implements this had better be mindful of the bad
experience we had with autocommit. If there is any way that this
behavior could be turned on underneath an application/script without
the app knowing about it, this would be spectacularly dangerous.
Like I said, my preference would be that it couldn't be turned on at all
except when psql's input is interactive.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > >>Added to TODO list: > > >>* Add an option to automatically use savepoints for each statement in a >> multi-statement transaction. > > >> When enabled, this would allow errors in multi-statement transactions >> to be automatically ignored. > Like I said, my preference would be that it couldn't be turned on at all > except when psql's input is interactive. I strongly vote for the same solution. Regards Gaetano Mendola
Hi Tom On Wed, 11 Aug 2004 11:47:35 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > The other objection is that this would create very substantial overhead. why? > It might be reasonable to offer that behavior as an option, but I think > I'd only want it on for interactive input. How could the server tell interactive input from scripted intput without an explicit setting meant to do it? Is ther already any conciousness on the server part about who is talking to it? Other than performance issues, I don't see why this is more dangerous than any other settings. Regards Haroldo -- Por favor registre haroldo.stenger@gmail.com como mi nueva y unica direccion de correo en lugar de la vieja hstenger@adinet.com.uy Please, record haroldo.stenger@gmail.com as my new and unique email address instead of ye old hstenger@adinet.com.uy Gracias. Thankyou.
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Added to TODO list: > > > * Add an option to automatically use savepoints for each statement in a > > multi-statement transaction. > > > When enabled, this would allow errors in multi-statement transactions > > to be automatically ignored. > > Note that whoever implements this had better be mindful of the bad > experience we had with autocommit. If there is any way that this > behavior could be turned on underneath an application/script without > the app knowing about it, this would be spectacularly dangerous. > > Like I said, my preference would be that it couldn't be turned on at all > except when psql's input is interactive. Should we allow it only to be enabled inside a multi-statement transaction, and it turns off when the transaction completes? Doing it only for interactive mode seems too error-prone to me (it works in psql, but not from my script). -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Haroldo Stenger <haroldo.stenger@gmail.com> writes:
> On Wed, 11 Aug 2004 11:47:35 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It might be reasonable to offer that behavior as an option, but I think
>> I'd only want it on for interactive input.
> How could the server tell interactive input from scripted intput
> without an explicit setting meant to do it?
The server wouldn't. One of the implications of this requirement is
that we don't implement the feature on the server side, but in psql.
psql then checks isatty(fileno(stdin)) before allowing it.
> Other than performance issues, I don't see why this is more dangerous
> than any other settings.
Silently ignoring errors is almost always a really bad idea. Doing it
underneath an application that isn't expecting it is always a bad idea.
Perhaps more to the point, what we are talking about here is an option
to change the behavior of transactions from what it formerly was,
without any change in the SQL commands being issued. From a backwards-
compatibility standpoint this is monstrously dangerous. If you have
forgotten the server-side-autocommit fiasco, I suggest you go and read
the archives for a bit ...
regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Doing it only for interactive mode seems too error-prone to me (it works
> in psql, but not from my script).
You're missing the point: a script cannot safely work this way. A human
typing at the terminal can notice that his command failed and react to
that, but a psql script cannot.
regards, tom lane
> Bruce Momjian > Tom Lane wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > Added to TODO list: > > > > > * Add an option to automatically use savepoints for each > statement in a > > > multi-statement transaction. > > > > > When enabled, this would allow errors in multi-statement > transactions > > > to be automatically ignored. > > > > Note that whoever implements this had better be mindful of the bad > > experience we had with autocommit. If there is any way that this > > behavior could be turned on underneath an application/script without > > the app knowing about it, this would be spectacularly dangerous. > > > > Like I said, my preference would be that it couldn't be turned on at all > > except when psql's input is interactive. > > Should we allow it only to be enabled inside a multi-statement > transaction, and it turns off when the transaction completes? > Yes, this is a very very good option to have. This behaviour allows much closer mimicking of Oracle and other RDBMS's transactional behaviour - also some people (not on this list) have decided that this is in fact the correct ANSI behaviour for transactions (accepting that this is unclear, and not wishing to restart that debate). - It should not apply ONLY to psql, but to all interfaces i.e. it should be server-side behaviour. - The option should be available at the session level, so that all transactions within a session exhibit this behaviour - and not just as an option within a transaction. Tom is right to draw analogy to Autocomit, not just because of earlier fiascos, but also because of the similarity that is required in the way that the option is set. i.e. it can be manipulated as a session level default, but is not completely transparent to the user. These aren't my just preferences, but an attempt at a clear statement of what is required for compatibility.... Best Regards, Simon Riggs
On Tue, Aug 17, 2004 at 03:33:01PM -0300, Haroldo Stenger wrote: Haroldo, > On Wed, 11 Aug 2004 11:47:35 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > The other objection is that this would create very substantial overhead. > > why? Establishing a savepoint is a non-trivial operation (cost-wise). Several internal server structures have to be prepared for it. It's way cheaper than normal transaction start and commit, but undesirable anyway if done once per query. Worse if it's going to be seldom used. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "La experiencia nos dice que el hombre peló millones de veces las patatas, pero era forzoso admitir la posibilidad de que en un caso entre millones, las patatas pelarían al hombre" (Ijon Tichy)
Alvaro Herrera Munoz <alvherre@dcc.uchile.cl> writes:
> Establishing a savepoint is a non-trivial operation (cost-wise). Several
> internal server structures have to be prepared for it.
Check
> It's way cheaper than normal transaction start and commit,
Is it? You have the same amount of work to do (sooner or later) in
terms of updating pg_clog, plus extra work to update pg_subtrans.
And in the abort case it can be worse than aborting a full xact, because
we have to do retail rather than wholesale release of locks, buffers, etc.
I have not had an opportunity to benchmark it but I fear a savepoint may
cost near as much as a full xact in practice.
regards, tom lane
Hola Alvaro On Tue, 17 Aug 2004 16:58:56 -0400, Alvaro Herrera Munoz <alvherre@dcc.uchile.cl> wrote: > > On Wed, 11 Aug 2004 11:47:35 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > > The other objection is that this would create very substantial overhead. > > > > why? > > Establishing a savepoint is a non-trivial operation (cost-wise). Several > internal server structures have to be prepared for it. It's way cheaper than > normal transaction start and commit, but undesirable anyway if done once per > query. Worse if it's going to be seldom used. I see. So, any heavy use of savepoints, say, issuing one before each SQL command that might violate a restriction, would worsen a lot the performance of my application? Saludos Haroldo -- Por favor registre haroldo.stenger@gmail.com como mi nueva y unica direccion de correo en lugar de la vieja hstenger@adinet.com.uy Please, record haroldo.stenger@gmail.com as my new and unique email address instead of ye old hstenger@adinet.com.uy Gracias. Thankyou.
At 07:10 AM 18/08/2004, Tom Lane wrote: >I have not had an opportunity to benchmark it but I fear a savepoint may >cost near as much as a full xact in practice. Out of curiosity, does this mean that using a savepoint per statement will be no worse than using psql in autocommit mode? ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp.mit.edu:11371 |/
At 04:44 AM 18/08/2004, Bruce Momjian wrote: >it turns off when the transaction completes? Not sure I like this part; I would like to be able to set the variable at the start of a psql session and have it run for the entire session. Or, even better, set it in a psql initialization file. Also, will the 'interactive-session' check consider an included file (\i) to be interactive? I'd vote yes. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp.mit.edu:11371 |/
At 09:26 AM 18/08/2004, Philip Warner wrote: >Also, will the 'interactive-session' check consider an included file (\i) >to be interactive? I'd vote yes. In retrospect, I assume the interactive-session limit would not be a problem here since the command would be entered interactively. The source of the following commands is irrelevant. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp.mit.edu:11371 |/
On Tue, Aug 17, 2004 at 08:21:13PM -0300, Haroldo Stenger wrote: Hola, > On Tue, 17 Aug 2004 16:58:56 -0400, Alvaro Herrera Munoz > <alvherre@dcc.uchile.cl> wrote: > > Establishing a savepoint is a non-trivial operation (cost-wise). Several > > internal server structures have to be prepared for it. It's way cheaper than > > normal transaction start and commit, but undesirable anyway if done once per > > query. Worse if it's going to be seldom used. > > I see. So, any heavy use of savepoints, say, issuing one before each > SQL command that might violate a restriction, would worsen a lot the > performance of my application? It depends. If you are going to establish savepoints just for the sake of it, and later release them, then yes --- there is no point in incurring in the overhead (and your application will be slower). But if the application has any chance to fail within the savepoint, and you can rollback to a savepoint to retry in a situation which would normally force you to rollback a transaction and start again, then you could _win_ performance. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "I personally became interested in Linux while I was dating an English major who wouldn't know an operating system if it walked up and bit him." (Val Henson)
On Wed, Aug 18, 2004 at 09:23:44AM +1000, Philip Warner wrote: > At 07:10 AM 18/08/2004, Tom Lane wrote: > >I have not had an opportunity to benchmark it but I fear a savepoint may > >cost near as much as a full xact in practice. > > Out of curiosity, does this mean that using a savepoint per statement will > be no worse than using psql in autocommit mode? I think it would be slightly better. A transaction-commit XLog entry requires fsync, whereas there is no XLog entry for savepoint release. As Tom points out, however, savepoint rollback may be more expensive than main transaction rollback due to retail releasing of some resources. But in the case of a long transaction and query-long savepoints, then retail-releasing may have little work to do and be not that much more expensive than wholesale releasing. OTOH, pg_clog and pg_subtrans entries are not fsync()'ed, so it's mostly write() traffic which should be cached at the kernel level. Of course, we should really measure it to be sure ... -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) La web junta la gente porque no importa que clase de mutante sexual seas, tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con ciervos incendiándose", y el computador dirá "especifique el tipo de ciervo" (Jason Alexander)
"Simon Riggs" <simon@2ndquadrant.com> writes: > This behaviour allows much closer mimicking of Oracle and other RDBMS's > transactional behaviour <begin 2c>This is my single biggest pet peeve with Postgres. When I was first starting it was the single most frustrating violation of the "least surprise" principle and now that I've been working with it for over a year it's the one that most continues to interfere with productive work. With Oracle I found it *extremely* useful on many occasions when doing manual updates to be able to check out the effects of my statements before committing them. It also meant I could do several updates or deletes and commit them all together. With Postgres I effectively have to work in autocommit mode. Starting over from scratch every time I make a typo is infeasible. It feels like trying to type in a C program using "cat". I've done it before but it's not something I want to repeat often. In an ideal world I would actually have suggested all drivers would want to work this way. They could wrap a nested transaction around every statement and throw an exception if it fails. The application code could catch the exception and handle it properly without having to manually fiddle with database bits. That's actually why I mentioned a couple times during the nested transaction development that it would be best to design with the assumption that there would be at least one level of nested transaction open at all times. But in practice I think applications can manage their own transactions reasonably. It's just users that expect to be able to recover from any individual statement at any time without advance planning. -- greg
On 17 Aug 2004, Greg Stark wrote: > With Postgres I effectively have to work in autocommit mode. Starting over > from scratch every time I make a typo is infeasible. It feels like trying to > type in a C program using "cat". I've done it before but it's not something I > want to repeat often. I agree. I would like to not use autocommit but I do way to many syntax errors for it to be practical in pg. Autocommit for me is just a hack to get around the above "limitation". > In an ideal world I would actually have suggested all drivers would want to > work this way. For interactive use I'd like simple errors like syntax error to not abort a transaction. For other uses it's good that it aborts. -- /Dennis Björklund
On Tue, Aug 17, 2004 at 02:56:19PM -0400, Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Doing it only for interactive mode seems too error-prone to me (it works > > in psql, but not from my script). > > You're missing the point: a script cannot safely work this way. A human > typing at the terminal can notice that his command failed and react to > that, but a psql script cannot. It is not just a typo that you make in the SQL command. You often need to do insert into table which has primary keyif the insert failed, do update of the existing record It should be upto the application to decide if the failed insert should lead to abortion of the transaction, or if it should be silently ignored and based on the return value of the insert proceed with update. -- ------------------------------------------------------------------------Honza Pazdziora | adelton@fi.muni.cz | http://www.fi.muni.cz/~adelton/.project:Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confidentpeople can be simple.
> It is not just a typo that you make in the SQL command. You often need > to do > > insert into table which has primary key > if the insert failed, do update of the existing record Do the update first then the insert. Chris
On Wed, Aug 18, 2004 at 02:47:26PM +0800, Christopher Kings-Lynne wrote: > >It is not just a typo that you make in the SQL command. You often need > >to do > > > > insert into table which has primary key > > if the insert failed, do update of the existing record > > Do the update first then the insert. That can still fail as concurrent session might run the same series of update (which affects 0 records) and insert (which will fail). Anyway, this was just an example of ways of using the database server to do part of the work -- letting the database server do the checks for you, raise an exception for you which you (your application) can test and happily ignore. It's not just the insert / update thing. The same goes for foreign keys, checks, anything where you knowingly run a statement which can fail, and you act based on the exception you get, _continuing_ with your transaction. This way, large part of the application logic is shifted to the server and to the database schema. The client just tries if the command will run OK. -- ------------------------------------------------------------------------Honza Pazdziora | adelton@fi.muni.cz | http://www.fi.muni.cz/~adelton/.project:Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confidentpeople can be simple.
On Wed, 2004-08-18 at 02:48, Greg Stark wrote: > "Simon Riggs" <simon@2ndquadrant.com> writes: > > > This behaviour allows much closer mimicking of Oracle and other RDBMS's > > transactional behaviour > > <begin 2c>This is my single biggest pet peeve with Postgres. When I was first > starting it was the single most frustrating violation of the "least surprise" > principle and now that I've been working with it for over a year it's the one > that most continues to interfere with productive work. > > With Oracle I found it *extremely* useful on many occasions when doing manual > updates to be able to check out the effects of my statements before committing > them. It also meant I could do several updates or deletes and commit them all > together. > > With Postgres I effectively have to work in autocommit mode. Starting over > from scratch every time I make a typo is infeasible. It feels like trying to > type in a C program using "cat". I've done it before but it's not something I > want to repeat often. It really isn't necessary to do that. If you are entering commands into psql manually, either they are so few that you can easily repeat them, using readline editing, or you can write the commands as a script in an external file, with BEGIN and END at its top and bottom, and run it with \i /path/to/file. If it fails, re-edit it with \!vi /path/to/file and repeat \i You certainly do not have to work in autocommit mode. It will be nice to have nested transactions, but this is not the compelling reason for doing it. Oliver Elphick
Oliver Elphick <olly@lfix.co.uk> writes: > It really isn't necessary to do that. If you are entering commands into > psql manually, either they are so few that you can easily repeat them, > using readline editing, That's what I meant by feeling like I was typing a C program using "cat". > or you can write the commands as a script in an > external file, with BEGIN and END at its top and bottom, and run it with > \i /path/to/file. Sure you could do that. But it kind of defeats the purpose of having an interactive client. It feels like being transported back to the 70s. In any case neither of these solutions addresses the situation where any of the steps in the sequence of commands takes a long time to run. It's wasteful and simply silly to be reexecuting 5 minute queries over and over (nevermind hour long queries or day-long queries) because I typoed on the select to see if it worked the way I expected at the end. That's actually the usual time that I want to double check my work before committing, not simple quick queries. Or similarly for the situation of non-deterministic queries. Sometimes I use random() for loading sample data but then I want to check to make sure things were distributed the way I wanted... -- greg