CODEWITHSUNDEEP
javapostgresqljdbctransactionshibernateexception
Gus
Gus

Reputation: 251

Postgres aborts transactions and ignores queries until the transaction is ended, is it possible to process the transaction before its rolled back?

I'm trying to improve upon error handling in a Java/Postgres application and an error that occurs frequently is input being too long for a column with a fixed length. I roll the transaction back when this occurs, but before the rollback is done I'm trying to find a way determine which column is violating length constraints to make debugging this error a lot easier and more efficient.

My approach is to:

  1. Query informationschema.columns to get all column names of the length displayed in the error message
  2. Query each column returned in step 1 to check length constraints

However, when i attempt to query the information schema I get this error: 

ERROR: current transaction is aborted, commands ignored until end of transaction block

Is there a way to still run queries from java on this transaction, or is there a better way to go about retrieving the columns violating their length constraints?

Upvotes: 0

Views: 273

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324691

It's sort-of possible to do what you want.

You can create a SAVEPOINT before every statement, then ROLLBACK TO SAVEPOINT after and error and continue.

This creates significant overhead for the transaction, so it's not usually a great idea, but there are occasions where it's useful.

In this case I'd just ROLLBACK the whole transaction then query the table state, or query it from another session. This is fine for information-only purposes unless you're doing DDL from within the transaction. If you are doing DDL from within the transaction of interest, consider grouping the DDL up and doing a SAVEPOINT after it before running the DML, so you can ROLLBACK TO SAVEPOINT and query the tables as they looked to the transaction.

You'll be interested in knowing that there's work going on to support more structured error messages from PostgreSQL, work that should carry over into PgJDBC to allow much more detail to be obtained about errors - including things like the table(s) affected, constraint(s) violated, etc.

Upvotes: 1

Related Questions