SEO

> 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

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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> an error. And I don't believe commit fails silently,
> it should give you
> an error.

Nope, no error. See the original post, there isn't any
error
in the postgresql logs either, and no error at the
JDBC layer.

> (notably Oracle). So I would expect you're coming
> from an oracle DB
> experience...

Nope. I've been using Postgresql for years now (after
having
used mysql/innodb and gotten rather tired of _that_).

:-]

> Please note that setting a savepoint in postgres is
> not nearly free, so
> be careful how often you do it.

After further testing, the standalone psql client also
shows
the same behavior so I guess this is a postgresql
database
thing, not driver specific.

Here's my humble suggestion. If technically possible,
have
the driver throw a SQLExeption when trying to commit()

and when the driver knows that commit() is going to
fail.

Maybe the driver can simple track if it's recieved any
kind
of error messages on a given connection. If so, then
when
commit() is called on that connection, the driver
should
throw a SQLException ?

Otherwise, you're asking for very esoteric bugs to say
you have
commit() succesfully yet fail silently. That's more
like a mysql
thing, *not* a postgresql thing right ?

Best regards,

--j





__________________________________
Do you Yahoo!?
Meet the all-new My Yahoo! - Try it today!
http://my.yahoo.com



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend