1
1
--
2
2
-- MERGE
3
3
--
4
- --\set VERBOSITY verbose
5
- --set debug_print_rewritten = true;
6
- --set debug_print_parse = true;
7
- --set debug_print_pretty = true;
8
4
CREATE USER regress_merge_privs;
9
5
CREATE USER regress_merge_no_privs;
10
6
DROP TABLE IF EXISTS target;
11
7
NOTICE: table "target" does not exist, skipping
12
8
DROP TABLE IF EXISTS source;
13
9
NOTICE: table "source" does not exist, skipping
14
- CREATE TABLE target (tid integer, balance integer);
15
- CREATE TABLE source (sid integer, delta integer); --no index
10
+ CREATE TABLE target (tid integer, balance integer)
11
+ WITH (autovacuum_enabled=off);
12
+ CREATE TABLE source (sid integer, delta integer) -- no index
13
+ WITH (autovacuum_enabled=off);
16
14
INSERT INTO target VALUES (1, 10);
17
15
INSERT INTO target VALUES (2, 20);
18
16
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
26
24
27
25
ALTER TABLE target OWNER TO regress_merge_privs;
28
26
ALTER TABLE source OWNER TO regress_merge_privs;
29
- CREATE TABLE target2 (tid integer, balance integer);
30
- CREATE TABLE source2 (sid integer, delta integer);
27
+ CREATE TABLE target2 (tid integer, balance integer)
28
+ WITH (autovacuum_enabled=off);
29
+ CREATE TABLE source2 (sid integer, delta integer)
30
+ WITH (autovacuum_enabled=off);
31
31
ALTER TABLE target2 OWNER TO regress_merge_no_privs;
32
32
ALTER TABLE source2 OWNER TO regress_merge_no_privs;
33
33
GRANT INSERT ON target TO regress_merge_no_privs;
@@ -664,8 +664,10 @@ WHEN MATCHED AND s.delta > 0 THEN
664
664
ERROR: unreachable WHEN clause specified after unconditional WHEN clause
665
665
ROLLBACK;
666
666
-- conditional WHEN clause
667
- CREATE TABLE wq_target (tid integer not null, balance integer DEFAULT -1);
668
- CREATE TABLE wq_source (balance integer, sid integer);
667
+ CREATE TABLE wq_target (tid integer not null, balance integer DEFAULT -1)
668
+ WITH (autovacuum_enabled=off);
669
+ CREATE TABLE wq_source (balance integer, sid integer)
670
+ WITH (autovacuum_enabled=off);
669
671
INSERT INTO wq_source (sid, balance) VALUES (1, 100);
670
672
BEGIN;
671
673
-- try a simple INSERT with default values first
@@ -1212,8 +1214,10 @@ SELECT * FROM target ORDER BY tid;
1212
1214
1213
1215
ROLLBACK;
1214
1216
-- subqueries in source relation
1215
- CREATE TABLE sq_target (tid integer NOT NULL, balance integer);
1216
- CREATE TABLE sq_source (delta integer, sid integer, balance integer DEFAULT 0);
1217
+ CREATE TABLE sq_target (tid integer NOT NULL, balance integer)
1218
+ WITH (autovacuum_enabled=off);
1219
+ CREATE TABLE sq_source (delta integer, sid integer, balance integer DEFAULT 0)
1220
+ WITH (autovacuum_enabled=off);
1217
1221
INSERT INTO sq_target(tid, balance) VALUES (1,100), (2,200), (3,300);
1218
1222
INSERT INTO sq_source(sid, delta) VALUES (1,10), (2,20), (4,40);
1219
1223
BEGIN;
@@ -1317,8 +1321,10 @@ LINE 10: RETURNING *;
1317
1321
^
1318
1322
ROLLBACK;
1319
1323
-- EXPLAIN
1320
- CREATE TABLE ex_mtarget (a int, b int);
1321
- CREATE TABLE ex_msource (a int, b int);
1324
+ CREATE TABLE ex_mtarget (a int, b int)
1325
+ WITH (autovacuum_enabled=off);
1326
+ CREATE TABLE ex_msource (a int, b int)
1327
+ WITH (autovacuum_enabled=off);
1322
1328
INSERT INTO ex_mtarget SELECT i, i*10 FROM generate_series(1,100,2) i;
1323
1329
INSERT INTO ex_msource SELECT i, i*10 FROM generate_series(1,100,1) i;
1324
1330
CREATE FUNCTION explain_merge(query text) RETURNS SETOF text
@@ -1510,10 +1516,14 @@ DROP TABLE sq_target, sq_source CASCADE;
1510
1516
NOTICE: drop cascades to view v
1511
1517
CREATE TABLE pa_target (tid integer, balance float, val text)
1512
1518
PARTITION BY LIST (tid);
1513
- CREATE TABLE part1 PARTITION OF pa_target FOR VALUES IN (1,4);
1514
- CREATE TABLE part2 PARTITION OF pa_target FOR VALUES IN (2,5,6);
1515
- CREATE TABLE part3 PARTITION OF pa_target FOR VALUES IN (3,8,9);
1516
- CREATE TABLE part4 PARTITION OF pa_target DEFAULT;
1519
+ CREATE TABLE part1 PARTITION OF pa_target FOR VALUES IN (1,4)
1520
+ WITH (autovacuum_enabled=off);
1521
+ CREATE TABLE part2 PARTITION OF pa_target FOR VALUES IN (2,5,6)
1522
+ WITH (autovacuum_enabled=off);
1523
+ CREATE TABLE part3 PARTITION OF pa_target FOR VALUES IN (3,8,9)
1524
+ WITH (autovacuum_enabled=off);
1525
+ CREATE TABLE part4 PARTITION OF pa_target DEFAULT
1526
+ WITH (autovacuum_enabled=off);
1517
1527
CREATE TABLE pa_source (sid integer, delta float);
1518
1528
-- insert many rows to the source table
1519
1529
INSERT INTO pa_source SELECT id, id * 10 FROM generate_series(1,14) AS id;
@@ -1617,10 +1627,14 @@ DROP TABLE pa_target CASCADE;
1617
1627
-- partitions which have columns in different order, dropped columns etc.
1618
1628
CREATE TABLE pa_target (tid integer, balance float, val text)
1619
1629
PARTITION BY LIST (tid);
1620
- CREATE TABLE part1 (tid integer, balance float, val text);
1621
- CREATE TABLE part2 (balance float, tid integer, val text);
1622
- CREATE TABLE part3 (tid integer, balance float, val text);
1623
- CREATE TABLE part4 (extraid text, tid integer, balance float, val text);
1630
+ CREATE TABLE part1 (tid integer, balance float, val text)
1631
+ WITH (autovacuum_enabled=off);
1632
+ CREATE TABLE part2 (balance float, tid integer, val text)
1633
+ WITH (autovacuum_enabled=off);
1634
+ CREATE TABLE part3 (tid integer, balance float, val text)
1635
+ WITH (autovacuum_enabled=off);
1636
+ CREATE TABLE part4 (extraid text, tid integer, balance float, val text)
1637
+ WITH (autovacuum_enabled=off);
1624
1638
ALTER TABLE part4 DROP COLUMN extraid;
1625
1639
ALTER TABLE pa_target ATTACH PARTITION part1 FOR VALUES IN (1,4);
1626
1640
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
1729
1743
FOR VALUES FROM ('2017-01-01') TO ('2017-02-01')
1730
1744
PARTITION BY LIST (tid);
1731
1745
CREATE TABLE part_m01_odd PARTITION OF part_m01
1732
- FOR VALUES IN (1,3,5,7,9);
1746
+ FOR VALUES IN (1,3,5,7,9) WITH (autovacuum_enabled=off) ;
1733
1747
CREATE TABLE part_m01_even PARTITION OF part_m01
1734
- FOR VALUES IN (2,4,6,8);
1748
+ FOR VALUES IN (2,4,6,8) WITH (autovacuum_enabled=off) ;
1735
1749
CREATE TABLE part_m02 PARTITION OF pa_target
1736
1750
FOR VALUES FROM ('2017-02-01') TO ('2017-03-01')
1737
1751
PARTITION BY LIST (tid);
1738
1752
CREATE TABLE part_m02_odd PARTITION OF part_m02
1739
- FOR VALUES IN (1,3,5,7,9);
1753
+ FOR VALUES IN (1,3,5,7,9) WITH (autovacuum_enabled=off) ;
1740
1754
CREATE TABLE part_m02_even PARTITION OF part_m02
1741
- FOR VALUES IN (2,4,6,8);
1742
- CREATE TABLE pa_source (sid integer, delta float);
1755
+ FOR VALUES IN (2,4,6,8) WITH (autovacuum_enabled=off);
1756
+ CREATE TABLE pa_source (sid integer, delta float)
1757
+ WITH (autovacuum_enabled=off);
1743
1758
-- insert many rows to the source table
1744
1759
INSERT INTO pa_source SELECT id, id * 10 FROM generate_series(1,14) AS id;
1745
1760
-- insert a few rows in the target table (odd numbered tid)
@@ -1772,9 +1787,12 @@ ROLLBACK;
1772
1787
DROP TABLE pa_source;
1773
1788
DROP TABLE pa_target CASCADE;
1774
1789
-- some complex joins on the source side
1775
- CREATE TABLE cj_target (tid integer, balance float, val text);
1776
- CREATE TABLE cj_source1 (sid1 integer, scat integer, delta integer);
1777
- CREATE TABLE cj_source2 (sid2 integer, sval text);
1790
+ CREATE TABLE cj_target (tid integer, balance float, val text)
1791
+ WITH (autovacuum_enabled=off);
1792
+ CREATE TABLE cj_source1 (sid1 integer, scat integer, delta integer)
1793
+ WITH (autovacuum_enabled=off);
1794
+ CREATE TABLE cj_source2 (sid2 integer, sval text)
1795
+ WITH (autovacuum_enabled=off);
1778
1796
INSERT INTO cj_source1 VALUES (1, 10, 100);
1779
1797
INSERT INTO cj_source1 VALUES (1, 20, 200);
1780
1798
INSERT INTO cj_source1 VALUES (2, 20, 300);
@@ -1833,7 +1851,8 @@ WHEN NOT MATCHED THEN
1833
1851
INSERT VALUES (s2.sid, delta, sval);
1834
1852
DROP TABLE cj_source2, cj_source1, cj_target;
1835
1853
-- Function scans
1836
- CREATE TABLE fs_target (a int, b int, c text);
1854
+ CREATE TABLE fs_target (a int, b int, c text)
1855
+ WITH (autovacuum_enabled=off);
1837
1856
MERGE INTO fs_target t
1838
1857
USING generate_series(1,100,1) AS id
1839
1858
ON t.a = id
@@ -1863,21 +1882,21 @@ CREATE TABLE measurement (
1863
1882
logdate date not null,
1864
1883
peaktemp int,
1865
1884
unitsales int
1866
- );
1885
+ ) WITH (autovacuum_enabled=off) ;
1867
1886
CREATE TABLE measurement_y2006m02 (
1868
1887
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
1869
- ) INHERITS (measurement);
1888
+ ) INHERITS (measurement) WITH (autovacuum_enabled=off) ;
1870
1889
CREATE TABLE measurement_y2006m03 (
1871
1890
CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
1872
- ) INHERITS (measurement);
1891
+ ) INHERITS (measurement) WITH (autovacuum_enabled=off) ;
1873
1892
CREATE TABLE measurement_y2007m01 (
1874
1893
filler text,
1875
1894
peaktemp int,
1876
1895
logdate date not null,
1877
1896
city_id int not null,
1878
1897
unitsales int
1879
1898
CHECK ( logdate >= DATE '2007-01-01' AND logdate < DATE '2007-02-01')
1880
- );
1899
+ ) WITH (autovacuum_enabled=off) ;
1881
1900
ALTER TABLE measurement_y2007m01 DROP COLUMN filler;
1882
1901
ALTER TABLE measurement_y2007m01 INHERIT measurement;
1883
1902
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
@@ -1919,7 +1938,7 @@ SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate;
1919
1938
measurement_y2007m01 | 1 | 01-17-2007 | 10 | 10
1920
1939
(6 rows)
1921
1940
1922
- CREATE TABLE new_measurement (LIKE measurement);
1941
+ CREATE TABLE new_measurement (LIKE measurement) WITH (autovacuum_enabled=off) ;
1923
1942
INSERT INTO new_measurement VALUES (1, '2006-03-01', 20, 10);
1924
1943
INSERT INTO new_measurement VALUES (1, '2006-02-16', 50, 10);
1925
1944
INSERT INTO new_measurement VALUES (2, '2006-02-10', 20, 20);
0 commit comments