How can view depends on primary key constraint in postgres

Question

Sometimes while bulk data loading it's recommended to temporary drop constraints and indexes on table. But when I am doing this I faced with some problems with dependency. My simplified example:

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;

My script:

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;

When I run it I am getting error:

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

It's strange to me that view can depends on some constraint. AFAIK postgres does not cache execution plan for a view.

When I change my view this way:

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;

the dependency is gone and my script executes successfully.

So my question is: What is the reason for this dependency between view and constraint.

EDIT

Here https://www.postgresql.org/docs/9.5/static/sql-select.html#SQL-GROUPBY postgres docs say:

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.

Is this a reason for such behavior? Even though I have no ungrouped columns in my view?

score 1 · Accepted Answer · answered Nov 15 '17 at 10:26

Your query from view will not work if you drop PK on public.t_place_type.

It will produce this error:

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
            ^

That is because, from your documentation citation, A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

Postgres knows that PK represents unique row, so once you group by it you might as well group by all columns from that table and get the same result.

Those give the same result, with is ungrouped rows:

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;

And you are using this dependency while selecting pt.c_name AS c_place_type, because you grouped that table by primary key pt.id, with is non existent PK once you drop it, so grouping by it makes pt.c_name both not used in aggregate and not used in group by. And that is why Postgres complains about view dependency - its query will not work anymore once you drop this PK.

You can try it yourself with modified example from your question:

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

Yes, you absolutely right. My mistake. If I modify GROUP BY like this: GROUP BY p.id, p.c_name, pt.c_name to avoid ungrouped columns everything works fine. Thank you! — Ivan Mogila, Nov 15 '17 at 11:00

How can view depends on primary key constraint in postgres

1 Answers1