0

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?

Jim Garrison
  • 85,615
  • 20
  • 155
  • 190
Gus
  • 241
  • 1
  • 6
  • 17
  • Why not do it the other way round? The column lengths on the db can't change can they? Just cache the column lengths from the database schema the first time the query runs and then, when the transaction fails for this reason, just compare the lengths of the input fields against the values you retrieved earlier. – DaveH Jun 07 '13 at 21:51
  • See this: http://stackoverflow.com/questions/2741919/can-i-ask-postgresql-to-ignore-errors-within-a-transaction – Denis de Bernardy Jun 08 '13 at 10:00

1 Answers1

1

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778