Как избежать некорректного изменения данных при работе расширения postgres_fdw с партиционированными таблицами

Статья описывает причину возникновения риска, условия его проявления и способы защиты данных. Материалы применимы к версиям СУБД Pangolin с расширением postgres_fdw. Внешнее подключение к партиционированным таблицам через расширение PostgreSQL Foreign Data Wrapper (postgres_fdw) может привести к некорректному изменению или удалению строк, не соответствующих исходному условию запроса.

Признаки проявления

• оператор UPDATE или DELETE через внешнюю таблицу, указывающую на удаленную корневую партиционированную таблицу, изменяет строки, не соответствующие исходному условию запроса;
• команда завершается успешно и возвращает ожидаемое число обработанных строк;
• логическая целостность данных нарушена, физические повреждения СУБД отсутствуют.

Условия возникновения

Риск возникает при одновременном выполнении следующих условий:

• внешняя таблица указывает на удаленную корневую партиционированную таблицу или родительскую таблицу в иерархии наследования;
• через внешнюю таблицу выполняется UPDATE или DELETE;
• изменение выполняется построчно отдельными командами, идентифицирующими строку по системному столбцу ctid;
• в другой физической таблице иерархии существует строка с таким же значением ctid.

Для идентификации строки используется системный столбец ctid:

UPDATE remote_schema.orders
SET status = $2
WHERE ctid = $1;

Риск реализуется, когда расширение postgres_fdw обрабатывает строку локально (в этом случае удаленная команда обновления идентифицирует строку только по значению ctid). Локальная обработка требуется при следующих сценариях:

• соединении с локальной таблицей;
• использовании в WHERE или SET выражения, которое нельзя передать на удаленный узел;
• наличии триггера уровня строки FOR EACH ROW или другого элемента, требующего локальной обработки изменяемой строки.

примечание

При использовании расширения postgres_fdw существует возможность настройки триггеров на внешних таблицах. Триггеры могут применяться для валидации данных до или после изменения, логирования операций или автоматического обновления связанных данных. Подробности о принципах работы и настройке триггеров приведены в документации.

Наличие триггера уровня строки на внешней таблице активирует локальную обработку, что является одним из условий возникновения риска. При использовании триггеров убедитесь, что изменяющие запросы используют полные логические ключи, а не опираются на столбец ctid.

Совпадения ctid в разных партициях являются нормальным следствием их независимого физического хранения. Отсутствие совпадений в текущий момент нельзя использовать как меру защиты.

Коллизия ctid между физическими таблицами не затрагивает:

• SELECT через внешнюю корневую таблицу;
• INSERT через внешнюю корневую таблицу;
• UPDATE и DELETE через внешнюю таблицу, указывающую непосредственно на одну физическую удаленную партицию;
• изменение локальной таблицы, если внешняя таблица используется как источник данных.

Причины

1. Расширение postgres_fdw не гарантирует полную передачу условий изменения на удаленный узел, если часть запроса должна обработаться локально;
2. Для идентификации строки используется системный столбец ctid, который однозначно идентифицирует строку внутри одной физической таблицы;
3. В разных партициях могут одновременно находиться строки с одинаковым значением ctid;
4. Если команда адресована корневой партиционированной таблице или родителю в иерархии наследования и не содержит исходного логического условия, одинаковое значение ctid проверяется во всех физических таблицах иерархии.

Диагностика

Для проверки целостности схемы выполните следующие действия.

На соответствующем удаленном узле проверьте тип целевой таблицы и наличие у нее дочерних таблиц:

SELECT
    current_database() AS local_database_name,
    n.nspname          AS local_schema_name,
    c.relname          AS local_foreign_table,
    s.srvname          AS foreign_server_name,
    COALESCE(
        (
            SELECT o.option_value
            FROM pg_catalog.pg_options_to_table(ft.ftoptions) AS o
            WHERE o.option_name = 'schema_name'
        ),
        n.nspname
    ) AS remote_schema_name,
    COALESCE(
        (
            SELECT o.option_value
            FROM pg_catalog.pg_options_to_table(ft.ftoptions) AS o
            WHERE o.option_name = 'table_name'
        ),
        c.relname
    ) AS remote_table_name
FROM pg_catalog.pg_foreign_table AS ft
JOIN pg_catalog.pg_class AS c
  ON c.oid = ft.ftrelid
JOIN pg_catalog.pg_namespace AS n
  ON n.oid = c.relnamespace
JOIN pg_catalog.pg_foreign_server AS s
  ON s.oid = ft.ftserver
JOIN pg_catalog.pg_foreign_data_wrapper AS fdw
  ON fdw.oid = s.srvfdw
WHERE fdw.fdwname = 'postgres_fdw'
ORDER BY
    n.nspname,
    c.relname;

Произведите проверку на удаленном узле, является ли целевая таблица партиционированной:

