> On 19 Jun 2024, at 19:56, Rich Shepard <rshep...@appl-ecosys.com> wrote: > > I now insert rows using a transaction. Sometimes psql halts with an error: > ERROR: current transaction is aborted, commands ignored until end of > transaction block
The error prior to those statements is what you need to look at. That’s what’s causing the transaction to fail. > I issue a rollback; command but cannot continue processing. What is the > appropriate way to respond to that error after fixing the syntax error? I get the impression that you’re executing shell scripts that run the psql command-line utility. That’s a great way to execute known-to-be-good sequences of SQL statements, but in case of errors it can be difficult to debug (although PostgreSQL is quite concise about it’s errors). If a rollback isn’t done from the same psql session, then you’re performing it from a different transaction - a different session even. It won’t affect the failed transaction from the original session, which would have rolled back automatically when that session closed. Instead, I’d suggest to run those statements from within psql, using \i to import your SQL file. Comment out any COMMIT statements in the SQL, add (named) SAVEPOINTs where you’re unsure of the results so that you can roll back to those specific points in the transaction, so that you can figure out where the problem originates. Alternatively, it may help to split your SQL file into chunks that you can run in sequence. Unfortunately, there’s no mode in psql that allows you to import an SQL file and step through the statements one by one. That would be helpful in your case I think. But maybe someone on the list has ideas about that? Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
