Best Approach for Swapping a Table with its Copy

From: Marcelo Fernandes <marcefern7(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Best Approach for Swapping a Table with its Copy
Date: 2025-02-12 20:57:36
Message-ID: CAM2F1VP9nn0F6fSjbmhJCNSjv-dbAJcAytZ2HkTkZ0dGYZvxrw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 associated TOAST tables correctly.

Both tables are fairly large and exist in a system where there are no
maintenance time windows where the application is shut down.

My key questions are:

- What is the best process for swapping the original table with the copy in
such a way that the foreign key relations are preserved?

- Are there any special considerations for managing the TOAST tables during
this swap?

- Should I perform this operation in multiple steps, or is there a
straightforward way to achieve this atomically?

- Are there any risks of potential issues I should be aware of when doing this
swap? Specifically related to foreign key integrity and TOAST data?

Thank you!
- Marcelo

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2025-02-12 21:02:44 Re: Best Approach for Swapping a Table with its Copy
Previous Message Adrian Klaver 2025-02-12 20:29:35 Re: psql command line editing