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.
The best option IMO is to use a cron job to generate the logs for 'yesterday'::date. You could also use triggers before insert/update/delete to update the other table but this adds complexity and overhead, and for the current day, but this gets pretty complicated. Generate your historical logs once the data won't change anymore.
In this case you write a sql query and run it via psql and cron.
I would also add a trigger denying update or delete to records covered in your historical data if you can.
This gives you a few benefits:
It is more obvious when it breaks
It is simpler, with simpler failure cases
Now, as per your concerns:
You say you need rows for every day. This can be handled a number of relatively easy ways in PostgreSQL (remember that dates support integer math so you can take a base date and add a series to it, to generate a date series). This is a pretty easy way to get around if you are generating rows per day of week, etc.
You say you cant guarantee things won't change. The key question here is what your change window is and to do your historical reports after this window has closed. For example if it is after a month, you can generate reports for all dates in a month a month prior (i.e. generate all dates in January during early March). You can then rely on a view to handle newer rows vs older rows in a live basis. You can then have a trigger which ensures that the date of an inserted row in the orders table is newer than the newest date in the other table.
In my experience worrying about keeping this as a live summary usually isn't necessary. Small organizations (with small data sets) tend to close out books at least once a year, and live reporting is an option there. Larger organizations with larger data sets tend to close out receivables and payables (i.e. invoices) once a month or so, and so the only areas that have to be reported live (because they are subject to adjustment or revision) are open orders (which can be revised) and invoices which may need to be reviewed occasionally (and should never be revised but may have adjustments issued against them which might or might not need to be tracked in such a system).
Best Answer
For that you can use Foreign keys (referential integrity constraints):
See the manual
I explain it a bit better.
You have a parent table A with an
id
, and you have a second table B, which has a reference to theid
in Table A withCASCADE DELETE
.As the check constraints don't support referencing other tables, you can use a trigger with a function:
This would prevent deletion from
child
until its parent record is deleted.