Transaction issue

On 6/19/24 10:56 AM, Rich Shepard 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

Is this being done in a script fed to psql?

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

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, Jun 19, 2024 at 10:56 AM 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

I issue a rollback; command but cannot continue processing. What is the
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.

On Wed, 19 Jun 2024, Adrian Klaver wrote:

Is this being done in a script fed to psql?

Adrian,

Yes. At the psql prompt I use the \i <filename> command to run the script.

Rich

On Wed, 19 Jun 2024, David G. Johnston wrote:

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

David,

That's what I thought I was doing when I re-entered the command to run the
script. That produced the same error because the transaction was not ended.

Rich

On 6/19/24 11:36 AM, Rich Shepard wrote:

On Wed, 19 Jun 2024, Adrian Klaver wrote:

Is this being done in a script fed to psql?

Adrian,

Yes. At the psql prompt I use the \i <filename> command to run the script.

Are there transaction statements e.g. BEGIN;, COMMIT; in the script?

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, Jun 19, 2024, 11:38 Rich Shepard <rshepard@appl-ecosys.com> wrote:

On Wed, 19 Jun 2024, David G. Johnston wrote:

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

David,

That's what I thought I was doing when I re-entered the command to run the
script. That produced the same error because the transaction was not ended.

Then I doubt your claim you sent a rollback command. Or maybe you still
have errors in the script.

David J.

On Wed, 19 Jun 2024, Adrian Klaver wrote:

Are there transaction statements e.g. BEGIN;, COMMIT; in the script?

Adrian,

Yes, each script has BEGIN; on line 1.

Rich

On 6/19/24 12:40, Rich Shepard wrote:

On Wed, 19 Jun 2024, Adrian Klaver wrote:

Are there transaction statements e.g. BEGIN;, COMMIT; in the script?

Adrian,

Yes, each script has BEGIN; on line 1.

Does it have a COMMIT; at the end?

At this point I think you need to create a simple test case where:

1) You have script with
BEGIN;
<A line that contains the syntax error>
COMMIT;

2) In psql do \i <the_script>

3) Do what you did before.

Shows us the content of the steps in your reply.

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, 19 Jun 2024, Adrian Klaver wrote:

Does it have a COMMIT; at the end?

Adrian,

No. I won't commit until I see the results are as intended.

At this point I think you need to create a simple test case where:

I killed the psql process and restarted it to allow me to work on other
issues right now; will run a test when I again get that error.

Thanks,

Rich

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

On Wed, 19 Jun 2024, Alban Hertroys wrote:

The error prior to those statements is what you need to look at. That’s
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

On 6/19/24 13:33, Rich Shepard wrote:

On Wed, 19 Jun 2024, Alban Hertroys wrote:

The error prior to those statements is what you need to look at. That’s
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

Looks to me you have a left over unresolved transaction in your psql
session. The easiest solution if that is the case is to exit the session
and start a new session to run the script.

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

Thanks,

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, 19 Jun 2024, Adrian Klaver wrote:

Looks to me you have a left over unresolved transaction in your psql session.
The easiest solution if that is the case is to exit the session and start a
new session to run the script.

Adrian, et al.:

That's what I've done. This time I commented out the BEGIN; line:
bustrac=# \i insert-law-offices-addr.sql
INSERT 0 66

There are no errors in the file but when I tried running it as a transaction
it failed.

I have not before used transactions when inserting or updating tables; I'm
surprised that starting the transaction from the psql command line chokes
the attempt.

Regards,

Rich

On Wed, Jun 19, 2024 at 4:33 PM Rich Shepard <rshepard@appl-ecosys.com>
wrote:

On Wed, 19 Jun 2024, Alban Hertroys wrote:

The error prior to those statements is what you need to look at. That’s
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.

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.

On Wed, Jun 19, 2024 at 4:54 PM Rich Shepard <rshepard@appl-ecosys.com>
wrote:

On Wed, 19 Jun 2024, Adrian Klaver wrote:

Looks to me you have a left over unresolved transaction in your psql

session.

The easiest solution if that is the case is to exit the session and

start a

new session to run the script.

Adrian, et al.:

That's what I've done. This time I commented out the BEGIN; line:
bustrac=# \i insert-law-offices-addr.sql
INSERT 0 66

There are no errors in the file but when I tried running it as a
transaction
it failed.

I have not before used transactions when inserting or updating tables; I'm
surprised that starting the transaction from the psql command line chokes
the attempt.

It doesn't. The rest of us have successfully done it before. You're Doing
*Something* Wrong. Don't know what, but you're doing it. Happens to *ALL
OF US*, and is why "\echo all" and "psql -a" were developed.

So we can see WTH we screwed up, and then fix it.

On 6/19/24 13:54, Rich Shepard wrote:

On Wed, 19 Jun 2024, Adrian Klaver wrote:

Looks to me you have a left over unresolved transaction in your psql
session. The easiest solution if that is the case is to exit the
session and start a new session to run the script.

Adrian, et al.:

That's what I've done. This time I commented out the BEGIN; line:
bustrac=# \i insert-law-offices-addr.sql INSERT 0 66

There are no errors in the file but when I tried running it as a
transaction
it failed.

I have not before used transactions when inserting or updating tables; I'm
surprised that starting the transaction from the psql command line chokes
the attempt.

It shouldn't:

cat transaction_test.sql
BEGIN;
insert into transaction_test values(1, 'test'), (2, 'dog'), (3, 'cat');

test=# create table transaction_test(id integer, fld_1 varchar);

test=# \i transaction_test.sql
BEGIN
INSERT 0 3

test=*# commit ;
COMMIT

test=# select * from transaction_test ;
id | fld_1
----+-------
1 | test
2 | dog
3 | cat
(3 rows)

Regards,

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

On 6/19/24 13:54, Rich Shepard wrote:

On Wed, 19 Jun 2024, Adrian Klaver wrote:

Looks to me you have a left over unresolved transaction in your psql
session. The easiest solution if that is the case is to exit the
session and start a new session to run the script.

Adrian, et al.:

That's what I've done. This time I commented out the BEGIN; line:
bustrac=# \i insert-law-offices-addr.sql INSERT 0 66

There are no errors in the file but when I tried running it as a
transaction
it failed.

I have not before used transactions when inserting or updating tables; I'm
surprised that starting the transaction from the psql command line chokes
the attempt.

I should have added to previous post:

What is the exact command string you are using to launch psql?

Regards,

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, 19 Jun 2024, Adrian Klaver wrote:

It shouldn't:

cat transaction_test.sql
BEGIN;
insert into transaction_test values(1, 'test'), (2, 'dog'), (3, 'cat');

test=# create table transaction_test(id integer, fld_1 varchar);

test=# \i transaction_test.sql
BEGIN
INSERT 0 3

test=*# commit ;
COMMIT

test=# select * from transaction_test ;
id | fld_1
----+-------
1 | test
2 | dog
3 | cat
(3 rows)

Yes, I see how this works if the transaction is committed. But before I
commit the transaction I run a select statement to ensure the rows added are
correct. Can I rollback a commited transaction? I've assumed not, so I won't
commit the transaction without testing. And I'm not getting a detailed error
message.

Rich

On Wed, 19 Jun 2024, Adrian Klaver wrote:

I should have added to previous post:
What is the exact command string you are using to launch psql?

$ psql bustrac

Rich