Message-ID: <5A9928F1.2010206@lab.ntt.co.jp>
Date: Fri, 02 Mar 2018 19:35:29 +0900
From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:11.0) Gecko/20120327 Thunderbird/11.0.1
MIME-Version: 1.0
Subject: Re: postgres_fdw: perform UPDATE/DELETE .. RETURNING on a join
 directly
References: <5A438A45.2000909@lab.ntt.co.jp> <5A57193A.2080003@lab.ntt.co.jp>
 <CA+TgmoYef9czAA1gJeqh4v47Tv=dqOp4EfdU6bV8EYkOdCwvUw@mail.gmail.com>
 <6166.1518044513@sss.pgh.pa.us>
 <CA+TgmoaxGGZyUAWQMz7c-3V85TV5-w2wmvpOYrLnALi2d9Z-dQ@mail.gmail.com>
 <5A7BE8E6.200@lab.ntt.co.jp> <28606.1518105934@sss.pgh.pa.us>
 <CA+Tgmobf+AXEwO4r0oCkvNhkaVnxPgoJaApoX+rAi0G7-N-Dyg@mail.gmail.com>
 <5A7CFDDA.8050906@lab.ntt.co.jp> <5A7D76CF.8080601@lab.ntt.co.jp>
 <CA+TgmoaAQCn0+xh3JG2avn35Lukj2NVmhxVi7hE2CwzSUQ-5ag@mail.gmail.com>
 <20134.1518201188@sss.pgh.pa.us>
 <CA+TgmoY5KNf-Y=zOdHVfa8Lmo=OMJe=dBpP-noT_1fdVK6EYPA@mail.gmail.com>
 <18653.1518297871@sss.pgh.pa.us> <5A82DCCE.3060107@lab.ntt.co.jp>
 <29147.1519769644@sss.pgh.pa.us>
 <CA+Tgmob_WGK+vKzE6FmrMmuwuv4myntfRAkEbo+tsDt9xUwCLQ@mail.gmail.com>
 <23783.1519929258@sss.pgh.pa.us>
In-Reply-To: <23783.1519929258@sss.pgh.pa.us>
Content-Type: multipart/mixed;
 boundary="------------000405070906010803010409"
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>,
        Pg Hackers <pgsql-hackers@postgresql.org>,
        Rushabh Lathia <rushabh.lathia@gmail.com>,
        Michael Paquier <michael.paquier@gmail.com>,
        Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------000405070906010803010409
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

(2018/03/02 3:34), Tom Lane wrote:
> Robert Haas<robertmhaas@gmail.com>  writes:
>> This is another patch that got past me.  Committed now.
>
>> I'd be lying if I said I was filled with confidence that this was
>> going to fix it, but I don't know what to do other than keep tinkering
>> with it until we find something that works.
>
> The buildfarm news on this isn't very good; jaguarundi seems happier,
> but we've seen intermittent failures on four other critters since
> this went in.
>
> The idea of disabling autovacuum seems reasonable to me, but perhaps
> it needs to be done to more of the tables.

Agreed.  Better safe than sorry, so I disabled autovacuum for all the 
tables created in the postgres_fdw regression test, except the ones with 
no data modification created in the sections "test handling of 
collations" and "test IMPORT FOREIGN SCHEMA".  I'm attaching a patch for 
that.

Best regards,
Etsuro Fujita

--------------000405070906010803010409
Content-Type: text/x-diff;
 name="postgres-fdw-regress-3.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="postgres-fdw-regress-3.patch"

*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 35,43 **** CREATE TABLE "S 1"."T 1" (
  	c8 user_enum,
  	CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
  );
