Postgres ALTER TABLE问题

Question

我对postgre中的ALTER TABLE有一个问题。我想更改varchar列的大小。当我尝试这样做时，它告诉我视图依赖于该列。我不能删除视图，因为其他东西都依赖于它。除了丢弃所有东西并重新创建它之外，还有其他方法吗？

我只找到了一个选项，那就是从视图中删除表连接，当我不会更改返回的列时，我可以这样做。但是，我仍然需要改变更多的观点。我怎么能说它应该被推迟并通过提交进行检查呢？

Answer 1

我遇到了这个问题，找不到任何解决方法。不幸的是，据我所知，必须删除视图，更改底层表上的列类型，然后重新创建视图。这完全可以在单个事务中发生。

约束延迟不适用于此问题。换句话说，即使是SET CONSTRAINTS ALL DEFERRED也不会对此限制产生影响。具体地说，约束延迟不适用于在试图更改视图底层列的类型时打印ERROR: cannot alter type of a column used by a view or rule的一致性检查。

Answer 2

我来得有点晚，但在这个问题发表多年后，通过下面引用的一篇文章发布了一个精彩的解决方案(不是我的--我只是感谢他的才华的受益者)。

我刚刚在一个对象上测试了这一点，该对象在136个单独的视图中被引用(在第一层)，并且每个视图都在其他视图中被引用。解决方案运行在短短几秒钟内。

因此，请阅读本文，复制并粘贴列出的表和两个函数：

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

实现示例：

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

错误:无法更改视图使用的列的类型或规则详细信息:视图toolbox_reporting上的规则_RETURN。“Average_setcost”依赖于列"prod_id“*错误*

错误:无法更改视图或规则使用的列的类型

现在来看看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');

-- 2018年11月13日编辑--

上面的链接可能已经死了。下面是这两个过程的代码：

存储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)
);

保存和删除：

--编辑2020年8月28日-这已停止使用Pg12。下面是将p_view_schema和p_view_name的参数从varchar更改为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$

恢复：

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;

Answer 3

如果您不需要更改字段的类型，而只需要更改字段的大小，则此方法应该有效：

从以下表格开始：

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

\d foo和\d voo都显示长度为10：

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

现在将pg_attribute表中的长度更改为20：

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

(注意: 20+4是一些疯狂的postgresql遗留的东西，+4是必须的。)

现在\d foo显示：

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

奖励:这比做以下事情要快得多：

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

从技术上讲，您可以在不更改视图列的大小的情况下更改表列的大小，但不能保证这会有什么副作用；最好同时更改这两个列。

源代码和更全面的解释：http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data