On Fri, Feb 14, 2025 at 4:09 AM Greg Sabino Mullane <htamf...@gmail.com> 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 <marcefe...@gmail.com> 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
