public inbox for [email protected]  

help / color / mirror / Atom feed

From: Alban Hertroys <[email protected]>
To: Rich Shepard <[email protected]>
Cc: PostgreSQL General <[email protected]>
Subject: Re: Transaction issue
Date: Wed, 19 Jun 2024 22:20:45 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>


> On 19 Jun 2024, at 19:56, Rich Shepard <[email protected]> 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.

view thread (9+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Transaction issue
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox