PostgreSQL создаёт дополнительные строки в системном каталоге внешних ключей, поскольку ссылка на секционированную таблицу должна работать и со всеми её физическими разделами. Чтобы определить, отражают ли эти строки одну связь или отдельные объявления, изучите ссылки на родительские ограничения и таблицы, на которые они ссылаются.

Это руководство предназначено для разработчика, который на Mac исследует неожиданный список ограничений в обозревателе базы данных или приложении Node.js. Задача — воспроизвести поведение на синтетических строках статей, проследить семейство ограничений и решить, что включить в отчёт о логической схеме.

Почему один внешний ключ создаёт несколько строк ограничений?

Секционированная таблица представляет собой логическую таблицу, строки которой хранятся в её разделах. У родительской таблицы нет собственного хранилища. Это различие важно, когда на неё ссылается другая таблица: PostgreSQL должен поддерживать связь при изменении строк в физических разделах. См. официальную документацию Секционирование таблиц.

В реализации PostgreSQL 18 этот механизм описан явно. Процедура addFkRecurseReferenced создаёт триггеры действий для внешних ключей и рекурсивно создаёт дополнительную строку pg_constraint для каждого раздела, на который ссылаются. В коде проверки поясняется, что эти строки на стороне таблицы, на которую ссылаются, нужны для триггеров действий, а не для отдельных проходов проверки. См. реализацию создания и проверки внешних ключей в PostgreSQL.

Следовательно, эти строки не означают, что каждое ссылающееся значение должно присутствовать в каждом разделе. Объявленная связь по-прежнему указывает на родительскую секционированную таблицу. PostgreSQL также должен обрабатывать изменения на стороне таблицы, на которую ссылаются, включая настроенное поведение при удалении строки или изменении её ключа. Это поведение описано в разделе Внешние ключи.

В каких версиях PostgreSQL это поддерживается?

Поддержка внешних ключей, ссылающихся на секционированную таблицу, появилась в PostgreSQL 12. В более ранних версиях нельзя использовать такое же объявление со ссылкой на родительскую таблицу. PostgreSQL 12 теперь считается неподдерживаемой версией; это историческая граница появления функции, а не рекомендуемая версия для установки. В этой статье используются актуальная документация PostgreSQL 18 и поддерживаемая ветка исходного кода. См. примечания к выпуску PostgreSQL 12.

Воспроизведите связь на синтетических строках статей

Используйте существующую локальную базу данных PostgreSQL для разработки и установленный psql. На Mac откройте Terminal, подключитесь командой psql -d postgres и проверьте сервер с помощью SHOW server_version;. При необходимости подставьте имя своей базы данных для разработки. В официальной документации psql описаны выбор базы данных и выполнение SQL-команд в интерактивном режиме.

Один раз выполните эту полную настройку. Она создаёт отдельную схему, не удаляя существующие объекты. Если такая схема уже есть, остановитесь и используйте новую базу для разработки или последовательно переименуйте схему во всех примерах.

BEGIN;

CREATE SCHEMA fk_partition_demo;

CREATE TABLE fk_partition_demo.articles (
    article_id integer NOT NULL,
    publication_year integer NOT NULL,
    title text NOT NULL,
    PRIMARY KEY (article_id, publication_year)
) PARTITION BY RANGE (publication_year);

CREATE TABLE fk_partition_demo.articles_2025
    PARTITION OF fk_partition_demo.articles
    FOR VALUES FROM (2025) TO (2026);

CREATE TABLE fk_partition_demo.articles_2026
    PARTITION OF fk_partition_demo.articles
    FOR VALUES FROM (2026) TO (2027);

CREATE TABLE fk_partition_demo.article_notes (
    note_id integer PRIMARY KEY,
    article_id integer NOT NULL,
    publication_year integer NOT NULL,
    note_text text NOT NULL,
    CONSTRAINT article_notes_article_fk
        FOREIGN KEY (article_id, publication_year)
        REFERENCES fk_partition_demo.articles
            (article_id, publication_year)
);

INSERT INTO fk_partition_demo.articles
    (article_id, publication_year, title)
VALUES
    (101, 2025, 'A guide to paper models'),
    (102, 2026, 'Choosing a notebook layout');

INSERT INTO fk_partition_demo.article_notes
    (note_id, article_id, publication_year, note_text)
VALUES
    (1, 101, 2025, 'Add a materials list'),
    (2, 102, 2026, 'Include a grid example');

COMMIT;

Составной первичный ключ выбран намеренно. PostgreSQL требует, чтобы первичный ключ или ограничение уникальности секционированной таблицы включали все столбцы ключа секционирования; также действуют дополнительные ограничения для выражений в ключах секционирования. Поскольку эта таблица секционируется по publication_year, первичный ключ включает этот столбец, а внешний ключ ссылается на полную пару. См. Декларативное секционирование: ограничения.

Таким образом, в примере статья определяется по (article_id, publication_year). Он не доказывает, что только article_id уникален во всей таблице. Если приложению нужны ссылки только по article_id, прежде чем использовать этот пример, пересмотрите схему секционирования и ключей.

Проследите семейство ограничений, а не сравнивайте имена

Полезные поля системного каталога: conrelid — таблица, содержащая ссылку; confrelid — таблица, на которую ссылаются; и conparentid — OID родительского ограничения. Имена ограничений не обязательно уникальны, поэтому по одним лишь именам нельзя установить, являются ли две строки независимыми. Эти поля описаны в pg_constraint.

После выполнения настройки запустите этот запрос. Он начинает с корневых внешних ключей, объявленных для article_notes, и рекурсивно проходит по дочерним ограничениям.

WITH RECURSIVE fk_family AS (
    SELECT c.oid, c.conparentid, c.oid AS root_oid, 0 AS depth
    FROM pg_catalog.pg_constraint AS c
    WHERE c.contype = 'f'
      AND c.conrelid =
          'fk_partition_demo.article_notes'::regclass
      AND c.conparentid = 0

    UNION ALL

    SELECT child.oid, child.conparentid,
           parent.root_oid, parent.depth + 1
    FROM pg_catalog.pg_constraint AS child
    JOIN fk_family AS parent
      ON child.conparentid = parent.oid
    WHERE child.contype = 'f'
)
SELECT
    family.root_oid,
    family.depth,
    c.oid AS constraint_oid,
    c.conname,
    c.conparentid,
    c.conrelid::regclass AS referencing_table,
    c.confrelid::regclass AS referenced_table,
    pg_get_constraintdef(c.oid) AS definition
FROM fk_family AS family
JOIN pg_catalog.pg_constraint AS c ON c.oid = family.oid
ORDER BY family.root_oid, family.depth, c.oid;

Для этого одноуровневого примера из реализации следует, что будет три строки внешних ключей: одна указывает на articles, а остальные — на каждый из двух его разделов. Это ожидаемый результат, выведенный из исходного кода, а не полученный при выполнении запросов. Не следует жёстко задавать сгенерированные имена и значения OID. Тот же код создаёт ограничения на стороне таблицы, на которую ссылаются, если раздел создают или присоединяют позже. См. tablecmds.c.

Строка в этом примере Таблица, содержащая ссылку Таблица, на которую ссылаются Ссылка на родительское ограничение
Корневая связь article_notes articles conparentid = 0
Дочернее ограничение раздела, на который ссылаются article_notes articles_2025 OID корневого ограничения
Дочернее ограничение раздела, на который ссылаются article_notes articles_2026 OID корневого ограничения

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

Запрос также выводит восстановленные определения с помощью pg_get_constraintdef(). PostgreSQL описывает эту функцию как восстановление определения, а не возврат исходного SQL-запроса. Используйте её, чтобы понять связь, а не восстановить точный текст миграции. См. Функции и операторы для получения системной информации.

Просмотрите те же строки с помощью node-postgres на Mac

node-postgres возвращает результаты запроса сервера; строки, связанные с разделами, создаются механизмом ограничений PostgreSQL. Документированный API запросов принимает SQL-текст и отдельный массив значений параметров. Здесь имя отношения передаётся как текстовое значение, которое PostgreSQL преобразует в regclass. См. Запросы.

Установив актуальную поддерживаемую версию Node.js, создайте рабочую директорию и установите pg. Команда установки и рекомендации по совместимости с версиями среды выполнения приведены во введении в node-postgres.

mkdir pg-fk-inspection
cd pg-fk-inspection
npm init -y
npm install pg

Сохраните следующую полную программу в файл inspect-fks.mjs. Для её работы требуется выполненная выше настройка SQL. В отличие от рекурсивного SQL-отчёта, эта программа выводит строки, непосредственно связанные с указанной таблицей, содержащей ссылку; для несекционированной таблицы article_notes в этом примере этого достаточно.

import pg from 'pg';

const { Client } = pg;
const client = new Client({ connectionTimeoutMillis: 5000 });
const relation = process.argv[2] ??
  'fk_partition_demo.article_notes';

try {
  await client.connect();

  const version = await client.query('SHOW server_version');
  console.log('PostgreSQL:', version.rows[0].server_version);

  const result = await client.query(`
    SELECT oid, conname,
           conrelid::regclass::text AS referencing_table,
           confrelid::regclass::text AS referenced_table,
           conparentid,
           conparentid = 0 AS is_root,
           pg_get_constraintdef(oid) AS definition
    FROM pg_catalog.pg_constraint
    WHERE contype = 'f' AND conrelid = $1::regclass
    ORDER BY conparentid, oid
  `, [relation]);

  console.table(result.rows);
} catch (error) {
  console.error(error.message);
  process.exitCode = 1;
} finally {
  await client.end();
}

Запустите программу для той же базы данных, в которой выполняли настройку SQL. node-postgres использует переменные среды для подключения к PostgreSQL, включая PGDATABASE; их значения по умолчанию описаны в разделе Подключение.

PGDATABASE=postgres node inspect-fks.mjs

Если отношение не найдено, убедитесь, что оба сеанса подключены к одной базе данных и что настройка успешно завершилась. Пустой результат означает, что этот запрос не нашёл внешних ключей, непосредственно связанных с этим отношением; входящие ссылки из других таблиц в нём не отображаются. Перед подготовкой этой статьи JavaScript прошёл проверку синтаксиса. Выполнить запросы к базе данных не удалось, поэтому примеры SQL и ожидаемая структура системного каталога были сверены с полученной документацией и исходным кодом.

Решите, что показывать и что исследовать

Если список ограничений оказался неожиданно длинным, действуйте в следующем порядке. Так вы сможете сформировать отчёт о логических связях или инвентаризацию реализации, сохранив полезные данные для анализа.

  1. Проверьте отношение и версию сервера. Укажите имя отношения с учётом схемы и запишите результат SHOW server_version;, прежде чем сопоставлять поведение с документацией.
  2. Проследите ссылки на родительские ограничения. В этом примере дочерние строки с общим корневым OID входят в семейство ограничений объявленной связи.
  3. Сравните обе стороны. Если confrelid меняется, а таблица, содержащая ссылку, остаётся той же, это объясняет строки для разделов, на которые ссылаются, показанные выше.
  4. Определите цель отчёта. Чтобы вывести корневые связи, объявленные для выбранной таблицы, используйте фильтр contype = 'f' AND conparentid = 0. Чтобы увидеть всё семейство, оставьте рекурсивный запрос.
  5. Исследуйте отдельные корневые ограничения по отдельности. Прежде чем решить, что два объявления избыточны, сравните пары столбцов, таблицы, на которые ссылаются, действия, состояние проверки и возможность отложенной проверки.

Этот подход к составлению отчёта использует идентификаторы и поля связей, описанные в pg_constraint. Фильтр только по корневым ограничениям имеет ограниченную область действия: раздел может участвовать в связях через унаследованные дочерние ограничения, поэтому такой фильтр для таблицы-листа не покажет все связи, которые на неё влияют.

Если нужны сведения об объектах, обеспечивающих соблюдение ограничений, pg_trigger.tgconstraint связывает триггер с ограничением, а tgrelid указывает таблицу, содержащую триггер. Системный каталог также отмечает триггеры, созданные внутри системы. Эти поля дают дополнительные возможности для изучения в pg_trigger.

Сохраняйте сгенерированные строки, пока объявленная связь нужна. В рассмотренном случае целесообразно показывать корневую связь в представлении логической схемы, а дочерние строки оставлять в диагностическом представлении. Когда видны ссылки на родительские ограничения и обе стороны связи, дополнительные строки объясняют, как PostgreSQL реализует ссылку на разделы.

Источники