Weird behavior in transaction handling (Possible bug ?) -- commit fails silently

Hi all:

I've been encountering some non-intuitive peculiar
behavior
with JDBC transaction handling.

I am using the postgres 74.213 driver against a 7.4
database.
I've shown some illustrative code fragments below and
also
shown the postgresql log file corresponding to when
the java
code was run.

Suppose our code skeleton looks like this:
-----------------------------------------------
try {
Connection con = getConnection();
con.setAutoCommit(false);
doInserts(con); //insert values in 1..n tables

con.commit(); <--- THIS FAILS SILENTLY
}
catch (Exception e) {
System.out.println("ERROR COULD NOT SAVE.....");
System.out.println("rolling back...");
con.rollback();
}

[and within the doInserts(Connection con) method]

/* using the supplied connection */
try {
...insert into table #1 ...
...insert into table #2 ...

/*
this will cause a unique constraint exception --
this is expected in this test
*/
...second insert into table #2 again ...
}
catch (Exception e)
{
e.printStackTrace();
}
-----------------------------------------------

Here is the problem. The commit() will NEVER work and
no data is ever saved to any table in the database.
No error message is generated, the commit() SILENTLY
fails to insert any data.

However, if I comment out the second insert into table
#2
(which was causing an error), then the inserts work
and the transaction is committed().

Here is the relevant part from the postgresql server
log.
I've annotated it a bit to make it clearer (my
comments
are marked as ANNOTATION)

-----------------------------------------------
LOG: statement: set datestyle to 'ISO'; select
version(), case when pg_encoding_to_char(1) =
'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding()
end;
LOG: statement: set client_encoding = 'UNICODE'

ANNOTATION: this is where we called
setAutoCommit(false);

LOG: statement: begin;

ANNOTATION: this is where we insert data into various
tables.

LOG: statement: INSERT into person (dbnum, name, dob,
age_years, age_months, sex, mr_num, hearing_loss,
congenital, congenital_type, onset, age_at_diagnosis,
doc_audiologic_testing, reported_by_parent, diag_oae,
diag_oae_left, diag_oae_right, diag_abr,
diag_soundbooth) values ('db123', 'person 1',
'3884-02-20', 2, 2, 'm', 'mr123', 'b', 'y', 'p', 3, 5,
'1', '1', 'n', 'p', 'a', 'y', 'n')
LOG: statement: SELECT 1 FROM ONLY "public"."lookups"
x WHERE "lookups_id" = $1 FOR UPDATE OF x
LOG: statement: select
currval('person_person_id_seq')
LOG: statement: INSERT into eardetail (person_id,
ear, type_lk, severity_lk, progression, fluctuating,
stable) values (16, 'l', 1, 4, 'y', 'n', 'n')
LOG: statement: SELECT 1 FROM ONLY "public"."person"
x WHERE "person_id" = $1 FOR UPDATE OF x
LOG: statement: SELECT 1 FROM ONLY "public"."lookups"
x WHERE "lookups_id" = $1 FOR UPDATE OF x
LOG: statement: SELECT 1 FROM ONLY "public"."lookups"
x WHERE "lookups_id" = $1 FOR UPDATE OF x
LOG: statement: INSERT into eardetail_pattern
(person_id, eardetail_ear, pattern_lk) values (16,
'l', 6)
LOG: statement: SELECT 1 FROM ONLY
"public"."eardetail" x WHERE "person_id" = $1 AND
"ear" = $2 FOR UPDATE OF x
LOG: statement: SELECT 1 FROM ONLY "public"."lookups"
x WHERE "lookups_id" = $1 FOR UPDATE OF x
LOG: statement: INSERT into eardetail_pattern
(person_id, eardetail_ear, pattern_lk) values (16,
'l', 6)

ANNOTATION: this is where one of our inserts fails

ERROR: duplicate key violates unique constraint
"un_eardetail_pattern_1"
STATEMENT: INSERT into eardetail_pattern (person_id,
eardetail_ear, pattern_lk) values (16, 'l', 6)

ANNOTATION: this is where we commit our transaction

LOG: statement: commit;begin;
LOG: statement: select * from person

ANNOTATION: this above transaction commit has failed
and NO DATA HAS BEEN WRITTEN TO ANY TABLE

