Thread: Insert/Update that doesn't
===========
The code:
==========
public boolean store( boolean addNew )
{
servlet.log("Category1(Store): save, addnew = " + (addNew
? "true" : "false") );
String sqlCommand = "";
if ( addNew ) {
sqlCommand = "INSERT INTO categories(ident, category,
description) VALUES(?,?,?)";
} else {
sqlCommand = "UPDATE categories SET " +
"category=?, " +
"description=? " +
" WHERE ident=?";
}
servlet.log("Category1(Store): Command=" + sqlCommand );
java.lang.Exception exception = null;
javax.sql.DataSource ds = servlet.findDataSource( null );
if ( ds != null ) {
servlet.log("Category1(Store): Connecting..." );
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = ds.getConnection();
servlet.log("Category1(Store): Connected." );
stmt = conn.prepareStatement(sqlCommand);
if ( addNew ) {
stmt.setString(1,category.getId().trim());
stmt.setString(2,category.getName());
stmt.setString(3,category.getDescription());
} else {
stmt.setString(1,category.getName());
stmt.setString(2,category.getDescription());
stmt.setString(3,category.getId().trim());
}
servlet.log("Category1(Store): Store..." );
int upcount = stmt.executeUpdate();
servlet.log("Category1(Store): Done " + upcount );
SQLWarning w = stmt.getWarnings();
if ( w != null ) {
servlet.log("Category1(Store): warnings..." +
w.toString() );
}
} catch ( Exception ex ) {
exception = ex;
} finally {
if ( stmt != null ) {
try {
servlet.log("Category1(Store): Closing statement..." );
stmt.close();
servlet.log("Category1(Store): Closed" );
} catch ( Exception ex ) {
// Assume that the first exception caught is the
worst
if ( exception == null ) exception = ex;
}
}
if ( conn != null ) {
try {
servlet.log("Category1(Store): Closing connection..." );
conn.close();
servlet.log("Category1(Store): Closed..." );
} catch ( Exception ex ) {
// Assume that the first exception caught is the
worst
if ( exception == null ) exception = ex;
}
}
} // end finally
if ( exception != null ) {
servlet.log("CATEGORYACTION: Exception - " +
exception.getMessage() );
return false;
}
}
return true;
}
==========
The Trace:
==========
2001-12-06 20:12:15 action: SAVECATEGORY: entering perform()
2001-12-06 20:12:15 action: Save category - ID=00001
2001-12-06 20:12:15 action: Save category - Name=foo123
2001-12-06 20:12:15 action: Category1(Store): save, addnew = false
2001-12-06 20:12:15 action: Category1(Store): Command=UPDATE categories
SET category= ?, description= ? WHERE ident= ?2001-12-06 20:12:15
action: Category1(Store): Connecting...
2001-12-06 20:12:15 action: Category1(Store): Connected.
2001-12-06 20:12:15 action: Category1(Store): Store...
2001-12-06 20:12:15 action: Category1(Store): Done 1
2001-12-06 20:12:15 action: Category1(Store): Closing statement...
2001-12-06 20:12:15 action: Category1(Store): Closed
2001-12-06 20:12:15 action: Category1(Store): Closing connection...
2001-12-06 20:12:15 action: Category1(Store): Closed...
===============
The Server Log:
===============
011206.20:10:27.632 [2694] ProcessQuery
011206.20:10:27.637 [2694] CommitTransactionCommand
011206.20:12:15.390 [2458] StartTransactionCommand
011206.20:12:15.391 [2458] query: UPDATE categories SET category=
'foo123', description= 'Paper documents' WHERE ident= '00001'
011206.20:12:15.392 [2458] ProcessQuery
011206.20:12:15.400 [2458] CommitTransactionCommand
011206.20:12:15.405 [2458] StartTransactionCommand
011206.20:12:15.405 [2458] query: rollback
011206.20:12:15.405 [2458] ProcessUtility: rollback
011206.20:12:15.406 [2458] CommitTransactionCommand
011206.20:12:15.408 [2458] StartTransactionCommand
011206.20:12:15.408 [2458] query: begin
011206.20:12:15.408 [2458] ProcessUtility: begin
011206.20:12:15.408 [2458] CommitTransactionCommand
011206.20:12:15.410 [2458] StartTransactionCommand
011206.20:12:15.410 [2458] query: SET TRANSACTION ISOLATION LEVEL READ
COMMITTED
011206.20:12:15.410 [2458] ProcessUtility: SET TRANSACTION ISOLATION
LEVEL READ COMMITTED
011206.20:12:15.410 [2458] CommitTransactionCommand
HOWEVER:
--------
011206.20:13:23.535 [2694] StartTransactionCommand
011206.20:13:23.535 [2694] query: select * from categories;
011206.20:13:23.537 [2694] ProcessQuery
011206.20:13:23.538 [2694] CommitTransactionCommand
011206.20:13:54.033 [2694] StartTransactionCommand
011206.20:13:54.033 [2694] query: update categories set category='paper'
where ident='00001';
011206.20:13:54.037 [2694] ProcessQuery
011206.20:13:54.038 [2694] CommitTransactionCommand
011206.20:30:47.148 [2694] proc_exit(0)
011206.20:30:47.148 [2694] shmem_exit(0)
011206.20:30:47.148 [2694] exit(0)
==========
SYNOPSIS:
==========
First I ran an update against categories from a JDBC client in Tomcat. The
Tomcat trace indicates that the update succeeded. It lies. Nothing gets
changed.
Running an equivalent command from psql works fine.
One thing I notice is that the JDBC request is a lot more complex in terms
of what the backend does for it, and part of that complexity looks like a
transaction rollback undoing the update.
Can anyone explain what's going on here? It's really frustrating when you
do everything "right", get (apparently) no errors, and yet it doesn't
work.
The exact same thing happens with non-parameterized JDBC SQL, BTW.
Thanks,
Tim Holloway
Tim,
My guess is that you are getting the connection with autocommit turned
off. So unless you explicitly commit the update it will do the rollback.
You can try changing the autocommit to true, and do the update, or
alternatively
Do an stmt.commit() at the end
Dave
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Tim Holloway
Sent: Thursday, December 06, 2001 8:40 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Insert/Update that doesn't
===========
The code:
==========
public boolean store( boolean addNew )
{
servlet.log("Category1(Store): save, addnew = " + (addNew
? "true" : "false") );
String sqlCommand = "";
if ( addNew ) {
sqlCommand = "INSERT INTO categories(ident, category,
description) VALUES(?,?,?)";
} else {
sqlCommand = "UPDATE categories SET " +
"category=?, " +
"description=? " +
" WHERE ident=?";
}
servlet.log("Category1(Store): Command=" + sqlCommand );
java.lang.Exception exception = null;
javax.sql.DataSource ds = servlet.findDataSource( null );
if ( ds != null ) {
servlet.log("Category1(Store): Connecting..." );
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = ds.getConnection();
servlet.log("Category1(Store): Connected." );
stmt = conn.prepareStatement(sqlCommand);
if ( addNew ) {
stmt.setString(1,category.getId().trim());
stmt.setString(2,category.getName());
stmt.setString(3,category.getDescription());
} else {
stmt.setString(1,category.getName());
stmt.setString(2,category.getDescription());
stmt.setString(3,category.getId().trim());
}
servlet.log("Category1(Store): Store..." );
int upcount = stmt.executeUpdate();
servlet.log("Category1(Store): Done " + upcount );
SQLWarning w = stmt.getWarnings();
if ( w != null ) {
servlet.log("Category1(Store): warnings..." +
w.toString() );
}
} catch ( Exception ex ) {
exception = ex;
} finally {
if ( stmt != null ) {
try {
servlet.log("Category1(Store): Closing statement..." );
stmt.close();
servlet.log("Category1(Store): Closed" );
} catch ( Exception ex ) {
// Assume that the first exception caught is the
worst
if ( exception == null ) exception = ex;
}
}
if ( conn != null ) {
try {
servlet.log("Category1(Store): Closing connection..." );
conn.close();
servlet.log("Category1(Store): Closed..." );
} catch ( Exception ex ) {
// Assume that the first exception caught is the
worst
if ( exception == null ) exception = ex;
}
}
} // end finally
if ( exception != null ) {
servlet.log("CATEGORYACTION: Exception - " +
exception.getMessage() );
return false;
}
}
return true;
}
==========
The Trace:
==========
2001-12-06 20:12:15 action: SAVECATEGORY: entering perform()
2001-12-06 20:12:15 action: Save category - ID=00001
2001-12-06 20:12:15 action: Save category - Name=foo123
2001-12-06 20:12:15 action: Category1(Store): save, addnew = false
2001-12-06 20:12:15 action: Category1(Store): Command=UPDATE categories
SET category= ?, description= ? WHERE ident= ?2001-12-06 20:12:15
action: Category1(Store): Connecting...
2001-12-06 20:12:15 action: Category1(Store): Connected.
2001-12-06 20:12:15 action: Category1(Store): Store...
2001-12-06 20:12:15 action: Category1(Store): Done 1
2001-12-06 20:12:15 action: Category1(Store): Closing statement...
2001-12-06 20:12:15 action: Category1(Store): Closed
2001-12-06 20:12:15 action: Category1(Store): Closing connection...
2001-12-06 20:12:15 action: Category1(Store): Closed...
===============
The Server Log:
===============
011206.20:10:27.632 [2694] ProcessQuery
011206.20:10:27.637 [2694] CommitTransactionCommand
011206.20:12:15.390 [2458] StartTransactionCommand
011206.20:12:15.391 [2458] query: UPDATE categories SET category=
'foo123', description= 'Paper documents' WHERE ident= '00001'
011206.20:12:15.392 [2458] ProcessQuery
011206.20:12:15.400 [2458] CommitTransactionCommand
011206.20:12:15.405 [2458] StartTransactionCommand
011206.20:12:15.405 [2458] query: rollback
011206.20:12:15.405 [2458] ProcessUtility: rollback
011206.20:12:15.406 [2458] CommitTransactionCommand
011206.20:12:15.408 [2458] StartTransactionCommand
011206.20:12:15.408 [2458] query: begin
011206.20:12:15.408 [2458] ProcessUtility: begin
011206.20:12:15.408 [2458] CommitTransactionCommand
011206.20:12:15.410 [2458] StartTransactionCommand
011206.20:12:15.410 [2458] query: SET TRANSACTION ISOLATION LEVEL READ
COMMITTED
011206.20:12:15.410 [2458] ProcessUtility: SET TRANSACTION ISOLATION
LEVEL READ COMMITTED
011206.20:12:15.410 [2458] CommitTransactionCommand
HOWEVER:
--------
011206.20:13:23.535 [2694] StartTransactionCommand
011206.20:13:23.535 [2694] query: select * from categories;
011206.20:13:23.537 [2694] ProcessQuery
011206.20:13:23.538 [2694] CommitTransactionCommand
011206.20:13:54.033 [2694] StartTransactionCommand
011206.20:13:54.033 [2694] query: update categories set
category='paper'
where ident='00001';
011206.20:13:54.037 [2694] ProcessQuery
011206.20:13:54.038 [2694] CommitTransactionCommand
011206.20:30:47.148 [2694] proc_exit(0)
011206.20:30:47.148 [2694] shmem_exit(0)
011206.20:30:47.148 [2694] exit(0)
==========
SYNOPSIS:
==========
First I ran an update against categories from a JDBC client in Tomcat.
The
Tomcat trace indicates that the update succeeded. It lies. Nothing gets
changed.
Running an equivalent command from psql works fine.
One thing I notice is that the JDBC request is a lot more complex in
terms
of what the backend does for it, and part of that complexity looks like
a
transaction rollback undoing the update.
Can anyone explain what's going on here? It's really frustrating when
you
do everything "right", get (apparently) no errors, and yet it doesn't
work.
The exact same thing happens with non-parameterized JDBC SQL, BTW.
Thanks,
Tim Holloway
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Tim,
I don't see any commit happening. If you want the changes to be final,
you need to commit them. The difference between psql and jdbc can be
explained by the fact that by default psql runs in autocommit mode (each
statement is automatically commited when executed), and it appears that
your jdbc is running in non-autocommit mode (see setAutoCommit()).
thanks,
--Barry
Tim Holloway wrote:
> ===========
> The code:
> ==========
>
> public boolean store( boolean addNew )
> {
> servlet.log("Category1(Store): save, addnew = " + (addNew
> ? "true" : "false") );
> String sqlCommand = "";
> if ( addNew ) {
> sqlCommand = "INSERT INTO categories(ident, category,
> description) VALUES(?,?,?)";
> } else {
> sqlCommand = "UPDATE categories SET " +
> "category=?, " +
> "description=? " +
> " WHERE ident=?";
> }
> servlet.log("Category1(Store): Command=" + sqlCommand );
> java.lang.Exception exception = null;
> javax.sql.DataSource ds = servlet.findDataSource( null );
> if ( ds != null ) {
> servlet.log("Category1(Store): Connecting..." );
>
> Connection conn = null;
> PreparedStatement stmt = null;
> try {
> conn = ds.getConnection();
> servlet.log("Category1(Store): Connected." );
> stmt = conn.prepareStatement(sqlCommand);
> if ( addNew ) {
> stmt.setString(1,category.getId().trim());
> stmt.setString(2,category.getName());
> stmt.setString(3,category.getDescription());
> } else {
> stmt.setString(1,category.getName());
> stmt.setString(2,category.getDescription());
> stmt.setString(3,category.getId().trim());
> }
> servlet.log("Category1(Store): Store..." );
> int upcount = stmt.executeUpdate();
> servlet.log("Category1(Store): Done " + upcount );
> SQLWarning w = stmt.getWarnings();
> if ( w != null ) {
> servlet.log("Category1(Store): warnings..." +
> w.toString() );
> }
> } catch ( Exception ex ) {
> exception = ex;
> } finally {
> if ( stmt != null ) {
> try {
> servlet.log("Category1(Store): Closing statement..." );
> stmt.close();
> servlet.log("Category1(Store): Closed" );
> } catch ( Exception ex ) {
> // Assume that the first exception caught is the
> worst
> if ( exception == null ) exception = ex;
> }
> }
> if ( conn != null ) {
> try {
> servlet.log("Category1(Store): Closing connection..." );
> conn.close();
> servlet.log("Category1(Store): Closed..." );
> } catch ( Exception ex ) {
> // Assume that the first exception caught is the
> worst
> if ( exception == null ) exception = ex;
> }
> }
> } // end finally
> if ( exception != null ) {
> servlet.log("CATEGORYACTION: Exception - " +
> exception.getMessage() );
> return false;
> }
> }
> return true;
> }
>
>
> ==========
> The Trace:
> ==========
>
> 2001-12-06 20:12:15 action: SAVECATEGORY: entering perform()
> 2001-12-06 20:12:15 action: Save category - ID=00001
> 2001-12-06 20:12:15 action: Save category - Name=foo123
> 2001-12-06 20:12:15 action: Category1(Store): save, addnew = false
> 2001-12-06 20:12:15 action: Category1(Store): Command=UPDATE categories
> SET category= ?, description= ? WHERE ident= ?2001-12-06 20:12:15
> action: Category1(Store): Connecting...
> 2001-12-06 20:12:15 action: Category1(Store): Connected.
> 2001-12-06 20:12:15 action: Category1(Store): Store...
> 2001-12-06 20:12:15 action: Category1(Store): Done 1
> 2001-12-06 20:12:15 action: Category1(Store): Closing statement...
> 2001-12-06 20:12:15 action: Category1(Store): Closed
> 2001-12-06 20:12:15 action: Category1(Store): Closing connection...
> 2001-12-06 20:12:15 action: Category1(Store): Closed...
>
> ===============
> The Server Log:
> ===============
>
> 011206.20:10:27.632 [2694] ProcessQuery
> 011206.20:10:27.637 [2694] CommitTransactionCommand
> 011206.20:12:15.390 [2458] StartTransactionCommand
> 011206.20:12:15.391 [2458] query: UPDATE categories SET category=
> 'foo123', description= 'Paper documents' WHERE ident= '00001'
> 011206.20:12:15.392 [2458] ProcessQuery
> 011206.20:12:15.400 [2458] CommitTransactionCommand
> 011206.20:12:15.405 [2458] StartTransactionCommand
> 011206.20:12:15.405 [2458] query: rollback
> 011206.20:12:15.405 [2458] ProcessUtility: rollback
> 011206.20:12:15.406 [2458] CommitTransactionCommand
> 011206.20:12:15.408 [2458] StartTransactionCommand
> 011206.20:12:15.408 [2458] query: begin
> 011206.20:12:15.408 [2458] ProcessUtility: begin
> 011206.20:12:15.408 [2458] CommitTransactionCommand
> 011206.20:12:15.410 [2458] StartTransactionCommand
> 011206.20:12:15.410 [2458] query: SET TRANSACTION ISOLATION LEVEL READ
> COMMITTED
> 011206.20:12:15.410 [2458] ProcessUtility: SET TRANSACTION ISOLATION
> LEVEL READ COMMITTED
> 011206.20:12:15.410 [2458] CommitTransactionCommand
>
> HOWEVER:
> --------
>
> 011206.20:13:23.535 [2694] StartTransactionCommand
> 011206.20:13:23.535 [2694] query: select * from categories;
>
>
> 011206.20:13:23.537 [2694] ProcessQuery
> 011206.20:13:23.538 [2694] CommitTransactionCommand
> 011206.20:13:54.033 [2694] StartTransactionCommand
> 011206.20:13:54.033 [2694] query: update categories set category='paper'
> where ident='00001';
> 011206.20:13:54.037 [2694] ProcessQuery
> 011206.20:13:54.038 [2694] CommitTransactionCommand
> 011206.20:30:47.148 [2694] proc_exit(0)
> 011206.20:30:47.148 [2694] shmem_exit(0)
> 011206.20:30:47.148 [2694] exit(0)
>
> ==========
> SYNOPSIS:
> ==========
>
> First I ran an update against categories from a JDBC client in Tomcat. The
> Tomcat trace indicates that the update succeeded. It lies. Nothing gets
> changed.
>
> Running an equivalent command from psql works fine.
>
> One thing I notice is that the JDBC request is a lot more complex in terms
> of what the backend does for it, and part of that complexity looks like a
> transaction rollback undoing the update.
>
> Can anyone explain what's going on here? It's really frustrating when you
> do everything "right", get (apparently) no errors, and yet it doesn't
> work.
>
> The exact same thing happens with non-parameterized JDBC SQL, BTW.
>
> Thanks,
>
> Tim Holloway
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
Never mind. I think the "autoCommit=false" line in the struts-config.xml
file answers my question!
Again, many thanks,
Tim Holloway
On Mon, 10 Dec 2001, Barry Lind wrote:
> Tim,
>
> I don't see any commit happening. If you want the changes to be final,
> you need to commit them. The difference between psql and jdbc can be
> explained by the fact that by default psql runs in autocommit mode (each
> statement is automatically commited when executed), and it appears that
> your jdbc is running in non-autocommit mode (see setAutoCommit()).
>
> thanks,
> --Barry
>
A call to commit() did the trick OK, but I'm still puzzled. Per the JDBC
javadocs: " This method should be used only when auto-commit mode has been
disabled".
There's an implication there that the default is for auto-commit to have
been ENabled, and I didn't explicitly enable OR disable auto-commit. Does
the builtin Jakarta Struts datasource pooling facility turn off
auto-commit?
Thanks!
Tim Holloway
On Mon, 10 Dec 2001, Barry Lind wrote:
> Tim,
>
> I don't see any commit happening. If you want the changes to be final,
> you need to commit them. The difference between psql and jdbc can be
> explained by the fact that by default psql runs in autocommit mode (each
> statement is automatically commited when executed), and it appears that
> your jdbc is running in non-autocommit mode (see setAutoCommit()).
>
> thanks,
> --Barry
>
>
> Tim Holloway wrote:
>
> > ===========
> > The code:
> > ==========
> >
> > public boolean store( boolean addNew )
> > {
> > servlet.log("Category1(Store): save, addnew = " + (addNew
> > ? "true" : "false") );
> > String sqlCommand = "";
> > if ( addNew ) {
> > sqlCommand = "INSERT INTO categories(ident, category,
> > description) VALUES(?,?,?)";
> > } else {
> > sqlCommand = "UPDATE categories SET " +
> > "category=?, " +
> > "description=? " +
> > " WHERE ident=?";
> > }
> > servlet.log("Category1(Store): Command=" + sqlCommand );
> > java.lang.Exception exception = null;
> > javax.sql.DataSource ds = servlet.findDataSource( null );
> > if ( ds != null ) {
> > servlet.log("Category1(Store): Connecting..." );
> >
> > Connection conn = null;
> > PreparedStatement stmt = null;
> > try {
> > conn = ds.getConnection();
> > servlet.log("Category1(Store): Connected." );
> > stmt = conn.prepareStatement(sqlCommand);
> > if ( addNew ) {
> > stmt.setString(1,category.getId().trim());
> > stmt.setString(2,category.getName());
> > stmt.setString(3,category.getDescription());
> > } else {
> > stmt.setString(1,category.getName());
> > stmt.setString(2,category.getDescription());
> > stmt.setString(3,category.getId().trim());
> > }
> > servlet.log("Category1(Store): Store..." );
> > int upcount = stmt.executeUpdate();
> > servlet.log("Category1(Store): Done " + upcount );
> > SQLWarning w = stmt.getWarnings();
> > if ( w != null ) {
> > servlet.log("Category1(Store): warnings..." +
> > w.toString() );
> > }
> > } catch ( Exception ex ) {
> > exception = ex;
> > } finally {
> > if ( stmt != null ) {
> > try {
> > servlet.log("Category1(Store): Closing statement..." );
> > stmt.close();
> > servlet.log("Category1(Store): Closed" );
> > } catch ( Exception ex ) {
> > // Assume that the first exception caught is the
> > worst
> > if ( exception == null ) exception = ex;
> > }
> > }
> > if ( conn != null ) {
> > try {
> > servlet.log("Category1(Store): Closing connection..." );
> > conn.close();
> > servlet.log("Category1(Store): Closed..." );
> > } catch ( Exception ex ) {
> > // Assume that the first exception caught is the
> > worst
> > if ( exception == null ) exception = ex;
> > }
> > }
> > } // end finally
> > if ( exception != null ) {
> > servlet.log("CATEGORYACTION: Exception - " +
> > exception.getMessage() );
> > return false;
> > }
> > }
> > return true;
> > }
> >
> >
> > ==========
> > The Trace:
> > ==========
> >
> > 2001-12-06 20:12:15 action: SAVECATEGORY: entering perform()
> > 2001-12-06 20:12:15 action: Save category - ID=00001
> > 2001-12-06 20:12:15 action: Save category - Name=foo123
> > 2001-12-06 20:12:15 action: Category1(Store): save, addnew = false
> > 2001-12-06 20:12:15 action: Category1(Store): Command=UPDATE categories
> > SET category= ?, description= ? WHERE ident= ?2001-12-06 20:12:15
> > action: Category1(Store): Connecting...
> > 2001-12-06 20:12:15 action: Category1(Store): Connected.
> > 2001-12-06 20:12:15 action: Category1(Store): Store...
> > 2001-12-06 20:12:15 action: Category1(Store): Done 1
> > 2001-12-06 20:12:15 action: Category1(Store): Closing statement...
> > 2001-12-06 20:12:15 action: Category1(Store): Closed
> > 2001-12-06 20:12:15 action: Category1(Store): Closing connection...
> > 2001-12-06 20:12:15 action: Category1(Store): Closed...
> >
> > ===============
> > The Server Log:
> > ===============
> >
> > 011206.20:10:27.632 [2694] ProcessQuery
> > 011206.20:10:27.637 [2694] CommitTransactionCommand
> > 011206.20:12:15.390 [2458] StartTransactionCommand
> > 011206.20:12:15.391 [2458] query: UPDATE categories SET category=
> > 'foo123', description= 'Paper documents' WHERE ident= '00001'
> > 011206.20:12:15.392 [2458] ProcessQuery
> > 011206.20:12:15.400 [2458] CommitTransactionCommand
> > 011206.20:12:15.405 [2458] StartTransactionCommand
> > 011206.20:12:15.405 [2458] query: rollback
> > 011206.20:12:15.405 [2458] ProcessUtility: rollback
> > 011206.20:12:15.406 [2458] CommitTransactionCommand
> > 011206.20:12:15.408 [2458] StartTransactionCommand
> > 011206.20:12:15.408 [2458] query: begin
> > 011206.20:12:15.408 [2458] ProcessUtility: begin
> > 011206.20:12:15.408 [2458] CommitTransactionCommand
> > 011206.20:12:15.410 [2458] StartTransactionCommand
> > 011206.20:12:15.410 [2458] query: SET TRANSACTION ISOLATION LEVEL READ
> > COMMITTED
> > 011206.20:12:15.410 [2458] ProcessUtility: SET TRANSACTION ISOLATION
> > LEVEL READ COMMITTED
> > 011206.20:12:15.410 [2458] CommitTransactionCommand
> >
> > HOWEVER:
> > --------
> >
> > 011206.20:13:23.535 [2694] StartTransactionCommand
> > 011206.20:13:23.535 [2694] query: select * from categories;
> >
> >
> > 011206.20:13:23.537 [2694] ProcessQuery
> > 011206.20:13:23.538 [2694] CommitTransactionCommand
> > 011206.20:13:54.033 [2694] StartTransactionCommand
> > 011206.20:13:54.033 [2694] query: update categories set category='paper'
> > where ident='00001';
> > 011206.20:13:54.037 [2694] ProcessQuery
> > 011206.20:13:54.038 [2694] CommitTransactionCommand
> > 011206.20:30:47.148 [2694] proc_exit(0)
> > 011206.20:30:47.148 [2694] shmem_exit(0)
> > 011206.20:30:47.148 [2694] exit(0)
> >
> > ==========
> > SYNOPSIS:
> > ==========
> >
> > First I ran an update against categories from a JDBC client in Tomcat. The
> > Tomcat trace indicates that the update succeeded. It lies. Nothing gets
> > changed.
> >
> > Running an equivalent command from psql works fine.
> >
> > One thing I notice is that the JDBC request is a lot more complex in terms
> > of what the backend does for it, and part of that complexity looks like a
> > transaction rollback undoing the update.
> >
> > Can anyone explain what's going on here? It's really frustrating when you
> > do everything "right", get (apparently) no errors, and yet it doesn't
> > work.
> >
> > The exact same thing happens with non-parameterized JDBC SQL, BTW.
> >
> > Thanks,
> >
> > Tim Holloway
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
> >
>
>
> There's an implication there that the default is for auto-commit to have > been ENabled, and I didn't explicitly enable OR disable auto-commit. Does > the builtin Jakarta Struts datasource pooling facility turn off > auto-commit? > I can vouch for auto-commit being enabled by default. Our apps use straight JDBC to the database & we've never encountered this problem. If you have some third-party code doing connectioon pooling in the middle, I think it is a likely candidate to check. -Nick -------------------------------------------------------------------------- Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/