Horton
Menu

Thread

  1. Transaction issue

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-19T17:56:25Z

    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

  2. Re: Transaction issue

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-06-19T17:58:19Z

    
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

  3. Re: Transaction issue

    David G. Johnston <david.g.johnston@gmail.com> — 2024-06-19T18:07:06Z

    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.

  4. Re: Transaction issue

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-19T18:36:49Z

    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

  5. Re: Transaction issue

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-19T18:38:30Z

    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

  6. Re: Transaction issue

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-06-19T18:40:29Z

    
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

  7. Re: Transaction issue

    David G. Johnston <david.g.johnston@gmail.com> — 2024-06-19T18:44:36Z

    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.

  8. Re: Transaction issue

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-19T19:40:10Z

    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

  9. Re: Transaction issue

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-06-19T20:10:02Z

    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

  10. Re: Transaction issue

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-19T20:13:31Z

    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

  11. Re: Transaction issue

    Alban Hertroys <haramrae@gmail.com> — 2024-06-19T20:20:45Z

    > 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 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.

  12. Re: Transaction issue

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-19T20:33:18Z

    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

  13. Re: Transaction issue

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-06-19T20:43:48Z

    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

  14. Re: Transaction issue

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-19T20:54:35Z

    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

  15. Re: Transaction issue

    Ron Johnson <ronljohnsonjr@gmail.com> — 2024-06-19T20:57:48Z

    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.

  16. Re: Transaction issue

    Ron Johnson <ronljohnsonjr@gmail.com> — 2024-06-19T21:02:19Z

    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.

  17. Re: Transaction issue

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-06-19T21:03:33Z

    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

  18. Re: Transaction issue

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-06-19T21:09:56Z

    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

  19. Re: Transaction issue

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-19T21:32:07Z

    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

  20. Re: Transaction issue

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-19T21:33:17Z

    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

  21. Re: Transaction issue

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-19T21:39:02Z

    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

  22. Re: Transaction issue

    Ron Johnson <ronljohnsonjr@gmail.com> — 2024-06-19T21:44:30Z

    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.

  23. Re: Transaction issue

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-06-19T21:48:18Z

    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

  24. Re: Transaction issue

    David G. Johnston <david.g.johnston@gmail.com> — 2024-06-19T21:55:32Z

    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.

  25. Re: Transaction issue

    Rob Sargent <robjsargent@gmail.com> — 2024-06-19T22:14:06Z

    
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?!!

  26. Re: Transaction issue

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-06-20T00:16:19Z

    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

  27. Re: Transaction issue

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-06-20T00:18:46Z

    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

  28. Re: Transaction issue

    David G. Johnston <david.g.johnston@gmail.com> — 2024-06-20T00:27:03Z

    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.

  29. Re: Transaction issue

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-06-20T00:30:34Z

    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

  30. Re: Transaction issue

    Karsten Hilbert <karsten.hilbert@gmx.net> — 2024-06-20T12:44:40Z

    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

  31. Re: Transaction issue

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-20T13:04:56Z

    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

  32. Re: Transaction issue

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-20T13:06:56Z

    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

  33. Re: Transaction issue

    Rob Sargent <robjsargent@gmail.com> — 2024-06-20T14:41:00Z

    
> 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
> 
>

  34. Re: Transaction issue

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-20T14:47:55Z

    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

  35. Re: Transaction issue

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-06-20T14:56:28Z

    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

  36. Re: Transaction issue

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-20T15:30:00Z

    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