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.
