I have tables
toy. There is a foreign key
toy. This foreign key is declared as
ON DELETE RESTRICT. I now want to delete all toys which are no longer declared as favorite in a violation-free and non-blocking manner:
- I want to delete as much orphaned toys as possible, while avoiding a foreign key violation because we attempt to delete a toy which is still in use.
- I do not want to wait for other ongoing transactions, which are possibly introducing a reference to a toy (which requires a key share lock) or simply updating a toy (which requires a (no key) update lock), to finish. Both lock types block our update lock request, required to delete the toy.
The first, naive approach would be:
delete from toy where not exists(select 1 from person where person.favorite_toy_id = toy.id)
This will not work in a concurrent environment: after the completion of the
not exists predicate, a concurrent transaction could declare the toy in question as favorite. In such case, we end up with a foreign key violation. Also, as said, I prefer this delete to happen in a non-blocking fashion, which is not attempted in this query.
So, my second approach in attempt to avoid this foreign key violation and any blocking is:
delete from toy where toy.id in ( select toy.id from toy where not exists(select 1 from person where person.favorite_toy_id = toy.id) for update skip locked )
However, this doesn't solve the requirement to avoid the foreign key violation, because the lock is taken after the evaluation of the
not exists predicate. So there is a small chance that we attempt to delete a toy which is still marked as favorite, resulting in a foreign key violation.
My third attempt to fix this is the following:
delete from toy where toy.id in ( select toy.id from toy where not exists(select 1 from person where person.favorite_toy_id = toy.id) for update skip locked ) and not exists(select 1 from person where person.favorite_toy_id = toy.id)
This applies double-checked locking (https://en.wikipedia.org/wiki/Double-checked_locking). This would work if and only if we have the guarantee that the subquery is always evaluated before the additional
not exists predicate. As far as I know, there is no such guarantee.
My question is rather educational: can this be solved in a pure SQL query? We can of course implement this in a
plpgsql function as shown below, but let's assume we want to solve this in one single plain SQL query.
create function prune_toys() returns void as $$ declare _id int; begin for _id in select toy.id from toy where not exists(...) for update skip locked loop delete from toy where toy.id = _id and not exists(...); end loop; end; $$ language plpgsql;
In all this, I assume the read committed transaction isolation level.