public inbox for [email protected]help / color / mirror / Atom feed
Re: Transaction issue 10+ messages / 4 participants [nested] [flat]
* Re: Transaction issue @ 2024-06-19 20:43 Adrian Klaver <[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:54 Rich Shepard <[email protected]> parent: 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 21:03 Adrian Klaver <[email protected]> parent: 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 21:09 Adrian Klaver <[email protected]> parent: 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 21:32 Rich Shepard <[email protected]> parent: Adrian Klaver <[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 21:33 Rich Shepard <[email protected]> parent: 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 21:48 Adrian Klaver <[email protected]> parent: Rich Shepard <[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
* Re: Transaction issue @ 2024-06-20 12:44 Karsten Hilbert <[email protected]> parent: 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-20 13:04 Rich Shepard <[email protected]> parent: Karsten Hilbert <[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-20 14:41 Rob Sargent <[email protected]> parent: Rich Shepard <[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
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