Postgres ALTER TABLE 的问题

Question

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

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

Answer 1

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

约束延迟不适用于此问题。换句话说，即使SET CONSTRAINTS ALL DEFERRED对此限制也没有影响。具体来说，约束延迟不适用于当尝试更改视图下的列类型时打印ERROR:cannot alter type of a column use 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');

-- 编辑 11/13/ 2018 年——

上面的链接似乎已失效。以下是两个过程的代码：

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

保存并删除：

-- 编辑 8/28/2020 --
-- 这对 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$

Restore:

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;

-- 编辑 9/14/2023 --

我们注意到这个问题的一个问题是它将视图所有权更改为运行该函数的任何人。如果这是一个问题，可以通过“保存和删除”功能的一个小补充来解决。

添加一个声明：

declare
  v_curr record;
  owner_id varchar;   -- add this

并在函数的最后添加以下内容：

  select viewowner
  into owner_id
  from pg_catalog.pg_views v
  where
    v.schemaname = v_curr.obj_schema and
    v.viewname = 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, 'ALTER VIEW OWNER TO ' || owner_id
    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, 'ALTER MATERIALIZED VIEW OWNER TO ' || owner_id
    from pg_matviews
    where schemaname = v_curr.obj_schema and matviewname = v_curr.obj_name;
  end if;

  -- code below already exists (for reference point)

  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;

Answer 3

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

从这些表开始：

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

\d foo 和 < code>\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-change-data

Answer 4

我今天遇到了这个问题，并找到了一种解决方法来避免删除和重新创建 VIEW 。我不能直接删除我的视图，因为它是一个主视图，有许多依赖于它的视图。如果没有重建脚本来删除级联，然后重新创建我的所有视图，这是一个解决方法。

我更改主视图以对有问题的列使用虚拟值，更改表中的列，然后将视图切换回该列。使用这样的设置：

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;

尝试像这样改变base_table_field1类型：

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

会给你这个错误：

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"

如果你改变master_view以使用像这样的列的虚拟值：

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

然后运行你的alter：

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

并切换回你的视图：

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

这一切都取决于如果您的 master_view 有一个不会改变的显式类型。由于我的 VIEW 使用 '(base_table_field1 * .01)::numeric AS field1' 它可以工作，但 'base_table_field1 AS field1' 不会，因为列类型发生了变化。这种方法可能对像我这样的某些情况有所帮助。

Answer 5

我想对第二个答案发表评论，但不能，因为我对 stackoverflow 太陌生，所以这里是我的评论：
对于那些对该答案中提到的原始文章感兴趣的人，blogspot 条目不再可用，但回程机仍然存储它： https://web.archive.org/web/20180323155900/http ://mwenus.blogspot.com/2014/04/postgresql-how-to-handle-table-and-view.html
以下是文章本身，以防 archive.org 在未来某个时间点被关闭：
2014-04-22
PostgreSQL：如何处理表和视图依赖关系
PostgreSQL 在修改现有对象方面非常严格。很多时候，当您尝试更改表或替换视图时，它会告诉您无法执行此操作，因为还有另一个对象（通常是视图或物化视图），这取决于您要修改的对象。似乎唯一的解决方案是删除依赖对象，对目标对象进行所需的更改，然后重新创建删除的对象。

这是乏味和麻烦的，因为那些依赖对象可以有进一步的依赖关系，这些依赖关系还可能有其他依赖关系等等。我创建了实用函数，它可以在这种情况下提供帮助。

使用方法非常简单 - 你只需调用：
选择 deps_save_and_drop_dependency(p_schema_name, p_object_name);
您必须传递两个参数：模式的名称和该模式中对象的名称。该对象可以是表、视图或物化视图。该函数将删除依赖于 p_schema_name.p_object_name 的所有视图和物化视图，并保存将它们恢复到辅助表中的 DDL。

当您想要恢复那些删除的对象时（例如，当您完成修改 p_schema_name.p_object_name 时），您只需要进行另一个简单的调用：
选择 deps_restore_dependency(p_schema_name, p_object_name);
并且删除的对象将被重新创建。

这些函数负责：
依赖层次结构
跨层次结构删除和创建视图/物化视图的正确顺序
恢复对视图/物化视图的评论和授权
单击此处查看有效的 sqlfiddle 示例 或检查 此要点以获得完整的源代码。

作者：Mateusz Wenus o 19:32

Answer 6

do $            
  declare gorev_lisans_ihlali_def text;
  declare exec_text text;
begin          
  gorev_lisans_ihlali_def := pg_get_viewdef('public.gorev_lisans_ihlali');
  drop view public.gorev_lisans_ihlali;

    
  exec_text := format('create view public.gorev_lisans_ihlali as %s', 
     gorev_lisans_ihlali_def);
      ALTER TABLE public.ara_bakis_duyma
        ALTER COLUMN gain TYPE   DOUBLE PRECISION;
  execute exec_text;
end $;