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.
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 ? ) |