On 2/13/25 14:59, Marcelo Fernandes wrote:
On Thu, Feb 13, 2025 at 7:37 PM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> 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
adrian.klaver@xxxxxxxxxxx
