И поэтому для таких пытливых любителей найти приключения на пятую точку и предлагаю вариант со скриптами. Ну что погнали!
Первое, что я сделал, это перебрал все реквизиты всех объектов БД с типом "Хранилище значений" и, узнав их абсолютные имена в терминах БД (в нашем случае это PGSQL), получил скрипт типа:
-- Удаляем временную таблицу, если она существует
DROP TABLE IF EXISTS temp_bytea_ids;
-- Создаем временную таблицу с уникальными bytea ID
CREATE TEMP TABLE temp_bytea_ids (
id_MD5 TEXT PRIMARY KEY, -- MD5 в текстовом виде (32 символа)
id TEXT --ID в текстовом виде (128 символа)
);
-- Заполняем времянку данными из всех таблиц с не пустым реквизитом типа ХЗ
INSERT INTO temp_bytea_ids (id_MD5, id)
SELECT
MD5(temp_ids.id) as id_MD5,
temp_ids.id as id
FROM (
SELECT
SUBSTRING(encode(_Fld3315, 'hex') FROM 33 FOR 32) as id_32, encode(_Fld3315, 'hex') as id
FROM public._Reference85 WHERE _Fld3315 IS NOT NULL AND octet_length(_Fld3315) > 0
UNION
SELECT
SUBSTRING(encode(_Fld3312, 'hex') FROM 33 FOR 32) as id_32, encode(_Fld3312, 'hex') as id
FROM public._Reference85 WHERE _Fld3312 IS NOT NULL AND octet_length(_Fld3312) > 0
UNION
.....
.....
.....
.....
SELECT
SUBSTRING(encode(_Fld8069, 'hex') FROM 33 FOR 32) as id_32, encode(_Fld8069, 'hex') as id
FROM public._Reference303_VT8065 WHERE _Fld8069 IS NOT NULL AND octet_length(_Fld8069) > 0
) as temp_ids;
;
-- Поиск префикса
SELECT
sum(1) as summa,
left(temp_id.id, 32)
FROM
temp_bytea_ids as temp_id
where
LENGTH(id) = 128
group by
left(temp_id.id, 32)
having
sum(1) > 1
Выполнив этот скрипт, мы получим от одного до N префиксов ID, которые используются при перемещении данных в binarydata. Изначально сразу после этого я удалял все, что не нашлось этим запросом. Но по итогу БД больше не открывалась. Это мне намекнуло, что найдены не все ID.
Я выяснил, что на стороне таблицы, ранее хранивший бинарные данные, теперь лежит ID, имеющий длину 128 символов:
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX - префикс 32 символа
YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY - ID, именно он хранится в качестве идентификатора в binarydata
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ - не анализировал.
Так что, зная длину 128 символов и префиксы, можно поискать со стороны БД. Выполним еще один скрипт. Он нужен для того что бы найти все ID используемые в БД (не важно хранилище значений там или нет). Все бинарные данные, превышающие минимальный размер хранимый "на месте" (в самой таблице владелице) в 8.3.27 переносятся в binarydata.
ИИ мне сказал, что вот так можно оббежать все таблицы БД и все их колонки с типом bytea и если там есть строка 128 символов и префикс равен найденным ранее, то мы нашли то, что нужно, а именно абсолютное имя таблицы и имя поля, где хранится ID из binarydata
DO $$
DECLARE
schema_name TEXT;
table_name TEXT;
column_name TEXT;
sql_query TEXT;
cnt INTEGER;
result_text TEXT := '';
prefix1 TEXT := '11111111111111111111111111111111';
prefix2 TEXT := '22222222222222222222222222222222';
BEGIN
FOR schema_name, table_name, column_name IN
SELECT
t.table_schema,
t.table_name,
c.column_name
FROM
information_schema.tables t
JOIN information_schema.columns c
ON t.table_schema = c.table_schema
AND t.table_name = c.table_name
WHERE
t.table_type = 'BASE TABLE'
AND t.table_schema NOT IN ('pg_catalog', 'information_schema')
AND c.data_type = 'bytea'
AND t.table_schema = 'public' -- Только схема public
ORDER BY
t.table_schema,
t.table_name,
c.column_name
LOOP
-- Проверяем, есть ли в этой колонке строки с нужными условиями
-- Используем OR для проверки двух префиксов
sql_query := FORMAT(
'SELECT COUNT(*) FROM %I.%I ' ||
'WHERE %I IS NOT NULL ' ||
'AND octet_length(%I) > 0 ' ||
'AND LENGTH(encode(%I, ''hex'')) = 128 ' ||
'AND (LEFT(encode(%I, ''hex''), 32) = %L ' ||
' OR LEFT(encode(%I, ''hex''), 32) = %L)',
schema_name,
table_name,
column_name,
column_name,
column_name,
column_name, prefix1,
column_name, prefix2
);
BEGIN
EXECUTE sql_query INTO cnt;
IF cnt > 0 THEN
result_text := result_text ||
FORMAT('Схема: %s, Таблица: %s, Колонка: %s, Найдено строк: %s' || E'\n',
schema_name,
table_name,
column_name,
cnt);
END IF;
EXCEPTION
WHEN OTHERS THEN
-- Пропускаем ошибки (например, если нет прав на таблицу)
CONTINUE;
END;
END LOOP;
-- Выводим результат
IF result_text = '' THEN
RAISE NOTICE 'Колонки с указанными условиями не найдены';
ELSE
RAISE NOTICE 'Найдены колонки:%', E'\n' || result_text;
END IF;
END $$;
Скрипт выше написан для нашего proda там было использовано два префикса.
Теперь дело за малым, зная все пары таблица + поле нашей БД, где хранится ID из binarydata, можно найти все используемые ID. И тогда все, что есть в binarydata, но не имеют пары, и есть лишние.)))
-- Удаляем временную таблицу, если она существует
DROP TABLE IF EXISTS temp_bytea_ids;
-- Создаем временную таблицу с уникальными bytea ID
CREATE TEMP TABLE temp_bytea_ids (
id_32 TEXT, -- ID в текстовом виде первые 32 символа (32 символа)
id_MD5 TEXT PRIMARY KEY -- MD5 в текстовом виде (32 символа)
);
-- Заполняем времянку данными из всех таблиц с реквизитом типа ХЗ
INSERT INTO temp_bytea_ids (id_32, id_MD5)
SELECT
temp_ids.id_32 as id_32
MD5(temp_ids.id) as id_MD5
FROM (
SELECT
SUBSTRING(encode(_fld80282, 'hex') FROM 33 FOR 32) as id_32, encode(_fld80282, 'hex') as id
FROM public._reference79206 WHERE _fld80282 IS NOT NULL AND octet_length(_fld80282) > 0
UNION
SELECT
SUBSTRING(encode(_fld75303, 'hex') FROM 33 FOR 32) as id_32, encode(_fld75303, 'hex') as id
FROM public._reference75088x1 WHERE _fld75303 IS NOT NULL AND octet_length(_fld75303) > 0
UNION
.....
.....
.....
.....
SELECT
SUBSTRING(encode(_fld75199, 'hex') FROM 33 FOR 32) as id_32, encode(_fld75199, 'hex') as id
FROM public._reference75082x1 WHERE _fld75199 IS NOT NULL AND octet_length(_fld75199) > 0
) as test;
-- Индекс для id_32 колонки
CREATE INDEX ON temp_bytea_ids(id_32);
-- Удаление записей из public.binarydata которых нет в temp_bytea_ids порциями по 1000
DO $$
DECLARE
rows_deleted INTEGER;
total_deleted INTEGER := 0;
batch_size INTEGER := 1000;
row_ids_to_delete TEXT[];
BEGIN
LOOP
-- 1. Сначала получаем ID для удаления (максимум batch_size)
SELECT array_agg(encode(bd.f_key, 'hex'))
INTO row_ids_to_delete
FROM public.binarydata bd
WHERE NOT EXISTS (
SELECT 1 FROM temp_bytea_ids tbi
WHERE tbi.id_32 = encode(bd.f_key, 'hex')
)
AND bd.f_key IS NOT NULL
LIMIT batch_size;
-- 2. Если массив пустой, выходим из цикла
EXIT WHEN row_ids_to_delete IS NULL;
-- 3. Удаляем записи по найденным ID
DELETE FROM public.binarydata bd
WHERE encode(bd.f_key, 'hex') = ANY(row_ids_to_delete);
-- 4. Получаем количество удаленных строк
GET DIAGNOSTICS rows_deleted = ROW_COUNT;
total_deleted := total_deleted + rows_deleted;
-- 5. Выводим информацию о прогрессе
RAISE NOTICE 'Удалено % записей, всего удалено: %', rows_deleted, total_deleted;
-- 6. Если удалено меньше batch_size, значит это последняя порция
EXIT WHEN rows_deleted < batch_size;
-- 7. Небольшая пауза для уменьшения нагрузки на БД
PERFORM pg_sleep(0.01);
END LOOP;
RAISE NOTICE 'Удаление завершено. Всего удалено записей: %', total_deleted;
END $$;
-- Проверка оставшихся записей (опционально)
SELECT COUNT(*) as remaining_rows
FROM public.binarydata bd
WHERE NOT EXISTS (
SELECT 1 FROM temp_bytea_ids tbi
WHERE tbi.id_32 = encode(bd.f_key, 'hex')
);
Запускаем скрипт, удаляем все лишнее и снова наслаждаемся своей небольшой БД.
Но опять же повторюсь: это лишь рассуждения на тему и я никого не призываю действовать по написанному Выше! Самый простой и верный способ по прежнему выгрузка/загрузка в dt. Только следите, чтобы загрузка произошла в полном объеме. А то мы и такое словили.))))
Если кому-то нужна обработка по генерации скрипта, пишите в комментарии, и я скину или добавлю к статье.
Вступайте в нашу телеграмм-группу Инфостарт
