Thread: psql reports back wrong number of affected rows.
Hi, I was surprised by the following behavior of Postgres (8.1). Consider the following table and constraint: ============================================= CREATE TABLE tblissue( issueid SERIAL PRIMARY KEY, title TEXT, comment TEXT, createtimestamp TIMESTAMP DEFAULT (current_timestamp), parentissueid INTEGER, caseclosed CHAR(1) ); alter table tblissue add constraint "tblissue_parentissueid_fkey_casc_del" FOREIGN KEY (parentissueid) REFERENCES tblissue(issueid) ON DELETE CASCADE; ============================================= So the parentissueid references the same table's PK, but can NULL too. All fine so far. Next I insert a few rows that use not null values for parentissueid, so the foreign key constraint is in effect. Suppose I created a few rows that have 1 as value for parentissueid. 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. Regards, Erwin Moller PS: I found a few possible relevant postings. One of them (by Tom Lane) pointed to here: http://www.postgresql.org/docs/8.0/static/rules-status.html but I am still unsure if it is relevant.
Erwin Moller <erwinmoller@xs4all.nl> writes:
> 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?
It's reporting the number of rows deleted from tblissue. Cascade
effects are not relevant.
regards, tom lane
On 6/14/2011 5:05 PM, Tom Lane wrote: > Erwin Moller<erwinmoller@xs4all.nl> writes: >> 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? > It's reporting the number of rows deleted from tblissue. Hi, But I deleted 6 from tblissue in my example. (1 directly, the other 5 by cascade) Hence my confusion. > Cascade > effects are not relevant. > Ok, clear. So the number of deleted rows (DELETE x) are only the ones that directly matched the ones in the where-clause. I suspected that much, but it is better to check. :-) Thanks for your time, Tom Regards, Erwin Moller > regards, tom lane
> 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. David J.
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. > > > >