Триггер удаления больших двоичных объектов в PostgreSQL 11

30 нояб 2022
Опубликовано в Статьи

PostgreSQL позволяет сохранять большие двоичные объекты (BLOB) в базе данных. При удалении непосредственно записи из таблицы, в которой загружены данные, двоичные объекты не удаляются, так как поле типа oid хранит только ссылку на BLOB.

Для удаления BLOB необходимо выполнить запрос:

SELECT lo_unlink( oid);

Чтобы удалить большие двоичные объекты из базы данных при удалении записи содержащей поле с типом oid можно создать триггер представленный ниже (пример для PostgreSQL 11):

CREATE OR REPLACE FUNCTION DELETE_MYTABLE_TRG_FUN()
  RETURNS trigger AS
$$
DECLARE
   result_unlink  integer;
BEGIN
  IF (TG_OP = 'DELETE' OR (TG_OP = 'UPDATE' AND OLD.FILE <> NEW.FILE)) THEN
    SELECT lo_unlink(oid) INTO result_unlink FROM pg_largeobject_metadata WHERE oid = OLD.file;
  END IF;
RETURN NULL;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER DELETE_MYTABLE_TRG
  AFTER UPDATE OR DELETE
  ON MYTABLE
  FOR EACH ROW
  EXECUTE PROCEDURE DELETE_MYTABLE_TRG_FUN();

После удаления большого объекта можно заметить, что зарезервированное место не освобождается:

SELECT pg_size_pretty(pg_table_size('pg_largeobject'));

Для уменьшения занимаемого места нужно так жк выполнить запрос:

VACUUM FULL ANALYZE pg_largeobject;

этот запрос можно добавить в cron для периодического распределения места занимаемого большими двоичными объектами.

PostgreSQL