ANNOTATION: we run the command below from the psql
prompt

LOG: statement: select * from person;
-----------------------------------------------

g=# select * from person;
.......
(0 rows)

This behavior might be within spec -- but if not, it
implies a fairly serious bug ? :-}

Best regards,

--j

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

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.

Dave

j.random.programmer wrote:

>Hi all:
>
>I've been encountering some non-intuitive peculiar
>behavior
>with JDBC transaction handling.
>
>I am using the postgres 74.213 driver against a 7.4
>database.
>I've shown some illustrative code fragments below and
>also
>shown the postgresql log file corresponding to when
>the java
>code was run.
>
>Suppose our code skeleton looks like this:
>-----------------------------------------------
>try {
> Connection con = getConnection();
> con.setAutoCommit(false);
> doInserts(con); //insert values in 1..n tables
>
> con.commit(); <--- THIS FAILS SILENTLY
> }
>catch (Exception e) {
> System.out.println("ERROR COULD NOT SAVE.....");
> System.out.println("rolling back...");
> con.rollback();
> }
>
>[and within the doInserts(Connection con) method]
>
>/* using the supplied connection */
>try {
> ...insert into table #1 ...
> ...insert into table #2 ...
>
> /*
> this will cause a unique constraint exception --
> this is expected in this test
> */
> ...second insert into table #2 again ...
> }
>catch (Exception e)
> {
> e.printStackTrace();
> }
>-----------------------------------------------
>
>Here is the problem. The commit() will NEVER work and
>no data is ever saved to any table in the database.
>No error message is generated, the commit() SILENTLY
>fails to insert any data.
>
>However, if I comment out the second insert into table
>#2
>(which was causing an error), then the inserts work
>and the transaction is committed().
>
>Here is the relevant part from the postgresql server
>log.
>I've annotated it a bit to make it clearer (my
>comments
>are marked as ANNOTATION)
>
>-----------------------------------------------
>LOG: statement: set datestyle to 'ISO'; select
>version(), case when pg_encoding_to_char(1) =
>'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding()
>end;
>LOG: statement: set client_encoding = 'UNICODE'
>
>ANNOTATION: this is where we called
>setAutoCommit(false);
>
>LOG: statement: begin;
>
>ANNOTATION: this is where we insert data into various
>tables.
>
>LOG: statement: INSERT into person (dbnum, name, dob,
>age_years, age_months, sex, mr_num, hearing_loss,
>congenital, congenital_type, onset, age_at_diagnosis,
>doc_audiologic_testing, reported_by_parent, diag_oae,
>diag_oae_left, diag_oae_right, diag_abr,
>diag_soundbooth) values ('db123', 'person 1',
>'3884-02-20', 2, 2, 'm', 'mr123', 'b', 'y', 'p', 3, 5,
>'1', '1', 'n', 'p', 'a', 'y', 'n')
>LOG: statement: SELECT 1 FROM ONLY "public"."lookups"
>x WHERE "lookups_id" = $1 FOR UPDATE OF x
>LOG: statement: select
>currval('person_person_id_seq')
>LOG: statement: INSERT into eardetail (person_id,
>ear, type_lk, severity_lk, progression, fluctuating,
>stable) values (16, 'l', 1, 4, 'y', 'n', 'n')
>LOG: statement: SELECT 1 FROM ONLY "public"."person"
>x WHERE "person_id" = $1 FOR UPDATE OF x
>LOG: statement: SELECT 1 FROM ONLY "public"."lookups"
>x WHERE "lookups_id" = $1 FOR UPDATE OF x
>LOG: statement: SELECT 1 FROM ONLY "public"."lookups"
>x WHERE "lookups_id" = $1 FOR UPDATE OF x
>LOG: statement: INSERT into eardetail_pattern
>(person_id, eardetail_ear, pattern_lk) values (16,
>'l', 6)
>LOG: statement: SELECT 1 FROM ONLY
>"public"."eardetail" x WHERE "person_id" = $1 AND
>"ear" = $2 FOR UPDATE OF x
>LOG: statement: SELECT 1 FROM ONLY "public"."lookups"
>x WHERE "lookups_id" = $1 FOR UPDATE OF x
>LOG: statement: INSERT into eardetail_pattern
>(person_id, eardetail_ear, pattern_lk) values (16,
>'l', 6)
>
>ANNOTATION: this is where one of our inserts fails
>
>ERROR: duplicate key violates unique constraint
>"un_eardetail_pattern_1"
>STATEMENT: INSERT into eardetail_pattern (person_id,
>eardetail_ear, pattern_lk) values (16, 'l', 6)
>
>ANNOTATION: this is where we commit our transaction
>
>LOG: statement: commit;begin;
>LOG: statement: select * from person
>
>ANNOTATION: this above transaction commit has failed
>and NO DATA HAS BEEN WRITTEN TO ANY TABLE
>
>ANNOTATION: we run the command below from the psql
>prompt
>
>LOG: statement: select * from person;
>-----------------------------------------------
>
>g=# select * from person;
>.......
>(0 rows)
>
>
>This behavior might be within spec -- but if not, it
>implies a fairly serious bug ? :-}
>
>Best regards,
>
>--j
>
>
>
>__________________________________
>Do you Yahoo!?
>Meet the all-new My Yahoo! - Try it today!
>http://my.yahoo.com
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