WITH target AS (
    SELECT
        c.oid,
        n.nspname AS schema_name,
        c.relname AS table_name,
        c.relkind,
        c.relispartition,
        EXISTS (
            SELECT 1
            FROM pg_catalog.pg_inherits AS i
            WHERE i.inhparent = c.oid
        ) AS has_children
    FROM pg_catalog.pg_class AS c
    JOIN pg_catalog.pg_namespace AS n
      ON n.oid = c.relnamespace
    WHERE n.nspname = 'remote_schema'
      AND c.relname = 'orders'
)
SELECT
    schema_name,
    table_name,
    relkind,
    relispartition,
    has_children,
    CASE
        WHEN relkind = 'p' AND has_children
            THEN 'партиционированная таблица с дочерними партициями'
        WHEN relkind = 'p'
            THEN 'партиционированная таблица без дочерних партиций'
        WHEN has_children
            THEN 'родительская таблица в иерархии наследования'
        WHEN relispartition
            THEN 'конечная партиция'
        WHEN relkind = 'r'
            THEN 'обычная таблица'
        WHEN relkind = 'f'
            THEN 'внешняя таблица'
        ELSE relkind::text
    END AS table_type
FROM target;

Значение relkind = 'p' указывает на партиционированную таблицу, значение has_children = true — на наличие дочерних таблиц или партиций. Внешние таблицы, указывающие на такие объекты, относятся к группе риска.

подсказка

В стандартной архитектуре используйте внешнюю корневую таблицу только для чтения. Для UPDATE и DELETE примените сопоставление отдельных партиций, либо выполните изменение непосредственно на удаленном узле.

Ограничение изменяющих операций через внешнюю корневую таблицу

Если корневая внешняя таблица предназначена только для чтения, запретите изменяющие операции с помощью параметра updatable:

ALTER FOREIGN TABLE local_schema.orders_ft
OPTIONS (ADD updatable 'false');

Если параметр уже задан, измените его с помощью SET:

ALTER FOREIGN TABLE local_schema.orders_ft
OPTIONS (SET updatable 'false');

Параметр updatable = false запрещает выполнение INSERT, UPDATE и DELETE.

Импорт удаленной схемы

При выполнении IMPORT FOREIGN SCHEMA корень удаленной партиционированной таблицы импортируется как обычная внешняя таблица, а дочерние партиции по умолчанию пропускаются. Они импортируются при явном перечислении в LIMIT TO. Поэтому из локального описания объекта может быть неочевидно, что он указывает на корень дерева партиций. При стандартном значении updatable = true и наличии необходимых прав через объект можно выполнить UPDATE или DELETE, подпадающие под описанное ограничение.

Не используйте импортированный корень дерева партиций для выполнения изменяющих операций. Если объект предназначен только для чтения, установите для него updatable = false. Для выполнения записи явно импортируйте конечные партиции с помощью LIMIT TO либо создайте для них отдельные сопоставления внешних таблиц.

Сопоставление отдельных партиций

Для выполнения запросов языка манипуляции данными (Data Manipulation Language, DML) создайте локальную партиционированную таблицу и отдельную внешнюю таблицу для каждой физической партиции на удаленном узле:

CREATE TABLE local_schema.orders (
    order_date date   NOT NULL,
    order_id   bigint NOT NULL,
    status     text
) PARTITION BY RANGE (order_date);

CREATE FOREIGN TABLE local_schema.orders_2026_08
    PARTITION OF local_schema.orders
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01')
SERVER remote_server
OPTIONS (
    schema_name 'remote_schema',
    table_name  'orders_2026_08'
);

Создайте сопоставление для каждой партиции. Параметр table_name должен указывать на физическую дочернюю таблицу, а не на корневую или промежуточную родительскую таблицу.

Границы партиций и размещение данных на обоих узлах должны совпадать. При изменении состава партиций одновременно обновляйте локальные определения внешних таблиц.

Не изменяйте ключ партиционирования через данную схему. Перемещение строки между партициями выполняйте отдельной контролируемой операцией на удаленном узле.

Выполнение изменений на удаленном узле

Для массовых изменений передайте набор данных в отдельную промежуточную таблицу на удаленном узле и выполните одну команду по полному логическому ключу:

UPDATE remote_schema.orders AS t
SET status = s.new_status
FROM remote_schema.orders_changes AS s
WHERE t.order_date = s.order_date
  AND t.order_id = s.order_id;

Операцию можно оформить в виде удаленной процедуры или выполнить через отдельное прикладное подключение. Уникальность полного логического ключа в целевой и промежуточной таблицах является обязательным условием корректного сопоставления строк.

Для больших наборов этот вариант предпочтительнее построчного цикла, так как он уменьшает количество сетевых обращений и время удержания блокировок.

Важно

