psql reports back wrong number of affected rows.

From: Erwin Moller <erwinmoller(at)xs4all(dot)nl>
To: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: psql reports back wrong number of affected rows.
Date: 2011-06-14 14:45:02
Message-ID: 4DF773EE.6060401@xs4all.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2011-06-14 14:55:08 Re: duplicate key violate error
Previous Message BangarRaju Vadapalli 2011-06-14 14:26:46 random backend crashes - how to debug ( Is crash dump handler released ? )