- -- "S 1"."T 1" will be heavily updated below, so disable autovacuum for
- -- the table to avoid unexpected effects of that
- ALTER TABLE "S 1"."T 1" SET (autovacuum_enabled = 'false');
  CREATE TABLE "S 1"."T 2" (
  	c1 int NOT NULL,
  	c2 text,
--- 35,40 ----
***************
*** 55,60 **** CREATE TABLE "S 1"."T 4" (
--- 52,62 ----
  	c3 text,
  	CONSTRAINT t4_pkey PRIMARY KEY (c1)
  );
+ -- Disable autovacuum for these tables to avoid unexpected effects of that
+ ALTER TABLE "S 1"."T 1" SET (autovacuum_enabled = 'false');
+ ALTER TABLE "S 1"."T 2" SET (autovacuum_enabled = 'false');
+ ALTER TABLE "S 1"."T 3" SET (autovacuum_enabled = 'false');
+ ALTER TABLE "S 1"."T 4" SET (autovacuum_enabled = 'false');
  INSERT INTO "S 1"."T 1"
  	SELECT id,
  	       id % 10,
***************
*** 6172,6177 **** ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative;
--- 6174,6180 ----
  -- test WITH CHECK OPTION constraints
  -- ===================================================================
  CREATE TABLE base_tbl (a int, b int);
+ ALTER TABLE base_tbl SET (autovacuum_enabled = 'false');
  CREATE FOREIGN TABLE foreign_tbl (a int, b int)
    SERVER loopback OPTIONS(table_name 'base_tbl');
  CREATE VIEW rw_view AS SELECT * FROM foreign_tbl
***************
*** 6232,6237 **** DROP TABLE base_tbl;
--- 6235,6241 ----
  -- test serial columns (ie, sequence-based defaults)
  -- ===================================================================
  create table loc1 (f1 serial, f2 text);
+ alter table loc1 set (autovacuum_enabled = 'false');
  create foreign table rem1 (f1 serial, f2 text)
    server loopback options(table_name 'loc1');
  select pg_catalog.setval('rem1_f1_seq', 10, false);
***************
*** 6779,6784 **** DROP TRIGGER trig_row_after_delete ON rem1;
--- 6783,6790 ----
  -- ===================================================================
  CREATE TABLE a (aa TEXT);
  CREATE TABLE loct (aa TEXT, bb TEXT);
+ ALTER TABLE a SET (autovacuum_enabled = 'false');
+ ALTER TABLE loct SET (autovacuum_enabled = 'false');
  CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a)
    SERVER loopback OPTIONS (table_name 'loct');
  INSERT INTO a(aa) VALUES('aaa');
***************
*** 6920,6931 **** DROP TABLE loct;
--- 6926,6941 ----
  -- Check SELECT FOR UPDATE/SHARE with an inherited source table
  create table loct1 (f1 int, f2 int, f3 int);
  create table loct2 (f1 int, f2 int, f3 int);
+ alter table loct1 set (autovacuum_enabled = 'false');
+ alter table loct2 set (autovacuum_enabled = 'false');
  create table foo (f1 int, f2 int);
  create foreign table foo2 (f3 int) inherits (foo)
    server loopback options (table_name 'loct1');
  create table bar (f1 int, f2 int);
  create foreign table bar2 (f3 int) inherits (bar)
    server loopback options (table_name 'loct2');
+ alter table foo set (autovacuum_enabled = 'false');
+ alter table bar set (autovacuum_enabled = 'false');
  insert into foo values(1,1);
  insert into foo values(3,3);
  insert into foo2 values(2,2,2);
***************
*** 7685,7690 **** SET enable_partitionwise_join=on;
--- 7695,7702 ----
  CREATE TABLE fprt1 (a int, b int, c varchar) PARTITION BY RANGE(a);
  CREATE TABLE fprt1_p1 (LIKE fprt1);
  CREATE TABLE fprt1_p2 (LIKE fprt1);
+ ALTER TABLE fprt1_p1 SET (autovacuum_enabled = 'false');
+ ALTER TABLE fprt1_p2 SET (autovacuum_enabled = 'false');
  INSERT INTO fprt1_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 2) i;
  INSERT INTO fprt1_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 2) i;
  CREATE FOREIGN TABLE ftprt1_p1 PARTITION OF fprt1 FOR VALUES FROM (0) TO (250)
***************
*** 7697,7702 **** ANALYZE fprt1_p2;
--- 7709,7716 ----
  CREATE TABLE fprt2 (a int, b int, c varchar) PARTITION BY RANGE(b);
  CREATE TABLE fprt2_p1 (LIKE fprt2);
  CREATE TABLE fprt2_p2 (LIKE fprt2);
+ ALTER TABLE fprt2_p1 SET (autovacuum_enabled = 'false');
+ ALTER TABLE fprt2_p2 SET (autovacuum_enabled = 'false');
  INSERT INTO fprt2_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 3) i;
  INSERT INTO fprt2_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 3) i;
  CREATE FOREIGN TABLE ftprt2_p1 PARTITION OF fprt2 FOR VALUES FROM (0) TO (250)
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 39,47 **** CREATE TABLE "S 1"."T 1" (
  	c8 user_enum,
  	CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
  );
