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