> 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

On Friday 14 January 2005 11:23, Dave Cramer 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.

For completeness sake, it should be mentioned that Oracle's way of
dealing with this is no less sensible. Oracle's transactions are
atomic in the sense that all of the statements that did not raise an
error are committed atomically. If any of the statements fail,
it's up to the application programmer to decide whether or not it
makes sense to continue with the remaining statements.

This question pops up frequently. Here's a random example:

http://archives.postgresql.org/pgsql-advocacy/2004-03/threads.php#00067

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

> 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

So correct me if I'm wrong.

even in psql you are getting no error message from the commit statement?

The driver can't possibly know when something is going to fail.

Dave

j.random.programmer wrote:

>>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
>
>
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

Actually, reviewing your original post. Yes the commit fails silently.
However the insert does not fail silently and should throw an error!

Do you check for errors here ?

Dave

j.random.programmer wrote:

>>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
>
>
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

Dave:

>Actually, reviewing your original post. Yes the
commit fails
> silently. However the insert does not fail silently
and should throw
> an error! Do you check for errors here ?

I was simple catching the exception but not rolling
back since
I presumed the rest of the transaction would succeed
(and
commit() didn't complain). It's only after playing
around that I
realized that the transaction was failing because of
the earlier
error.

> So correct me if I'm wrong.
> even in psql you are getting no error message from
> the commit statement?

Here's a psql session
-----------------------------------------------
g=# create table foo (id int primary key, words text);
g=# begin;
g=# insert into foo values (1, 'hello');
g=# insert into foo values (1, 'hello');
ERROR: duplicate key violates unique constraint
"foo_pkey"
g=# end;
COMMIT
g=# select * from foo;
+----+-------+
| id | words |
+----+-------+
+----+-------+
(0 rows)
------------------------------------------------

Note, the first insert failed silently too.

So yeah, this looks like a postgres database specific
thing. But postgres is *better* than that -- the above
behavior is expected from myql BUT NOT postgres, right
?

So maybe, the database folks can do something about
this in version 8.0. Maybe you can also forward this
message
to the core postgres folks ?

> The driver can't possibly know when something is
> going to fail.

It can, since it gets an error back from the database
and "knows" the internal postgresql behavior.

Specifically and in the MEANTIME, why can't you do
the
follwing in the JDBC driver ?

----------- JDBC driver code ------------------

boolean sawExceptionInConnection = false;
String errorMessageInConnection;
....
if an error is thrown back from the database
then
sawExceptionInConnection = true;
/*the error message that was actually recieved saved
here*/
errorMessageInConnection =
"ERROR: duplicate key violates unique constraint";
....
in the commit() method implementation

if (sawExceptionInConnection)
throw new SQLException(
"postgres will not allow this commit() to succeed
since an error was recieved from the database.
The error = " + errorMessageInConnection);

----------------------------------------------
Is there any technical reason why the above
cannot/should not
be implemented ? It would be the RIGHT thing to do
since
it would get rid of SILENT failure (which is
absolutely, utterly
wrong in any database).

Best regards,

--j

__________________________________
Do you Yahoo!?
The all-new My Yahoo! - What will yours do?
http://my.yahoo.com

On January 14, 2005 01:51 pm, j.random.programmer wrote:
[...]
> I was simple catching the exception but not rolling
> back since
> I presumed the rest of the transaction would succeed
> (and
> commit() didn't complain). It's only after playing
> around that I
> realized that the transaction was failing because of
> the earlier
> error.
>
[...]
>
> So maybe, the database folks can do something about
> this in version 8.0. Maybe you can also forward this
> message
> to the core postgres folks ?
>

I *strongly* disagree. As mentioned before, a transaction is supposed to be an
*atomic* unit of work; either it succeeds completely or it's not executed at
all. Consider the canonical example of a transaction: money being tranfered
from one account to another. You don't want one update to be committed if the
other failed.

JdV!!

--
--------------------------------------------------------------
Jan de Visser jdevisser(at)digitalfairway(dot)com

Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------

j.random.programmer wrote:

>Dave:
>
>
>
>>Actually, reviewing your original post. Yes the
>>
>>
>commit fails
>
>
>>silently. However the insert does not fail silently
>>
>>
>and should throw
>
>
>>an error! Do you check for errors here ?
>>
>>
>
>I was simple catching the exception but not rolling
>back since
>I presumed the rest of the transaction would succeed
>(and
>commit() didn't complain). It's only after playing
>around that I
>realized that the transaction was failing because of
>the earlier
>error.
>
>
>
>>So correct me if I'm wrong.
>>even in psql you are getting no error message from
>>the commit statement?
>>
>>
>
>Here's a psql session
>-----------------------------------------------
>g=# create table foo (id int primary key, words text);
>g=# begin;
>g=# insert into foo values (1, 'hello');
>g=# insert into foo values (1, 'hello');
>ERROR: duplicate key violates unique constraint
>"foo_pkey"
>g=# end;
>COMMIT
>g=# select * from foo;
>+----+-------+
>| id | words |
>+----+-------+
>+----+-------+
>(0 rows)
>------------------------------------------------
>
>Note, the first insert failed silently too.
>
>
The first insert does not fail, it is the duplicate key of the second
insert that is the error.

>So yeah, this looks like a postgres database specific
>thing. But postgres is *better* than that -- the above
>behavior is expected from myql BUT NOT postgres, right
>?
>
>So maybe, the database folks can do something about
>this in version 8.0. Maybe you can also forward this
>message
>to the core postgres folks ?
>
>
They read this and will probably not respond because they do not
consider this an error.

>
>
>>The driver can't possibly know when something is
>>going to fail.
>>
>>
>
>It can, since it gets an error back from the database
>and "knows" the internal postgresql behavior.
>
>Specifically and in the MEANTIME, why can't you do
>the
>follwing in the JDBC driver ?
>
>----------- JDBC driver code ------------------
>
>boolean sawExceptionInConnection = false;
>String errorMessageInConnection;
>....
>if an error is thrown back from the database
>then
> sawExceptionInConnection = true;
> /*the error message that was actually recieved saved
>here*/
> errorMessageInConnection =
> "ERROR: duplicate key violates unique constraint";
>....
>in the commit() method implementation
>
>if (sawExceptionInConnection)
>throw new SQLException(
> "postgres will not allow this commit() to succeed
> since an error was recieved from the database.
> The error = " + errorMessageInConnection);
>
>----------------------------------------------
>Is there any technical reason why the above
>cannot/should not
>be implemented ? It would be the RIGHT thing to do
>since
>it would get rid of SILENT failure (which is
>absolutely, utterly
>wrong in any database).
>
>
This is not a silent failure. The insert errored out and gave you a
message. commit is basically just end transaction here.
The user is supposed to handle the errors, not the driver.

Dave

>Best regards,
>
>--j
>
>
>
>
>__________________________________
>Do you Yahoo!?
>The all-new My Yahoo! - What will yours do?
>http://my.yahoo.com
>
>
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

> >
> The first insert does not fail, it is the duplicate
> key of the second
> insert that is the error.

It does fail since the commit() failed silently. There
is nothing in the database at all.

> >
> They read this and will probably not respond because
> they do not
> consider this an error.

But it's contrary to what oracle, db2, sybase (I
think) and
mysql/innodb do.

It's not documented in the postgresql documentation
either
as far as I can tell.

> This is not a silent failure. The insert errored out
> and gave you a
> message. commit is basically just end transaction
> here.

But why should a insert error mean that I have to
abort
the entire transaction ? And if so, then the driver
can
warn about that, no ? (especially since the same JDBC
code
runs fine on oracle, db2, mysql/innodb and sybase).
It's
a postgres specific non-documented thing, so
shouldn't
the JDBC driver warn us as to what's going on ?

Or are you saying that I need to special case JDBC
code
of postgres ?

> The user is supposed to handle the errors, not the
> driver.

But the error is postgresql specific. There is no
error, only
a insert within a transaction failed. Why should that
affect
the rest of the transaction, if *I* as the user don't
want it to?

Best regards,

--j

__________________________________
Do you Yahoo!?
The all-new My Yahoo! - What will yours do?
http://my.yahoo.com

j.random.programmer wrote:

>>The first insert does not fail, it is the duplicate
>>key of the second
>>insert that is the error.
>>
>>
>
>It does fail since the commit() failed silently. There
>is nothing in the database at all.
>
>
What I meant to say it is not the cause of the error, only the second is
the cause of the error
now as I said before because one of the statements in the transaction
had an error none of the statements in the transaction will commit
a transaction is a unit of work, and it is atomic

http://www.postgresql.org/docs/7.4/interactive/tutorial-transactions.html

as the docs referred to above suggest.

>
>
>
>>They read this and will probably not respond because
>>they do not
>>consider this an error.
>>
>>
>
>But it's contrary to what oracle, db2, sybase (I
>think) and
>mysql/innodb do.
>
>It's not documented in the postgresql documentation
>either
>as far as I can tell.
>
>
>
>>This is not a silent failure. The insert errored out
>>and gave you a
>>message. commit is basically just end transaction
>>here.
>>
>>
>
>But why should a insert error mean that I have to
>abort
>the entire transaction ? And if so, then the driver
>can
>warn about that, no ? (especially since the same JDBC
>code
>runs fine on oracle, db2, mysql/innodb and sybase).
>It's
>a postgres specific non-documented thing, so
>shouldn't
>the JDBC driver warn us as to what's going on ?
>
>Or are you saying that I need to special case JDBC
>code
>of postgres ?
>
>
>
Because transactions in postgresql are ATOMIC. It's all or nothing.

How do you handle the case of moving money from one bank account to
another ( for example) and the second update fails.

ie
update bank1 set balance=balance-20;
update bank2 set balance=balance+20;

Does the money just dissappear ? What if the program can't "fix" the
problem. ie the disk is full.

I understand you have an investment in your code, but this is the way
postgres works, no amount of emails are going to change that fact.

>>The user is supposed to handle the errors, not the
>>driver.
>>
>>
>
>But the error is postgresql specific. There is no
>error, only
>a insert within a transaction failed. Why should that
>affect
>the rest of the transaction, if *I* as the user don't
>want it to?
>
>
>
How does postgres know which parts you don't care about ?

Dave

>Best regards,
>
>--j
>
>
>
>__________________________________
>Do you Yahoo!?
>The all-new My Yahoo! - What will yours do?
>http://my.yahoo.com
>
>
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

j.random.programmer wrote:

> Here is the problem. The commit() will NEVER work and
> no data is ever saved to any table in the database.
> No error message is generated, the commit() SILENTLY
> fails to insert any data.
>
> However, if I comment out the second insert into table
> #2
> (which was causing an error), then the inserts work
> and the transaction is committed().

When postgresql hits an error, the transaction is marked for rollback
and all subsequent queries in that transaction will fail. A subsequent
COMMIT will not actually commit; it will roll back. There are arguments
both ways about whether this is a good idea (mostly correctness vs.
compatibility with other systems), but that's the way it is and the way
it has been for ages. Don't ignore errors from your queries!

...

It might be worthwhile having commit() throw an exception if the
transaction did not actually commit, rather than only reporting
server-generated errors. What do people think?

Pre-7.4 returns a COMMIT status for any COMMIT even if the transaction
actually rolled back, and the v2 protocol has no mechanism to detect
transactions that have failed. So the only way to detect this would be
to track transaction state internally -- seems a bit ugly and unreliable.

7.4 returns COMMIT for rolled-back COMMITs, but does report transactions
that have failed via the v3 protocol. 8.0 returns ROLLBACK for
rolled-back COMMITs and also uses the v3 protocol. So it should be
possible to detect this case for both 7.4 and 8.0 reasonably easily.

...

Also in 8.0 and later, there is savepoint support that helps with this
case. The pattern to use is something like this:

establish savepoint
INSERT ....;
if insert caused an error:
rollback to savepoint
else:
release savepoint

See java.sql.Savepoint, and the Postgres docs on SAVEPOINT for more info.

That pattern will cause a subtransaction to be started for the INSERT.
If the INSERT fails, and we ROLLBACK TO SAVEPOINT, then all the results
of the INSERT (including the marking-txn-for-rollback) are discarded and
your original transaction can continue.

There is a performance cost when using savepoints, but I don't know how
large.

It'd be possible to have optional "automatic savepoint wrapping" in the
driver, where every user query was transparently wrapped in
subtransaction. You might prefer to write the code to make the driver do
this, rather than change your application.

-O

On Friday 14 January 2005 16:38, Oliver Jowett wrote:
> It might be worthwhile having commit() throw an exception if the
> transaction did not actually commit, rather than only reporting
> server-generated errors. What do people think?

Sounds like a good idea.

> It'd be possible to have optional "automatic savepoint wrapping" in the
> driver, where every user query was transparently wrapped in
> subtransaction. You might prefer to write the code to make the driver do
> this, rather than change your application.

Also seems like a useful feature at first blush.

Vadim Nasardinov wrote:

>On Friday 14 January 2005 16:38, Oliver Jowett wrote:
>
>
>>It might be worthwhile having commit() throw an exception if the
>>transaction did not actually commit, rather than only reporting
>>server-generated errors. What do people think?
>>
>>
>
>Sounds like a good idea.
>
>
>
>>It'd be possible to have optional "automatic savepoint wrapping" in the
>>driver, where every user query was transparently wrapped in
>>subtransaction. You might prefer to write the code to make the driver do
>>this, rather than change your application.
>>
>>
>
>Also seems like a useful feature at first blush.
>
>
>
I'd hope this was optional, I certainly don't want every statement
wrapped in a savepoint.

I see no point in either of these as the solution is simple... Don't
ignore errors.
However I wouldn't argue if the first was implemented. The second is
questionable due to the extra code complexity and the overhead imposed.
How many savepoints can the system handle ? What if I have a huge
transaction ?

Dave

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

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

On Friday 14 January 2005 17:12, Dave Cramer wrote:
> I see no point in either of these as the solution is simple... Don't
> ignore errors.

This is a misrepresentation of the other side's argument.

I mentioned this thread earlier in this discussion:
http://archives.postgresql.org/pgsql-advocacy/2004-03/threads.php#00067

Consider, in particular,
http://archives.postgresql.org/pgsql-advocacy/2004-03/msg00070.php

The guy is most emphatically _not_ ignoring errors. Wouldn't you
agree?

The point of the solutions that Oliver proposed is not hard to see. I
can write code that works unchanged with Oracle, Sybase, DB2,
MySQL/InnoDB, Firebird and god knows what else. As soon as I throw
PostgreSQL into the mix, I need to handle a radically different
transaction semantics all of a sudden. Oliver's proposal obviates the
need for special-casing PostgreSQL in my application code, albeit
admittedly at the expense of incurring a measurable performance hit.
Which is fine with me, as long as I'm informed of the tradeoff.

YMMV.

Hi:

> fail. A subsequent
> COMMIT will not actually commit; it will roll back.
> ...snip...
> it has been for ages. Don't ignore errors from your
> queries!

ok !

> It might be worthwhile having commit() throw an
> exception if the
> transaction did not actually commit, rather than
> only reporting
> server-generated errors. What do people think?

Yup. But if that's too much of a dirty hack, then an
alternative
is to put the current behavior in the JDBC FAQ section
located
at:

http://jdbc.postgresql.org/documentation/faq.html

>
> 7.4 returns COMMIT for rolled-back COMMITs, but does
> report transactions
> that have failed via the v3 protocol. 8.0 returns
> ROLLBACK for
> rolled-back COMMITs and also uses the v3 protocol.
> So it should be
> possible to detect this case for both 7.4 and 8.0
> reasonably easily.

Cool, then maybe the 8.x driver should do so. And the
current
driver's (or actually the postgres database's)
behavior can
be described in the FAQ ? I think that would be _very_
useful
for newbies who might otherwise be bit by this
behavior and/or
post the same question again and again to the mailing
list.

> Also in 8.0 and later, there is savepoint support
> that helps with this
> case. The pattern to use is something like this:
> [....snip lots of info...]

Thanks for that additional info.

Best regards,

--j

__________________________________
Do you Yahoo!?
The all-new My Yahoo! - What will yours do?
http://my.yahoo.com

Vadim Nasardinov <vadimn(at)redhat(dot)com> writes:
> On Friday 14 January 2005 16:38, Oliver Jowett wrote:
>> It might be worthwhile having commit() throw an exception if the
>> transaction did not actually commit, rather than only reporting
>> server-generated errors. What do people think?

> Sounds like a good idea.

Doesn't the JDBC spec have anything to say about what this should do?

regards, tom lane

Dave Cramer wrote:
>
>>On Friday 14 January 2005 16:38, Oliver Jowett wrote:
>>
>>>It'd be possible to have optional "automatic savepoint wrapping" in the
>>>driver,

> However I wouldn't argue if the first was implemented. The second is
> questionable due to the extra code complexity and the overhead imposed.
> How many savepoints can the system handle ? What if I have a huge
> transaction ?

That's why I said "optional" -- you'd only really want this for
compatibility with other systems.

-O

Tom Lane wrote:
> Vadim Nasardinov <vadimn(at)redhat(dot)com> writes:
>
>>On Friday 14 January 2005 16:38, Oliver Jowett wrote:
>>
>>>It might be worthwhile having commit() throw an exception if the
>>>transaction did not actually commit, rather than only reporting
>>>server-generated errors. What do people think?
>
>
>>Sounds like a good idea.
>
>
> Doesn't the JDBC spec have anything to say about what this should do?

The JDBC spec is vague as usual.

The Connection.commit() javadoc says:

===

Makes all changes made since the previous commit/rollback permanent
and releases any database locks currently held by this Connection
object. This method should be used only when auto-commit mode has been
disabled.

Throws:
SQLException - if a database access error occurs or this
Connection object is in auto-commit mode

===

What counts as a "database access error"? We currently throw on
communication errors or server-generated errors only. A previous error
causing transaction rollback doesn't seem like an access error really,
but given that you can get other errors thrown when the transaction does
not commit for other reasons, I'm not sure why that case should be
different.

-O

[snip]
> > >
> > But the error is postgresql specific. There is no
> > error, only
> > a insert within a transaction failed. Why should that
> > affect
> > the rest of the transaction, if *I* as the user don't
> > want it to?
> >
> >
> How does postgres know which parts you don't care about ?
>

Dave, this argument goes over and over again on the postgres lists, but
basically the answer is simple: if I want to roll back, than I will
issue a roll-back command on catching the error, and if I want to commit
the previously successful part, I issue commit.
So postgres does not need to do any black magic, just commit if I say
commit, and roll back only if I say so. This is how it works in Oracle &
co, and it is very useful where an error will only make the transaction
take a different path instead of failing.
The banking example is simple: if I catch an error, I will roll back
instead of commit. In any case, the choice is mine and the server should
not decide for me.

Now it's a different problem that postgres has no support for this, and
I understand that, I just don't understand the uproar in the developers
corner each time somebody tries to explain that the above feature is not
against the transaction atomicity principle but it gives more choice to
the programmer. It is an interpretation issue about what is a
transaction: all the executed SQLs, or just the set of successful SQLs ?
I don't think the specs have anything clear to say about this, and then
it's a subjective matter, and both parties should accept that the other
version is also valid, instead of bashing the other.

Now that savepoints were implemented, it is possible in postgres too to
design a transaction with different SUCCESS path on an error. It was not
possible before other than retry the whole transaction again. However,
it still needs special code for postgres compared to Oracle ( I can't
compare with others as I only used Oracle).

So I would also vote to make it an optional feature of the postgres JDBC
driver to automatically wrap each statement in a savepoint, even if it
does have a performance hit. The default behavior should be no wrapping,
but it would help a lot with rapidly porting from Oracle.

Cheers,
Csaba