- -- "S 1"."T 1" will be heavily updated below, so disable autovacuum for
- -- the table to avoid unexpected effects of that
- ALTER TABLE "S 1"."T 1" SET (autovacuum_enabled = 'false');
  CREATE TABLE "S 1"."T 2" (
  	c1 int NOT NULL,
  	c2 text,
--- 39,44 ----
***************
*** 60,65 **** CREATE TABLE "S 1"."T 4" (
--- 57,68 ----
  	CONSTRAINT t4_pkey PRIMARY KEY (c1)
  );
  
+ -- Disable autovacuum for these tables to avoid unexpected effects of that
+ ALTER TABLE "S 1"."T 1" SET (autovacuum_enabled = 'false');
+ ALTER TABLE "S 1"."T 2" SET (autovacuum_enabled = 'false');
+ ALTER TABLE "S 1"."T 3" SET (autovacuum_enabled = 'false');
+ ALTER TABLE "S 1"."T 4" SET (autovacuum_enabled = 'false');
+ 
  INSERT INTO "S 1"."T 1"
  	SELECT id,
  	       id % 10,
***************
*** 1260,1265 **** ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative;
--- 1263,1269 ----
  -- ===================================================================
  
  CREATE TABLE base_tbl (a int, b int);
+ ALTER TABLE base_tbl SET (autovacuum_enabled = 'false');
  CREATE FOREIGN TABLE foreign_tbl (a int, b int)
    SERVER loopback OPTIONS(table_name 'base_tbl');
  CREATE VIEW rw_view AS SELECT * FROM foreign_tbl
***************
*** 1283,1288 **** DROP TABLE base_tbl;
--- 1287,1293 ----
  -- test serial columns (ie, sequence-based defaults)
  -- ===================================================================
  create table loc1 (f1 serial, f2 text);
+ alter table loc1 set (autovacuum_enabled = 'false');
  create foreign table rem1 (f1 serial, f2 text)
    server loopback options(table_name 'loc1');
  select pg_catalog.setval('rem1_f1_seq', 10, false);
***************
*** 1599,1604 **** DROP TRIGGER trig_row_after_delete ON rem1;
--- 1604,1611 ----
  
  CREATE TABLE a (aa TEXT);
  CREATE TABLE loct (aa TEXT, bb TEXT);
+ ALTER TABLE a SET (autovacuum_enabled = 'false');
+ ALTER TABLE loct SET (autovacuum_enabled = 'false');
  CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a)
    SERVER loopback OPTIONS (table_name 'loct');
  
***************
*** 1645,1650 **** DROP TABLE loct;
--- 1652,1660 ----
  create table loct1 (f1 int, f2 int, f3 int);
  create table loct2 (f1 int, f2 int, f3 int);
  
+ alter table loct1 set (autovacuum_enabled = 'false');
+ alter table loct2 set (autovacuum_enabled = 'false');
+ 
  create table foo (f1 int, f2 int);
  create foreign table foo2 (f3 int) inherits (foo)
    server loopback options (table_name 'loct1');
***************
*** 1652,1657 **** create table bar (f1 int, f2 int);
--- 1662,1670 ----
  create foreign table bar2 (f3 int) inherits (bar)
    server loopback options (table_name 'loct2');
  
+ alter table foo set (autovacuum_enabled = 'false');
+ alter table bar set (autovacuum_enabled = 'false');
+ 
  insert into foo values(1,1);
  insert into foo values(3,3);
  insert into foo2 values(2,2,2);
***************
*** 1866,1871 **** SET enable_partitionwise_join=on;
--- 1879,1886 ----
  CREATE TABLE fprt1 (a int, b int, c varchar) PARTITION BY RANGE(a);
  CREATE TABLE fprt1_p1 (LIKE fprt1);
  CREATE TABLE fprt1_p2 (LIKE fprt1);
+ ALTER TABLE fprt1_p1 SET (autovacuum_enabled = 'false');
+ ALTER TABLE fprt1_p2 SET (autovacuum_enabled = 'false');
  INSERT INTO fprt1_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 2) i;
  INSERT INTO fprt1_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 2) i;
  CREATE FOREIGN TABLE ftprt1_p1 PARTITION OF fprt1 FOR VALUES FROM (0) TO (250)
***************
*** 1879,1884 **** ANALYZE fprt1_p2;
--- 1894,1901 ----
  CREATE TABLE fprt2 (a int, b int, c varchar) PARTITION BY RANGE(b);
  CREATE TABLE fprt2_p1 (LIKE fprt2);
  CREATE TABLE fprt2_p2 (LIKE fprt2);
+ ALTER TABLE fprt2_p1 SET (autovacuum_enabled = 'false');
+ ALTER TABLE fprt2_p2 SET (autovacuum_enabled = 'false');
  INSERT INTO fprt2_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 3) i;
  INSERT INTO fprt2_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 3) i;
  CREATE FOREIGN TABLE ftprt2_p1 PARTITION OF fprt2 FOR VALUES FROM (0) TO (250)

--------------000405070906010803010409--