Триггер подсчета ссылок в PostgreSQL

Используйте NOT EXISTS< /a> анти-полусоединения.

DELETE FROM refe r
WHERE NOT EXISTS (SELECT 1 FROM foo WHERE refe = r.id)
AND   NOT EXISTS (SELECT 1 FROM bar WHERE refe = r.id);

DELETE FROM refe r
WHERE r.id = 12345   -- your id here
AND   NOT EXISTS (SELECT 1 FROM foo WHERE refe = r.id)
AND   NOT EXISTS (SELECT 1 FROM bar WHERE refe = r.id);

• Ограничения внешнего ключа в отношениях "многие ко многим"

CREATE TRIGGER foo_updelaft_kill_orphaned_refe
AFTER DELETE OR UPDATE OF refe
ON foo
FOR EACH ROW EXECUTE PROCEDURE f_trg_kill_orphaned_refe();

Подробности в руководстве.

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

CREATE FUNCTION check_ref_count()
RETURNS trigger
LANGUAGE plpgsql
AS $$
  BEGIN
    DELETE FROM Refe WHERE id NOT IN (SELECT refe from liverefe);
    RETURN NULL;
  END;
$$;

А затем прикрепите его к рассматриваемым таблицам.

CREATE TRIGGER slay_orphans
AFTER DELETE ON Foo
FOR EACH STATEMENT
EXECUTE PROCEDURE check_ref_count();

CREATE TRIGGER slay_orphans
AFTER DELETE ON Bar
FOR EACH STATEMENT
EXECUTE PROCEDURE check_ref_count();

(В зависимости от того, как вы работаете с этими элементами, вам, возможно, придется говорить AFTER DELETE OR UPDATE, а не просто AFTER DELETE. Особенно если вы часто меняете refe. Если вы обычно устанавливаете его один раз и не трогаете, AFTER DELETE должно быть достаточно.)

Примечание: как только вы сделаете это, запись в Refe не сохранится при следующем удалении, если какая-либо запись в liverefe не имеет своего идентификатора. Даже если это то, что вы хотите, это означает, что если удаления являются обычным явлением, даже совершенно новые записи могут быть удалены прямо из-под вас.

Вы можете сделать вещи более строгими, проверяя только те Refe, на которые указывали удаленные строки. Просто добавьте id = old.refe к условиям удаления и сделайте триггеры FOR EACH ROW, а не FOR EACH STATEMENT. Это сделает так, что только ранее указанные записи будут кандидатами на удаление. (Но это также означает, что очистка не имеет обратной силы — текущие сироты не будут очищены, если что-то не укажет на них, а затем не укажет на что-то еще или не будет удалено. И если вы идете по этому пути, AFTER DELETE OR UPDATE необходимо — небрежность удалить триггерный дескриптор всего, приводит к утечке строк, если вы не очищаете всю таблицу.)

Или добавьте отметку времени к Refe и измените запрос, чтобы игнорировать записи, отметка времени которых меньше X секунд/минут/независимо от того, что было раньше.

Как бы вы ни решили эту проблему, вы, вероятно, также захотите рассмотреть возможность использования транзакций
, чтобы гарантировать, что запись не будет удалена прямо в тот момент, когда вы пытаетесь указать на нее. И убедитесь, что ваши внешние ключи указаны как ON DELETE RESTRICT (так что удаление записи в Refe не будет разрешено, если запись действительно не потеряна).

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

Просто создайте скрипт, который выдает этот запрос:

DELETE FROM Refe WHERE id NOT IN (SELECT refe FROM liverefe)

и настройте это как задание cron (или запланированное задание в Windows).