diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index d2c735eedae..79cec947f4d 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -1717,10 +1717,13 @@ lazy_scan_prune(LVRelState *vacrel, int nfrozen; OffsetNumber deadoffsets[MaxHeapTuplesPerPage]; xl_heap_freeze_tuple frozen[MaxHeapTuplesPerPage]; +int numretries = 0; maxoff = PageGetMaxOffsetNumber(page); retry: +numretries++; +Assert(numretries < 100); /* Initialize (or reset) page-level counters */ tuples_deleted = 0; diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index 64173a8738c..165427d9d7e 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -1,497 +1,5 @@ --- --- Test inheritance features --- -CREATE TABLE a (aa TEXT); -CREATE TABLE b (bb TEXT) INHERITS (a); -CREATE TABLE c (cc TEXT) INHERITS (a); -CREATE TABLE d (dd TEXT) INHERITS (b,c,a); - -INSERT INTO a(aa) VALUES('aaa'); -INSERT INTO a(aa) VALUES('aaaa'); -INSERT INTO a(aa) VALUES('aaaaa'); -INSERT INTO a(aa) VALUES('aaaaaa'); -INSERT INTO a(aa) VALUES('aaaaaaa'); -INSERT INTO a(aa) VALUES('aaaaaaaa'); - -INSERT INTO b(aa) VALUES('bbb'); -INSERT INTO b(aa) VALUES('bbbb'); -INSERT INTO b(aa) VALUES('bbbbb'); -INSERT INTO b(aa) VALUES('bbbbbb'); -INSERT INTO b(aa) VALUES('bbbbbbb'); -INSERT INTO b(aa) VALUES('bbbbbbbb'); - -INSERT INTO c(aa) VALUES('ccc'); -INSERT INTO c(aa) VALUES('cccc'); -INSERT INTO c(aa) VALUES('ccccc'); -INSERT INTO c(aa) VALUES('cccccc'); -INSERT INTO c(aa) VALUES('ccccccc'); -INSERT INTO c(aa) VALUES('cccccccc'); - -INSERT INTO d(aa) VALUES('ddd'); -INSERT INTO d(aa) VALUES('dddd'); -INSERT INTO d(aa) VALUES('ddddd'); -INSERT INTO d(aa) VALUES('dddddd'); -INSERT INTO d(aa) VALUES('ddddddd'); -INSERT INTO d(aa) VALUES('dddddddd'); - -SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; -SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; -SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; -SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; -SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; -SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; -SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; -SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; - -UPDATE a SET aa='zzzz' WHERE aa='aaaa'; -UPDATE ONLY a SET aa='zzzzz' WHERE aa='aaaaa'; -UPDATE b SET aa='zzz' WHERE aa='aaa'; -UPDATE ONLY b SET aa='zzz' WHERE aa='aaa'; -UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%'; - -SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; -SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; -SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; -SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; -SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; -SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; -SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; -SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; - -UPDATE b SET aa='new'; - -SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; -SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; -SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; -SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; -SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; -SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; -SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; -SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; - -UPDATE a SET aa='new'; - -DELETE FROM ONLY c WHERE aa='new'; - -SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; -SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; -SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; -SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; -SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; -SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; -SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; -SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; - -DELETE FROM a; - -SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; -SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; -SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; -SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; -SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; -SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; -SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; -SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; - --- Confirm PRIMARY KEY adds NOT NULL constraint to child table -CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a); -INSERT INTO z VALUES (NULL, 'text'); -- should fail - --- Check inherited UPDATE with all children excluded -create table some_tab (a int, b int); -create table some_tab_child () inherits (some_tab); -insert into some_tab_child values(1,2); - -explain (verbose, costs off) -update some_tab set a = a + 1 where false; -update some_tab set a = a + 1 where false; -explain (verbose, costs off) -update some_tab set a = a + 1 where false returning b, a; -update some_tab set a = a + 1 where false returning b, a; -table some_tab; - -drop table some_tab cascade; - --- Check UPDATE with inherited target and an inherited source table -create temp table foo(f1 int, f2 int); -create temp table foo2(f3 int) inherits (foo); -create temp table bar(f1 int, f2 int); -create temp table bar2(f3 int) inherits (bar); - -insert into foo values(1,1); -insert into foo values(3,3); -insert into foo2 values(2,2,2); -insert into foo2 values(3,3,3); -insert into bar values(1,1); -insert into bar values(2,2); -insert into bar values(3,3); -insert into bar values(4,4); -insert into bar2 values(1,1,1); -insert into bar2 values(2,2,2); -insert into bar2 values(3,3,3); -insert into bar2 values(4,4,4); - -update bar set f2 = f2 + 100 where f1 in (select f1 from foo); - -select tableoid::regclass::text as relname, bar.* from bar order by 1,2; - --- Check UPDATE with inherited target and an appendrel subquery -update bar set f2 = f2 + 100 -from - ( select f1 from foo union all select f1+3 from foo ) ss -where bar.f1 = ss.f1; - -select tableoid::regclass::text as relname, bar.* from bar order by 1,2; - --- Check UPDATE with *partitioned* inherited target and an appendrel subquery -create table some_tab (a int); -insert into some_tab values (0); -create table some_tab_child () inherits (some_tab); -insert into some_tab_child values (1); -create table parted_tab (a int, b char) partition by list (a); -create table parted_tab_part1 partition of parted_tab for values in (1); -create table parted_tab_part2 partition of parted_tab for values in (2); -create table parted_tab_part3 partition of parted_tab for values in (3); -insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a'); - -update parted_tab set b = 'b' -from - (select a from some_tab union all select a+1 from some_tab) ss (a) -where parted_tab.a = ss.a; -select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2; - -truncate parted_tab; -insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a'); -update parted_tab set b = 'b' -from - (select 0 from parted_tab union all select 1 from parted_tab) ss (a) -where parted_tab.a = ss.a; -select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2; - --- modifies partition key, but no rows will actually be updated -explain update parted_tab set a = 2 where false; - -drop table parted_tab; - --- Check UPDATE with multi-level partitioned inherited target -create table mlparted_tab (a int, b char, c text) partition by list (a); -create table mlparted_tab_part1 partition of mlparted_tab for values in (1); -create table mlparted_tab_part2 partition of mlparted_tab for values in (2) partition by list (b); -create table mlparted_tab_part3 partition of mlparted_tab for values in (3); -create table mlparted_tab_part2a partition of mlparted_tab_part2 for values in ('a'); -create table mlparted_tab_part2b partition of mlparted_tab_part2 for values in ('b'); -insert into mlparted_tab values (1, 'a'), (2, 'a'), (2, 'b'), (3, 'a'); - -update mlparted_tab mlp set c = 'xxx' -from - (select a from some_tab union all select a+1 from some_tab) ss (a) -where (mlp.a = ss.a and mlp.b = 'b') or mlp.a = 3; -select tableoid::regclass::text as relname, mlparted_tab.* from mlparted_tab order by 1,2; - -drop table mlparted_tab; -drop table some_tab cascade; - -/* Test multiple inheritance of column defaults */ - -CREATE TABLE firstparent (tomorrow date default now()::date + 1); -CREATE TABLE secondparent (tomorrow date default now() :: date + 1); -CREATE TABLE jointchild () INHERITS (firstparent, secondparent); -- ok -CREATE TABLE thirdparent (tomorrow date default now()::date - 1); -CREATE TABLE otherchild () INHERITS (firstparent, thirdparent); -- not ok -CREATE TABLE otherchild (tomorrow date default now()) - INHERITS (firstparent, thirdparent); -- ok, child resolves ambiguous default - -DROP TABLE firstparent, secondparent, jointchild, thirdparent, otherchild; - --- Test changing the type of inherited columns -insert into d values('test','one','two','three'); -alter table a alter column aa type integer using bit_length(aa); -select * from d; - --- The above verified that we can change the type of a multiply-inherited --- column; but we should reject that if any definition was inherited from --- an unrelated parent. -create temp table parent1(f1 int, f2 int); -create temp table parent2(f1 int, f3 bigint); -create temp table childtab(f4 int) inherits(parent1, parent2); -alter table parent1 alter column f1 type bigint; -- fail, conflict w/parent2 -alter table parent1 alter column f2 type bigint; -- ok - --- Test non-inheritable parent constraints -create table p1(ff1 int); -alter table p1 add constraint p1chk check (ff1 > 0) no inherit; -alter table p1 add constraint p2chk check (ff1 > 10); --- connoinherit should be true for NO INHERIT constraint -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname = 'p1' order by 1,2; - --- Test that child does not inherit NO INHERIT constraints -create table c1 () inherits (p1); -\d p1 -\d c1 - --- Test that child does not override inheritable constraints of the parent -create table c2 (constraint p2chk check (ff1 > 10) no inherit) inherits (p1); --fails - -drop table p1 cascade; - --- Tests for casting between the rowtypes of parent and child --- tables. See the pgsql-hackers thread beginning Dec. 4/04 -create table base (i integer); -create table derived () inherits (base); -create table more_derived (like derived, b int) inherits (derived); -insert into derived (i) values (0); -select derived::base from derived; -select NULL::derived::base; --- remove redundant conversions. -explain (verbose on, costs off) select row(i, b)::more_derived::derived::base from more_derived; -explain (verbose on, costs off) select (1, 2)::more_derived::derived::base; -drop table more_derived; -drop table derived; -drop table base; - -create table p1(ff1 int); -create table p2(f1 text); -create function p2text(p2) returns text as 'select $1.f1' language sql; -create table c1(f3 int) inherits(p1,p2); -insert into c1 values(123456789, 'hi', 42); -select p2text(c1.*) from c1; -drop function p2text(p2); -drop table c1; -drop table p2; -drop table p1; - -CREATE TABLE ac (aa TEXT); -alter table ac add constraint ac_check check (aa is not null); -CREATE TABLE bc (bb TEXT) INHERITS (ac); -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; - -insert into ac (aa) values (NULL); -insert into bc (aa) values (NULL); - -alter table bc drop constraint ac_check; -- fail, disallowed -alter table ac drop constraint ac_check; -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; - --- try the unnamed-constraint case -alter table ac add check (aa is not null); -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; - -insert into ac (aa) values (NULL); -insert into bc (aa) values (NULL); - -alter table bc drop constraint ac_aa_check; -- fail, disallowed -alter table ac drop constraint ac_aa_check; -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; - -alter table ac add constraint ac_check check (aa is not null); -alter table bc no inherit ac; -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; -alter table bc drop constraint ac_check; -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; -alter table ac drop constraint ac_check; -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; - -drop table bc; -drop table ac; - -create table ac (a int constraint check_a check (a <> 0)); -create table bc (a int constraint check_a check (a <> 0), b int constraint check_b check (b <> 0)) inherits (ac); -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; - -drop table bc; -drop table ac; - -create table ac (a int constraint check_a check (a <> 0)); -create table bc (b int constraint check_b check (b <> 0)); -create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc); -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2; - -alter table cc no inherit bc; -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2; - -drop table cc; -drop table bc; -drop table ac; - -create table p1(f1 int); -create table p2(f2 int); -create table c1(f3 int) inherits(p1,p2); -insert into c1 values(1,-1,2); -alter table p2 add constraint cc check (f2>0); -- fail -alter table p2 add check (f2>0); -- check it without a name, too -delete from c1; -insert into c1 values(1,1,2); -alter table p2 add check (f2>0); -insert into c1 values(1,-1,2); -- fail -create table c2(f3 int) inherits(p1,p2); -\d c2 -create table c3 (f4 int) inherits(c1,c2); -\d c3 -drop table p1 cascade; -drop table p2 cascade; - -create table pp1 (f1 int); -create table cc1 (f2 text, f3 int) inherits (pp1); -alter table pp1 add column a1 int check (a1 > 0); -\d cc1 -create table cc2(f4 float) inherits(pp1,cc1); -\d cc2 -alter table pp1 add column a2 int check (a2 > 0); -\d cc2 -drop table pp1 cascade; - --- Test for renaming in simple multiple inheritance -CREATE TABLE inht1 (a int, b int); -CREATE TABLE inhs1 (b int, c int); -CREATE TABLE inhts (d int) INHERITS (inht1, inhs1); - -ALTER TABLE inht1 RENAME a TO aa; -ALTER TABLE inht1 RENAME b TO bb; -- to be failed -ALTER TABLE inhts RENAME aa TO aaa; -- to be failed -ALTER TABLE inhts RENAME d TO dd; -\d+ inhts - -DROP TABLE inhts; - --- Test for renaming in diamond inheritance -CREATE TABLE inht2 (x int) INHERITS (inht1); -CREATE TABLE inht3 (y int) INHERITS (inht1); -CREATE TABLE inht4 (z int) INHERITS (inht2, inht3); - -ALTER TABLE inht1 RENAME aa TO aaa; -\d+ inht4 - -CREATE TABLE inhts (d int) INHERITS (inht2, inhs1); -ALTER TABLE inht1 RENAME aaa TO aaaa; -ALTER TABLE inht1 RENAME b TO bb; -- to be failed -\d+ inhts - -WITH RECURSIVE r AS ( - SELECT 'inht1'::regclass AS inhrelid -UNION ALL - SELECT c.inhrelid FROM pg_inherits c, r WHERE r.inhrelid = c.inhparent -) -SELECT a.attrelid::regclass, a.attname, a.attinhcount, e.expected - FROM (SELECT inhrelid, count(*) AS expected FROM pg_inherits - WHERE inhparent IN (SELECT inhrelid FROM r) GROUP BY inhrelid) e - JOIN pg_attribute a ON e.inhrelid = a.attrelid WHERE NOT attislocal - ORDER BY a.attrelid::regclass::name, a.attnum; - -DROP TABLE inht1, inhs1 CASCADE; - - --- Test non-inheritable indices [UNIQUE, EXCLUDE] constraints -CREATE TABLE test_constraints (id int, val1 varchar, val2 int, UNIQUE(val1, val2)); -CREATE TABLE test_constraints_inh () INHERITS (test_constraints); -\d+ test_constraints -ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key; -\d+ test_constraints -\d+ test_constraints_inh -DROP TABLE test_constraints_inh; -DROP TABLE test_constraints; - -CREATE TABLE test_ex_constraints ( - c circle, - EXCLUDE USING gist (c WITH &&) -); -CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints); -\d+ test_ex_constraints -ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl; -\d+ test_ex_constraints -\d+ test_ex_constraints_inh -DROP TABLE test_ex_constraints_inh; -DROP TABLE test_ex_constraints; - --- Test non-inheritable foreign key constraints -CREATE TABLE test_primary_constraints(id int PRIMARY KEY); -CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id)); -CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints); -\d+ test_primary_constraints -\d+ test_foreign_constraints -ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey; -\d+ test_foreign_constraints -\d+ test_foreign_constraints_inh -DROP TABLE test_foreign_constraints_inh; -DROP TABLE test_foreign_constraints; -DROP TABLE test_primary_constraints; - --- Test foreign key behavior -create table inh_fk_1 (a int primary key); -insert into inh_fk_1 values (1), (2), (3); -create table inh_fk_2 (x int primary key, y int references inh_fk_1 on delete cascade); -insert into inh_fk_2 values (11, 1), (22, 2), (33, 3); -create table inh_fk_2_child () inherits (inh_fk_2); -insert into inh_fk_2_child values (111, 1), (222, 2); -delete from inh_fk_1 where a = 1; -select * from inh_fk_1 order by 1; -select * from inh_fk_2 order by 1, 2; -drop table inh_fk_1, inh_fk_2, inh_fk_2_child; - --- Test that parent and child CHECK constraints can be created in either order -create table p1(f1 int); -create table p1_c1() inherits(p1); - -alter table p1 add constraint inh_check_constraint1 check (f1 > 0); -alter table p1_c1 add constraint inh_check_constraint1 check (f1 > 0); - -alter table p1_c1 add constraint inh_check_constraint2 check (f1 < 10); -alter table p1 add constraint inh_check_constraint2 check (f1 < 10); - -select conrelid::regclass::text as relname, conname, conislocal, coninhcount -from pg_constraint where conname like 'inh\_check\_constraint%' -order by 1, 2; - -drop table p1 cascade; - --- Test that a valid child can have not-valid parent, but not vice versa -create table invalid_check_con(f1 int); -create table invalid_check_con_child() inherits(invalid_check_con); - -alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0) not valid; -alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0); -- fail -alter table invalid_check_con_child drop constraint inh_check_constraint; - -insert into invalid_check_con values(0); - -alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0); -alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0) not valid; - -insert into invalid_check_con values(0); -- fail -insert into invalid_check_con_child values(0); -- fail - -select conrelid::regclass::text as relname, conname, - convalidated, conislocal, coninhcount, connoinherit -from pg_constraint where conname like 'inh\_check\_constraint%' -order by 1, 2; - --- We don't drop the invalid_check_con* tables, to test dump/reload with - --- --- Test parameterized append plans for inheritance trees --- +SELECT pg_sleep(random()/10); -create temp table patest0 (id, x) as - select x, x from generate_series(0,1000) x; -create temp table patest1() inherits (patest0); -insert into patest1 - select x, x from generate_series(0,1000) x; -create temp table patest2() inherits (patest0); -insert into patest2 - select x, x from generate_series(0,1000) x; -create index patest0i on patest0(id); -create index patest1i on patest1(id); -create index patest2i on patest2(id); -analyze patest0; -analyze patest1; -analyze patest2; - -explain (costs off) -select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; -select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; - -drop index patest2i; - -explain (costs off) select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql index 18cb7fd08ac..e9232356b6e 100644 --- a/src/test/regress/sql/vacuum.sql +++ b/src/test/regress/sql/vacuum.sql @@ -3,78 +3,11 @@ -- CREATE TABLE vactst (i INT); -INSERT INTO vactst VALUES (1); -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst VALUES (0); -SELECT count(*) FROM vactst; -DELETE FROM vactst WHERE i != 0; -SELECT * FROM vactst; -VACUUM FULL vactst; -UPDATE vactst SET i = i + 1; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst SELECT * FROM vactst; -INSERT INTO vactst VALUES (0); -SELECT count(*) FROM vactst; -DELETE FROM vactst WHERE i != 0; -VACUUM (FULL) vactst; -DELETE FROM vactst; -SELECT * FROM vactst; - -VACUUM (FULL, FREEZE) vactst; -VACUUM (ANALYZE, FULL) vactst; CREATE TABLE vaccluster (i INT PRIMARY KEY); ALTER TABLE vaccluster CLUSTER ON vaccluster_pkey; CLUSTER vaccluster; -CREATE FUNCTION do_analyze() RETURNS VOID VOLATILE LANGUAGE SQL - AS 'ANALYZE pg_am'; -CREATE FUNCTION wrap_do_analyze(c INT) RETURNS INT IMMUTABLE LANGUAGE SQL - AS 'SELECT $1 FROM do_analyze()'; -CREATE INDEX ON vaccluster(wrap_do_analyze(i)); -INSERT INTO vaccluster VALUES (1), (2); -ANALYZE vaccluster; - --- Test ANALYZE in transaction, where the transaction surrounding --- analyze performed modifications. This tests for the bug at --- https://postgr.es/m/c7988239-d42c-ddc4-41db-171b23b35e4f%40ssinger.info --- (which hopefully is unlikely to be reintroduced), but also seems --- independently worthwhile to cover. -INSERT INTO vactst SELECT generate_series(1, 300); -DELETE FROM vactst WHERE i % 7 = 0; -- delete a few rows outside -BEGIN; -INSERT INTO vactst SELECT generate_series(301, 400); -DELETE FROM vactst WHERE i % 5 <> 0; -- delete a few rows inside -ANALYZE vactst; -COMMIT; - -VACUUM FULL pg_am; -VACUUM FULL pg_class; -VACUUM FULL pg_database; -VACUUM FULL vaccluster; -VACUUM FULL vactst; - -VACUUM (DISABLE_PAGE_SKIPPING) vaccluster; - -- PARALLEL option CREATE TABLE pvactst (i INT, a INT[], p POINT) with (autovacuum_enabled = off); INSERT INTO pvactst SELECT i, array[1,2,3], point(i, i+1) FROM generate_series(1,1000) i; @@ -144,97 +77,7 @@ VACUUM no_index_cleanup; -- Test some extra relations. VACUUM (INDEX_CLEANUP FALSE) vaccluster; VACUUM (INDEX_CLEANUP AUTO) vactst; -- index cleanup option is ignored if no indexes -VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster; - --- TRUNCATE option -CREATE TABLE vac_truncate_test(i INT NOT NULL, j text) - WITH (vacuum_truncate=true, autovacuum_enabled=false); -INSERT INTO vac_truncate_test VALUES (1, NULL), (NULL, NULL); -VACUUM (TRUNCATE FALSE) vac_truncate_test; -SELECT pg_relation_size('vac_truncate_test') > 0; -VACUUM vac_truncate_test; -SELECT pg_relation_size('vac_truncate_test') = 0; -VACUUM (TRUNCATE FALSE, FULL TRUE) vac_truncate_test; -DROP TABLE vac_truncate_test; - --- partitioned table -CREATE TABLE vacparted (a int, b char) PARTITION BY LIST (a); -CREATE TABLE vacparted1 PARTITION OF vacparted FOR VALUES IN (1); -INSERT INTO vacparted VALUES (1, 'a'); -UPDATE vacparted SET b = 'b'; -VACUUM (ANALYZE) vacparted; -VACUUM (FULL) vacparted; -VACUUM (FREEZE) vacparted; - --- check behavior with duplicate column mentions -VACUUM ANALYZE vacparted(a,b,a); -ANALYZE vacparted(a,b,b); - --- partitioned table with index -CREATE TABLE vacparted_i (a int primary key, b varchar(100)) - PARTITION BY HASH (a); -CREATE TABLE vacparted_i1 PARTITION OF vacparted_i - FOR VALUES WITH (MODULUS 2, REMAINDER 0); -CREATE TABLE vacparted_i2 PARTITION OF vacparted_i - FOR VALUES WITH (MODULUS 2, REMAINDER 1); -INSERT INTO vacparted_i SELECT i, 'test_'|| i from generate_series(1,10) i; -VACUUM (ANALYZE) vacparted_i; -VACUUM (FULL) vacparted_i; -VACUUM (FREEZE) vacparted_i; -SELECT relname, relhasindex FROM pg_class - WHERE relname LIKE 'vacparted_i%' AND relkind IN ('p','r') - ORDER BY relname; -DROP TABLE vacparted_i; - --- multiple tables specified -VACUUM vaccluster, vactst; -VACUUM vacparted, does_not_exist; -VACUUM (FREEZE) vacparted, vaccluster, vactst; -VACUUM (FREEZE) does_not_exist, vaccluster; -VACUUM ANALYZE vactst, vacparted (a); -VACUUM ANALYZE vactst (does_not_exist), vacparted (b); -VACUUM FULL vacparted, vactst; -VACUUM FULL vactst, vacparted (a, b), vaccluster (i); -ANALYZE vactst, vacparted; -ANALYZE vacparted (b), vactst; -ANALYZE vactst, does_not_exist, vacparted; -ANALYZE vactst (i), vacparted (does_not_exist); -ANALYZE vactst, vactst; -BEGIN; -- ANALYZE behaves differently inside a transaction block -ANALYZE vactst, vactst; -COMMIT; - --- parenthesized syntax for ANALYZE -ANALYZE (VERBOSE) does_not_exist; -ANALYZE (nonexistent-arg) does_not_exist; -ANALYZE (nonexistentarg) does_not_exit; - --- ensure argument order independence, and that SKIP_LOCKED on non-existing --- relation still errors out. Suppress WARNING messages caused by concurrent --- autovacuums. -SET client_min_messages TO 'ERROR'; -ANALYZE (SKIP_LOCKED, VERBOSE) does_not_exist; -ANALYZE (VERBOSE, SKIP_LOCKED) does_not_exist; - --- SKIP_LOCKED option -VACUUM (SKIP_LOCKED) vactst; -VACUUM (SKIP_LOCKED, FULL) vactst; -ANALYZE (SKIP_LOCKED) vactst; -RESET client_min_messages; - --- ensure VACUUM and ANALYZE don't have a problem with serializable -SET default_transaction_isolation = serializable; -VACUUM vactst; -ANALYZE vactst; -RESET default_transaction_isolation; -BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; -ANALYZE vactst; -COMMIT; - --- PROCESS_TOAST option -ALTER TABLE vactst ADD COLUMN t TEXT; -ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL; -VACUUM (PROCESS_TOAST FALSE) vactst; +SELECT pg_sleep(random()/2); VACUUM (PROCESS_TOAST FALSE, FULL) vactst; DROP TABLE vaccluster;