Скрипт переведённый с MS SQL На PostgreSQL нестабильно работает

@Georg IV · Регистрация: 28.04.2021

Студворк — интернет-сервис помощи студентам

Коллеги, привет!

Был скрипт вот такого плана на MS SQL:

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DECLARE @ToDelete INT
SET @ToDelete = 1
WHILE @ToDelete > 0
BEGIN
    DELETE FROM [TABLE_NAME]
    WHERE
     [Id] IN (
    SELECT
        TOP 1000
     [Id]
    FROM
     [TABLE_NAME] WITH(NOLOCK)
    WHERE
     [CreatedOn] < '2023-09-01'
    )
    SET @ToDelete = @@ROWCOUNT
END

Я переписал его для PostgreSQL в меру понимания:

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE OR REPLACE FUNCTION delSPS () RETURNS INTEGER AS $BODY$  
DECLARE 
     ToDelete INTEGER = 1; 
BEGIN 
 
WHILE ToDelete > 0 
LOOP 
    DELETE FROM "Table_Name" WHERE "Id" IN ( SELECT "Id" FROM  "Table_Name"
    WHERE "CreatedOn" < '2023-08-01'  
    LIMIT 1000);
SELECT INTO ToDelete COUNT (*) FROM "Table_Name" WHERE "CreatedOn" < '2023-08-01';    
    
END LOOP; 
RETURN ToDelete; 
END; 
$BODY$ LANGUAGE plpgsql; 
 
SELECT delSPS ();

Он работает для таблиц у которых 500к записей и до 1млн. А вот когда его выполнили на таблицу в 21 миллион записей - работать отказался.
Что с ним не так и как его корректно переписать, чтобы он сработал?

@oktogen · 23.12.2023, 02:33

Сообщение от Georg IV

работать отказался.

Прямо так и написал? Матом хоть не ругался?

Скорее всего скрипт упёрся в лимиты. Какие -- зависит от сообщения об ошибке.

@Georg IV · 23.12.2023, 11:23 **[ТС]**

Сообщение от oktogen

Прямо так и написал? Матом хоть не ругался?

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

@oktogen · 23.12.2023, 15:56

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

@Swa111 · 24.12.2023, 21:01

в PG плохо работает count(*). Вместо этого можно использовать

SQL
1
GET DIAGNOSTICS nDeleted = ROW_COUNT;

Которая вернет в переменную nDeleted число удаленных строк. Хотя это тоже не панацея так как удаления будут выполнять все медленнее и медленнее на каждом цикле.

@oktogen · 25.12.2023, 09:53

Попробуйте абстрактно сформировать задачу которую вы решаете этим способом.

@Swa111 · 25.12.2023, 10:40

можно и не абстрактно

Oracle 11 SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE OR REPLACE FUNCTION delSPS () RETURNS INTEGER AS $BODY$  
DECLARE 
     nDeleted INTEGER; 
BEGIN 
 
LOOP 
    DELETE FROM "Table_Name" WHERE "Id" IN ( SELECT "Id" FROM  "Table_Name"
    WHERE "CreatedOn" < '2023-08-01'  
    LIMIT 100000);
 
    GET DIAGNOSTICS nDeleted = ROW_COUNT;
 
    EXIT WHEN nDeleted = 0;
END LOOP; 
RETURN 0; 
END; 
$BODY$ LANGUAGE plpgsql;

@Georg IV · 26.12.2023, 21:15 **[ТС]**

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

У меня возникла мысль, что он просто вначале долго инициализируется и не удаляет, а если его в этот момент прервать, то он и не удалит ничего.
Как бы делать так, чтобы он все-таки удалял, даже если его прерывают.

@Swa111 · 27.12.2023, 06:49

На самом деле он удаляет, но так как Вы прерываете операцию, то транзакция откатывается, поэтому результатов не видно.

Как вариант вынесите отдельную итерацию цикла в отдельную функцию и вызывайте ее через отдельное подключение с коммитом каждый раз.

@Georg IV · 28.12.2023, 14:27 **[ТС]**

Сообщение от Swa111

Как вариант вынесите отдельную итерацию цикла в отдельную функцию и вызывайте ее через отдельное подключение с коммитом каждый раз.

А можете показать, как это выглядит на примере? А то я туговато в плане реализации с нуля соображаю.

@Swa111 · 29.12.2023, 08:04

Что то вроде этого:

Oracle 11 SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE OR REPLACE FUNCTION delSPS_chank () RETURNS INTEGER AS $BODY$  
DECLARE 
     nDeleted INTEGER; 
BEGIN 
    DELETE FROM "Table_Name" WHERE "Id" IN ( SELECT "Id" FROM  "Table_Name"
    WHERE "CreatedOn" < '2023-08-01'  
    LIMIT 100000);
 
    GET DIAGNOSTICS nDeleted = ROW_COUNT;
