From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Cc: Rushabh Lathia <rushabh.lathia@gmail.com>
Cc: Michael Paquier <michael.paquier@gmail.com>
Cc: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
Subject: Re: postgres_fdw: perform UPDATE/DELETE .. RETURNING on a join directly
Date: Fri, 02 Mar 2018 19:35:29 +0900
Message-ID: <5A9928F1.2010206@lab.ntt.co.jp> (raw)
In-Reply-To: <23783.1519929258@sss.pgh.pa.us>
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>
(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
Attachments:postgres-fdw-regress-3.patch (text/x-diff, 8.6K) download
*** 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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: fujita.etsuro@lab.ntt.co.jp, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, rushabh.lathia@gmail.com, michael.paquier@gmail.com, ashutosh.bapat@enterprisedb.com
Subject: Re: postgres_fdw: perform UPDATE/DELETE .. RETURNING on a join directly
In-Reply-To: <5A9928F1.2010206@lab.ntt.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox