Subject: Transaction issue

From: Rich Shepard <[email-masked]>
Date: Wed, 19 Jun 2024 10:56:25 -0700 (PDT)

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

I issue a rollback; command but cannot continue processing. What is the
appropriate way to respond to that error after fixing the syntax error?

TIA,

Rich

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 19 Jun 2024 10:58:19 -0700

> ERROR:  current transaction is aborted, commands ignored until end of 

Is this being done in a script fed to psql?

-- 
Adrian Klaver
[email-masked]

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 19 Jun 2024 11:07:06 -0700

> appropriate way to respond to that error after fixing the syntax error?

Simplest process, after rollback you fix the problem and start again from
the top of the transaction.

David J.

---

From: Alban Hertroys <[email-masked]>
Date: Wed, 19 Jun 2024 22:20:45 +0200

> On 19 Jun 2024, at 19:56, Rich Shepard <[email-masked]> wrote:
> 

The error prior to those statements is what you need to look at. That’s what’s causing the transaction to fail.

> 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.

---

From: Rich Shepard <[email-masked]>
Date: Wed, 19 Jun 2024 13:33:18 -0700 (PDT)

> what’s causing the transaction to fail.

Alban/Adrian,

> I get the impression that you’re executing shell scripts that run the psql
> command-line utility.

That's what I do as I've not taken the time to finish the GUI with TKinter.

Here's the latest example:
bustrac=# \i insert-law-offices-addr.sql 
psql:insert-law-offices-addr.sql:1: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:insert-law-offices-addr.sql:69: ERROR:  current transaction is aborted, commands ignored until end of transaction block

Line 1 is the BEGIN; statement; line 69 is the last row of data to be
inserted.

Thanks,

Rich

---

From: Ron Johnson <[email-masked]>
Date: Wed, 19 Jun 2024 16:57:48 -0400

On Wed, Jun 19, 2024 at 4:33 PM Rich Shepard <[email-masked]>
wrote:

> psql:insert-law-offices-addr.sql:1: ERROR:  current transaction is
> aborted, commands ignored until end of transaction block
> psql:insert-law-offices-addr.sql:69: ERROR:  current transaction is
> aborted, commands ignored until end of transaction block
>

The problem is that you don't know where it's failing.

I suggest you run "\echo all"  before running "\i
insert-law-offices-addr.sql".  That way, you'll see which line it barfs on.

---

From: Rich Shepard <[email-masked]>
Date: Wed, 19 Jun 2024 14:39:02 -0700 (PDT)

> The problem is that you don't know where it's failing.

Ron,

True that. There's no specificity to why the transaction didn't complete.

> insert-law-offices-addr.sql".  That way, you'll see which line it barfs on.

Good point. I'll do that. In the meantime, commenting out (or removing) the
BEGIN; command inserts all rows without error.

Regards,

Rich

---

From: Ron Johnson <[email-masked]>
Date: Wed, 19 Jun 2024 17:44:30 -0400

> BEGIN; command inserts all rows without error.
In addition, manually run the "BEGIN;" before the "\i insert-blarge.sql"
command.

That way, insert-blarge.sql just inserts.  My reasoning: since you control
the ROLLBACK, you should also control the BEGIN.

---

From: Rich Shepard <[email-masked]>
Date: Thu, 20 Jun 2024 06:06:56 -0700 (PDT)

Ron,

Hadn't thought of doing that, but now will. Thanks for the excellent
recomendation.

Regards,

Rich