MIME-Version: 1.0
References: <CAM2F1VP9nn0F6fSjbmhJCNSjv-dbAJcAytZ2HkTkZ0dGYZvxrw@mail.gmail.com>
 <8ac6a1dd-db82-4ea7-8186-0c354d79049f@aklaver.com> <CAM2F1VOOn1izCrtcrDx4YUtCY-H64Vj5yvN5H1Lhk8kCY+_W6Q@mail.gmail.com>
 <9fa75099-1ee9-43de-ac6e-945841b184eb@aklaver.com> <CAM2F1VONo8M9gcybhmf8-CdmRUC2QrFGZkZLYF-+uLZuhRKaxQ@mail.gmail.com>
 <22406cdf-83f5-4b98-b07e-7827afe97f93@aklaver.com> <CAM2F1VMdDSxLhccn=0pXriJrR6a7Oe9Kir1DB_0pW1v88bsvCg@mail.gmail.com>
 <CAKAnmmJ04kH_gWR+cOFWD0h63RDck1k6hqQYH8K30-cfCvtO_Q@mail.gmail.com>
 <CAFCRh-9xC0CXKM2GTQBfjYY5GQx29Uit+fY5PpBFTvX927YC8w@mail.gmail.com>
 <94e1205e-8ddf-403b-8477-02d7939f2905@aklaver.com> <CAM2F1VMApyPvdA4ERoF7Fob6CXimgOWMKC1vayO5OQa3TtJAFQ@mail.gmail.com>
In-Reply-To: <CAM2F1VMApyPvdA4ERoF7Fob6CXimgOWMKC1vayO5OQa3TtJAFQ@mail.gmail.com>
From: Marcelo Fernandes <marcefern7@gmail.com>
Date: Fri, 14 Feb 2025 12:06:40 +1300
Message-ID: <CAM2F1VNnm_x5F7xSKcv6x-hQHY-zxvFgyAdrdwTaQ9ZTVUQ3pQ@mail.gmail.com>
Subject: Re: Best Approach for Swapping a Table with its Copy
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Dominique Devienne <ddevienne@gmail.com>, Greg Sabino Mullane <htamfids@gmail.com>, 
	pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAM2F1VNnm_x5F7xSKcv6x-hQHY-zxvFgyAdrdwTaQ9ZTVUQ3pQ%40mail.gmail.com>
Precedence: bulk

On Fri, Feb 14, 2025 at 4:09=E2=80=AFAM Greg Sabino Mullane <htamfids@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. E=
verything directly related to the data must be swapped: heap, indexes, toas=
t.

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=E2=80=AFAM Marcelo Fernandes <marcefern7@gmai=
l.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 to=
ast
>    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 =3D
> TOAST_X.indrelid AND TOAST_X.indisvalid
>   WHERE X.oid =3D ('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 cas=
e I
>    need to rollback).
> 5. Commit
>
> Step 4 is what I am stuck on. What bits of the catalogue do I need to cha=
nge?
> And for what reasons?
>
> It may be a risky operation, as you say and I might decide not to do purs=
ue it
> in the end, but first I must understand (-:

Regards,
  - Marcelo