RETURN COALESCE(nDeleted,0); 
END; 
$BODY$ LANGUAGE plpgsql;
 
CREATE OR REPLACE FUNCTION delSPS () RETURNS INTEGER AS $BODY$  
DECLARE 
     nDeleted INTEGER; 
BEGIN 
  LOOP 
    EXIT WHEN (SELECT nCount FROM dblink_exec('dbname=' || current_database(), 'select delSPS_chank()') AS t (nCount INTEGER)) = 0;
  END LOOP; 
  RETURN 0; 
END; 
$BODY$ LANGUAGE plpgsql;

Новые блоги и статьи Все статьи Все блоги /
Управление камерой с помощью скрипта OrbitControls.js на Three.js: Вращение, зум и панорамирование 8Observer8 05.03.2026 Содержание блога Финальная демка в браузере работает на Desktop и мобильных браузерах. Итоговый код: orbit-controls-threejs-js. zip. На мобильном - сканируйте QR-код. Вращайте камеру одним пальцем,. . .	SDL3 для Web (WebAssembly): Синхронизация спрайтов SDL3 и тел Box2D 8Observer8 04.03.2026 Содержание блога Финальная демка в браузере. Итоговый код: finish-sync-physics-sprites-sdl3-c. zip На первой гифке отладочные линии отключены, а на второй включены:. . .	SDL3 для Web (WebAssembly): Идентификация объектов на Box2D v3 - использование userData и событий коллизий 8Observer8 02.03.2026 Содержание блога Финальная демка в браузере. Итоговый код: finish-collision-events-sdl3-c. zip Сканируйте QR-код на мобильном и вы увидите, что появится джойстик для управления главным героем. . . .	Реалии Hrethgir 01.03.2026 Нет, я не закончил до сих пор симулятор. Эта задача сложнее. Не получилось уйти в плавсостав, но оно и к лучшему, возможно. Точнее получалось - но сварщиком в палубную команду, а это значит, в моём. . .
Ритм жизни kumehtar 27.02.2026 Иногда приходится жить в ритме, где дел становится всё больше, а вовлечения в происходящее — всё меньше. Плотный график не даёт вниманию закрепиться ни на одном событии. Утро начинается с быстрых,. . .	SDL3 для Web (WebAssembly): Сборка библиотек: SDL3, Box2D, FreeType, SDL3_ttf, SDL3_mixer и SDL3_image из исходников с помощью CMake и Emscripten 8Observer8 27.02.2026 Недавно вышла версия 3. 4. 2 библиотеки SDL3. На странице официальной релиза доступны исходники, готовые DLL (для x86, x64, arm64), а также библиотеки для разработки под Android, MinGW и Visual Studio. . . .	SDL3 для Web (WebAssembly): Реализация движения на Box2D v3 - трение и коллизии с повёрнутыми стенами 8Observer8 20.02.2026 Содержание блога Box2D позволяет легко создать главного героя, который не проходит сквозь стены и перемещается с заданным трением о препятствия, которые можно располагать под углом, как верхнее. . .	Конвертировать закладки radiotray-ng в m3u-плейлист damix 19.02.2026 Это можно сделать скриптом для PowerShell. Использование . \СonvertRadiotrayToM3U. ps1 <path_to_bookmarks. json> Рядом с файлом bookmarks. json появится файл bookmarks. m3u с результатом. # Check if. . .

@oktogen 55 / 50 / 5 Регистрация: 30.06.2022 Сообщений: 251
	23.12.2023, 15:56
	Если система нагруженная, таблица часто перезаписывается, то индекс разбухает и ваш скрипт скорее всего ждёт освобождения какого нибудь индекса. Придётся рефакторить. 0

@oktogen 55 / 50 / 5 Регистрация: 30.06.2022 Сообщений: 251
	25.12.2023, 09:53
	Попробуйте абстрактно сформировать задачу которую вы решаете этим способом. 0

@Georg IV 0 / 0 / 0 Регистрация: 28.04.2021 Сообщений: 19
	26.12.2023, 21:15 [ТС]
	Я немного дополню мой вопрос. На большой таблице скрипт выполняется долго и если его отменить (если в PGAdmin оператор нажмет на остановку, потому что техокно для этой базы закончилось и нужно возобновлять работу), то он остановится, а ни одна запись не удаляется. У меня возникла мысль, что он просто вначале долго инициализируется и не удаляет, а если его в этот момент прервать, то он и не удалит ничего. Как бы делать так, чтобы он все-таки удалял, даже если его прерывают. 0

@Swa111 913 / 287 / 57 Регистрация: 01.06.2023 Сообщений: 812
	27.12.2023, 06:49
	На самом деле он удаляет, но так как Вы прерываете операцию, то транзакция откатывается, поэтому результатов не видно. Как вариант вынесите отдельную итерацию цикла в отдельную функцию и вызывайте ее через отдельное подключение с коммитом каждый раз. 0