SEO

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

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...eads.php#00067

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

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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...eads.php#00067

Consider, in particular,
http://archives.postgresql.org/pgsql...3/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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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@postgresql.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 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.

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

Vadim Nasardinov <vadimn@redhat.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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Tom Lane wrote:
> Vadim Nasardinov <vadimn@redhat.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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match