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


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.

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:
-- before committing the deletion of the child.

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:

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.

  • thanks for the detailed reply, I guess I shall go with the first method, get the row count and if its 1, delete the parent record
    – Akash
    Dec 10 '12 at 14:47
  • @Akash Personally I'd use serializable isolation, or use SELECT ... FOR UPDATE combined with a DELETE FROM parent WHERE ... AND NOT EXISTS (...) Dec 10 '12 at 23:13
  • thanks, I also think that the second Delete statement that you mentioned (the one with not exists), would be safer and the statement would be executed in one go
    – Akash
    Dec 13 '12 at 3:10
  • @Akash So long as you lock the parent record with a SELECT ... FOR UPDATE to prevent concurrent deletions by other clients, it is. Dec 13 '12 at 4:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.