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

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.

2) What are the FK relationships and how many?

Also could you just not add the FK's as NOT VALID?

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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com