Thread: count on cascading deletes
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.
Thanks in advance
Tred
Eildert Groeneveld <eildert.groeneveld@fli.bund.de> wrote: > 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. Do you want to block anyone who attempts to modify data such that the counts might become different, for an indefinite amount of time waiting for a response to this question? Or would you rather that the actual delete when they say Y potentially do something very different from what the prompt is asking? -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 09/05/2014 09:49 AM, 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. My guess is because it is a complex problem, for the following reasons: 1) Because of MVCC(http://www.postgresql.org/docs/9.3/static/mvcc-intro.html) the numbers are only valid for that session. 2) You are assuming the FK is set up to cascade. It also possible to have other options: http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html " NO ACTION Produce an error indicating that the deletion or update would create a foreign key constraint violation. If the constraint is deferred, this error will be produced at constraint check time if there still exist any referencing rows. This is the default action. RESTRICT Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that the check is not deferrable. CASCADE Delete any rows referencing the deleted row, or update the values of the referencing column(s) to the new values of the referenced columns, respectively. SET NULL Set the referencing column(s) to null. SET DEFAULT Set the referencing column(s) to their default values. (There must be a row in the referenced table matching the default values, if they are not null, or the operation will fail.) " 3) Given the above you could start with a CASCADE that then leads to non-CASCADE options. So trying to handle all the various situations and dealing with possible rollbacks could get complicated in a hurry. > > Thanks in advance > > Tred > > > > > -- Adrian Klaver adrian.klaver@aklaver.com
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.