¿Cómo puedo intercambiar nombres de tabla y sus referencias atómicamente en postgres sin tener problemas?
Quiero intercambiar los nombres de dos tablas entre sí. Tabla A <> Tabla B. También de manera atómica para evitar problemas de lectura/escritura. Sé que puedo hacer esto en una transacción.
Estoy creando la tabla usando – “CREATE TABLE TableB (LIKE TableA INCLUDING ALL);”. Luego también copio las FK de A a B. Luego hago un “INSERT INTO TABLEA….” para copiar los datos también (irrelevantes para esto). Una vez que todo esto está hecho, renombro la tabla usando “ALTER TABLE RENAME” para intercambiar los nombres TableA <> TableB en una transacción también. Así que TableA se convierte en TableA_Old y TableB se convierte en el nuevo TableA (ejemplo a continuación)
Sin embargo, esto no actualiza las referencias a esta nueva tabla en otras tablas, como TableC, que aún tiene una FK contra TableA_Old. Cuando hago un “\d+” en la tabla recién renombrada TableA (que era TableB antes), no veo las referencias. Todavía apuntan a TableA_Old.
Aquí hay un ejemplo
Creo 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)
Creo TableC con referencia a TableA
CREATE TABLE TableC(
id serial PRIMARY KEY,
tablea_id INT,
CONSTRAINT tableatablecfk
FOREIGN KEY(tablea_id)
REFERENCES TableA(id)
);
\d TableC
Table "public.tablec"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+------------------------------------
id | integer | | not null | nextval('tablec_id_seq'::regclass)
tablea_id | integer | | |
Indexes:
"tablec_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"tableatablecfk" FOREIGN KEY (tablea_id) REFERENCES tablea(id)
Puede ver la referencia
Ahora creo TableB que se parece a TableA usando una función
create or replace function createtablelike(sourcetable text, newtable text)
returns void language plpgsql
as $$
declare
rec record;
begin
execute format(
'create table %s (like %s including all)',
newtable, sourcetable);
for rec in
select oid, conname
from pg_constraint
where contype = 'f'
and conrelid = sourcetable::regclass
loop
execute format(
'alter table %s add constraint %s %s',
newtable,
replace(rec.conname, sourcetable, newtable),
pg_get_constraintdef(rec.oid));
end loop;
end $$;
select createtablelike('TableA', 'TableB');
\d TableB
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
id | integer | | not null | nextval('tablea_id_seq'::regclass)
Indexes:
"tableb_pkey" PRIMARY KEY, btree (id)
Ahora los renombro
BEGIN;
ALTER TABLE TableA RENAME to TableA_OLD;
ALTER TABLE TableB RENAME to TableA;
COMMIT;
Ahora cuando veo las estructura, la referencia sigue siendo a TableA_OLD en 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 TableAold
Table "public.tableaold"
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 "tableatablecfk" FOREIGN KEY (tablea_id) REFERENCES tableaold(id)
Y TableC apunta a la tabla antigua – REFERENCES tableaold(id)
¿Existe una forma segura de hacer esto sin tener que eliminar y recrear las restricciones nuevamente?
Sé que también puedo actualizar el puntero relfilenode en pg_class y cambiarlos. Sin embargo, eso es arriesgado ya que TableB podría tener un esquema ligeramente diferente. Entonces, me pregunto si puedo actualizar otra tabla en el catálogo del sistema o usar un DDL que no requiera eliminar la restricción.
davy.ai
Una manera segura de intercambiar los nombres de dos tablas entre sí sin eliminar y recrear restricciones es usar un proceso de tres pasos. Los pasos son los siguientes:
Este proceso es seguro porque permite que el sistema actualice las referencias a la nueva tabla y evite la corrupción de datos. Aquí hay un ejemplo de cómo implementar este proceso con
TableA
yTableB
:Este enfoque garantiza que todas las referencias se actualicen atómicamente y elimina la necesidad de eliminar y recrear restricciones de clave externa.