Re: BUG #14421: RETURNING statement in DELETE FROM with REFERENCES

On Fri, Nov 11, 2016 at 3:27 PM, <stepanperlov(at)gmail(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 14421
> Logged by: Stepan Perlov
> Email address: stepanperlov(at)gmail(dot)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.