Eildert Groeneveld wrote
> Dear All
>
> prior to issuing a cascading delete in an interactive program
> I would like to retrieve from Postgresql what is involved in the
> particular delete, so that this can be printed to the console
> and the user can be asked:
>
> This is what your delete would do in the database:
> deleting panel = 123 would imply deleting the following children:
> in foo: 123 records
> in fuu: 123456 records
>
> do you really want to do this? (y/N)
>
> As this is a general problem, I would assume, that someone has written
> a function that would do that. Unfortunately, no luck with google.
It is a general problem and one that is fairly easy to solve - like others
have said it depends on how safe/accurate you wish to be. It also depends
strictly on the schema being used so any solution will have to be
customized.
1. Query the tables and save your counts somewhere
2. Prompt the user for a response
3. If yes, affect the delete - one statement per table - and compare the
counts to the values saved previously.
4. If the deletes succeed but the counts do not match raise your own
exception
Basically, an optimistic locking protocol for delete cascade
Obviously not fool-proof but since you are only reporting numbers to the
user anyway the obvious delete+insert of distinct child records doesn't
appear to be something that will concern you.
You can always save and re-check more than just the counts if you'd like.
DELETE ... RETURNING into a temporary table and comparing that to Step #1
above could be put to good use.
As mentioned there is no feasible way for PostgreSQL itself to use the FK w/
ON DELETE CASCADE to assist here - while it could report the final cascaded
counts (in theory at least) the API to access and use that information from
SQL or pl/pgsql would be cumbersome and likely only of limited general use -
since an interactive mode is not be something that would be facilitated in
any case.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/count-on-cascading-deletes-tp5817957p5817965.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
