> On 19 Jun 2024, at 19:56, Rich Shepard <rshepard@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
executeknown-to-be-good sequences of SQL statements, but in case of errors it can be difficult to debug (although
PostgreSQLis 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
differentsession even. It won’t affect the failed transaction from the original session, which would have rolled back
automaticallywhen 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
statementsin the SQL, add (named) SAVEPOINTs where you’re unsure of the results so that you can roll back to those
specificpoints 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
modein psql that allows you to import an SQL file and step through the statements one by one. That would be helpful in
yourcase 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.