Re: count on cascading deletes

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(dot)klaver(at)aklaver(dot)com