Thread: Transaction issue
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
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 executeknown-to-be-good sequences of SQL statements, but in case of errors it can be difficult to debug (although PostgreSQLis 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 differentsession even. It won’t affect the failed transaction from the original session, which would have rolled back automaticallywhen 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 statementsin the SQL, add (named) SAVEPOINTs where you’re unsure of the results so that you can roll back to those specificpoints 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 modein psql that allows you to import an SQL file and step through the statements one by one. That would be helpful in yourcase 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
On Wed, 19 Jun 2024, Ron Johnson wrote: > 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. > I suggest you run "\echo all" before running "\i > 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
On Wed, Jun 19, 2024 at 5:39 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:
On Wed, 19 Jun 2024, Ron Johnson wrote:
> 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.
> I suggest you run "\echo all" before running "\i
> 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.
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.
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 adrian.klaver@aklaver.com
On Wednesday, June 19, 2024, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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.
You hallucinated a dash in front of the bustrac. psql bustract is a perfectly valid psql command. User gets inferred from the OS user.
David J.
On 6/19/24 15:55, David G. Johnston wrote:
On Wednesday, June 19, 2024, Adrian Klaver <adrian.klaver@aklaver.com> wrote:Oh no. Adrian is an AI?!!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.You hallucinated a dash in front of the bustrac. psql bustract is a perfectly valid psql command. User gets inferred from the OS user.David J.
On 6/19/24 14:55, David G. Johnston wrote: > On Wednesday, June 19, 2024, Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > 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. > > > You hallucinated a dash in front of the bustrac. psql bustract is a > perfectly valid psql command. User gets inferred from the OS user. As in?: psql -d test -U postgres bustrac psql: warning: extra command-line argument "bustrac" ignored psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1), server 15.7 (Ubuntu 15.7-1.pgdg22.04+1)) Type "help" for help. test=# In which case bustrac is ignored. > > David J. > -- Adrian Klaver adrian.klaver@aklaver.com
On 6/19/24 15:14, Rob Sargent wrote: > > > On 6/19/24 15:55, David G. Johnston wrote: >> On Wednesday, June 19, 2024, Adrian Klaver <adrian.klaver@aklaver.com> >> wrote: >> >> 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. >> >> >> You hallucinated a dash in front of the bustrac. psql bustract is a >> perfectly valid psql command. User gets inferred from the OS user. >> >> David J. >> > Oh no. Adrian is an AI?!! > No, I am perfectly capable of providing incorrect answers solely on the basis of my 'little gray cells'. -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, Jun 19, 2024 at 5:16 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> You hallucinated a dash in front of the bustrac. psql bustract is a
> perfectly valid psql command. User gets inferred from the OS user.
As in?:
psql -d test -U postgres bustrac
Well no, that is the specification of -U is the exact opposite of "user gets inferred from the OS user".
psql: warning: extra command-line argument "bustrac" ignored
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1), server 15.7 (Ubuntu
15.7-1.pgdg22.04+1))
Type "help" for help.
test=#
In which case bustrac is ignored.
You are missing the fact that bustrac is the name of the database so when you specify the -d option you are being redundant and being told that by psql.
psql [option...] [dbname [username]]
You like to specify both dbname and username via options but as shown one can also use arguments.
David J.
On 6/19/24 17:27, David G. Johnston wrote: > On Wed, Jun 19, 2024 at 5:16 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > > You are missing the fact that bustrac is the name of the database so > when you specify the -d option you are being redundant and being told > that by psql. > > psql [option...] [dbname [username]] Yep, completely missed that. > > You like to specify both dbname and username via options but as shown > one can also use arguments. > > David J. > -- Adrian Klaver adrian.klaver@aklaver.com
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
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
On Wed, 19 Jun 2024, Ron Johnson wrote: > 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. Ron, Hadn't thought of doing that, but now will. Thanks for the excellent recomendation. Regards, Rich
> On Jun 20, 2024, at 7:05 AM, Rich Shepard <rshepard@appl-ecosys.com> 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 > >
On Thu, 20 Jun 2024, Rob Sargent wrote: > Is psql running in emacs (which is my preferred sql shell. M-x sql-postgres)? Rob, Not when I'm entering new data or updating existing tables. Otherwise, yes. Regards, Rich
On 6/20/24 07:47, Rich Shepard wrote: > On Thu, 20 Jun 2024, Rob Sargent wrote: > >> Is psql running in emacs (which is my preferred sql shell. M-x >> sql-postgres)? > > Rob, > > Not when I'm entering new data or updating existing tables. Otherwise, yes. From one of my previous posts(modified): 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; --optional 2) In psql do \i <the_script> 3) Do what you did before to 'recover' from the error. Shows us the content of the steps in your reply. > > Regards, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, 20 Jun 2024, Adrian Klaver wrote: > From one of my previous posts(modified): > 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; --optional > > 2) In psql do \i <the_script> > > 3) Do what you did before to 'recover' from the error. > > Shows us the content of the steps in your reply. Adrian, I belive that I did this yesterday. Regardless, either leaving off `BEGIN;' from the top of the script, or entering it prior to running the script, resolves the issue. I've not seen a problem since finding the solutions. Best regards, Rich