(2018/03/02 3:34), Tom Lane wrote:
Robert Haas<robertmh...@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
*** 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)
