Postgresql Exception Handling

[trevorh]

I have been using JBoss-2.4.0_Tomcat-3.2.3 with Postgresql 7.1.2. In my code I sometimes drop a table which does not exist creating a SQLException which I catch but just ignore. Unfortunatly this seems to
abort the current database transaction and progresql quietly ignores all sql commands until the end of the user transaction. When the transactionis closed a TransactionRolledbackException is not thrown although postgresql has rolled back the database transaction.

According the the ejb spec secition 12.1.2 an application exception should not automatically rollback a transaction. The hypersonic database does not appear
to have this problem.

Is there any way to stop postgresql from automatically rolling back when it raises an SQLException. I think the correct behaviour would be for the Transaction Manager to commit or roll back the database at the end of the user transaction.

[davidjencks]

If Hypersonic throws an exception when you try to drop a nonexistent table, this is a problem with Postgres.

1. You might check for the existance of the table with DatabaseMetaData.getTables before you try to drop it.

2. Any mixing of DDL and DML in the same transaction is not likely to be very portable between rdbms. As another example besides Postgre's behavior, Firebird/Interbase executes all DDL at the end of a transaction, after all DML is complete.