Re: Two features left - Mailing list 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
>