Nano Hash - криптовалюты, майнинг, программирование

Как можно просмотреть, зависит от ограничения первичного ключа в postgres

Иногда при массовой загрузке данных рекомендуется временно сбрасывать ограничения и индексы на таблицу. Но когда я делаю это, я столкнулся с некоторыми проблемами с зависимостью. Мой упрощенный пример:

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;

Когда я запускаю его, я получаю сообщение об ошибке:

ОШИБКА: невозможно удалить ограничение pk_t_place_type для таблицы t_place_type, поскольку от него зависят другие объекты. ПОДРОБНЕЕ: представление v_place зависит от ограничения pk_t_place_type для таблицы t_place_type.

Мне странно, что view может зависеть от какого-то ограничения. 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 говорится:

Когда присутствует GROUP BY или присутствуют какие-либо агрегатные функции, недопустимо, чтобы выражения списка SELECT ссылались на несгруппированные столбцы, кроме как в агрегатных функциях, или когда несгруппированный столбец функционально зависит от сгруппированных столбцов, поскольку в противном случае было бы больше. чем одно возможное значение для несгруппированного столбца. Функциональная зависимость существует, если сгруппированные столбцы (или их подмножество) являются первичным ключом таблицы, содержащей несгруппированный столбец.

Является ли это причиной такого поведения? Даже если у меня нет несгруппированных столбцов в моем представлении?

postgresql constraints sql-view postgresql-9.5
15.11.2017

Ответы:


1

Ваш запрос из представления не будет работать, если вы сбросите PK на public.t_place_type.

Это приведет к этой ошибке:

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
            ^

Это потому, что, судя по вашей цитате из документации, A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

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 жалуется на зависимость представления — его запрос больше не будет работать, как только вы удалите этот 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
            ^
*/
15.11.2017
  • Да, вы абсолютно правы. Виноват. Если я изменю GROUP BY следующим образом: GROUP BY p.id, p.c_name, pt.c_name, чтобы избежать несгруппированных столбцов, все работает нормально. Благодарю вас! 15.11.2017
    • Новые материалы
    Кластеризация: более глубокий взгляд
    Кластеризация — это метод обучения без учителя, в котором мы пытаемся найти группы в наборе данных на основе некоторых известных или неизвестных свойств, которые могут существовать. Независимо от..
    Как написать эффективное резюме
    Предложения по дизайну и макету, чтобы представить себя профессионально Вам не позвонили на собеседование после того, как вы несколько раз подали заявку на работу своей мечты? У вас может..
    Частный метод Python: улучшение инкапсуляции и безопасности
    Введение Python — универсальный и мощный язык программирования, известный своей простотой и удобством использования. Одной из ключевых особенностей, отличающих Python от других языков, является..
    Как я автоматизирую тестирование с помощью Jest
    Шутка для победы, когда дело касается автоматизации тестирования Одной очень важной частью разработки программного обеспечения является автоматизация тестирования, поскольку она создает..
    Работа с векторными символическими архитектурами, часть 4 (искусственный интеллект)
    Hyperseed: неконтролируемое обучение с векторными символическими архитектурами (arXiv) Автор: Евгений Осипов , Сачин Кахавала , Диланта Хапутантри , Тимал Кемпития , Дасвин Де Сильва ,..
    Понимание расстояния Вассерштейна: мощная метрика в машинном обучении
    В обширной области машинного обучения часто возникает необходимость сравнивать и измерять различия между распределениями вероятностей. Традиционные метрики расстояния, такие как евклидово..
    Обеспечение масштабируемости LLM: облачный анализ с помощью AWS Fargate и Copilot
    В динамичной области искусственного интеллекта все большее распространение получают модели больших языков (LLM). Они жизненно важны для различных приложений, таких как интеллектуальные..