Re: Best Approach for Swapping a Table with its Copy

On Sun, Feb 16, 2025 at 5:58 PM Marcelo Fernandes wrote: > - The foreign keys are not being updated to point to the new table. > You started out okay with your test script, but the pg_depend bit needs work. I would recommend examining that table closely until you have a really good understanding

Re: Best Approach for Swapping a Table with its Copy

On Sat, Feb 15, 2025 at 4:12 AM Greg Sabino Mullane wrote: > The pg_repack link posted earlier has the details on how it is done. But > messing with system catalogs like this is highly discouraged, for good > reasons. Still, if you need to go that route, test heavily and post the > solutions he

Re: Best Approach for Swapping a Table with its Copy

On Fri, Feb 14, 2025 at 1:02 AM Michał Kłeczek wrote: > Create index concurrently and then fiddle with the catalog tables to > define the constraint using this index? > You mean an ALTER TABLE ... ADD CONSTRAINT ... EXCLUDE without actually doing an ALTER TABLE. Nope, that's far worse than the p

Re: Best Approach for Swapping a Table with its Copy

On Fri, Feb 14, 2025 at 12:41 AM Laurenz Albe wrote: > Moreover, you have to make sure to send out invalidation messages so that > every session that caches statistics or > execution plans for the tables discards them. Hmm...is that really necessary? Because if so, there is no direct SQL-level

Re: Best Approach for Swapping a Table with its Copy

On Thu, Feb 13, 2025 at 6:06 PM Marcelo Fernandes wrote: > > It's technically possible to do something similar for your use case, but > it's not trivial. All the cab to trailer wires must be precisely changed. > Everything directly related to the data must be swapped: heap, indexes, > toast. > >

Re: Best Approach for Swapping a Table with its Copy

> On 12 Feb 2025, at 22:27, Marcelo Fernandes wrote: > > On Thu, Feb 13, 2025 at 10:02 AM Adrian Klaver > > Also pulling in your question in the other reply: > >> Why can't you just add the exclusion constraint to the original table? > > With unique constraints, one can use a unique index t

Re: Best Approach for Swapping a Table with its Copy

On Fri, 2025-02-14 at 11:59 +1300, Marcelo Fernandes wrote: > On Thu, Feb 13, 2025 at 7:37 PM Laurenz Albe wrote: > > The big challenge here would be to do the swap in a safe way. How do > > you intend to guarantee that the foreign keys are valid without a table > > scan? How do you handle concu

Re: Best Approach for Swapping a Table with its Copy

On Fri, Feb 14, 2025 at 12:35 PM Adrian Klaver wrote: > 1) In a previous post you said: > > "Yes, in this scenario the copy is already created, and triggers keep > the copy in sync with the original table." > > In that case the copy will already have TOAST tables associated with it. If we follow

Re: Best Approach for Swapping a Table with its Copy

On 2/13/25 14:59, Marcelo Fernandes wrote: On Thu, Feb 13, 2025 at 7:37 PM Laurenz Albe wrote: Yes, but only if you are willing to write C code that runs inside the database server. That way, you can do anything (and cause arbitrary damage). The big challenge here would be to do the swap in a

Re: Best Approach for Swapping a Table with its Copy

On Fri, Feb 14, 2025 at 4:09 AM Greg Sabino Mullane wrote: > It is surgically replacing all pointers to the old data with pointers to the > new data. Yes, with lots of system catalog shenanigans. Love your analogy Greg, thanks for that. > It's technically possible to do something similar for yo

Re: Best Approach for Swapping a Table with its Copy

On Thu, Feb 13, 2025 at 7:37 PM Laurenz Albe wrote: > Yes, but only if you are willing to write C code that runs inside the > database server. That way, you can do anything (and cause arbitrary > damage). > > The big challenge here would be to do the swap in a safe way. How do > you intend to gu

Re: Best Approach for Swapping a Table with its Copy

On 2/13/25 07:25, Dominique Devienne wrote: On Thu, Feb 13, 2025 at 4:09 PM Greg Sabino Mullane > wrote: Thanks for the colorful analogy Greg :). Maybe the better option is to support ALTER TABLE to ADD an exclusion constraint, no? That exists: select version();

Re: Best Approach for Swapping a Table with its Copy

On Thu, Feb 13, 2025 at 4:09 PM Greg Sabino Mullane wrote: > On Wed, Feb 12, 2025 at 9:02 PM Marcelo Fernandes > wrote: > >> What I am after is the same, but I seek a deeper understanding of what it >> does, and why it does it. For example, it swaps relfilenode. Why? > > > It is surgically repla

Re: Best Approach for Swapping a Table with its Copy

On Wed, Feb 12, 2025 at 9:02 PM Marcelo Fernandes wrote: > What I am after is the same, but I seek a deeper understanding of what it > does, and why it does it. For example, it swaps relfilenode. Why? It is surgically replacing all pointers to the old data with pointers to the new data. Yes, wi

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