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>
In-Reply-To: <94e1205e-8ddf-403b-8477-02d7939f2905@aklaver.com>
From: Marcelo Fernandes <marcefern7@gmail.com>
Date: Fri, 14 Feb 2025 11:59:50 +1300
Message-ID: <CAM2F1VMApyPvdA4ERoF7Fob6CXimgOWMKC1vayO5OQa3TtJAFQ@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/CAM2F1VMApyPvdA4ERoF7Fob6CXimgOWMKC1vayO5OQa3TtJAFQ%40mail.gmail.com>
Precedence: bulk

On Thu, Feb 13, 2025 at 7:37=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec=
.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 toas=
t
   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 th=
e
   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 chang=
e?
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