Re: Weird behavior in transaction handling (Possible bug ?)

Dear random programmer,

I understand your grief perfectly, and agree completely, I've been
there. But I think the spec is at least ambiguous about this matter, if
not leaning towards the postgres behavior of rolling back everything on
an error. And I don't believe commit fails silently, it should give you
an error.
The demand for the convenience of deciding after a failed query to
continue or not comes from other DB engines readily implementing it
(notably Oracle). So I would expect you're coming from an oracle DB
experience...
In any case, if you want similar behavior in postgres > 8.0, you could
use a savepoint before the auxialiary query and roll back to it on an
error. Then the result of the queries before the savepoint can be
committed.
Please note that setting a savepoint in postgres is not nearly free, so
be careful how often you do it.

HTH,
Csaba.


 Fri, 2005-01-14 at 17:29, j.random.programmer wrote:
> > With postgres once an error occurs in a transaction
> block you need
> > to rollback. None of the transaction will commit.
> >
> > This behaviour makes sense as it assumes that the
> transaction block
> > is atomic and it should all succeed or all fail.
>
>
> This is VERY counter-intuitive. I can have really
> important data
> for say 5 tables which has committed properly but at
> the 6th
> insert into a non-important auxillary table, I may
> encounter a
> transient exception. I still want to be able to commit
> my data.
>
> There are many similar scenarios such as the above,
> right ?
>
> As a programmer, shouldn't it be upto me to decide
> when to
> commit and when to rollback ?  Is this even within
> spec ? And
> at the very least, commit() should then not fail
> SILENTLY ! (and
> this should be documented).
>
> :-]
>
> Best regards,
>
> --j
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Mail - Helps protect you from nasty viruses.
> http://promotions.yahoo.com/new_mail
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org