From: Alexander Lakhin <exclusion@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17257: (auto)vacuum hangs within lazy_scan_prune()
Date: Fri, 29 Oct 2021 16:30:00 +0300
Message-ID: <d5d5af5d-ba46-aff3-9f91-776c70246cc3@gmail.com> (raw)
In-Reply-To: <17257-1e46de26bec11433@postgresql.org>
References: <17257-1e46de26bec11433@postgresql.org>
29.10.2021 16:00, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17257
> Logged by: Alexander Lakhin
> Email address: exclusion@gmail.com
> PostgreSQL version: 14.0
> Operating system: Ubuntu 20.04
> Description:
>
> When running concurrent installchecks (x100) I've observed the autovacuum
> process hanging:
>
I can propose the debugging patch to reproduce the issue that replaces
the hang with the assert and modifies a pair of crash-causing test
scripts to simplify the reproducing. (Sorry, I have no time now to prune
down the scripts further as I have to leave for a week.)
The reproducing script is:
###
export PGDATABASE=regression
createdb regression
echo "
vacuum (verbose, skip_locked, index_cleanup off) pg_catalog.pg_class;
select pg_sleep(random()/50);
" >/tmp/pseudo-autovacuum.sql
( timeout 10m bash -c "while true; do psql -f
src/test/regress/sql/inherit.sql >>psql1.log 2>&1; coredumpctl
--no-pager >/dev/null 2>&1 && break; done" ) &
( timeout 10m bash -c "while true; do psql -f
src/test/regress/sql/vacuum.sql >>psql2.log 2>&1; coredumpctl --no-pager
>/dev/null 2>&1 && break; done" ) &
pgbench -n -f /tmp/pseudo-autovacuum.sql -C -c 40 -T 600 >pgbench.log 2>&1 &
wait
###
(I've set:
autovacuum=off
fsync=off
in postgresql.conf)
It leads to:
TIME PID UID GID SIG COREFILE EXE
Fri 2021-10-29 16:15:59 MSK 2337121 1000 1000 6 present
.../usr/local/pgsql/bin/postgres
real 2m19,425s
user 4m26,078s
sys 0m31,658s
Core was generated by `postgres: law regression [local]
VACUUM '.
Program terminated with signal SIGABRT, Aborted.
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
50 ../sysdeps/unix/sysv/linux/raise.c: No such file or directory.
(gdb) bt
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1 0x00007f3d17e88859 in __GI_abort () at abort.c:79
#2 0x0000557358fbecb8 in ExceptionalCondition
(conditionName=conditionName@entry=0x557359027544 "numretries < 100",
errorType=errorType@entry=0x55735901800b "FailedAssertion",
fileName=0x7ffef5bbfa70 "\231\354\373XsU",
fileName@entry=0x557359027520 "vacuumlazy.c",
lineNumber=lineNumber@entry=1726) at assert.c:69
#3 0x0000557358ba0bdb in lazy_scan_prune (vacrel=0x55735a508d60,
buf=16184, blkno=270, page=0x7f3d16ef6f80 "",
vistest=0x5573592dd540 <GlobalVisCatalogRels>,
prunestate=0x7ffef5bc0fb0) at vacuumlazy.c:1823
#4 0x0000557358ba38c5 in lazy_scan_heap (aggressive=false,
params=0x7ffef5bc13a0, vacrel=<optimized out>)
at vacuumlazy.c:1384
#5 heap_vacuum_rel (rel=0x7f3d183ebec8, params=0x7ffef5bc13a0,
bstrategy=<optimized out>) at vacuumlazy.c:638
#6 0x0000557358cec785 in table_relation_vacuum (bstrategy=<optimized
out>, params=0x7ffef5bc13a0, rel=0x7f3d183ebec8)
at ../../../src/include/access/tableam.h:1678
#7 vacuum_rel (relid=1259, relation=<optimized out>,
params=params@entry=0x7ffef5bc13a0) at vacuum.c:2034
#8 0x0000557358cedf9e in vacuum (relations=0x55735a57bea8,
params=0x7ffef5bc13a0, bstrategy=<optimized out>,
isTopLevel=<optimized out>) at vacuum.c:475
#9 0x0000557358cee4f5 in ExecVacuum
(pstate=pstate@entry=0x55735a4ab730, vacstmt=vacstmt@entry=0x55735a48a408,
isTopLevel=isTopLevel@entry=true) at vacuum.c:268
#10 0x0000557358e9de4e in standard_ProcessUtility (pstmt=0x55735a48a758,
queryString=0x55735a4895e0 "vacuum (verbose, skip_locked,
index_cleanup off) pg_catalog.pg_class;",
readOnlyTree=<optimized out>, context=PROCESS_UTILITY_TOPLEVEL,
params=0x0, queryEnv=0x0, dest=0x55735a48a848,
qc=0x7ffef5bc1700) at utility.c:858
#11 0x0000557358e9c411 in PortalRunUtility
(portal=portal@entry=0x55735a4ed040, pstmt=pstmt@entry=0x55735a48a758,
isTopLevel=isTopLevel@entry=true,
setHoldSnapshot=setHoldSnapshot@entry=false,
dest=dest@entry=0x55735a48a848,
qc=qc@entry=0x7ffef5bc1700) at pquery.c:1155
#12 0x0000557358e9c54d in PortalRunMulti
(portal=portal@entry=0x55735a4ed040, isTopLevel=isTopLevel@entry=true,
setHoldSnapshot=setHoldSnapshot@entry=false,
dest=dest@entry=0x55735a48a848, altdest=altdest@entry=0x55735a48a848,
qc=qc@entry=0x7ffef5bc1700) at pquery.c:1312
#13 0x0000557358e9cbe9 in PortalRun (portal=portal@entry=0x55735a4ed040,
count=count@entry=9223372036854775807,
isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true,
dest=dest@entry=0x55735a48a848,
altdest=altdest@entry=0x55735a48a848, qc=0x7ffef5bc1700) at pquery.c:788
#14 0x0000557358e9895b in exec_simple_query (
query_string=0x55735a4895e0 "vacuum (verbose, skip_locked,
index_cleanup off) pg_catalog.pg_class;")
at postgres.c:1214
#15 0x0000557358e9a561 in PostgresMain (argc=argc@entry=1,
argv=argv@entry=0x7ffef5bc1b70, dbname=<optimized out>,
username=<optimized out>) at postgres.c:4486
#16 0x0000557358e064dd in BackendRun (port=0x55735a4aeb00,
port=0x55735a4aeb00) at postmaster.c:4506
#17 BackendStartup (port=0x55735a4aeb00) at postmaster.c:4228
#18 ServerLoop () at postmaster.c:1745
#19 0x0000557358e07481 in PostmasterMain (argc=<optimized out>,
argv=<optimized out>) at postmaster.c:1417
#20 0x0000557358b352e2 in main (argc=3, argv=0x55735a4838a0) at main.c:209
Sometimes the server crashes other way as described in bug #17255.
And I've also seen the following crash (maybe it's yet another bug, but
I can't explore it now):
Core was generated by `postgres: law regression [local]
EXPLAIN '.
Program terminated with signal SIGSEGV, Segmentation fault.
#0 RelationBuildPartitionDesc (omit_detached=true, rel=0x7f09b87a27e8)
at partdesc.c:230
230 datum = heap_getattr(tuple,
Anum_pg_class_relpartbound,
(gdb) bt
#0 RelationBuildPartitionDesc (omit_detached=true, rel=0x7f09b87a27e8)
at partdesc.c:230
#1 RelationGetPartitionDesc (rel=0x7f09b87a27e8,
omit_detached=<optimized out>) at partdesc.c:110
#2 0x000055ec12169032 in PartitionDirectoryLookup (pdir=0x55ec12ee7660,
rel=rel@entry=0x7f09b87a27e8)
at partdesc.c:425
#3 0x000055ec12158b35 in set_relation_partition_info
(relation=0x7f09b87a27e8, rel=0x55ec12f337c0,
root=0x55ec12effae0) at plancat.c:2197
#4 get_relation_info (root=<optimized out>, relationObjectId=<optimized
out>, inhparent=<optimized out>,
rel=<optimized out>) at plancat.c:472
#5 0x000055ec1215d26f in build_simple_rel
(root=root@entry=0x55ec12effae0, relid=5,
parent=parent@entry=0x55ec12ee7330) at relnode.c:307
#6 0x000055ec1214f495 in expand_partitioned_rtentry
(root=0x55ec12effae0, relinfo=0x55ec12ee7330,
parentrte=0x55ec12e006d0, parentRTindex=1, parentrel=0x7f09b87a0630,
top_parentrc=0x0, lockmode=1) at inherit.c:398
#7 0x000055ec1214f67e in expand_inherited_rtentry
(root=root@entry=0x55ec12effae0, rel=0x55ec12ee7330,
rte=0x55ec12e006d0, rti=rti@entry=1) at inherit.c:143
#8 0x000055ec1212eb8d in add_other_rels_to_query
(root=root@entry=0x55ec12effae0) at initsplan.c:163
#9 0x000055ec12131e3f in query_planner (root=root@entry=0x55ec12effae0,
qp_callback=qp_callback@entry=0x55ec12132b20 <standard_qp_callback>,
qp_extra=qp_extra@entry=0x7ffcffe4e890)
at planmain.c:264
#10 0x000055ec121371b7 in grouping_planner (root=<optimized out>,
tuple_fraction=<optimized out>) at planner.c:1442
#11 0x000055ec12139d0e in subquery_planner
(glob=glob@entry=0x55ec12ef3eb8, parse=parse@entry=0x55ec12e005b8,
parent_root=parent_root@entry=0x0,
hasRecursion=hasRecursion@entry=false,
tuple_fraction=tuple_fraction@entry=0)
at planner.c:1019
#12 0x000055ec1213a383 in standard_planner (parse=0x55ec12e005b8,
query_string=<optimized out>, cursorOptions=2048,
boundParams=<optimized out>) at planner.c:400
#13 0x000055ec1220d7f8 in pg_plan_query (querytree=0x55ec12e005b8,
querytree@entry=0x7ffcffe4ead0,
query_string=query_string@entry=0x55ec12dff520 "explain (costs off)
select * from range_list_parted;",
cursorOptions=cursorOptions@entry=2048,
boundParams=boundParams@entry=0x0) at postgres.c:847
#14 0x000055ec12025fef in ExplainOneQuery (query=0x7ffcffe4ead0,
cursorOptions=2048, into=0x0, es=0x55ec12ef3bd0,
queryString=0x55ec12dff520 "explain (costs off) select * from
range_list_parted;", params=0x0, queryEnv=0x0)
at explain.c:397
#15 0x000055ec1202679f in ExplainQuery (pstate=0x55ec12ef3a70,
stmt=0x55ec12e003d8, params=0x0, dest=0x55ec12ef39d8)
at ../../../src/include/nodes/nodes.h:604
#16 0x000055ec122132c9 in standard_ProcessUtility (pstmt=0x55ec12e01058,
queryString=0x55ec12dff520 "explain (costs off) select * from
range_list_parted;", readOnlyTree=<optimized out>,
context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0,
dest=0x55ec12ef39d8, qc=0x7ffcffe4ed60)
at utility.c:862
#17 0x000055ec122116cf in PortalRunUtility
(portal=portal@entry=0x55ec12e63060, pstmt=0x55ec12e01058,
isTopLevel=<optimized out>,
setHoldSnapshot=setHoldSnapshot@entry=true, dest=dest@entry=0x55ec12ef39d8,
qc=qc@entry=0x7ffcffe4ed60) at pquery.c:1155
#18 0x000055ec12211bb0 in FillPortalStore (portal=0x55ec12e63060,
isTopLevel=<optimized out>)
at ../../../src/include/nodes/nodes.h:604
#19 0x000055ec12211ebd in PortalRun (portal=portal@entry=0x55ec12e63060,
count=count@entry=9223372036854775807,
isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true,
dest=dest@entry=0x55ec12e01148,
altdest=altdest@entry=0x55ec12e01148, qc=0x7ffcffe4ef60) at pquery.c:760
#20 0x000055ec1220dcf5 in exec_simple_query (
query_string=0x55ec12dff520 "explain (costs off) select * from
range_list_parted;") at postgres.c:1214
#21 0x000055ec1220f886 in PostgresMain (argc=argc@entry=1,
argv=argv@entry=0x7ffcffe4f4b0, dbname=<optimized out>,
username=<optimized out>) at postgres.c:4486
#22 0x000055ec1218071a in BackendRun (port=<optimized out>,
port=<optimized out>) at postmaster.c:4506
#23 BackendStartup (port=<optimized out>) at postmaster.c:4228
#24 ServerLoop () at postmaster.c:1745
#25 0x000055ec12181682 in PostmasterMain (argc=argc@entry=3,
argv=argv@entry=0x55ec12df9ce0) at postmaster.c:1417
#26 0x000055ec11eca3a0 in main (argc=3, argv=0x55ec12df9ce0) at main.c:209
Best regards,
Alexander
Attachments:vacuum-hang-demo.patch (text/x-patch, 28.5K) downloaddiff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.cindex 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.sqlindex 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.sqlindex 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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: exclusion@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17257: (auto)vacuum hangs within lazy_scan_prune()
In-Reply-To: <d5d5af5d-ba46-aff3-9f91-776c70246cc3@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox