Re: 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 =3D 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=3D2.  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 =E2=80=8Boutput of the causing command (i.e., you get D=
ELETE 1
in the command response, not DELETE 6).


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

=E2=80=8B"=E2=80=8B
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.
=E2=80=8B"=E2=80=8B


David J.