Re: postgres_fdw misbehaviour using "DELETE ... RETURNING *"

Hello. Thank you for the self-contained reproducing stuff.

At Fri, 18 May 2018 22:38:43 -0300, Fabrízio de Royes Mello <fabriziomello@gmail.com> wrote in
<CAFcNs+qi7gV4nnWGeWwrj-qyRNOiTfaiWTt6+m5Lr6ov29WXmA@mail.gmail.com>
> Using "DELETE ... RETURNING *" against a remote partitioned table (parent)
> returns wrong results according attached test case.

It is the same issue with that being discussed in -hackers ML.
In the case, not only returning wrong rows, 6 rows in all should
have been deleted in the three partitions.

https://www.postgresql.org/message-id/CAFjFpRfcgwsHRmpvoOK-GUQi-n8MgAS+OxcQo=aBDn1COywmcg@mail.gmail.com

> I've tested it against all supported versions and got error just in 9.3,
> 9.4 and 9.5 version.

The cause of 9.6 and later not having the misbehavior is a
feature called "remote UPDATEs and DELETEs" introduced in
9.6. You will see the same misbehavior by the queries that
doesn't use the feature even on 9.6 and later. random() inhibits
remote DELETE in the following query.

## in 9.6, 10 and devel.
BEGIN;
\echo Should return IDs 200006 and 300005
WITH x AS (DELETE FROM foo WHERE id = ANY('{200006,300005}'::bigint[]) AND random() <= 1 RETURNING *) SELECT * FROM x;
ROLLBACK;
   id
--------
 100006
 100005
(2 rows)

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center