Re: Two features left
От | Nicolai Tufar |
---|---|
Тема | Re: Two features left |
Дата | |
Msg-id | 02e901c2965f$32e2a950$8016a8c0@apb.com.tr обсуждение исходный текст |
Ответ на | Re: Two features left (Bruce Momjian <pgman@candle.pha.pa.us>) |
Список | pgsql-general |
I would like to jump in and make another example to make the case clear. I have a nightly batch load of a 5000 rows or so which I have wrapped in BEGIN; ... COMMIT; to make it faster. Some of rows generate errors due to maiformed date column. Under Oracle the whole load is commited except for the rows that cause errors. I check script logs and replly ejected rows. Under PostgreSQL, however, a single error is causing transaction to abort and fills logs with nasty error messages, one for every remaining row in batch, telling that transaction is in abort state. Maybe it is possible to make a session variable so we can choose the behavior. Something like: set ON_TRANSACTION_ERROR=CONTINUE or set ON_TRANSACTION_ERROR=ABORT Regards, Nick ----- Original Message ----- From: "Jon Swinth" <jswinth@atomicpc.com> To: "Bruce Momjian" <pgman@candle.pha.pa.us>; "Jean-Luc Lachance" <jllachan@nsd.ca> Cc: <pgsql-general@postgresql.org> Sent: Wednesday, November 27, 2002 10:46 PM Subject: Re: [GENERAL] Two features left > Maybe what you are talking about will not help. The question is are you > trying to make nested transactions or savepoints? > > Nested transactions would be useful for trying to interrupt a transaction and > have another action happen or not happen on it's own. An example would be > when you want a credit card transaction to generate a log reguardless of > whether the out transaction is commited or rolled back. The problem with > nested transactions is that it is easy to generate deadlocks, especially with > the write locks currently on foreign keys. > > What may help is the concept of savepoint (if implemented internally). > Savepoints are usually named and allow rollback to a specific point in the > transaction. There is no issue with deadlock since everything is still in > the same transaction. You then don't have to have something call ABORT, you > simple need to say ROLLBACK TO <savepoint_name>. > > BEGIN; > SELECT... > INSERT... > SAVEPOINT a ; > UPDATE... > ROLLBACK TO a ; > DELETE... > COMMIT; > > On Wednesday 27 November 2002 12:25 pm, Bruce Momjian wrote: > > Jean-Luc Lachance wrote: > > > Bruce, > > > > > > I assume one will be able to ABORT the current transaction without > > > aborting the higher transaction and ABORT ALL to abort all if needed. > > > > Right. I hadn't planned on ABORT ALL, but it could be done to abort the > > entire transaction. Is there any standard on that? > > > > > What syntax will be available to the upper transaction to detect a lower > > > ABORT? > > > While there be something ? la Java ( try catch)? > > > > My initial implementation will be simple: > > > > BEGIN; > > SELECT ... > > BEGIN; > > UPDATE ... > > ABORT; > > DELETE ... > > COMMIT; > > > > and later savepoints which allow you to abort back to a saved spot in your> > transaction. > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
В списке pgsql-general по дате отправления: