Re: [HACKERS] Does psql use nested transactions?
On Wed, 2004-08-18 at 02:48, Greg Stark wrote: Simon Riggs [EMAIL PROTECTED] writes: This behaviour allows much closer mimicking of Oracle and other RDBMS's transactional behaviour begin 2cThis 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Does psql use nested transactions?
On Tue, Aug 17, 2004 at 02:56:19PM -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 key if 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 | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confident people can be simple. ---(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] Does psql use nested transactions?
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 ---(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] Does psql use nested transactions?
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 | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confident people can be simple. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Does psql use nested transactions?
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 [EMAIL PROTECTED] | (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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Does psql use nested transactions?
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Does psql use nested transactions?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Does psql use nested transactions?
Hi Tom On Wed, 11 Aug 2004 11:47:35 -0400, Tom Lane [EMAIL PROTECTED] 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 [EMAIL PROTECTED] como mi nueva y unica direccion de correo en lugar de la vieja [EMAIL PROTECTED] Please, record [EMAIL PROTECTED] as my new and unique email address instead of ye old [EMAIL PROTECTED] Gracias. Thankyou. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Does psql use nested transactions?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Does psql use nested transactions?
Haroldo Stenger [EMAIL PROTECTED] writes: On Wed, 11 Aug 2004 11:47:35 -0400, Tom Lane [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Does psql use nested transactions?
Bruce Momjian [EMAIL PROTECTED] 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 ---(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] Does psql use nested transactions?
Bruce Momjian Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 ---(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] Does psql use nested transactions?
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 [EMAIL PROTECTED] 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 ([EMAIL PROTECTED]) 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) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Does psql use nested transactions?
Alvaro Herrera Munoz [EMAIL PROTECTED] 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 ---(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] Does psql use nested transactions?
Hola Alvaro On Tue, 17 Aug 2004 16:58:56 -0400, Alvaro Herrera Munoz [EMAIL PROTECTED] wrote: On Wed, 11 Aug 2004 11:47:35 -0400, Tom Lane [EMAIL PROTECTED] 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 [EMAIL PROTECTED] como mi nueva y unica direccion de correo en lugar de la vieja [EMAIL PROTECTED] Please, record [EMAIL PROTECTED] as my new and unique email address instead of ye old [EMAIL PROTECTED] Gracias. Thankyou. ---(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] Does psql use nested transactions?
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 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Does psql use nested transactions?
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 |/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Does psql use nested transactions?
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 |/ ---(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] Does psql use nested transactions?
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 [EMAIL PROTECTED] 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) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Does psql use nested transactions?
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) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Does psql use nested transactions?
Simon Riggs [EMAIL PROTECTED] writes: This behaviour allows much closer mimicking of Oracle and other RDBMS's transactional behaviour begin 2cThis 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Does psql use nested transactions?
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] 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 |/ ---(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] 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. 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Does psql use nested transactions?
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 |/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Does psql use nested transactions?
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 ---(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
