public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: Marcelo Fernandes <[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: Thu, 13 Feb 2025 15:35:43 -0800
Message-ID: <[email protected]> (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 2/13/25 14:59, Marcelo Fernandes wrote:
> On Thu, Feb 13, 2025 at 7:37 PM Laurenz Albe <[email protected]> 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 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?
1) In a previous post you said:
"Yes, in this scenario the copy is already created, and triggers keep
the copy in sync with the original table."
In that case the copy will already have TOAST tables associated with it.
2) What are the FK relationships and how many?
Also could you just not add the FK's as NOT VALID?
>
> 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
--
Adrian Klaver
[email protected]
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: <[email protected]>
* 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