Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: Transaction issue
From: Alban Hertroys <haramrae@gmail.com>
In-Reply-To: <b77e58b-dd8c-373b-2a25-9eb224488e8b@appl-ecosys.com>
Date: Wed, 19 Jun 2024 22:20:45 +0200
Cc: PostgreSQL General <pgsql-general@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <65F95FD4-CBEA-4E66-AE63-EBC9F617C988@gmail.com>
References: <b77e58b-dd8c-373b-2a25-9eb224488e8b@appl-ecosys.com>
To: Rich Shepard <rshepard@appl-ecosys.com>
Archived-At: <https://www.postgresql.org/message-id/65F95FD4-CBEA-4E66-AE63-EBC9F617C988%40gmail.com>
Precedence: bulk


> On 19 Jun 2024, at 19:56, Rich Shepard <rshepard@appl-ecosys.com> =
wrote:
>=20
> 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=E2=80=99s what=E2=80=99s 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=E2=80=99re executing shell scripts that =
run the psql command-line utility. That=E2=80=99s 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=E2=80=99s errors).

If a rollback isn=E2=80=99t done from the same psql session, then =
you=E2=80=99re performing it from a different transaction - a different =
session even. It won=E2=80=99t affect the failed transaction from the =
original session, which would have rolled back automatically when that =
session closed.

Instead, I=E2=80=99d 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=E2=80=99re 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=E2=80=99s 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.