How can I atomically swap table names and its references in postgres without any issues?-CodePudding

I want to swap names of two tables with each other. TableA <> TableB. Also atomically to avoid any issues of read/writes. I know I can do that in a transction.

I am creating the table using - CREATE TABLE TableB (LIKE TableA INCLUDING ALL);. Then I am also copying over the FKs from from A to B. I am then doing a INSERT INTO TABLEA.... to copy over the data as well (irrelevant for this). Once all of this is done, I rename the table using ALTER TABLE RENAME to swap the names TableA <> TableB in a transaction as well. So TableA becomes TableA_Old and TableB becomes the new TableA (example below)

However, this doesn't update the references to this new table in other tables, like TableC which still hold a FK against TableA_Old. When I do a \d on the newly renamed table TableA (which was TableB) before, I don't see the references. They still point to TableA_Old.

Here is an example

I create TableA

CREATE TABLE TableA (
    id serial PRIMARY KEY
);


testdb=> \d TableA
                            Table "public.tablea"
 Column |  Type   | Collation | Nullable |              Default               
-------- --------- ----------- ---------- ------------------------------------
 id     | integer |           | not null | nextval('tablea_id_seq'::regclass)
Indexes:
    "tablea_pkey" PRIMARY KEY, btree (id)

I create TableC with reference to TableA

CREATE TABLE TableC(
    id serial PRIMARY KEY,
    table_a_id INT,
   CONSTRAINT table_a_table_c_fk
      FOREIGN KEY(table_a_id) 
      REFERENCES TableA(id)
);


\d TableC
                              Table "public.tablec"
   Column   |  Type   | Collation | Nullable |              Default               
------------ --------- ----------- ---------- ------------------------------------
 id         | integer |           | not null | nextval('tablec_id_seq'::regclass)
 table_a_id | integer |           |          | 
Indexes:
    "tablec_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "table_a_table_c_fk" FOREIGN KEY (table_a_id) REFERENCES tablea(id)

You can see the reference

Now I create TableB that looks like TableA using a function

create or replace function create_table_like(source_table text, new_table text)
returns void language plpgsql
as $$
declare
    rec record;
begin
    execute format(
        'create table %s (like %s including all)',
        new_table, source_table);
    for rec in
        select oid, conname
        from pg_constraint
        where contype = 'f' 
        and conrelid = source_table::regclass
    loop
        execute format(
            'alter table %s add constraint %s %s',
            new_table,
            replace(rec.conname, source_table, new_table),
            pg_get_constraintdef(rec.oid));
    end loop;
end $$;

select create_table_like('TableA', 'TableB');

\d TableB

testdb=> select create_table_like('TableA', 'TableB');
 create_table_like 
-------------------
 
(1 row)

testdb=> \d TableB

                            Table "public.tableb"
 Column |  Type   | Collation | Nullable |              Default               
-------- --------- ----------- ---------- ------------------------------------
 id     | integer |           | not null | nextval('tablea_id_seq'::regclass)
Indexes:
    "tableb_pkey" PRIMARY KEY, btree (id)

Now I rename them

BEGIN;
ALTER TABLE TableA RENAME to TableA_OLD;
ALTER TABLE TableB RENAME to TableA;
COMMIT;

Now when I look at the structures, the reference is still to TableA_OLD from TableC

testdb=> \d TableA
                            Table "public.tablea"
 Column |  Type   | Collation | Nullable |              Default               
-------- --------- ----------- ---------- ------------------------------------
 id     | integer |           | not null | nextval('tablea_id_seq'::regclass)
Indexes:
    "tableb_pkey" PRIMARY KEY, btree (id)

testdb=> \d TableB



testdb=> \d TableA_old
                          Table "public.tablea_old"
 Column |  Type   | Collation | Nullable |              Default               
-------- --------- ----------- ---------- ------------------------------------
 id     | integer |           | not null | nextval('tablea_id_seq'::regclass)
Indexes:
    "tablea_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "tablec" CONSTRAINT "table_a_table_c_fk" FOREIGN KEY (table_a_id) REFERENCES tablea_old(id)

And TableC is pointing at the old table - REFERENCES tablea_old(id)

testdb=> \d TableC
                              Table "public.tablec"
   Column   |  Type   | Collation | Nullable |              Default               
------------ --------- ----------- ---------- ------------------------------------
 id         | integer |           | not null | nextval('tablec_id_seq'::regclass)
 table_a_id | integer |           |          | 
Indexes:
    "tablec_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "table_a_table_c_fk" FOREIGN KEY (table_a_id) REFERENCES tablea_old(id)

Is there a safe way to do this without dropping and recreating the constraints again?

I know i can also update the relfilenode pointer in pg_class and swap them. However, that is risky since TableB could have a slightly different looking schema. So, I am wondering if I can update some other table in the system catalogue or use a DDL that wouldn't require dropping the constraint.

CodePudding user response：

The true identity of an object is its object ID, a number that is used to refer to the object in foreign key constraints an other internal database matters (with the notable exception of most function bodies). Renaming an object does not change its identity. You have to drop and re-create foreign key constraints.

CodePudding user response：

If you use the rename table command then automatically will be renamed all dependencies objects, foreign keys, references linked to this table. For example:

ALTER TABLE public.table1 RENAME TO table2;