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

У меня есть схема new_schema с таблицей result. Эта таблица имеет множество дочерних таблиц с наследованием, называемых result_23, result_45 и т. д.

У меня 3500 миллионов строк, и база данных работает медленно. В одной строке есть поле метки времени с именем new_date. Начало с 01.01.2022. Я хочу удалить все строки до: 2023-11-01.

Удаление из родительской таблицы занимает слишком много времени. Я пытаюсь удалить из дочерних таблиц непосредственно в SP, выполняемом с суперпользователем postgres. Если что-то случится, я смогу продолжить работу позже.

CREATE OR REPLACE FUNCTION new_schema.delete_old_rows()

RETURNS TABLE (child_table text)
LANGUAGE plpgsql
AS $function$
DECLARE
    child_table text;
    sql_query text;
BEGIN
    FOR child_table IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'new_schema'
        AND table_name LIKE 'result_%'
    loop
        sql_query := 'DELETE FROM new_schema.' || child_table || ' WHERE new_date < ''2023-11-01'';';
        EXECUTE consulta_sql;
        RAISE NOTICE 'Data deleted in table: %', child_table;
    END LOOP;
END
$function$;

Я DELETE из каждой дочерней таблицы в цикле, и это, кажется, работает (хотя для больших таблиц это занимает много времени). Но когда я проверяю SELECT, данные за 2022 год все еще там.

При печати строки запроса DELETE выглядит идеально:

КОНТЕКСТ: оператор SQL «DELETE FROM new_schema.result_26». ГДЕ new_date < '2023-11-01';"

Я пробовал применять COMMIT после каждого DELETE, но это не сработало.

🤔 А знаете ли вы, что...
PostgreSQL предоставляет механизмы сжатия данных для экономии места на диске.

[РЕШЕНО]Перейти к решению

Database18531 вопрос Postgresql21591 вопрос Stored procedures3134 вопроса Plpgsql528 вопросов

Ответ:

Решено

Вы хотите ПРОЦЕДУРУ вместо FUNCTION. Там вы можете оформить COMMIT. Видеть:

Могло бы работать так:

CREATE OR REPLACE PROCEDURE new_schema.delete_old_rows()  -- !!!
  -- no RETURNS clause  -- !!!
  LANGUAGE plpgsql AS
$func$
DECLARE
   _child text;
   _row_ct int;
BEGIN
   FOR _child IN
      SELECT quote_ident(table_name)  -- !!!
      FROM   information_schema.tables
      WHERE  table_schema = 'new_schema'
      AND    table_name LIKE 'result_%'
   LOOP
      EXECUTE 'DELETE FROM new_schema.' || _child || ' WHERE new_date < ''2023-11-01''';      
      GET DIAGNOSTICS _row_ct = ROW_COUNT;
      COMMIT;  -- !!!
      RAISE NOTICE '% rows deleted from table: %', _row_ct, _child;
   END LOOP;
END
$func$;

Выполните с помощью ЗВОНКА (важно!):

CALL new_schema.delete_old_rows();

Также обратите внимание, что ваш оригинал был открыт для атак с использованием SQL-инъекций. Идентификаторы следует рассматривать как вводимые пользователем данные в динамическом SQL. Видеть:

И вы перепутали имена переменных: consulta_sql и sql_query. Я упростил.
За это время я добавил счетчик строк (практически бесплатно). Видеть:

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

Ответ:

Интересные вопросы для изучения