postgresql - example - Problema con Postgres ALTER TABLE

postgres alter add column

Question

Tengo un problema con la ALTER TABLE en postgre. Quiero cambiar el tamaño de la columna varchar. Cuando intento hacer esto, dice que la vista depende de esa columna. No puedo dejar de lado la vista porque cometer más depende de ello. ¿Hay alguna otra manera que dejar todo y volver a crearlo?

Acabo de encontrar una opción, que es eliminar la tabla que se une a la vista, cuando no cambie las columnas devueltas, puedo hacerlo. Pero aún así, hay más puntos de vista que tendré que cambiar. ¿No hay nada, cómo puedo decir que debería aplazarse y comprobarse con confirmación?

Answer 1

Llego un poco tarde a la fiesta, pero años después de que se publicara esta pregunta, se publicó una solución brillante a través de un artículo al que se hace referencia a continuación (no es mío; simplemente soy un agradecido beneficiario de su brillantez).

Acabo de probar esto en un objeto al que se hace referencia (en el primer nivel) en 136 vistas separadas, y se hace referencia a cada una de esas vistas en otras vistas. La solución se ejecutó en unos pocos segundos.

Entonces, lea este artículo y copie y pegue la tabla y dos funciones enumeradas:

http://mwenus.blogspot.com/2014/04/postgresql-how-to-handle-table-and-view.html

Ejemplo de implementación:

alter table mdm.global_item_master_swap
alter column prod_id type varchar(128),
alter column prod_nme type varchar(512);

ERROR: no se puede alterar el tipo de una columna utilizada por una vista o regla DETALLE: regla _RETURN on view toolbox_reporting. "Average_setcost" depende de la columna "prod_id" ********** Error ******** **

ERROR: no se puede alterar el tipo de una columna utilizada por una vista o regla

Y ahora para la magia del ninja PostgreSQL:

select util.deps_save_and_drop_dependencies(''mdm'', ''global_item_master_swap'');


alter table mdm.global_item_master_swap
alter column prod_id type varchar(128),
alter column prod_nme type varchar(512);


select util.deps_restore_dependencies(''mdm'', ''global_item_master_swap'');

Answer 2

Me encontré con este problema hoy y encontré una solución para evitar dejar caer y recrear el VIEW. No puedo soltar mi VISTA porque es una VISTA maestra que tiene muchas VISTAS dependientes construidas encima de ella. Aparte de tener un script de reconstrucción para DROP CASCADE y luego recrear TODAS mis VISTAS, esto es un problema.

Cambié mi VISTA maestra para usar un valor ficticio para la columna ofensiva, modifiqué la columna en la tabla y cambié mi VISTA a la columna. Usando una configuración como esta:

CREATE TABLE base_table
(
  base_table_id integer,
  base_table_field1 numeric(10,4)
);

CREATE OR REPLACE VIEW master_view AS 
  SELECT
    base_table_id AS id,
    (base_table_field1 * .01)::numeric AS field1
  FROM base_table;

CREATE OR REPLACE VIEW dependent_view AS 
  SELECT
    id AS dependent_id,
    field1 AS dependent_field1
  FROM master_view;

Intentando alterar el tipo base_table_field1 de esta manera:

ALTER TABLE base_table ALTER COLUMN base_table_field1 TYPE numeric(10,6);

Te daré este error:

ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view master_view depends on column "base_table_field1"

Si cambia master_view para usar un valor ficticio para la columna como esta:

CREATE OR REPLACE VIEW master_view AS 
  SELECT
    base_table_id AS id,
    0.9999 AS field1
  FROM base_table;

A continuación, ejecute su alter:

ALTER TABLE base_table ALTER COLUMN base_table_field1 TYPE numeric(10,6);

Y cambia tu vista de nuevo:

CREATE OR REPLACE VIEW master_view AS 
  SELECT
    base_table_id AS id,
    (base_table_field1 * .01)::numeric AS field1
  FROM base_table;

Todo depende de si su master_view tiene un tipo explícito que no cambia. Dado que mi VISTA usa ''(base_table_field1 * .01) :: numeric AS field1'' funciona, pero ''base_table_field1 AS field1'' no funcionaría porque el tipo de columna cambia. Este enfoque podría ayudar en algunos casos como el mío.

Answer 3

Me he encontrado con este problema y no pude encontrar ninguna manera de evitarlo. Desafortunadamente, lo mejor que puedo decir es que se deben eliminar las vistas, modificar el tipo de columna en la tabla subyacente y luego volver a crear las vistas. Esto puede ocurrir completamente en una sola transacción.

El aplazamiento de restricciones no se aplica a este problema. En otras palabras, incluso SET CONSTRAINTS ALL DEFERRED no tiene impacto en esta limitación. Para ser específico, el aplazamiento de la restricción no se aplica a la verificación de consistencia que imprime ERROR: cannot alter type of a column used by a view or rule cuando se intenta modificar el tipo de una columna subyacente a una vista.

Answer 4

Si no necesita cambiar el tipo de campo, sino el tamaño del mismo, este enfoque debería funcionar:

A partir de estas tablas:

CREATE TABLE foo (id integer primary key, names varchar(10));
CREATE VIEW voo AS (SELECT id, names FROM foo);

/d foo y /d voo muestran la longitud como 10:

id     | integer               | not null
names  | character varying(10) | 

Ahora cambie las longitudes a 20 en la tabla pg_attribute :

UPDATE pg_attribute SET atttypmod = 20+4
WHERE attrelid IN (''foo''::regclass, ''voo''::regclass)
AND attname = ''names'';

(nota: el 20 + 4 es una locura del legado postgresql, el +4 es obligatorio).

Ahora /d foo shows:

id     | integer               | not null
names  | character varying(10) | 

Bonus: eso fue más rápido que hacerlo:

ALTER TABLE foo ALTER COLUMN names TYPE varchar(20);

Técnicamente, puede cambiar el tamaño de la columna de la tabla sin cambiar el tamaño de la columna de la vista, pero no hay garantías sobre qué efectos secundarios tendrá; Probablemente sea mejor cambiarlos a la vez.

fuente y explicación más completa: http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data