Thread: count on cascading deletes

count on cascading deletes

From
Eildert Groeneveld
Date:
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





Re: count on cascading deletes

From
Kevin Grittner
Date:
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


Re: count on cascading deletes

From
Adrian Klaver
Date:
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


Re: count on cascading deletes

From
David G Johnston
Date:
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.