postgresql Postgres ALTER TABLE 的问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3243863/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Problem with Postgres ALTER TABLE
提问by martin.malek
I have one problem with the ALTER TABLE in postgre. I want to change size of the varchar column. When I try to do this, It says that the view is dependent on that column. I can't drop the view because comething else is dependent on it. Is there any other way than to drop everything and recreate it again?
我对 postgre 中的 ALTER TABLE 有一个问题。我想更改 varchar 列的大小。当我尝试这样做时，它说视图依赖于该列。我不能放弃这个观点，因为其他东西依赖于它。除了丢弃所有内容并重新创建之外，还有其他方法吗？
I just found one option, which is to remove the table joining from the view, when I will not change the returned columns, I can do that. But still, there is more views I'll need to change. Isn't there anything how can I say that it should be deferred and checked with commit?
我刚刚找到了一个选项，即从视图中删除加入的表，当我不更改返回的列时，我可以这样做。但是，我仍然需要改变更多的观点。没有什么我怎么能说它应该被推迟并通过提交检查？
采纳答案by Dan LaRocque
I have run into this problem and couldn't find any way around it. Unfortunately, as best I can tell, one must drop the views, alter the column type on the underlying table, and then recreate the views. This can happen entirely in a single transaction.
我遇到了这个问题，但找不到任何解决方法。不幸的是，据我所知，必须删除视图，更改基础表上的列类型，然后重新创建视图。这可以完全在单个事务中发生。
Constraint deferral doesn't apply to this problem. In other words, even SET CONSTRAINTS ALL DEFERREDhas no impact on this limitation. To be specific, constraint deferral does not apply to the consistency check that prints ERROR: cannot alter type of a column used by a view or rulewhen one tries to alter the type of a column underlying a view.
约束延迟不适用于此问题。换句话说，即使SET CONSTRAINTS ALL DEFERRED对这个限制没有影响。具体来说，约束延迟不适用于在ERROR: cannot alter type of a column used by a view or rule尝试更改视图下的列的类型时打印的一致性检查。
回答by Hambone
I'm a little late to the party, but years after this question was posted, a brilliant solution was posted via an article referenced below (not mine -- I'm simply a thankful beneficiary of his brilliance).
我参加聚会有点晚了，但是在发布这个问题多年后，通过下面引用的一篇文章发布了一个绝妙的解决方案（不是我的 - 我只是他才华横溢的感谢受益者）。
I just tested this on an object that is referenced (on the first level) in 136 separate views, and each of those views is referenced in other views. The solution ran in mere seconds.
我刚刚在 136 个单独视图中引用（在第一级）的对象上对此进行了测试，并且这些视图中的每一个都在其他视图中引用。解决方案仅在几秒钟内运行。
So, read this article and copy and paste the table and two functions listed:
因此，请阅读本文并复制并粘贴表和列出的两个函数：
http://mwenus.blogspot.com/2014/04/postgresql-how-to-handle-table-and-view.html
http://mwenus.blogspot.com/2014/04/postgresql-how-to-handle-table-and-view.html
Implementation example:
实现示例：
alter table mdm.global_item_master_swap
alter column prod_id type varchar(128),
alter column prod_nme type varchar(512);
ERROR: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on view toolbox_reporting."Average_setcost" depends on column "prod_id" ********** Error **********
ERROR: cannot alter type of a column used by a view or rule
错误：无法更改视图或规则使用的列的类型详细信息：视图 toolbox_reporting 上的规则 _RETURN。“Average_setcost”取决于列“prod_id”********** 错误 ******** **
错误：无法更改视图或规则使用的列的类型
And now for the PostgreSQL ninja's magic:
现在来看看 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');
-- EDIT 11/13/2018 --
-- 编辑 11/13/2018 --
It appears the link above might be dead. Here is the code for the two procedures:
看来上面的链接可能已经死了。下面是两个过程的代码：
Table that stores DDL:
存储 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)
);
Save and Drop:
保存和删除：
CREATE OR REPLACE FUNCTION util.deps_save_and_drop_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 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$
LANGUAGE plpgsql VOLATILE
COST 100;
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;
回答by craigds
If you don't need to change the typeof the field, but just the size of it, this approach should work:
如果您不需要更改字段的类型，而只需要更改它的大小，则此方法应该有效：
Starting with these tables:
从这些表开始：
CREATE TABLE foo (id integer primary key, names varchar(10));
CREATE VIEW voo AS (SELECT id, names FROM foo);
\d fooand \d vooboth show the length as 10:
\d foo并且\d voo都将长度显示为 10：
id | integer | not null
names | character varying(10) |
Now change the lengths to 20 in the pg_attributetable:
现在将pg_attribute表中的长度更改为 20 ：
UPDATE pg_attribute SET atttypmod = 20+4
WHERE attrelid IN ('foo'::regclass, 'voo'::regclass)
AND attname = 'names';
(note: the 20+4 is some crazy postgresql legacy thing, the +4 is compulsory.)
（注意：20+4 是一些疯狂的 postgresql 遗留问题，+4 是强制性的。）
Now \d fooshows:
现在\d foo显示：
id | integer | not null
names | character varying(20) |
Bonus: that was waaay faster than doing:
奖励：这比做的要快：
ALTER TABLE foo ALTER COLUMN names TYPE varchar(20);
Technically you can change the size of the table column without changing the size of the view column, but no guarantees on what side effects that will have; it's probably best to change them both at once.
从技术上讲，您可以在不更改视图列大小的情况下更改表列的大小，但不能保证会产生什么副作用；最好同时更改它们。
source and fuller explanation: http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data
来源和更全面的解释：http: //sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changed-data
回答by bendiy
I ran into this problem today and found a work around to avoid dropping and recreating the VIEW . I cannot just drop my VIEW because it is a master VIEW that has many dependent VIEWs built on top of it. Short of having a rebuild script to DROP CASCADE and then recreate ALL of my VIEWs this is a work around.
我今天遇到了这个问题，并找到了一个解决方法来避免删除和重新创建 VIEW 。我不能只是放弃我的 VIEW，因为它是一个主 VIEW，在它之上构建了许多依赖的 VIEW。缺少重建脚本来删除级联，然后重新创建我的所有视图，这是一种解决方法。
I change my master VIEW to use a dummy value for the offending column, altered the column in the table, and switched my VIEW back to the column. Using a setup like this:
我将主 VIEW 更改为对违规列使用虚拟值，更改了表中的列，并将我的 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;
Trying to alter base_table_field1 type like this:
尝试像这样改变 base_table_field1 类型：
ALTER TABLE base_table ALTER COLUMN base_table_field1 TYPE numeric(10,6);
Will give you this 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"
If you change master_view to use a dummy value for the column like this:
如果您更改 master_view 以使用列的虚拟值，如下所示：
CREATE OR REPLACE VIEW master_view AS
SELECT
base_table_id AS id,
0.9999 AS field1
FROM base_table;
Then run your alter:
然后运行你的alter：
ALTER TABLE base_table ALTER COLUMN base_table_field1 TYPE numeric(10,6);
And switch your view back:
并切换回您的视图：
CREATE OR REPLACE VIEW master_view AS
SELECT
base_table_id AS id,
(base_table_field1 * .01)::numeric AS field1
FROM base_table;
It all depends on if your master_view has an explicit type that does not change. Since my VIEW uses '(base_table_field1 * .01)::numeric AS field1' it works, but 'base_table_field1 AS field1' would not because the column type changes. This approach might help in some cases like mine.
这完全取决于您的 master_view 是否具有不变的显式类型。由于我的 VIEW 使用 '(base_table_field1 * .01)::numeric AS field1' 它可以工作，但 'base_table_field1 AS field1' 不会，因为列类型发生了变化。这种方法在某些情况下可能会有所帮助，例如我的情况。
