BUG #14421: RETURNING statement in DELETE FROM with REFERENCES

On Fri, Nov 11, 2016 at 3:27 PM, <stepanperlov@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 14421
Logged by: Stepan Perlov
Email address: stepanperlov@gmail.com
PostgreSQL version: 9.5.5
Operating system: ubuntu 14.04
Description:

Hello

CREATE TABLE test(
id bigserial PRIMARY KEY,
parent bigint REFERENCES test(id) ON DELETE CASCADE ON UPDATE CASCADE
);
INSERT INTO test VALUES (1, null);
INSERT INTO test VALUES (2, 1);
INSERT INTO test VALUES (3, 2);
INSERT INTO test VALUES (4, 2);
INSERT INTO test VALUES (5, 2);
INSERT INTO test VALUES (6, 2);
INSERT INTO test VALUES (7, 2);

DELETE FROM test
WHERE id = 2
RETURNING id;

Returns:
id
2

I expect:
id
2
3
4
5
6
7

Incorrect expectations, not a bug.

The only record deleted from the table specified in DELETE FROM "test" is
the record having id=2. The fact that other records just happened to be
deleted (and just happened to be on the same table) due to a cascade
doesn't factor into it. If you generalize to a normal DELETE/CASCADE,
where the PK is on a different table, you should understand why it doesn't
work that way. In fact, the actions of the CASCADE triggers are invisible
when looking at the ​output of the causing command (i.e., you get DELETE 1
in the command response, not DELETE 6).

https://www.postgresql.org/docs/9.6/static/sql-delete.html

​"​
An expression to be computed and returned by the DELETE command after each
row is deleted. The expression can use any column names of the table named
by table_name or table(s) listed in USING. Write * to return all columns.
​"​

David J.