Postgresql – Depending on Foreign Key Constraints to avoid deletion

postgresql

In PostgreSQL, I have a 2 tables with father/parent relationship. I wish to delete the father record if no records of child are associated with it (using after triggers)

Currently, I have been doing something like

  1. Get the count of records (having the same parent id as the current child)
  2. If the count is 1, delete current child record and delete the parent record of it
  3. If the count is greater than 1, just delete the child record (since more child's depend on the parent record)

I was wondering if I directly fire the delete statement without getting the count of parent record, like

  1. Delete Child record
  2. Delete Parent Record (shall throw a error if there are dependencies)

In this case if any child is associated with the parent (via a foreign key), it shall throw a error and not be deleted

Is the 2nd way right, to get this done, are such errors (foreign key constraints dependancies) logged in PostgreSQL

Best Answer

It seems like your intention is to delete the parent record when the last child is deleted.

If so, while you can issue an unconditional DELETE to remove the parent and let foreign key relationships with existing children prevent it, this may not be the best strategy. To make it work at all you must do the DELETE of the parent in a different transaction (or sub-transaction) to the DELETE of the child, otherwise the whole transaction's work will be undone when the DELETE of the parent fails and the transaction aborts.

Doing it in an entirely separate transaction is not very safe as it'll leave a window where there's a parent with no children - and if you ever re-use keys it's totally unacceptable.

Deleting the child then deleting the parent in a subtransaction using SAVEPOINT and ROLLBACK TO SAVEPOINT is OK, albeit awkward. You need to think about what happens when two concurrent transactions delete the last two children of the same parent, though; you'd land up with an orphan parent record because each would see the "remaining" child the other one is in the process of deleting. To prevent that you'll need to SELECT ... FOR UPDATE the parent before deleting the child.

BEGIN;
SELECT 1 FROM parent WHERE parent_id = 1 FOR UPDATE;
DELETE FROM child WHERE child_id = 11 and parent_id = 1;
SAVEPOINT delete_parent;
DELETE FROM parent WHERE parent_id = 1;
-- In the application see if the DELETE was successful. If it was, COMMIT.
-- if it failed, run:
ROLLBACK TO SAVEPOINT delete_parent;
-- before committing the deletion of the child.
COMMIT;

Alternately, because you're forcing transactions that remove children from a parent to occur serially, you can just replace the SAVEPOINT and everything below with:

DELETE FROM parent 
WHERE parent_id = 1 AND NOT EXISTS (
  SELECT 1 FROM child c WHERE c.parent_id = parent.parent_id
);

An alternative strategy is to use a SERIALIZABLE isolation transaction to optimistically delete the parent instead of using the initial SELECT ... FOR UPDATE. If somebody else creates a new child or does something else that'll conflict, serializable isolation in PostgreSQL 9.1 or above will abort one of the conflicting transactions. Your application must be prepared to deal with errors and re-run aborted transactions.

Related Question