Используйте временные шаблоны только там, где пока нельзя перейти на рекомендуемую схему. Точную форму запроса предварительно согласуйте для конкретной схемы данных и версии СУБД. Любое изменение шаблона требует повторной оценки.

Шаблон для одного значения

Если локальный источник гарантированно возвращает не более одной строки, вычислите значения в материализованном CTE и передайте их в простую команду изменения как скалярные значения:

WITH c AS MATERIALIZED (
    SELECT order_date, order_id, new_status
    FROM local_schema.order_changes
    WHERE order_date = DATE '2026-08-15'
      AND order_id = 1001
)
UPDATE local_schema.orders_ft AS t
SET status = (SELECT new_status FROM c)
WHERE t.order_date = (SELECT order_date FROM c)
  AND t.order_id = (SELECT order_id FROM c);

В шаблоне значения из общего табличного выражения (Common Table Expression, CTE) вычисляются локально до отправки команды на удаленный узел. В удаленный запрос передаются константы, а не ссылки на локальные таблицы, поэтому условие WHERE содержит полные логические ключи и не опирается на ctid.

Не добавляйте CTE в FROM команды UPDATE: конструкция WITH ... UPDATE ... FROM сохраняет локальное соединение и небезопасна.

Для этого варианта должны выполняться все условия:

• локальный источник возвращает не более одной строки;
• логический ключ (order_date, order_id) уникален на удаленной целевой таблице;
• не используется LIMIT 1 для сокрытия дубликатов;
• во внешней таблице отсутствуют триггеры уровня строки и другие объекты, требующие локальной обработки изменяемой строки;
• форма запроса не дополняется локальным соединением, локальной функцией или коррелированным подзапросом.

При пустом CTE изменение не выполняется; при нескольких строках скалярный подзапрос завершается ошибкой.

Для удаления одной строки используйте тот же подход:

WITH c AS MATERIALIZED (
    SELECT order_date, order_id
    FROM local_schema.order_changes
    WHERE order_date = DATE '2026-08-15'
      AND order_id = 1001
)
DELETE FROM local_schema.orders_ft AS t
WHERE t.order_date = (SELECT order_date FROM c)
  AND t.order_id = (SELECT order_id FROM c);

Шаблон для небольшого набора значений

Для небольшого набора выполните отдельную простую команду по полному логическому ключу для каждой строки локального источника:

DO $plpgsql$
DECLARE
    r          record;
    v_affected bigint;
BEGIN
    FOR r IN
        SELECT order_date, order_id, new_status
        FROM local_schema.order_changes
        ORDER BY order_date, order_id
    LOOP
        UPDATE local_schema.orders_ft AS t
        SET status = r.new_status
        WHERE t.order_date = r.order_date
          AND t.order_id = r.order_id;

        GET DIAGNOSTICS v_affected = ROW_COUNT;

        IF v_affected <> 1 THEN
            RAISE EXCEPTION
                'Для order_date=%, order_id=% ожидалась одна строка, изменено: %',
                r.order_date, r.order_id, v_affected;
        END IF;
    END LOOP;
END;
$plpgsql$;

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

Исключение об неожиданном числе строк не должно перехватываться, так как оно должно отменить весь блок.

Цикл выполняет одну удаленную команду на строку, поэтому такой способ применим только для небольших наборов. Число сетевых обращений растет линейно, а блокировки удерживаются до конца транзакции. Проверка ROW_COUNT = 1 контролирует число строк, корректность шаблона обеспечивает полный логический ключ, сохраненный в удаленной команде.

Рекомендации

Для предотвращения риска некорректного изменения данных:

1. Используйте внешнюю корневую таблицу только для чтения, установите updatable = false;
2. Для изменяющих операций создавайте внешние таблицы для конкретных физических партиций;
3. Используйте полные логические ключи в условиях UPDATE/DELETE;
4. Учитывайте, что наличие триггеров уровня строки на внешних таблицах требует локальной обработки и может активировать риск;
5. Проводите регулярную сверку схем на локальном и удаленном узле;
6. Не полагайтесь на отсутствие совпадающих ctid как на меру защиты.

Что не обеспечивает корректность изменения данных:

• ограничение PRIMARY KEY, UNIQUE или условие по ключу партиционирования, если удаленная команда по-прежнему идентифицирует строку по ctid;
• отсутствие совпадающих ctid при разовой сверке;
• предложение RETURNING либо контроль количества обработанных строк;
• материализованный CTE или цикл, если их форма отличается от согласованного шаблона.

Полное выполнение простого запроса на удаленном узле в текущий момент не гарантирует его постоянную защищенность, поскольку план выполнения может измениться после изменения запроса, схемы, функций, триггеров, настроек или версии СУБД.

Дополнительная информация

1. Партиционирование таблиц
2. Расширение postgres_fdw
3. Триггеры