On Fri, Feb 14, 2025 at 4:09 AM Greg Sabino Mullane <htamfids@xxxxxxxxx> 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 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.
I'd really appreciate to know more about how I can do this, as I think this is
the crux of what I am trying to solve.
If you have pointers, thoughts, or resources where I can better understand
what's involved, that would be much appreciated.
In terms of where I am at currently, I summarised in my previous reply:
On Fri, Feb 14, 2025 at 11:59 AM Marcelo Fernandes <marcefern7@xxxxxxxxx> wrote:
> 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
