public inbox for [email protected]
help / color / mirror / Atom feedTransaction issue
9+ messages / 5 participants
[nested] [flat]
* Transaction issue
@ 2024-06-19 17:56 Rich Shepard <[email protected]>
2024-06-19 17:58 ` Re: Transaction issue Adrian Klaver <[email protected]>
2024-06-19 18:07 ` Re: Transaction issue David G. Johnston <[email protected]>
2024-06-19 20:20 ` Re: Transaction issue Alban Hertroys <[email protected]>
0 siblings, 3 replies; 9+ messages in thread
From: Rich Shepard @ 2024-06-19 17:56 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Transaction issue
2024-06-19 17:56 Transaction issue Rich Shepard <[email protected]>
@ 2024-06-19 17:58 ` Adrian Klaver <[email protected]>
2 siblings, 0 replies; 9+ messages in thread
From: Adrian Klaver @ 2024-06-19 17:58 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; pgsql-general
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
[email protected]
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Transaction issue
2024-06-19 17:56 Transaction issue Rich Shepard <[email protected]>
@ 2024-06-19 18:07 ` David G. Johnston <[email protected]>
2 siblings, 0 replies; 9+ messages in thread
From: David G. Johnston @ 2024-06-19 18:07 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: pgsql-general
On Wed, Jun 19, 2024 at 10:56 AM Rich Shepard <[email protected]>
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Transaction issue
2024-06-19 17:56 Transaction issue Rich Shepard <[email protected]>
@ 2024-06-19 20:20 ` Alban Hertroys <[email protected]>
2024-06-19 20:33 ` Re: Transaction issue Rich Shepard <[email protected]>
2 siblings, 1 reply; 9+ messages in thread
From: Alban Hertroys @ 2024-06-19 20:20 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: pgsql-general
> On 19 Jun 2024, at 19:56, Rich Shepard <[email protected]> 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Transaction issue
2024-06-19 17:56 Transaction issue Rich Shepard <[email protected]>
2024-06-19 20:20 ` Re: Transaction issue Alban Hertroys <[email protected]>
@ 2024-06-19 20:33 ` Rich Shepard <[email protected]>
2024-06-19 20:57 ` Re: Transaction issue Ron Johnson <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Rich Shepard @ 2024-06-19 20:33 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Transaction issue
2024-06-19 17:56 Transaction issue Rich Shepard <[email protected]>
2024-06-19 20:20 ` Re: Transaction issue Alban Hertroys <[email protected]>
2024-06-19 20:33 ` Re: Transaction issue Rich Shepard <[email protected]>
@ 2024-06-19 20:57 ` Ron Johnson <[email protected]>
2024-06-19 21:39 ` Re: Transaction issue Rich Shepard <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Ron Johnson @ 2024-06-19 20:57 UTC (permalink / raw)
To: pgsql-general
On Wed, Jun 19, 2024 at 4:33 PM Rich Shepard <[email protected]>
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Transaction issue
2024-06-19 17:56 Transaction issue Rich Shepard <[email protected]>
2024-06-19 20:20 ` Re: Transaction issue Alban Hertroys <[email protected]>
2024-06-19 20:33 ` Re: Transaction issue Rich Shepard <[email protected]>
2024-06-19 20:57 ` Re: Transaction issue Ron Johnson <[email protected]>
@ 2024-06-19 21:39 ` Rich Shepard <[email protected]>
2024-06-19 21:44 ` Re: Transaction issue Ron Johnson <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Rich Shepard @ 2024-06-19 21:39 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Transaction issue
2024-06-19 17:56 Transaction issue Rich Shepard <[email protected]>
2024-06-19 20:20 ` Re: Transaction issue Alban Hertroys <[email protected]>
2024-06-19 20:33 ` Re: Transaction issue Rich Shepard <[email protected]>
2024-06-19 20:57 ` Re: Transaction issue Ron Johnson <[email protected]>
2024-06-19 21:39 ` Re: Transaction issue Rich Shepard <[email protected]>
@ 2024-06-19 21:44 ` Ron Johnson <[email protected]>
2024-06-20 13:06 ` Re: Transaction issue Rich Shepard <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Ron Johnson @ 2024-06-19 21:44 UTC (permalink / raw)
To: pgsql-general
On Wed, Jun 19, 2024 at 5:39 PM Rich Shepard <[email protected]>
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Transaction issue
2024-06-19 17:56 Transaction issue Rich Shepard <[email protected]>
2024-06-19 20:20 ` Re: Transaction issue Alban Hertroys <[email protected]>
2024-06-19 20:33 ` Re: Transaction issue Rich Shepard <[email protected]>
2024-06-19 20:57 ` Re: Transaction issue Ron Johnson <[email protected]>
2024-06-19 21:39 ` Re: Transaction issue Rich Shepard <[email protected]>
2024-06-19 21:44 ` Re: Transaction issue Ron Johnson <[email protected]>
@ 2024-06-20 13:06 ` Rich Shepard <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Rich Shepard @ 2024-06-20 13:06 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-06-20 13:06 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-19 17:56 Transaction issue Rich Shepard <[email protected]>
2024-06-19 17:58 ` Adrian Klaver <[email protected]>
2024-06-19 18:07 ` David G. Johnston <[email protected]>
2024-06-19 20:20 ` Alban Hertroys <[email protected]>
2024-06-19 20:33 ` Rich Shepard <[email protected]>
2024-06-19 20:57 ` Ron Johnson <[email protected]>
2024-06-19 21:39 ` Rich Shepard <[email protected]>
2024-06-19 21:44 ` Ron Johnson <[email protected]>
2024-06-20 13:06 ` Rich Shepard <[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