Re: [ADMIN] transaction error handling
Very naïve question here: Why would you want to save the data from the first insert? I thought the purpose of a transaction was to make sure that all steps in the transaction executed, or none of them executed. If Oracle saves half of the data between the beginning and ending of the transaction, doesn't that defeat the purpose of the transaction? RobR -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] transaction error handling
I have the same confusion... 于 2011/11/30 2:34, Rob Richardson 写道: Very naïve question here: Why would you want to save the data from the first insert? I thought the purpose of a transaction was to make sure that all steps in the transaction executed, or none of them executed. If Oracle saves half of the data between the beginning and ending of the transaction, doesn't that defeat the purpose of the transaction? RobR -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] transaction error handling
On 29 November 2011 21:34, Rob Richardson rdrichard...@rad-con.com wrote: If Oracle saves half of the data between the beginning and ending of the transaction, doesn't that defeat the purpose of the transaction? It sure enough kills Atomicity. I can see a use for this on importing data from external sources that may violate existing unique keys, so illegal inserts are ignored, but you still are left without any knowledge of what rows where silently dropped. Since when is Oracle doing this, FMI? (It's been a long while since I used it for anything serious) Bèrto -- == If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
Re: [ADMIN] transaction error handling
-Original Message- From: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin- ow...@postgresql.org] On Behalf Of Rob Richardson Sent: Tuesday, November 29, 2011 1:35 PM To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] transaction error handling Very naïve question here: Why would you want to save the data from the first insert? You might want your code to recover from an error and take a different approach. I thought the purpose of a transaction was to make sure that all steps in the transaction executed, or none of them executed. If Oracle saves half of the data between the beginning and ending of the transaction, doesn't that defeat the purpose of the transaction? This functionality is something that Postgres can do today. We expose the ability to do this with explicit savepoints. The difference is that Oracle allows you to set it on a per transaction basis (I believe) and it will behave this way for all statements in the transaction, where as we need to do it explicitly. Looking through the archives there does seem to be a performance problem on commit in Postgres if you issue a lot of savepoints (there were discussions of a fix but I am not sure the status of this). Brad. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] transaction error handling
Hi, Oracle does not save the data, in mid transaction, in the sense of a commit. It keeps the association of the memory address related to the error free changes to the transaction and allows you – the developer to capture the error on that single incorrect change, and then continue with the subsequent sql statements that are part of that long transaction. While in that state, the changes pertaining to that transaction are not written to any logs and are not written to file, you can still roll back the entire transaction. Only when a commit occurs, does the transaction get written to SGA, archiving, file etc… With Postgres that is not the case, if the 50th sql statement in a long transaction incurs an error, the whole transaction is rolled back for you automatically, you the developer have no say in that unless you bracket each statement with a savepoint creation and destruction, just to be able to capture the potential error that may arise on that 50th sql statement. Sincerely, Kasia From: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin-ow...@postgresql.org] On Behalf Of Bèrto ëd Sèra Sent: Wednesday, November 30, 2011 12:49 AM To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] transaction error handling On 29 November 2011 21:34, Rob Richardson rdrichard...@rad-con.commailto:rdrichard...@rad-con.com wrote: If Oracle saves half of the data between the beginning and ending of the transaction, doesn't that defeat the purpose of the transaction? It sure enough kills Atomicity. I can see a use for this on importing data from external sources that may violate existing unique keys, so illegal inserts are ignored, but you still are left without any knowledge of what rows where silently dropped. Since when is Oracle doing this, FMI? (It's been a long while since I used it for anything serious) Bèrto -- == If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
Re: [ADMIN] transaction error handling
On 11/30/2011 09:19 PM, Nicholson, Brad (Toronto, ON, CA) wrote: This functionality is something that Postgres can do today. We expose the ability to do this with explicit savepoints. The difference is that Oracle allows you to set it on a per transaction basis (I believe) and it will behave this way for all statements in the transaction, where as we need to do it explicitly. Looking through the archives there does seem to be a performance problem on commit in Postgres if you issue a lot of savepoints (there were discussions of a fix but I am not sure the status of this). Savepoint performance has had several improvements over time. Back in 8.1 when I got started developing against Pg seriously I was having *MASSIVE* performance issues with PL/PgSQL exception blocks (which use savepoints) in loops; these days it's perfect. To make automatic savepoints viable, Pg would need to be able to completely forget a savepoint once it's been released, so there's no ongoing cost. That way a transaction would only need two savepoints at any particular point in time. My understanding is that it's not there yet; AFAIK released savepoints still have a non-trivial cost that would add up if someone was using automatic savepoints in (say) a 10,000 INSERT transaction. -- Craig Ringer -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] transaction error handling
Hi On 30 November 2011 22:30, Kasia Tuszynska ktuszyn...@esri.com wrote: With Postgres that is not the case, if the 50th sql statement in a long transaction incurs an error, the whole transaction is rolled back for you automatically, you the developer have no say in that unless you bracket each statement with a savepoint creation and destruction, just to be able to capture the potential error that may arise on that 50th sql statement. Thanks, now I finally got what you meant. Bèrto
[ADMIN] transaction error handling
Hi Everybody, This is an architectural question. I am testing on Postgres 9.0.2 on windows and linux(suse, rhel, ubuntu) I want to make sure that I have the correct understanding of the Postgres architecture and would like to enquire if there are any plans to change it. Comparing Oracle and Postgres from the perspective of error handling on the transaction level I observed the following: Oracle: Begin transaction Insert - no error Implicit savepoint Insert - error raised Implicit rollback to the savepoint, no transaction loss, error raised on the insert statement that errored out. End transaction, implicit commit, with the single error free insert. Postgres: Begin transaction Insert - no error Insert - error raised Transaction loss = no implicit rollback to the single error free insert. Is this a correct interpretation of the Postgres transaction error handling? If so, are there any changes being considered, or perhaps already implemented? Sincerely, Kasia -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] transaction error handling
Kasia Tuszynska ktuszyn...@esri.com wrote: Oracle: Begin transaction Insert - no error Implicit savepoint Insert - error raised Implicit rollback to the savepoint, no transaction loss, error raised on the insert statement that errored out. End transaction, implicit commit, with the single error free insert. Postgres: Begin transaction Insert - no error Insert - error raised Transaction loss = no implicit rollback to the single error free insert. Is this a correct interpretation of the Postgres transaction error handling? Well, in psql you can set ON_ERROR_ROLLBACK so that each statement will be automatically preceded by a SAVEPOINT which will be automatically rolled back if the statement has an error. There are various constructs for accomplishing this in supported PLs, depending on the language. I'm not aware of any explicitly start a transaction but guess at whether a commit is intended feature in PostgreSQL. An explicit transaction is committed if and when you say so. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] transaction error handling
Hi Kevin, Thank you, that is very helpful. I am not worried about the implicit commits. The no implicit savepoint was more of an issue, since it created a necessity to create and destroy savepoints per each sql statement to capture any statement level error without losing a transaction, that approach has prohibitive performance repercussions. I will check out the ON_ERROR_ROLLBACK feature. Thank you, Sincerely, Kasia -Original Message- From: Kevin Grittner [mailto:kevin.gritt...@wicourts.gov] Sent: Tuesday, November 29, 2011 10:55 AM To: Kasia Tuszynska; pgsql-admin@postgresql.org Subject: Re: [ADMIN] transaction error handling Kasia Tuszynska ktuszyn...@esri.com wrote: Oracle: Begin transaction Insert - no error Implicit savepoint Insert - error raised Implicit rollback to the savepoint, no transaction loss, error raised on the insert statement that errored out. End transaction, implicit commit, with the single error free insert. Postgres: Begin transaction Insert - no error Insert - error raised Transaction loss = no implicit rollback to the single error free insert. Is this a correct interpretation of the Postgres transaction error handling? Well, in psql you can set ON_ERROR_ROLLBACK so that each statement will be automatically preceded by a SAVEPOINT which will be automatically rolled back if the statement has an error. There are various constructs for accomplishing this in supported PLs, depending on the language. I'm not aware of any explicitly start a transaction but guess at whether a commit is intended feature in PostgreSQL. An explicit transaction is committed if and when you say so. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] transaction error handling
On Tue, Nov 29, 2011 at 10:57 AM, Kasia Tuszynska ktuszyn...@esri.com wrote: Postgres: Begin transaction Insert - no error Insert - error raised Transaction loss = no implicit rollback to the single error free insert. Is this a correct interpretation of the Postgres transaction error handling? If so, are there any changes being considered, or perhaps already implemented? You can insert a savepoint explicitly if you wish, but without setting one, then the whole transaction will be rolled back. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] transaction error handling
On Tue, 29 Nov 2011 09:57:24 -0800, Kasia Tuszynska wrote: Hi Everybody, This is an architectural question. I am testing on Postgres 9.0.2 on windows and linux(suse, rhel, ubuntu) I want to make sure that I have the correct understanding of the Postgres architecture and would like to enquire if there are any plans to change it. Comparing Oracle and Postgres from the perspective of error handling on the transaction level I observed the following: Oracle: Begin transaction Insert - no error Implicit savepoint Insert - error raised Implicit rollback to the savepoint, no transaction loss, error raised on the insert statement that errored out. End transaction, implicit commit, with the single error free insert. Postgres: Begin transaction Insert - no error Insert - error raised Transaction loss = no implicit rollback to the single error free insert. Is this a correct interpretation of the Postgres transaction error handling? If so, are there any changes being considered, or perhaps already implemented? I suspect you may be barking up the wrong tree. Comparing default behaviour of PSQL to SQL*Plus is not the same thing as comparing PostgreSQL to Oracle. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] transaction error handling
Hi, Yes, I believe that you are right. As far as I can gather, the postgres transaction error handling is like oracle stored procedures. If you do not catch the error the whole transaction is rolled back. I am curious why Postgres has gone with a model that does not allow the user a choice to deal with the statement level errors that may arise in a long transaction. That either calls for very short transactions or an introduction of explicit savepoint creation and explicit savepoint destruction for every statement, if you - the user, want the ability to deal with statement errors that may arise. I realize that it is almost impossible to change that architecture now, since it would be such a low level change, but I am surprised that it is not a common complaint from the user community, since bulk ddl loads would truly suffer. I do not wish to compare Postgres to Oracle per se, I used oracle because I am more familiar with it than the Sql Server transaction model, they did a rewrite on transaction handling for SS 2005 and I never fully got into it. Sincerely, Kasia -Original Message- From: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin-ow...@postgresql.org] On Behalf Of Walter Hurry Sent: Tuesday, November 29, 2011 12:50 PM To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] transaction error handling On Tue, 29 Nov 2011 09:57:24 -0800, Kasia Tuszynska wrote: Hi Everybody, This is an architectural question. I am testing on Postgres 9.0.2 on windows and linux(suse, rhel, ubuntu) I want to make sure that I have the correct understanding of the Postgres architecture and would like to enquire if there are any plans to change it. Comparing Oracle and Postgres from the perspective of error handling on the transaction level I observed the following: Oracle: Begin transaction Insert - no error Implicit savepoint Insert - error raised Implicit rollback to the savepoint, no transaction loss, error raised on the insert statement that errored out. End transaction, implicit commit, with the single error free insert. Postgres: Begin transaction Insert - no error Insert - error raised Transaction loss = no implicit rollback to the single error free insert. Is this a correct interpretation of the Postgres transaction error handling? If so, are there any changes being considered, or perhaps already implemented? I suspect you may be barking up the wrong tree. Comparing default behaviour of PSQL to SQL*Plus is not the same thing as comparing PostgreSQL to Oracle. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] transaction error handling
-Original Message- From: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin- ow...@postgresql.org] On Behalf Of Kasia Tuszynska Sent: Tuesday, November 29, 2011 3:35 PM To: Kevin Grittner; pgsql-admin@postgresql.org Subject: Re: [ADMIN] transaction error handling Hi Kevin, Thank you, that is very helpful. I am not worried about the implicit commits. The no implicit savepoint was more of an issue, since it created a necessity to create and destroy savepoints per each sql statement to capture any statement level error without losing a transaction, that approach has prohibitive performance repercussions. I will check out the ON_ERROR_ROLLBACK feature. Thank you, Sincerely, Kasia Be aware that this option is a psql option and not one in the database itself, which means unless you are executing your SQL via psql it will not be of help to you. Also the implementation of this is that psql issues implicit savepoints for you before each command in a transaction and handles the rollback for you if needed (which sounds an awful lot like the performance concern you have). This is a major pain for porting Oracle based applications over for those that rely on this functionality. Brad. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
