postgresql – 视图如何依赖于postgres中的主键约束

发布时间：2020-12-13 16:08:26 所属栏目：百科来源：网络整理

导读：有时,在批量数据加载时,建议临时删除表上的约束和索引.但是当我这样做时,我遇到了一些依赖问题.我的简化示例： CREATE TABLE public.t_place_type( id serial NOT NULL,c_name character varying(100),CONSTRAINT pk_t_place_type PRIMARY KEY (id));CREATE

有时,在批量数据加载时,建议临时删除表上的约束和索引.但是当我这样做时,我遇到了一些依赖问题.我的简化示例：

CREATE TABLE public.t_place_type
(
  id serial NOT NULL,c_name character varying(100),CONSTRAINT pk_t_place_type PRIMARY KEY (id)
);

CREATE TABLE public.t_place
(
  id serial NOT NULL,c_name character varying(50),id_place_type integer,CONSTRAINT pk_t_place PRIMARY KEY (id),CONSTRAINT fk_t_place_t_place_type FOREIGN KEY (id_place_type)
      REFERENCES public.t_place_type (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE OR REPLACE VIEW public.v_place AS 
 SELECT p.id,p.c_name,pt.c_name AS c_place_type
   FROM t_place p
     LEFT JOIN t_place_type pt ON pt.id = p.id_place_type
  GROUP BY p.id,pt.id,p.c_name;

我的剧本：

ALTER TABLE public.t_place DROP CONSTRAINT fk_t_place_t_place_type;
ALTER TABLE public.t_place DROP CONSTRAINT pk_t_place;
ALTER TABLE public.t_place_type DROP CONSTRAINT pk_t_place_type;

当我运行它时,我收到错误：

ERROR: cannot drop constraint pk_t_place_type on table t_place_type
because other objects depend on it
DETAIL: view v_place depends on constraint pk_t_place_type on table
t_place_type

我很奇怪,视图可以取决于某些约束. AFAIK postgres不会缓存视图的执行计划.

当我以这种方式改变我的观点时：

CREATE OR REPLACE VIEW public.v_place AS 
 SELECT p.id,p.c_name AS c_name,pt.c_name AS c_place_type
   FROM t_place p
     LEFT JOIN t_place_type pt ON pt.id = p.id_place_type;

依赖关系消失了,我的脚本成功执行了.

所以我的问题是：视图和约束之间存在这种依赖关系的原因是什么.

编辑

这里有https://www.postgresql.org/docs/9.5/static/sql-select.html#SQL-GROUPBY个postgres文档说：

When GROUP BY is present,or any aggregate functions are present,it
is not valid for the SELECT list expressions to refer to ungrouped
columns except within aggregate functions or when the ungrouped column
is functionally dependent on the grouped columns,since there would
otherwise be more than one possible value to return for an ungrouped
column. A functional dependency exists if the grouped columns (or a
subset thereof) are the primary key of the table containing the
ungrouped column.

这是这种行为的原因吗？即使我的观点中没有未分组的列？

解决方法

如果在public.t_place_type上删除PK,则视图中的查询将不起作用.

它会产生这个错误：

ERROR:  column "pt.c_name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3:     pt.c_name AS c_place_type
            ^

这是因为,根据您的文档引用,如果分组列(或其子集)是包含未分组列的表的主键,则存在功能依赖性.

Postgres知道PK代表唯一的行,所以一旦你按它分组,你也可以将该表中的所有列分组并得到相同的结果.

那些给出相同的结果,是未分组的行：

SELECT * FROM public.t_place;
SELECT * FROM public.t_place GROUP BY id;
SELECT * FROM public.t_place GROUP BY id,c_name;
SELECT * FROM public.t_place GROUP BY id,c_name,id_place_type;
SELECT * FROM public.t_place GROUP BY id,id_place_type;

并且在选择pt.c_name AS c_place_type时使用此依赖关系,因为您按主键pt.id对该表进行了分组,一旦删除它就不存在PK,因此按它进行分组会使pt.c_name都不在聚合中使用不用于分组.这就是Postgres抱怨视图依赖性的原因 – 一旦你放弃这个PK,它的查询将不再起作用.

您可以使用您问题中的修改示例自行尝试：

CREATE TABLE public.t_place_type
(
  id serial NOT NULL,c_name character varying(100)
);

CREATE TABLE public.t_place
(
  id serial NOT NULL,CONSTRAINT pk_t_place PRIMARY KEY (id)
);

 SELECT p.id,p.c_name;

/* RESULT:
ERROR:  column "pt.c_name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3:     pt.c_name AS c_place_type
            ^
*/

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!