Re: Best Approach for Swapping a Table with its Copy

On Thu, 2025-02-13 at 11:04 +1300, Marcelo Fernandes wrote: > > I am not seeing how this can be done without some outage for that table. > > Swapping tables without an outage is possible. Yes, but only if you are willing to write C code that runs inside the database server. That way, you can do

Re: Best Approach for Swapping a Table with its Copy

On Thu, Feb 13, 2025 at 1:33 PM Adrian Klaver wrote: > Not seeing it: > > https://reorg.github.io/pg_repack/ > > "Details > Full Table Repacks > > [...] > > pg_repack will only hold an ACCESS EXCLUSIVE lock for a short period > during initial setup (steps 1 and 2 above) and during the final > swap

Re: Best Approach for Swapping a Table with its Copy

On 2/12/25 14:04, Marcelo Fernandes wrote: On Thu, Feb 13, 2025 at 10:40 AM Adrian Klaver wrote: Do you know this will not fail on the existing data? Yes, all the current data in the original table respects the constraint. Do you have room for a complete copy of the table? Yes, in this sc

Re: psql command line editing

On 2/12/2025 11:44, Rich Shepard wrote: > I run psql from a urxvt virtual console, not emacs (which is where I > write scripts.) As with everything in Emacs, there's a mode for that. In case you're not aware of it, sql-mode allows you not only to interact with psql from within Emacs, but also to

Re: Best Approach for Swapping a Table with its Copy

On Thu, Feb 13, 2025 at 10:40 AM Adrian Klaver wrote: > Do you know this will not fail on the existing data? Yes, all the current data in the original table respects the constraint. > Do you have room for a complete copy of the table? Yes, in this scenario the copy is already created, and trigg

Re: Best Approach for Swapping a Table with its Copy

On 2/12/25 1:27 PM, Marcelo Fernandes wrote: On Thu, Feb 13, 2025 at 10:02 AM Adrian Klaver This is a simplified version of the original table: CREATE TABLE bookings ( id SERIAL PRIMARY KEY, resource_id INT NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL );

Re: Best Approach for Swapping a Table with its Copy

On Thu, Feb 13, 2025 at 10:02 AM Adrian Klaver wrote: > This needs more information: > 1) Postgres version. That would be for Postgres 15. > 2) The table definition. This is a simplified version of the original table: CREATE TABLE bookings ( id SERIAL PRIMARY KEY, resource_id INT NOT N

Re: psql command line editing

Rob Sargent writes: >> There's got to be something in your shell config which says "use emacs >> keys" instead of "use vi keys". > Or something in yours setting vi? I do have export EDITOR=emacs Possibly libreadline is reacting to that, but I've not checked into it. r

Re: psql command line editing

There's got to be something in your shell config which says "use emacs keys" instead of "use vi keys". -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster! Or something in yours setting vi?

Re: Best Approach for Swapping a Table with its Copy

On 2/12/25 12:57 PM, Marcelo Fernandes wrote: Hi folks, I have a scenario where I need to swap an original table with a copy of that table. Should have added to previous post: Why can't you just add the exclusion constraint to the original table? -- Adrian Klaver adrian.kla...@aklaver.co

Re: Best Approach for Swapping a Table with its Copy

On 2/12/25 12:57 PM, Marcelo Fernandes wrote: Hi folks, I have a scenario where I need to swap an original table with a copy of that table. The copy has an exclusion constraint that the original does not have. The main challenge is to ensure that the swap does not break any existing foreign

Best Approach for Swapping a Table with its Copy

Hi folks, I have a scenario where I need to swap an original table with a copy of that table. The copy has an exclusion constraint that the original does not have. The main challenge is to ensure that the swap does not break any existing foreign keys to the original table and handles the associat

Re: psql command line editing

On 2/12/25 12:03, Ron Johnson wrote: On Wed, Feb 12, 2025 at 2:29 PM Tom Lane > wrote: There's got to be something in your shell config which says "use emacs keys" instead of "use vi keys". From man readline: "By default, the line editing commands are similar to th

Re: psql command line editing

On Wed, Feb 12, 2025 at 2:29 PM Tom Lane wrote: > Ron Johnson writes: > > On Wed, Feb 12, 2025 at 1:50 PM Rich Shepard > > wrote: > >> My web searches suggest that using the psql command line I'm limited to > >> moving the cursor one character at a time. Is there a way to use a small > >> edito

Re: psql command line editing

On Wed, 12 Feb 2025, Adrian Klaver wrote: The point Tom was making is that psql uses readline for it's editing positioning and that for him Emacs movement commands work in psql. Adrian, Thanks for clarifying; the emacs commands work for me, too, in psql. Regards, Rich

Re: psql command line editing

On 2/12/25 11:44, Rich Shepard wrote: On Wed, 12 Feb 2025, Tom Lane wrote: On my machine, Ctrl-Left/Right don't seem to do anything, but the usual Emacs combinations Ctrl-A, Ctrl-E, Escape-B, Escape-F and so on work in psql. I don't believe I did anything special to configure that. Check your

Re: psql command line editing

On Wed, 12 Feb 2025, Tom Lane wrote: On my machine, Ctrl-Left/Right don't seem to do anything, but the usual Emacs combinations Ctrl-A, Ctrl-E, Escape-B, Escape-F and so on work in psql. I don't believe I did anything special to configure that. Check your local readline documentation. Tom, I

Re: psql command line editing

Ron Johnson writes: > On Wed, Feb 12, 2025 at 1:50 PM Rich Shepard > wrote: >> My web searches suggest that using the psql command line I'm limited to >> moving the cursor one character at a time. Is there a way to use a small >> editor, e.g., joe on linux, to move by words or to the begining and

Re: psql command line editing

On 2/12/25 10:50, Rich Shepard wrote: My web searches suggest that using the psql command line I'm limited to moving the cursor one character at a time. Is there a way to use a small editor, e.g., joe on linux, to move by words or to the begining and end of the line? From: https://www.postgres

Re: psql command line editing

On Wed, Feb 12, 2025 at 1:50 PM Rich Shepard wrote: > My web searches suggest that using the psql command line I'm limited to > moving the cursor one character at a time. Is there a way to use a small > editor, e.g., joe on linux, to move by words or to the begining and end of > the line? The P

psql command line editing

My web searches suggest that using the psql command line I'm limited to moving the cursor one character at a time. Is there a way to use a small editor, e.g., joe on linux, to move by words or to the begining and end of the line? TIA, Rich