Удаление данных из дочерних таблиц с помощью хранимой процедуры
У меня есть схема 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, но это не сработало.
Спасибо, да, это была моя ошибка при публикации, потому что «fecha» — это настоящее имя столбца, и когда я вставлял его, я забыл изменить его имя. Но сейчас исправлю пост. Так что проблема не в этом. Еще раз спасибо.
Почему вы возвращаете TABLE? Мне кажется, ты возвращаешься пустым. Я думаю, что возврат NULL может вызвать проблемы.
Может быть, я утилизирую другой старый SP. Но я думал, что это не имеет значения, потому что важно EXECUTE в цикле. Честно говоря, я не знаю, может ли это быть проблемой. Что я должен вернуть? пустота?
ФУНКЦИЯ — это не ПРОЦЕДУРА. В процедуре, которую вы можете зафиксировать, измените свой код на процедуру и добавьте фиксацию. Используйте CALL для вызова процедуры.
Ответы 1
Вы хотите ПРОЦЕДУРУ вместо FUNCTION. Там вы можете оформить COMMIT. Видеть:
- Как принудительно выполнить COMMIT внутри функции, чтобы другие сеансы могли видеть обновленную строку?
- Выполняется ли процедура в транзакции в PostgreSQL?
Могло бы работать так:
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. Видеть:
- Имя таблицы как параметр функции PostgreSQL
- Определить имена таблиц и столбцов в качестве аргументов в функции plpgsql?
И вы перепутали имена переменных: consulta_sql и sql_query. Я упростил.
За это время я добавил счетчик строк (практически бесплатно). Видеть:
- Подсчитайте строки, затронутые DELETE
- Переменные для идентификаторов внутри IF EXISTS в функции plpgsql
Спасибо, Эрвин, за уделенное время. Прежде всего, функция не возвращает никакой ошибки, которую я получаю из-за ошибки ввода (или для фиксации, я не помню). Я взял эту полную строку, чтобы показать, какой оператор sql был создан и выполнен. Спасибо за разъяснения по поводу фиксации внутри процедуры, для меня процедура не возвращает, а функция что-то возвращает, для этого я перерабатываю эту старую функцию и изменяю ее. Я перейду к процедуре и попробую ваш код. Я никогда не использую «вызов» для выполнения, всегда использую с выбором, я тоже попробую.
О: Consulta_sql и sql_query, это была моя ошибка при наборе, потому что я изменил исходный код. Еще раз спасибо за советы, попробую изменить и расскажу, как работает.
@Alvaro: CALL выполняет процедуру, это необязательно. Кроме того, процедура с операторами управления транзакциями не может быть обернута во внешнюю транзакцию. Пройдите по ссылкам, которые я предоставил. Кроме того, если бы ваша функция не возвращала никаких ошибок, удаления были бы зафиксированы.
Огромное спасибо, все работает отлично, только мне нужно поставить пробел между «простая цитата» и «где». Но сейчас удаляю. Еще раз спасибо, Эрвин!
Я не знаю, переводили ли вы просто для нашей пользы, но в вашем коде используется
new_date, пока отображается распечаткаfecha. Позвольте мне предложить кое-что, что может ускорить вашу программу. Помимо той схемы секционирования, которую вы сейчас используете, также разделите таблицу по датам (скажем, по месяцам). В этом случае вы можете отсоединить и удалить целые таблицы, что происходит очень быстро, вместо удаления записей, что происходит медленно.