Solución:
Me he encontrado con este problema y no pude encontrar ninguna forma de solucionarlo. Desafortunadamente, lo mejor que puedo decir, uno debe eliminar las vistas, alterar el tipo de columna en la tabla subyacente y luego volver a crear las vistas. Esto puede suceder 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 ningún impacto en esta limitación. Para ser específico, el aplazamiento de restricciones no se aplica a la verificación de coherencia que imprime ERROR: cannot alter type of a column used by a view or rule cuando se intenta alterar el tipo de columna subyacente a una vista.
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 el 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 cada una de esas vistas se hace referencia en otras vistas. La solución se ejecutó en cuestión de segundos.
Entonces, lea este artículo y copie y pegue la tabla y las 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 columna utilizada por una vista o regla DETALLE: regla _RETURN en la vista toolbox_reporting. “Average_setcost” depende de la columna “prod_id” ********** Error ******** **
ERROR: no se puede alterar el tipo de columna utilizada por una vista o regla
Y ahora la magia del ninja de 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');
– EDITAR 13/11/2018 –
Parece que el enlace anterior podría estar inactivo. Aquí está el código para los dos procedimientos:
Tabla que almacena DDL:
CREATE TABLE util.deps_saved_ddl
(
deps_id serial NOT NULL,
deps_view_schema character varying(255),
deps_view_name character varying(255),
deps_ddl_to_run text,
CONSTRAINT deps_saved_ddl_pkey PRIMARY KEY (deps_id)
);
Guardar y soltar:
– Editar 28/8/2020 – – Esto dejó de funcionar con Pg12. La solución se encuentra a continuación para cambiar los parámetros de p_view_schema y p_view_name de varchar a name:
CREATE OR REPLACE FUNCTION util.deps_save_and_drop_dependencies(
p_view_schema name, p_view_name name)
RETURNS void
LANGUAGE plpgsql
COST 100
AS $BODY$
declare
v_curr record;
begin
for v_curr in
(
select obj_schema, obj_name, obj_type from
(
with recursive recursive_deps(obj_schema, obj_name, obj_type, depth) as
(
select p_view_schema, p_view_name, null::varchar, 0
union
select dep_schema::varchar, dep_name::varchar, dep_type::varchar, recursive_deps.depth + 1 from
(
select ref_nsp.nspname ref_schema, ref_cl.relname ref_name,
rwr_cl.relkind dep_type,
rwr_nsp.nspname dep_schema,
rwr_cl.relname dep_name
from pg_depend dep
join pg_class ref_cl on dep.refobjid = ref_cl.oid
join pg_namespace ref_nsp on ref_cl.relnamespace = ref_nsp.oid
join pg_rewrite rwr on dep.objid = rwr.oid
join pg_class rwr_cl on rwr.ev_class = rwr_cl.oid
join pg_namespace rwr_nsp on rwr_cl.relnamespace = rwr_nsp.oid
where dep.deptype="n"
and dep.classid = 'pg_rewrite'::regclass
) deps
join recursive_deps on deps.ref_schema = recursive_deps.obj_schema and deps.ref_name = recursive_deps.obj_name
where (deps.ref_schema != deps.dep_schema or deps.ref_name != deps.dep_name)
)
select obj_schema, obj_name, obj_type, depth
from recursive_deps
where depth > 0
) t
group by obj_schema, obj_name, obj_type
order by max(depth) desc
) loop
insert into util.deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'COMMENT ON ' ||
case
when c.relkind = 'v' then 'VIEW'
when c.relkind = 'm' then 'MATERIALIZED VIEW'
else ''
end
|| ' ' || n.nspname || '.' || c.relname || ' IS ''' || replace(d.description, '''', '''''') || ''';'
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
join pg_description d on d.objoid = c.oid and d.objsubid = 0
where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null;
insert into util.deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'COMMENT ON COLUMN ' || n.nspname || '.' || c.relname || '.' || a.attname || ' IS ''' || replace(d.description, '''', '''''') || ''';'
from pg_class c
join pg_attribute a on c.oid = a.attrelid
join pg_namespace n on n.oid = c.relnamespace
join pg_description d on d.objoid = c.oid and d.objsubid = a.attnum
where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null;
insert into util.deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'GRANT ' || privilege_type || ' ON ' || table_schema || '.' || table_name || ' TO ' || grantee
from information_schema.role_table_grants
where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;
if v_curr.obj_type="v" then
insert into util.deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'CREATE VIEW ' || v_curr.obj_schema || '.' || v_curr.obj_name || ' AS ' || view_definition
from information_schema.views
where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;
elsif v_curr.obj_type="m" then
insert into util.deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'CREATE MATERIALIZED VIEW ' || v_curr.obj_schema || '.' || v_curr.obj_name || ' AS ' || definition
from pg_matviews
where schemaname = v_curr.obj_schema and matviewname = v_curr.obj_name;
end if;
execute 'DROP ' ||
case
when v_curr.obj_type="v" then 'VIEW'
when v_curr.obj_type="m" then 'MATERIALIZED VIEW'
end
|| ' ' || v_curr.obj_schema || '.' || v_curr.obj_name;
end loop;
end;
$BODY$
Restaurar:
CREATE OR REPLACE FUNCTION util.deps_restore_dependencies(
p_view_schema character varying,
p_view_name character varying)
RETURNS void AS
$BODY$
declare
v_curr record;
begin
for v_curr in
(
select deps_ddl_to_run
from util.deps_saved_ddl
where deps_view_schema = p_view_schema and deps_view_name = p_view_name
order by deps_id desc
) loop
execute v_curr.deps_ddl_to_run;
end loop;
delete from util.deps_saved_ddl
where deps_view_schema = p_view_schema and deps_view_name = p_view_name;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Si no necesita cambiar el escribe del campo, pero solo el tamaño, este enfoque debería funcionar:
Comenzando con 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 ambos muestran la longitud como 10:
id | integer | not null
names | character varying(10) |
Ahora cambie las longitudes a 20 en el pg_attribute mesa:
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 de postgresql, el +4 es obligatorio).
Ahora d foo muestra:
id | integer | not null
names | character varying(20) |
Bono: eso fue mucho más rápido que hacer:
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 de qué efectos secundarios tendrá; probablemente sea mejor cambiarlos a los dos a la vez.
fuente y explicación más completa: http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data