From 1733460f0205fc6d6bbe4c14911049a918c6e073 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Fri, 2 Mar 2018 13:16:01 -0500 Subject: [PATCH] postgres_fdw: Fourth attempt to stabilize regression tests. Commit 1bc0100d270e5bcc980a0629b8726a32a497e788 added this test, and commits 882ea509fe7a4711fe25463427a33262b873dfa1, 958e20e42d6c346ab89f6c72e4262230161d1663, 4fa396464e5fe238b7994535182f28318c61c78e tried to stabilize it. It's still not stable, so keep trying. The latest comment from Tom Lane is that disabling autovacuum seems like a good strategy, but we might need to do it on more tables, hence this patch. Etsuro Fujita Discussion: http://postgr.es/m/5A9928F1.2010206@lab.ntt.co.jp --- .../postgres_fdw/expected/postgres_fdw.out | 20 +++++++++++++--- contrib/postgres_fdw/sql/postgres_fdw.sql | 23 ++++++++++++++++--- 2 files changed, 37 insertions(+), 6 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 08b30f83e0..a2b13846e0 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -35,9 +35,6 @@ 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, @@ -55,6 +52,11 @@ CREATE TABLE "S 1"."T 4" ( 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,6 +6174,7 @@ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative; -- 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,6 +6235,7 @@ DROP TABLE base_tbl; -- 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,6 +6783,8 @@ DROP TRIGGER trig_row_after_delete ON rem1; -- =================================================================== 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,12 +6926,16 @@ DROP TABLE loct; -- 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,6 +7695,8 @@ SET enable_partitionwise_join=on; 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,6 +7709,8 @@ ANALYZE fprt1_p2; 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) diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 7f4d0dab25..4d2e43c9f0 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -39,9 +39,6 @@ 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, @@ -60,6 +57,12 @@ CREATE TABLE "S 1"."T 4" ( 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,6 +1263,7 @@ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative; -- =================================================================== 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,6 +1287,7 @@ DROP TABLE base_tbl; -- 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,6 +1604,8 @@ DROP TRIGGER trig_row_after_delete ON rem1; 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,6 +1652,9 @@ DROP TABLE loct; 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,6 +1662,9 @@ 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); @@ -1866,6 +1879,8 @@ SET enable_partitionwise_join=on; 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,6 +1894,8 @@ ANALYZE fprt1_p2; 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) -- 2.40.0