Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Copied and pasted from stackoverflow.com/questions/13794407/… –  Craig Ringer Dec 10 '12 at 4:17
    
delete the question at stackoverflow, sorry for that –  Akash Dec 10 '12 at 14:46
    
Thanks, downvote removed. –  Craig Ringer Dec 10 '12 at 23:11

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer
    
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 (...) –  Craig Ringer 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. –  Craig Ringer Dec 13 '12 at 4:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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