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

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