Postgres Pro
Downloads
Home   >   mailing lists

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

postgres_fdw misbehaviour using "DELETE ... RETURNING *"

From
Fabrízio de Royes Mello
Date:
Hi all,

Using "DELETE ... RETURNING *" against a remote partitioned table (parent) returns wrong results according attached test case.

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

Attached ziped test case and results:
- fdw_test.sql (main test case script)
- fdw_test.expected.out (expected output)
- fdw_test.result-*.out (wrong results)

Some environment details:

fabrizio@macanudo:/tmp 
$ uname -a
Linux macanudo 4.13.0-41-generic #46~16.04.1-Ubuntu SMP Thu May 3 10:06:43 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

fabrizio@macanudo:/tmp 
$ gcc --version
gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609
Copyright (C) 2015 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Attachment

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

From
Kyotaro HORIGUCHI
Date:
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