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>
 <CAM2F1VNnm_x5F7xSKcv6x-hQHY-zxvFgyAdrdwTaQ9ZTVUQ3pQ@mail.gmail.com>
 <CAKAnmmJ0=9m3gXvjvWVAbyJpU=Uopn1K5sQDOPWJG7GNRctPmg@mail.gmail.com> <CAM2F1VNJaxm9SpPJbBSPeWdcvCT+A+xC1NTa54E7MDgUrKgkgQ@mail.gmail.com>
In-Reply-To: <CAM2F1VNJaxm9SpPJbBSPeWdcvCT+A+xC1NTa54E7MDgUrKgkgQ@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Sun, 16 Feb 2025 18:48:43 -0500
Message-ID: <CAKAnmm+aGX9_HMbJQrGR8nwFbd60x4NqfexnYi22-wtrCT-cXw@mail.gmail.com>
Subject: Re: Best Approach for Swapping a Table with its Copy
To: Marcelo Fernandes <marcefern7@gmail.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, Dominique Devienne <ddevienne@gmail.com>, 
	pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000006c4816062e4b0eea"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmm%2BaGX9_HMbJQrGR8nwFbd60x4NqfexnYi22-wtrCT-cXw%40mail.gmail.com>
Precedence: bulk

--0000000000006c4816062e4b0eea
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sun, Feb 16, 2025 at 5:58=E2=80=AFPM Marcelo Fernandes <marcefern7@gmail=
.com>
wrote:

> - The foreign keys are not being updated to point to the new table.
>

You started out okay with your test script, but the pg_depend bit needs
work. I would recommend examining that table closely until you have a
really good understanding of how it works - both on the objid and refobjid
side. Create and modify tables and see how the pg_depend entries change.
Hint: Try it with and without foreign keys.

https://www.postgresql.org/docs/current/catalog-pg-depend.html


> - pg_repack seems to update relcache entries. I am not sure how to do tha=
t
> with SQL.


Yes, as I mentioned upthread, the safest thing will probably be a restart.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

--0000000000006c4816062e4b0eea
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Sun, Feb 16, 2025 at 5:58=E2=80=AFPM M=
arcelo Fernandes &lt;<a href=3D"mailto:marcefern7@gmail.com">marcefern7@gma=
il.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"=
><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border=
-left:1px solid rgb(204,204,204);padding-left:1ex">- The foreign keys are n=
ot being updated to point to the new table.<br></blockquote><div><br></div>=
<div>You started out okay with=C2=A0your test script, but the pg_depend bit=
 needs work. I would recommend examining that table closely until you have =
a really good understanding=C2=A0of how it works - both on the objid and re=
fobjid side. Create and modify tables and see how the pg_depend entries cha=
nge. Hint: Try it with=C2=A0and without foreign=C2=A0keys.</div><div><br></=
div><div><a href=3D"https://www.postgresql.org/docs/current/catalog-pg-depe=
nd.html">https://www.postgresql.org/docs/current/catalog-pg-depend.html</a>=
</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
- pg_repack seems to update relcache entries. I am not sure how to do that =
with SQL.</blockquote><div><br></div><div>Yes, as I mentioned upthread, the=
 safest thing will probably be a restart.</div></div><div><br></div><div>Ch=
eers,</div><div>Greg</div><div><br></div><div dir=3D"ltr" class=3D"gmail_si=
gnature"><div dir=3D"ltr"><div>--</div><div>Crunchy Data - <a href=3D"https=
://www.crunchydata.com" target=3D"_blank">https://www.crunchydata.com</a></=
div><div>Enterprise Postgres Software Products &amp; Tech Support</div><div=
><br></div></div></div></div>

--0000000000006c4816062e4b0eea--