postgresql - 如何查看取决于postgres中的主键约束

Question

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

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

令我感到奇怪的是， View 可以依赖于某些约束。 AFAIK postgres 不会缓存 View 的执行计划。

当我这样改变我的观点时:

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;

依赖关系消失了，我的脚本成功执行了。

所以我的问题是: View 和约束之间存在这种依赖关系的原因是什么。

编辑

在这里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.

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

Answer 1

如果您将 PK 放在 public.t_place_type 上，您的 View 查询将不起作用。

它会产生这个错误:

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 既不用于聚合也不用于 group by。这就是 Postgres 提示 View 依赖性的原因 - 一旦您删除此 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,
  c_name character varying(50),
  id_place_type integer,
  CONSTRAINT pk_t_place PRIMARY KEY (id)
);

 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;

/* 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
            ^
*/