logo标签列表

Postgres ALTER TABLE存在问题

postgresqldependenciesalter-table
48

我在PostgreSQL中使用ALTER TABLE时遇到了问题。我想要改变一个varchar列的大小，但是当我尝试这样做时，它显示这个视图依赖于那一列，因此我无法删除该视图，因为它会影响其他内容。有没有办法除了删除所有内容并重新创建以外？

我找到了一种解决方案，即从视图中删除表格连接，只要不更改返回的列，我就可以这样做。但仍然有更多的视图需要更改。是否有任何方法可以说应该延迟检查，并在提交时进行检查？

2
复制视图查询，然后删除它并对表进行更改。 - TaherT
1
我刚刚发现，带有 EXISTS ( SELECT * FROM ... 的视图会在exists子查询中涉及的所有表的所有列上引入非常不必要的依赖关系，因此创建带有 EXISTS (SELECT 1 FROM ... 的视图可能是一个更好的选择。 - Ezequiel Tolnay
6个回答
34

我遇到了这个问题，但找不到解决方法。据我所知，唯一的方法是删除视图，修改底层表的列类型，然后重新创建视图。这可以在一个事务中完成。

约束延迟不适用于此问题。换句话说，即使使用SET CONSTRAINTS ALL DEFERRED也无法解决这个限制。具体来说，约束延迟不适用于一致性检查，当试图更改视图下面的列的类型时，会打印出ERROR: cannot alter type of a column used by a view or rule。

我们发现了这段代码（https://gist.github.com/mateuszwenus/11187288），它可以更轻松地删除和重新创建依赖视图 - 它将定义存储在表中，然后使用该定义来重建视图。它包括授权和注释，但不包括物化索引。此外，如果运行函数的角色没有对视图拥有所有权（或等效）权限，则会出现错误。 - Dan
31
我有点晚来参加这个派对，但是在这个问题发布多年后，一个非常棒的解决方案通过下面引用的一篇文章发布了出来（不是我发布的 -- 我只是他的智慧的受益者而已）。
我刚刚在一个被引用的对象上进行了测试（在第一层级上），它在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."Average_setcost"上的规则_RETURN依赖于列"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)
);

保存和删除：

-- 编辑 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;

-- 编辑于2023年9月14日 --
我们注意到这个问题是它将视图的所有权更改为运行函数的人。如果这是个问题，可以通过对“保存并删除”函数进行小的修改来解决。
添加一个声明：
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;
2
你是一个伟大的灵魂。 - Vishnu
1
@Hambone，你能否分享一下上述两个函数中使用的表格结构？链接已失效，而你只发布了函数代码。 - Harsh Kumar
1
虽然我在2020年更改了这行代码：select p_view_schema, p_view_name, 'GRANT ' || privilege_type || ' ON ' || table_schema || '.' || table_name || ' TO ' || grantee，但仍在使用它。更改后的代码为：select p_view_schema, p_view_name, 'GRANT ' || privilege_type || ' ON ' || table_schema || '.' || table_name || ' TO "' || grantee || '"'，因为我的用户名中有一个句点。您仍然可以通过psql用户名进行SQL注入，因此请谨慎使用此脚本！ - Joshua Klein
2
这太棒了 - 对我来说完美地更新了有数百个依赖视图的表。 - scook
2
@RajshriMohanKS - 这似乎是与Postgres 12有关的问题。我正在调查，因为我刚刚遇到了同样的错误。 - Hambone
显示剩余7条评论
16

如果您不需要更改字段的类型，只需更改其大小，则可以尝试以下方法：

从以下表格开始：

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

14
尽可能避免手动更改目录（如pg_attribute）。存在真正的风险，会在你最不希望出错的时候导致错误，包括崩溃和数据损坏。只有在最后一步时才进行更改，并在查看源代码以确保没有遗漏任何内容后再进行操作。没有任何免责声明的建议是不负责任的。 - intgr
1
这是数据引擎在幕后真正执行的方式。您不会更改类型，而是更改大小。由于它是VARCHAR，因此不会对数据造成任何损害。+1 - Luiz Vaz
6
今天我遇到了这个问题，并找到了一个解决方法，可以避免删除和重新创建视图。 我不能只删除我的视图，因为它是一个主视图，有许多依赖于它的视图。 除了拥有一个重建脚本来DROP CASCADE并重新创建所有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 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'则不行，因为列类型会发生变化。这种方法可能在某些情况下有所帮助。

6
这种方法比删除视图、修改表格然后重新创建视图更好在哪里？我认为这种方法更糟糕，因为需要查看视图的数据定义语言（DDL）并找到列的实例。如果你要删除，只需保留原始视图的DDL副本，以便可以重新创建它。 - ADTC
5
相比于直接删除视图，使用这种方法有何优势？第一行提到了一个重要信息：“这是一个被很多依赖视图所构建的主视图。”也就是说，删除该视图会级联将其依赖的子视图一并删除。 - Rob Bygrave
1
我想评论第二个答案，但由于我在stackoverflow上太新了，所以不能评论，这里是我的评论： 对于那些对提到的原始文章感兴趣的人，博客文章已不再可用，但wayback machine仍然保存着它：https://web.archive.org/web/20180323155900/http://mwenus.blogspot.com/2014/04/postgresql-how-to-handle-table-and-view.html 以下是文章本身，以防将来存档网站关闭： 2014-04-22 PostgreSQL：如何处理表和视图依赖关系 PostgreSQL在修改现有对象时非常严格。当你尝试ALTER TABLE或REPLACE VIEW时，通常会告诉你不能这样做，因为有另一个对象（通常是视图或材料化视图）依赖于你要修改的对象。似乎唯一的解决方案是删除相关对象，对目标对象进行所需更改，然后重新创建已删除的对象。
这是一项繁琐且复杂的工作，因为这些依赖对象可能有进一步的依赖关系，而这些依赖关系又可能有其他依赖关系，以此类推。我创建了实用函数，可以在这种情况下提供帮助。
使用非常简单 - 只需调用： select deps_save_and_drop_dependencies(p_schema_name, p_object_name); 您需要传递两个参数：模式的名称和该模式中对象的名称。该对象可以是表、视图或物化视图。该函数将删除所有依赖于p_schema_name.p_object_name的视图和物化视图，并保存DDL以在辅助表中恢复它们。
当您想要恢复这些已删除的对象（例如，当您完成修改p_schema_name.p_object_name时），您只需要进行另一个简单的调用： select deps_restore_dependencies(p_schema_name, p_object_name); 然后这些已删除的对象就会被重新创建。
这些函数负责： 依赖层次结构 跨层次正确删除和创建视图/物化视图的顺序 恢复视图/物化视图上的注释和授予权限 点击此处查看可工作的sqlfiddle示例 或查看 此gist 获取完整源代码。

作者：Mateusz Wenus于19:32

0
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 $$;
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接