Проверить наличие внешних связей

[fmcoder]

Есть небольшая проблема...

Имеем 1 таблицу (далее - основная) с полем id и еще кучей других полей, далее есть еще N таблиц (дополнительные) связанных с таблицей 1 по полю id.

Задача такова: при удалении из дополнительной таблицы, если больше ни одна из дополнительных таблиц не содержит данного id то удалить запись с этим id из основной таблицы.

Пока нашел 2 решения:
1. Просто попробовать удалить, в случае чего (если есть связи по этому id) вывалится эксепшн и ничего не удалится. Мне кажется как-то грубо

2. Прогнать "select count(*) from ... where id = myid" по всем доп. таблицам, если везде 0 записей то удалить из основной. Это по моему как-то долго и еще нужно нагенерить запросов для каждой таблицы. Или может можно как-то одним запросом сделать? Пробовал "select count(*) from "Table1", "Table2" where "id" = 1" говорит ambiguous field name...

Есть ли способы проще?

[SAMZ]

В данном случае вместо select count(*) эффективнее работает exists
Я бы эти проверки организовал в одной процедуре и вызывал бы ее из триггеров после удаления в дочерних таблицах.

[Dimitry Sibiryakov]

Задача такова: при удалении из дополнительной таблицы, если больше ни одна из дополнительных таблиц не содержит данного id то удалить запись с этим id из основной таблицы.

Есть ли способы проще?

Просто и тупо пытайся удалить запись из основной таблицы и гаси исключение с помощью WHEN. Если подчиненных записей нет - она удалится. Если есть - никакие проверки не помогут.

[Slavik]

Я бы ещё ПЕРЕД удалением подчинённой записи делал блокировку мастер-записи (через update или select ... for update with lock), чтоб другим транзакциям неповадно было.

[WildSery]

Я бы ещё ПЕРЕД удалением подчинённой записи делал блокировку мастер-записи (через update или select ... for update with lock), чтоб другим транзакциям неповадно было.

Чего именно неповадно?

[Slavik]

Чего именно неповадно?

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

Блокировка же мастер-записи даст по мордам второй конкурентной транзакции.

[WildSery]

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

Чтобы так не было, удалять родителя надо отдельной транзакцией.

Блокировка же мастер-записи даст по мордам второй конкурентной транзакции.

Блокировка даст по мордам всем, в итоге ни одну дочернюю запись нельзя будет удалить, пока не отпустят. Хорошо ли это?

[fmcoder]

Всем спасибо за ответы!
А что за WHEN? Информации по нему найти не смог, слишком общеупотребительное слово... Я так думаю это то что надо (гасить исключения), просто пытаться удалить, если получится - удалится, если нет то нет и никаких проблем.

Насчет lock и нескольких транзакций одновременно это врядли. У меня система расчитана на работу на одном компьютере (и сервер и клиент), а кол-во программ-клиентов где-то от 3-х до 12, и то удалять может только одна (максимум 3) из них, остальные читают или update'ят, поэтому тут думаю можно не бояться конфликтов.

[WildSery]

А что за WHEN?

Искать нужно было конкретно в синтаксисе PSQL-блоков. Ловля исключений в процедурах/триггерах.
Например,

Код: Выделить всё

delete from Table1 where id=:id;
when any do begin
  insert into log (id, val) values (:id, 'Not deleted');
end

Всместо ANY может быть указана конкретная ошибка.

то удалять может только одна (максимум 3) из них, остальные читают или update'ят, поэтому тут думаю можно не бояться конфликтов.

Конфликтов чего? Ты ж сам правильно сказал, что исключение на невозможность удаления можно просто давить и всё.

[Slavik]

Чтобы так не было, удалять родителя надо отдельной транзакцией.

Можно и так, хоть и остаётся некоторая вероятность облома, не связанного с наличием подчинённых записей.

Блокировка даст по мордам всем, в итоге ни одну дочернюю запись нельзя будет удалить, пока не отпустят. Хорошо ли это?

Автор, похоже, блокировок не боится

[WildSery]

Можно и так, хоть и остаётся некоторая вероятность облома, не связанного с наличием подчинённых записей.

Какого например?

Автор, похоже, блокировок не боится

Не понял. Почему он не боится? Все боятся

[Slavik]

Какого например?

Сервер прилёг отдохнуть, сетка тоже устала...

Не понял. Почему он не боится? Все боятся

Откуда ж я знаю. Говорит "можно не бояться конфликтов"...

[fmcoder]

Спасибо, с when буду разбираться.

Насчет блокировок, я же написал, что в моих программах никто ничего не блокирует, изменения в один момент времени делаются только одной программой и т.п.
Я в технологиях БД новенький, неделю где-то осваиваю, так что может чего не понимаю пока.

[kdv]

изменения в один момент времени делаются только одной программой и т.п.

а одну программу может запустить только один человек с одного компа?

[fmcoder]

изменения в один момент времени делаются только одной программой и т.п.

а одну программу может запустить только один человек с одного компа?

Да, именно так.