Re: Transaction issue

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; COMMIT; --optional 2) In psql do \i 3) Do what you did before to 'recover' from the error. Shows us the conte

Re: Transaction issue

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 poi

Re: Transaction issue

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

Re: Transaction issue

> On Jun 20, 2024, at 7:05 AM, Rich Shepard 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

Re: Transaction issue

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

Re: Transaction issue

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 . Regards, Rich

Re: Transaction issue

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

Re: Transaction issue

On 6/19/24 17:27, David G. Johnston wrote: On Wed, Jun 19, 2024 at 5:16 PM Adrian Klaver > 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 [

Re: Transaction issue

On Wed, Jun 19, 2024 at 5:16 PM Adrian Klaver 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

Re: Transaction issue

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

Re: Transaction issue

On 6/19/24 14:55, David G. Johnston wrote: On Wednesday, June 19, 2024, Adrian Klaver > 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

Re: Transaction issue

On 6/19/24 15:55, David G. Johnston wrote: On Wednesday, June 19, 2024, Adrian Klaver 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 us

Re: Transaction issue

On Wednesday, June 19, 2024, Adrian Klaver 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 difficu

Re: Transaction issue

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

Re: Transaction issue

On Wed, Jun 19, 2024 at 5:39 PM Rich Shepard 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

Re: Transaction issue

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 b

Re: Transaction issue

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

Re: Transaction issue

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

Re: Transaction issue

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 wha

Re: Transaction issue

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 wha

Re: Transaction issue

On Wed, Jun 19, 2024 at 4:54 PM 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

Re: Transaction issue

On Wed, Jun 19, 2024 at 4:33 PM 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 scrip

Re: Transaction issue

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

Re: Transaction issue

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-

Re: Transaction issue

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

Re: Transaction issue

> On 19 Jun 2024, at 19:56, 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 The error prior to those statements is what you need to look at. That’s wh

Re: Transaction issue

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 issue

Re: Transaction issue

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 cas

Re: Transaction issue

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

Re: Transaction issue

On Wed, Jun 19, 2024, 11:38 Rich Shepard 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

Re: Transaction issue

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 command to run the script. Are there transaction statements e.g. BEGIN;, COMMIT; in the script? Rich -- Ad

Re: Transaction issue

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 wa

Re: Transaction issue

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 command to run the script. Rich

Re: Transaction issue

On Wed, Jun 19, 2024 at 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 > > I issue a rollback; command but cannot continue processing. What is the

Re: Transaction issue

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 c