Subject: Re: Transaction issue

From: Adrian Klaver <[email-masked]>
Date: Wed, 19 Jun 2024 13:43:48 -0700

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
[email-masked]

---

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

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

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 19 Jun 2024 14:03:33 -0700

On 6/19/24 13:54, Rich Shepard wrote:
>> 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.:
> 
> bustrac=# \i insert-law-offices-addr.sql INSERT 0 66
> 

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)

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 19 Jun 2024 14:09:56 -0700

> surprised that starting the transaction from the psql command line chokes

I should have added to previous post:

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

---

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

> test=# select * from transaction_test ;

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

---

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

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

$ psql bustrac

Rich

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 19 Jun 2024 14:48:18 -0700

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

I find it difficult to believe that actually runs.

psql -d test -U postgres -bustrac

psql: hint: Try "psql --help" for more information.
aklaver@mauradog:~$ psql -d test -U postgres -bstrac
/usr/lib/postgresql/16/bin/psql: invalid option -- 'r'
psql: hint: Try "psql --help" for more information.
aklaver@mauradog:~$ psql -d test -U postgres -bstac
/usr/lib/postgresql/16/bin/psql: option requires an argument -- 'c'
psql: hint: Try "psql --help" for more information.

Simplify:

psql -d <db_name> -U <user_name>

---

From: Karsten Hilbert <[email-masked]>
Date: Thu, 20 Jun 2024 14:44:40 +0200

> commit the transaction without testing. And I'm not getting a detailed error

Shot in the dark: are you be any chance using tab-completion
when running the SELECT before the COMMIT ?

I've seen it happen a few times that tab completion ran
queries behind the scenes which failed and thereby set the
open transaction to abort state ...

And, no, I can't reproduce :/

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

---

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

> when running the SELECT before the COMMIT ?

Karsten,

Nope. I prepare DDL, DML, and DQL scripts in emacs, then run them from the
psql prompt using \i <filename>.

Regards,

Rich

---

From: Rob Sargent <[email-masked]>
Date: Thu, 20 Jun 2024 08:41:00 -0600

> On Jun 20, 2024, at 7:05 AM, Rich Shepard <[email-masked]> wrote:
> 

Is psql running in emacs (which is my preferred sql shell. M-x sql-postgres)?

Also change all semi-colons to \p\g to confirm the error location.  Maybe trap the output