public inbox for [email protected]
help / color / mirror / Atom feedFrom: Marcelo Fernandes <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: Dominique Devienne <[email protected]>
Cc: Greg Sabino Mullane <[email protected]>
Cc: [email protected]
Subject: Re: Best Approach for Swapping a Table with its Copy
Date: Fri, 14 Feb 2025 12:06:40 +1300
Message-ID: <CAM2F1VNnm_x5F7xSKcv6x-hQHY-zxvFgyAdrdwTaQ9ZTVUQ3pQ@mail.gmail.com> (raw)
In-Reply-To: <CAM2F1VMApyPvdA4ERoF7Fob6CXimgOWMKC1vayO5OQa3TtJAFQ@mail.gmail.com>
References: <CAM2F1VP9nn0F6fSjbmhJCNSjv-dbAJcAytZ2HkTkZ0dGYZvxrw@mail.gmail.com>
<[email protected]>
<CAM2F1VOOn1izCrtcrDx4YUtCY-H64Vj5yvN5H1Lhk8kCY+_W6Q@mail.gmail.com>
<[email protected]>
<CAM2F1VONo8M9gcybhmf8-CdmRUC2QrFGZkZLYF-+uLZuhRKaxQ@mail.gmail.com>
<[email protected]>
<CAM2F1VMdDSxLhccn=0pXriJrR6a7Oe9Kir1DB_0pW1v88bsvCg@mail.gmail.com>
<CAKAnmmJ04kH_gWR+cOFWD0h63RDck1k6hqQYH8K30-cfCvtO_Q@mail.gmail.com>
<CAFCRh-9xC0CXKM2GTQBfjYY5GQx29Uit+fY5PpBFTvX927YC8w@mail.gmail.com>
<[email protected]>
<CAM2F1VMApyPvdA4ERoF7Fob6CXimgOWMKC1vayO5OQa3TtJAFQ@mail.gmail.com>
On Fri, Feb 14, 2025 at 4:09 AM Greg Sabino Mullane <[email protected]> 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 <[email protected]> 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
view thread (13+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Best Approach for Swapping a Table with its Copy
In-Reply-To: <CAM2F1VNnm_x5F7xSKcv6x-hQHY-zxvFgyAdrdwTaQ9ZTVUQ3pQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox