Re: psql reports back wrong number of affected rows.

On 6/14/2011 8:08 PM, David Johnston wrote:
>> alter table tblissue add constraint
>> "tblissue_parentissueid_fkey_casc_del" FOREIGN KEY (parentissueid)
>> REFERENCES tblissue(issueid) ON DELETE CASCADE;
>> =============================================
>>
>> Then:
>> delete from tblissue where issueid=1;
>> DELETE 1
>>
>> Postgresql now deletes all rows that had a 1 for parentissueid. (5 in my
>> testcase).
>> That was correct, and as I intended, but why does Postgres answer "DELETE
>> 1" instead of DELETE 6?
>>
>> Can somebody explain that to me please?
>> Thanks for your time.
> You only explicitly deleted a single row; all the rest were done via the
> CASCADE and thus are not counted in the delete count.
>
> Make sense; If I delete a record and see "DELETE 1000" because 999 FK
> records were deleted I would have no way of know if I foo-barred the DELETE
> query itself and actually killed 1000 records using the DELETE itself or got
> it right and hit the 1 intended record and simply got 999 more deletions
> indirectly.
>
> I can see where a more helpful response would be: "DELETE 1 \n NOTICE: 999
> FK references were deleted due to Cascade" but the "DELETE 1" MUST show me
> explicitly how many records were deleted solely due to my DELETE statement's
> FROM and WHERE clauses.

Agree 100%.
I am not a big fan of CASCADING effects (I rather do it 'by hand'), but
in this case it was a really easy solution.

Thanks you for your response.

Regards,
Erwin Moller

> David J.
>
>
>
>