aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/test/regress/expected/merge.out91
-rw-r--r--src/test/regress/sql/merge.sql93
2 files changed, 110 insertions, 74 deletions
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
index 729ae2eb065..4047c3e761e 100644
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -1,18 +1,16 @@
--
-- MERGE
--
---\set VERBOSITY verbose
---set debug_print_rewritten = true;
---set debug_print_parse = true;
---set debug_print_pretty = true;
CREATE USER regress_merge_privs;
CREATE USER regress_merge_no_privs;
DROP TABLE IF EXISTS target;
NOTICE: table "target" does not exist, skipping
DROP TABLE IF EXISTS source;
NOTICE: table "source" does not exist, skipping
-CREATE TABLE target (tid integer, balance integer);
-CREATE TABLE source (sid integer, delta integer); --no index
+CREATE TABLE target (tid integer, balance integer)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE source (sid integer, delta integer) -- no index
+ WITH (autovacuum_enabled=off);
INSERT INTO target VALUES (1, 10);
INSERT INTO target VALUES (2, 20);
INSERT INTO target VALUES (3, 30);
@@ -26,8 +24,10 @@ SELECT t.ctid is not null as matched, t.*, s.* FROM source s FULL OUTER JOIN tar
ALTER TABLE target OWNER TO regress_merge_privs;
ALTER TABLE source OWNER TO regress_merge_privs;
-CREATE TABLE target2 (tid integer, balance integer);
-CREATE TABLE source2 (sid integer, delta integer);
+CREATE TABLE target2 (tid integer, balance integer)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE source2 (sid integer, delta integer)
+ WITH (autovacuum_enabled=off);
ALTER TABLE target2 OWNER TO regress_merge_no_privs;
ALTER TABLE source2 OWNER TO regress_merge_no_privs;
GRANT INSERT ON target TO regress_merge_no_privs;
@@ -664,8 +664,10 @@ WHEN MATCHED AND s.delta > 0 THEN
ERROR: unreachable WHEN clause specified after unconditional WHEN clause
ROLLBACK;
-- conditional WHEN clause
-CREATE TABLE wq_target (tid integer not null, balance integer DEFAULT -1);
-CREATE TABLE wq_source (balance integer, sid integer);
+CREATE TABLE wq_target (tid integer not null, balance integer DEFAULT -1)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE wq_source (balance integer, sid integer)
+ WITH (autovacuum_enabled=off);
INSERT INTO wq_source (sid, balance) VALUES (1, 100);
BEGIN;
-- try a simple INSERT with default values first
@@ -1212,8 +1214,10 @@ SELECT * FROM target ORDER BY tid;
ROLLBACK;
-- subqueries in source relation
-CREATE TABLE sq_target (tid integer NOT NULL, balance integer);
-CREATE TABLE sq_source (delta integer, sid integer, balance integer DEFAULT 0);
+CREATE TABLE sq_target (tid integer NOT NULL, balance integer)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE sq_source (delta integer, sid integer, balance integer DEFAULT 0)
+ WITH (autovacuum_enabled=off);
INSERT INTO sq_target(tid, balance) VALUES (1,100), (2,200), (3,300);
INSERT INTO sq_source(sid, delta) VALUES (1,10), (2,20), (4,40);
BEGIN;
@@ -1317,8 +1321,10 @@ LINE 10: RETURNING *;
^
ROLLBACK;
-- EXPLAIN
-CREATE TABLE ex_mtarget (a int, b int);
-CREATE TABLE ex_msource (a int, b int);
+CREATE TABLE ex_mtarget (a int, b int)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE ex_msource (a int, b int)
+ WITH (autovacuum_enabled=off);
INSERT INTO ex_mtarget SELECT i, i*10 FROM generate_series(1,100,2) i;
INSERT INTO ex_msource SELECT i, i*10 FROM generate_series(1,100,1) i;
CREATE FUNCTION explain_merge(query text) RETURNS SETOF text
@@ -1510,10 +1516,14 @@ DROP TABLE sq_target, sq_source CASCADE;
NOTICE: drop cascades to view v
CREATE TABLE pa_target (tid integer, balance float, val text)
PARTITION BY LIST (tid);
-CREATE TABLE part1 PARTITION OF pa_target FOR VALUES IN (1,4);
-CREATE TABLE part2 PARTITION OF pa_target FOR VALUES IN (2,5,6);
-CREATE TABLE part3 PARTITION OF pa_target FOR VALUES IN (3,8,9);
-CREATE TABLE part4 PARTITION OF pa_target DEFAULT;
+CREATE TABLE part1 PARTITION OF pa_target FOR VALUES IN (1,4)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE part2 PARTITION OF pa_target FOR VALUES IN (2,5,6)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE part3 PARTITION OF pa_target FOR VALUES IN (3,8,9)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE part4 PARTITION OF pa_target DEFAULT
+ WITH (autovacuum_enabled=off);
CREATE TABLE pa_source (sid integer, delta float);
-- insert many rows to the source table
INSERT INTO pa_source SELECT id, id * 10 FROM generate_series(1,14) AS id;
@@ -1617,10 +1627,14 @@ DROP TABLE pa_target CASCADE;
-- partitions which have columns in different order, dropped columns etc.
CREATE TABLE pa_target (tid integer, balance float, val text)
PARTITION BY LIST (tid);
-CREATE TABLE part1 (tid integer, balance float, val text);
-CREATE TABLE part2 (balance float, tid integer, val text);
-CREATE TABLE part3 (tid integer, balance float, val text);
-CREATE TABLE part4 (extraid text, tid integer, balance float, val text);
+CREATE TABLE part1 (tid integer, balance float, val text)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE part2 (balance float, tid integer, val text)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE part3 (tid integer, balance float, val text)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE part4 (extraid text, tid integer, balance float, val text)
+ WITH (autovacuum_enabled=off);
ALTER TABLE part4 DROP COLUMN extraid;
ALTER TABLE pa_target ATTACH PARTITION part1 FOR VALUES IN (1,4);
ALTER TABLE pa_target ATTACH PARTITION part2 FOR VALUES IN (2,5,6);
@@ -1729,17 +1743,18 @@ CREATE TABLE part_m01 PARTITION OF pa_target
FOR VALUES FROM ('2017-01-01') TO ('2017-02-01')
PARTITION BY LIST (tid);
CREATE TABLE part_m01_odd PARTITION OF part_m01
- FOR VALUES IN (1,3,5,7,9);
+ FOR VALUES IN (1,3,5,7,9) WITH (autovacuum_enabled=off);
CREATE TABLE part_m01_even PARTITION OF part_m01
- FOR VALUES IN (2,4,6,8);
+ FOR VALUES IN (2,4,6,8) WITH (autovacuum_enabled=off);
CREATE TABLE part_m02 PARTITION OF pa_target
FOR VALUES FROM ('2017-02-01') TO ('2017-03-01')
PARTITION BY LIST (tid);
CREATE TABLE part_m02_odd PARTITION OF part_m02
- FOR VALUES IN (1,3,5,7,9);
+ FOR VALUES IN (1,3,5,7,9) WITH (autovacuum_enabled=off);
CREATE TABLE part_m02_even PARTITION OF part_m02
- FOR VALUES IN (2,4,6,8);
-CREATE TABLE pa_source (sid integer, delta float);
+ FOR VALUES IN (2,4,6,8) WITH (autovacuum_enabled=off);
+CREATE TABLE pa_source (sid integer, delta float)
+ WITH (autovacuum_enabled=off);
-- insert many rows to the source table
INSERT INTO pa_source SELECT id, id * 10 FROM generate_series(1,14) AS id;
-- insert a few rows in the target table (odd numbered tid)
@@ -1772,9 +1787,12 @@ ROLLBACK;
DROP TABLE pa_source;
DROP TABLE pa_target CASCADE;
-- some complex joins on the source side
-CREATE TABLE cj_target (tid integer, balance float, val text);
-CREATE TABLE cj_source1 (sid1 integer, scat integer, delta integer);
-CREATE TABLE cj_source2 (sid2 integer, sval text);
+CREATE TABLE cj_target (tid integer, balance float, val text)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE cj_source1 (sid1 integer, scat integer, delta integer)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE cj_source2 (sid2 integer, sval text)
+ WITH (autovacuum_enabled=off);
INSERT INTO cj_source1 VALUES (1, 10, 100);
INSERT INTO cj_source1 VALUES (1, 20, 200);
INSERT INTO cj_source1 VALUES (2, 20, 300);
@@ -1833,7 +1851,8 @@ WHEN NOT MATCHED THEN
INSERT VALUES (s2.sid, delta, sval);
DROP TABLE cj_source2, cj_source1, cj_target;
-- Function scans
-CREATE TABLE fs_target (a int, b int, c text);
+CREATE TABLE fs_target (a int, b int, c text)
+ WITH (autovacuum_enabled=off);
MERGE INTO fs_target t
USING generate_series(1,100,1) AS id
ON t.a = id
@@ -1863,13 +1882,13 @@ CREATE TABLE measurement (
logdate date not null,
peaktemp int,
unitsales int
-);
+) WITH (autovacuum_enabled=off);
CREATE TABLE measurement_y2006m02 (
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
-) INHERITS (measurement);
+) INHERITS (measurement) WITH (autovacuum_enabled=off);
CREATE TABLE measurement_y2006m03 (
CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
-) INHERITS (measurement);
+) INHERITS (measurement) WITH (autovacuum_enabled=off);
CREATE TABLE measurement_y2007m01 (
filler text,
peaktemp int,
@@ -1877,7 +1896,7 @@ CREATE TABLE measurement_y2007m01 (
city_id int not null,
unitsales int
CHECK ( logdate >= DATE '2007-01-01' AND logdate < DATE '2007-02-01')
-);
+) WITH (autovacuum_enabled=off);
ALTER TABLE measurement_y2007m01 DROP COLUMN filler;
ALTER TABLE measurement_y2007m01 INHERIT measurement;
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
@@ -1919,7 +1938,7 @@ SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate;
measurement_y2007m01 | 1 | 01-17-2007 | 10 | 10
(6 rows)
-CREATE TABLE new_measurement (LIKE measurement);
+CREATE TABLE new_measurement (LIKE measurement) WITH (autovacuum_enabled=off);
INSERT INTO new_measurement VALUES (1, '2006-03-01', 20, 10);
INSERT INTO new_measurement VALUES (1, '2006-02-16', 50, 10);
INSERT INTO new_measurement VALUES (2, '2006-02-10', 20, 20);
diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql
index e0c450736bd..4d486b4433d 100644
--- a/src/test/regress/sql/merge.sql
+++ b/src/test/regress/sql/merge.sql
@@ -1,19 +1,15 @@
--
-- MERGE
--
---\set VERBOSITY verbose
-
---set debug_print_rewritten = true;
---set debug_print_parse = true;
---set debug_print_pretty = true;
-
CREATE USER regress_merge_privs;
CREATE USER regress_merge_no_privs;
DROP TABLE IF EXISTS target;
DROP TABLE IF EXISTS source;
-CREATE TABLE target (tid integer, balance integer);
-CREATE TABLE source (sid integer, delta integer); --no index
+CREATE TABLE target (tid integer, balance integer)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE source (sid integer, delta integer) -- no index
+ WITH (autovacuum_enabled=off);
INSERT INTO target VALUES (1, 10);
INSERT INTO target VALUES (2, 20);
INSERT INTO target VALUES (3, 30);
@@ -22,8 +18,10 @@ SELECT t.ctid is not null as matched, t.*, s.* FROM source s FULL OUTER JOIN tar
ALTER TABLE target OWNER TO regress_merge_privs;
ALTER TABLE source OWNER TO regress_merge_privs;
-CREATE TABLE target2 (tid integer, balance integer);
-CREATE TABLE source2 (sid integer, delta integer);
+CREATE TABLE target2 (tid integer, balance integer)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE source2 (sid integer, delta integer)
+ WITH (autovacuum_enabled=off);
ALTER TABLE target2 OWNER TO regress_merge_no_privs;
ALTER TABLE source2 OWNER TO regress_merge_no_privs;
@@ -445,8 +443,10 @@ WHEN MATCHED AND s.delta > 0 THEN
ROLLBACK;
-- conditional WHEN clause
-CREATE TABLE wq_target (tid integer not null, balance integer DEFAULT -1);
-CREATE TABLE wq_source (balance integer, sid integer);
+CREATE TABLE wq_target (tid integer not null, balance integer DEFAULT -1)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE wq_source (balance integer, sid integer)
+ WITH (autovacuum_enabled=off);
INSERT INTO wq_source (sid, balance) VALUES (1, 100);
@@ -787,8 +787,10 @@ ROLLBACK;
-- subqueries in source relation
-CREATE TABLE sq_target (tid integer NOT NULL, balance integer);
-CREATE TABLE sq_source (delta integer, sid integer, balance integer DEFAULT 0);
+CREATE TABLE sq_target (tid integer NOT NULL, balance integer)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE sq_source (delta integer, sid integer, balance integer DEFAULT 0)
+ WITH (autovacuum_enabled=off);
INSERT INTO sq_target(tid, balance) VALUES (1,100), (2,200), (3,300);
INSERT INTO sq_source(sid, delta) VALUES (1,10), (2,20), (4,40);
@@ -874,8 +876,10 @@ RETURNING *;
ROLLBACK;
-- EXPLAIN
-CREATE TABLE ex_mtarget (a int, b int);
-CREATE TABLE ex_msource (a int, b int);
+CREATE TABLE ex_mtarget (a int, b int)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE ex_msource (a int, b int)
+ WITH (autovacuum_enabled=off);
INSERT INTO ex_mtarget SELECT i, i*10 FROM generate_series(1,100,2) i;
INSERT INTO ex_msource SELECT i, i*10 FROM generate_series(1,100,1) i;
@@ -972,10 +976,14 @@ DROP TABLE sq_target, sq_source CASCADE;
CREATE TABLE pa_target (tid integer, balance float, val text)
PARTITION BY LIST (tid);
-CREATE TABLE part1 PARTITION OF pa_target FOR VALUES IN (1,4);
-CREATE TABLE part2 PARTITION OF pa_target FOR VALUES IN (2,5,6);
-CREATE TABLE part3 PARTITION OF pa_target FOR VALUES IN (3,8,9);
-CREATE TABLE part4 PARTITION OF pa_target DEFAULT;
+CREATE TABLE part1 PARTITION OF pa_target FOR VALUES IN (1,4)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE part2 PARTITION OF pa_target FOR VALUES IN (2,5,6)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE part3 PARTITION OF pa_target FOR VALUES IN (3,8,9)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE part4 PARTITION OF pa_target DEFAULT
+ WITH (autovacuum_enabled=off);
CREATE TABLE pa_source (sid integer, delta float);
-- insert many rows to the source table
@@ -1026,10 +1034,14 @@ DROP TABLE pa_target CASCADE;
CREATE TABLE pa_target (tid integer, balance float, val text)
PARTITION BY LIST (tid);
-CREATE TABLE part1 (tid integer, balance float, val text);
-CREATE TABLE part2 (balance float, tid integer, val text);
-CREATE TABLE part3 (tid integer, balance float, val text);
-CREATE TABLE part4 (extraid text, tid integer, balance float, val text);
+CREATE TABLE part1 (tid integer, balance float, val text)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE part2 (balance float, tid integer, val text)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE part3 (tid integer, balance float, val text)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE part4 (extraid text, tid integer, balance float, val text)
+ WITH (autovacuum_enabled=off);
ALTER TABLE part4 DROP COLUMN extraid;
ALTER TABLE pa_target ATTACH PARTITION part1 FOR VALUES IN (1,4);
@@ -1088,18 +1100,19 @@ CREATE TABLE part_m01 PARTITION OF pa_target
FOR VALUES FROM ('2017-01-01') TO ('2017-02-01')
PARTITION BY LIST (tid);
CREATE TABLE part_m01_odd PARTITION OF part_m01
- FOR VALUES IN (1,3,5,7,9);
+ FOR VALUES IN (1,3,5,7,9) WITH (autovacuum_enabled=off);
CREATE TABLE part_m01_even PARTITION OF part_m01
- FOR VALUES IN (2,4,6,8);
+ FOR VALUES IN (2,4,6,8) WITH (autovacuum_enabled=off);
CREATE TABLE part_m02 PARTITION OF pa_target
FOR VALUES FROM ('2017-02-01') TO ('2017-03-01')
PARTITION BY LIST (tid);
CREATE TABLE part_m02_odd PARTITION OF part_m02
- FOR VALUES IN (1,3,5,7,9);
+ FOR VALUES IN (1,3,5,7,9) WITH (autovacuum_enabled=off);
CREATE TABLE part_m02_even PARTITION OF part_m02
- FOR VALUES IN (2,4,6,8);
+ FOR VALUES IN (2,4,6,8) WITH (autovacuum_enabled=off);
-CREATE TABLE pa_source (sid integer, delta float);
+CREATE TABLE pa_source (sid integer, delta float)
+ WITH (autovacuum_enabled=off);
-- insert many rows to the source table
INSERT INTO pa_source SELECT id, id * 10 FROM generate_series(1,14) AS id;
-- insert a few rows in the target table (odd numbered tid)
@@ -1123,9 +1136,12 @@ DROP TABLE pa_target CASCADE;
-- some complex joins on the source side
-CREATE TABLE cj_target (tid integer, balance float, val text);
-CREATE TABLE cj_source1 (sid1 integer, scat integer, delta integer);
-CREATE TABLE cj_source2 (sid2 integer, sval text);
+CREATE TABLE cj_target (tid integer, balance float, val text)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE cj_source1 (sid1 integer, scat integer, delta integer)
+ WITH (autovacuum_enabled=off);
+CREATE TABLE cj_source2 (sid2 integer, sval text)
+ WITH (autovacuum_enabled=off);
INSERT INTO cj_source1 VALUES (1, 10, 100);
INSERT INTO cj_source1 VALUES (1, 20, 200);
INSERT INTO cj_source1 VALUES (2, 20, 300);
@@ -1186,7 +1202,8 @@ WHEN NOT MATCHED THEN
DROP TABLE cj_source2, cj_source1, cj_target;
-- Function scans
-CREATE TABLE fs_target (a int, b int, c text);
+CREATE TABLE fs_target (a int, b int, c text)
+ WITH (autovacuum_enabled=off);
MERGE INTO fs_target t
USING generate_series(1,100,1) AS id
ON t.a = id
@@ -1215,13 +1232,13 @@ CREATE TABLE measurement (
logdate date not null,
peaktemp int,
unitsales int
-);
+) WITH (autovacuum_enabled=off);
CREATE TABLE measurement_y2006m02 (
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
-) INHERITS (measurement);
+) INHERITS (measurement) WITH (autovacuum_enabled=off);
CREATE TABLE measurement_y2006m03 (
CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
-) INHERITS (measurement);
+) INHERITS (measurement) WITH (autovacuum_enabled=off);
CREATE TABLE measurement_y2007m01 (
filler text,
peaktemp int,
@@ -1229,7 +1246,7 @@ CREATE TABLE measurement_y2007m01 (
city_id int not null,
unitsales int
CHECK ( logdate >= DATE '2007-01-01' AND logdate < DATE '2007-02-01')
-);
+) WITH (autovacuum_enabled=off);
ALTER TABLE measurement_y2007m01 DROP COLUMN filler;
ALTER TABLE measurement_y2007m01 INHERIT measurement;
@@ -1264,7 +1281,7 @@ INSERT INTO measurement VALUES (1, '2007-01-17', 10, 10);
SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate;
-CREATE TABLE new_measurement (LIKE measurement);
+CREATE TABLE new_measurement (LIKE measurement) WITH (autovacuum_enabled=off);
INSERT INTO new_measurement VALUES (1, '2006-03-01', 20, 10);
INSERT INTO new_measurement VALUES (1, '2006-02-16', 50, 10);
INSERT INTO new_measurement VALUES (2, '2006-02-10', 20, 20);
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-03 09:42:41 +0000