Re: Best Approach for Swapping a Table with its Copy

On Thu, Feb 13, 2025 at 7:37 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> 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 guarantee that the foreign keys are valid without a table
> scan? How do you handle concurrent data modifications?

Exactly! This is part of what I am trying to figure out (-:

The plan that I have in mind so far to swap a table safely is:

1. Run the following query to grab information about the toast and the toast
index of the tables involved in the swapping:

SELECT
X.oid,
X.reltoastrelid,
X.relowner,
TOAST_X.indexrelid
FROM pg_catalog.pg_class X
LEFT JOIN
pg_catalog.pg_index TOAST_X ON X.reltoastrelid =
TOAST_X.indrelid AND TOAST_X.indisvalid
WHERE X.oid = ('my_sweet_table')::regclass;

2. Open a transaction and acquire an access exclusive lock.
3. Ensure that the cloned table has the same owner as the original table

ALTER TABLE copy_table OWNER TO owner_of_the_original_table;

4. Now I need to swap all the data in the catalogue tables that point to the
old table and the toast to point to the new one and vice-versa (in case I
need to rollback).
5. Commit

Step 4 is what I am stuck on. What bits of the catalogue do I need to change?
And for what reasons?

It may be a risky operation, as you say and I might decide not to do pursue it
in the end, but first I must understand (-:

Regards,
- Marcelo