public inbox for [email protected]
help / color / mirror / Atom feedRe: Transaction issue
10+ messages / 4 participants
[nested] [flat]
* Re: Transaction issue
@ 2024-06-19 20:43 Adrian Klaver <[email protected]>
2024-06-19 20:54 ` Re: Transaction issue Rich Shepard <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Adrian Klaver @ 2024-06-19 20:43 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; pgsql-general
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 protected]
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Transaction issue
2024-06-19 20:43 Re: Transaction issue Adrian Klaver <[email protected]>
@ 2024-06-19 20:54 ` Rich Shepard <[email protected]>
2024-06-19 21:03 ` Re: Transaction issue Adrian Klaver <[email protected]>
2024-06-19 21:09 ` Re: Transaction issue Adrian Klaver <[email protected]>
0 siblings, 2 replies; 10+ messages in thread
From: Rich Shepard @ 2024-06-19 20:54 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Transaction issue
2024-06-19 20:43 Re: Transaction issue Adrian Klaver <[email protected]>
2024-06-19 20:54 ` Re: Transaction issue Rich Shepard <[email protected]>
@ 2024-06-19 21:03 ` Adrian Klaver <[email protected]>
2024-06-19 21:32 ` Re: Transaction issue Rich Shepard <[email protected]>
1 sibling, 1 reply; 10+ messages in thread
From: Adrian Klaver @ 2024-06-19 21:03 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; pgsql-general
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
[email protected]
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Transaction issue
2024-06-19 20:43 Re: Transaction issue Adrian Klaver <[email protected]>
2024-06-19 20:54 ` Re: Transaction issue Rich Shepard <[email protected]>
2024-06-19 21:03 ` Re: Transaction issue Adrian Klaver <[email protected]>
@ 2024-06-19 21:32 ` Rich Shepard <[email protected]>
2024-06-20 12:44 ` Re: Transaction issue Karsten Hilbert <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Rich Shepard @ 2024-06-19 21:32 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Transaction issue
2024-06-19 20:43 Re: Transaction issue Adrian Klaver <[email protected]>
2024-06-19 20:54 ` Re: Transaction issue Rich Shepard <[email protected]>
2024-06-19 21:03 ` Re: Transaction issue Adrian Klaver <[email protected]>
2024-06-19 21:32 ` Re: Transaction issue Rich Shepard <[email protected]>
@ 2024-06-20 12:44 ` Karsten Hilbert <[email protected]>
2024-06-20 13:04 ` Re: Transaction issue Rich Shepard <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Karsten Hilbert @ 2024-06-20 12:44 UTC (permalink / raw)
To: [email protected]; pgsql-general
Am Wed, Jun 19, 2024 at 02:32:07PM -0700 schrieb Rich Shepard:
> 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.
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Transaction issue
2024-06-19 20:43 Re: Transaction issue Adrian Klaver <[email protected]>
2024-06-19 20:54 ` Re: Transaction issue Rich Shepard <[email protected]>
2024-06-19 21:03 ` Re: Transaction issue Adrian Klaver <[email protected]>
2024-06-19 21:32 ` Re: Transaction issue Rich Shepard <[email protected]>
2024-06-20 12:44 ` Re: Transaction issue Karsten Hilbert <[email protected]>
@ 2024-06-20 13:04 ` Rich Shepard <[email protected]>
2024-06-20 14:41 ` Re: Transaction issue Rob Sargent <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Rich Shepard @ 2024-06-20 13:04 UTC (permalink / raw)
To: [email protected]; pgsql-general
On Thu, 20 Jun 2024, Karsten Hilbert wrote:
> Shot in the dark: are you be any chance using tab-completion
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Transaction issue
2024-06-19 20:43 Re: Transaction issue Adrian Klaver <[email protected]>
2024-06-19 20:54 ` Re: Transaction issue Rich Shepard <[email protected]>
2024-06-19 21:03 ` Re: Transaction issue Adrian Klaver <[email protected]>
2024-06-19 21:32 ` Re: Transaction issue Rich Shepard <[email protected]>
2024-06-20 12:44 ` Re: Transaction issue Karsten Hilbert <[email protected]>
2024-06-20 13:04 ` Re: Transaction issue Rich Shepard <[email protected]>
@ 2024-06-20 14:41 ` Rob Sargent <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Rob Sargent @ 2024-06-20 14:41 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: [email protected]; pgsql-general
> On Jun 20, 2024, at 7:05 AM, Rich Shepard <[email protected]> wrote:
>
> On Thu, 20 Jun 2024, Karsten Hilbert wrote:
>
>> Shot in the dark: are you be any chance using tab-completion
>> 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>.
>
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
> Regards,
>
> Rich
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Transaction issue
2024-06-19 20:43 Re: Transaction issue Adrian Klaver <[email protected]>
2024-06-19 20:54 ` Re: Transaction issue Rich Shepard <[email protected]>
@ 2024-06-19 21:09 ` Adrian Klaver <[email protected]>
2024-06-19 21:33 ` Re: Transaction issue Rich Shepard <[email protected]>
1 sibling, 1 reply; 10+ messages in thread
From: Adrian Klaver @ 2024-06-19 21:09 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; pgsql-general
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
[email protected]
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Transaction issue
2024-06-19 20:43 Re: Transaction issue Adrian Klaver <[email protected]>
2024-06-19 20:54 ` Re: Transaction issue Rich Shepard <[email protected]>
2024-06-19 21:09 ` Re: Transaction issue Adrian Klaver <[email protected]>
@ 2024-06-19 21:33 ` Rich Shepard <[email protected]>
2024-06-19 21:48 ` Re: Transaction issue Adrian Klaver <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Rich Shepard @ 2024-06-19 21:33 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Transaction issue
2024-06-19 20:43 Re: Transaction issue Adrian Klaver <[email protected]>
2024-06-19 20:54 ` Re: Transaction issue Rich Shepard <[email protected]>
2024-06-19 21:09 ` Re: Transaction issue Adrian Klaver <[email protected]>
2024-06-19 21:33 ` Re: Transaction issue Rich Shepard <[email protected]>
@ 2024-06-19 21:48 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Adrian Klaver @ 2024-06-19 21:48 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; pgsql-general
On 6/19/24 14:33, Rich Shepard wrote:
> 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
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>
>
> Rich
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2024-06-20 14:41 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-19 20:43 Re: Transaction issue Adrian Klaver <[email protected]>
2024-06-19 20:54 ` Rich Shepard <[email protected]>
2024-06-19 21:03 ` Adrian Klaver <[email protected]>
2024-06-19 21:32 ` Rich Shepard <[email protected]>
2024-06-20 12:44 ` Karsten Hilbert <[email protected]>
2024-06-20 13:04 ` Rich Shepard <[email protected]>
2024-06-20 14:41 ` Rob Sargent <[email protected]>
2024-06-19 21:09 ` Adrian Klaver <[email protected]>
2024-06-19 21:33 ` Rich Shepard <[email protected]>
2024-06-19 21:48 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox