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

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 syn

Re: Proper format for pg_dump file date

On Wed, 19 Jun 2024, Ron Johnson wrote: "Click select" stops at dash in some ssh clients, but not in others. That's what drives *my* decision making. (Also, "%F" is equivalent to "%Y-%m-%d".) Thanks, Ron. Rich

Re: Proper format for pg_dump file date

On Wed, Jun 19, 2024 at 1:14 PM Rich Shepard wrote: > On Wed, 19 Jun 2024, David G. Johnston wrote: > > > If you are doing a custom format dump using .sql as the extension is > > objectively wrong. it is correct if you are doing an sql dump, but those > > are generally best avoided. > > David, >

Re: Proper format for pg_dump file date

On Wed, Jun 19, 2024 at 11:55 AM Rich Shepard wrote: > Is the correct date format for pg_dump > -$(date +%Y-%m-%d).sql > or > --MM-DD.sql > "Click select" stops at dash in some ssh clients, but not in others. That's what drives *my* decision making. (Also, "%F" is equivalent

Re: Proper format for pg_dump file date

On Wed, 19 Jun 2024, David G. Johnston wrote: If you are doing a custom format dump using .sql as the extension is objectively wrong. it is correct if you are doing an sql dump, but those are generally best avoided. David, I use the default text format so restoration is by psql. Rich

Re: Manual Failover

On Wed, Jun 19, 2024 at 11:03 AM Yongye Serkfem wrote: > Hello Engineers, > I am facing an issue with the manual failover of the standby to the master > role. I was able to promote the standby and got it out of recovery mode. > How do I direct applications to point to the standby which has assume

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

On Wednesday, June 19, 2024, Ayush Vatsa wrote: > Hi David, > Thanks for clarification > > I prefer TABLE. Using setof is more useful when the returned type is > predefined > But in the table also isn't the returned type predefined? Example: > CREATE FUNCTION fun1(integer) > RETURNS TABLE( >

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

Hi David, Thanks for clarification > I prefer TABLE. Using setof is more useful when the returned type is predefined But in the table also isn't the returned type predefined? Example: CREATE FUNCTION fun1(integer) RETURNS TABLE( col1 integer, col2 text ) AS 'MODULE_PATHNAME', 'fun1

Re: Proper format for pg_dump file date

On Wed, Jun 19, 2024 at 8:55 AM Rich Shepard wrote: > Is the correct date format for pg_dump > -$(date +%Y-%m-%d).sql > or > --MM-DD.sql > or something else? > > If you are doing a custom format dump using .sql as the extension is objectively wrong. it is correct if you are d

Re: Proper format for pg_dump file date [RESOLVED]

On Wed, 19 Jun 2024, Adrian Klaver wrote: That is your choice, whatever makes sense to you. Thank you, Adrian. Regards, Rich

Re: Proper format for pg_dump file date

On 6/19/24 08:55, Rich Shepard wrote: Is the correct date format for pg_dump -$(date +%Y-%m-%d).sql or --MM-DD.sql or something else? That is your choice, whatever makes sense to you. TIA, Rich -- Adrian Klaver adrian.kla...@aklaver.com

Proper format for pg_dump file date

Is the correct date format for pg_dump -$(date +%Y-%m-%d).sql or --MM-DD.sql or something else? TIA, Rich

Re: Manual Failover

On 6/19/24 09:03, Yongye Serkfem wrote: Hello Engineers, I am facing an issue with the manual failover of the standby to the master role. I was able to promote the standby and got it out of recovery mode. How do I direct applications to point to the standby which has assumed the role of the

Manual Failover

Hello Engineers, I am facing an issue with the manual failover of the standby to the master role. I was able to promote the standby and got it out of recovery mode. How do I direct applications to point to the standby which has assumed the role of the current master, and what should I do after comp