Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ma1mq-00022o-Ln for pgsql-hackers@arkaria.postgresql.org; Mon, 11 Oct 2021 20:20:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ma1mn-0005mw-Jq for pgsql-hackers@arkaria.postgresql.org; Mon, 11 Oct 2021 20:20:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ma1mm-0005mo-LI for pgsql-hackers@lists.postgresql.org; Mon, 11 Oct 2021 20:20:37 +0000 Received: from mail-pj1-x1033.google.com ([2607:f8b0:4864:20::1033]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ma1mi-0005On-0r for pgsql-hackers@postgresql.org; Mon, 11 Oct 2021 20:20:33 +0000 Received: by mail-pj1-x1033.google.com with SMTP id ls18-20020a17090b351200b001a00250584aso253511pjb.4 for ; Mon, 11 Oct 2021 13:20:31 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=enhfTOUfAUZyg0x9zmc09F989rKcdbvOVclGM0gBr9U=; b=SsLAK0fpQ67rhpaqOese23TIRF9WlnB/J3VJCokupjfhj6RDCdx28NyeiAvxwqvcFh ET/ubEynICZ8RFqOiqqvAnQUzBxoyluVpfMKx7DE+TEa8s2/IpECW8CszwA80w5hAevt kApr5xyAM6bTKJxKhOIXHmYA7UNHi9qcM8kmpTq3q83rpaaaW7ITy1ZDihANd971NW0A xPIIDza4mCYth1akujmCo6dhpLRPMcJZgNbNjIwPmxbRL8NlDakla/cBK9lDYHiLwju+ KN8Zg5eZt6psgf32sK3wLlTK3wr/+Q8Hk9iuonsRuOVYgOPAaIKou0SFnP1ugWgCyVZX 2qJg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=enhfTOUfAUZyg0x9zmc09F989rKcdbvOVclGM0gBr9U=; b=of4NAe13fOtkUB4m7CSyg80sKzVV1iUZoq0Yxf/jhUcPYNpDiu1FTfSTdLcDit+Cqh negO59sOQp9Ay+q0y8nQtUzAVcmCTdn0YS3TRFYcGFqVyhYKQV1OT01qq5jExpfbcNmJ rVYuUeOHsbShX7pZahiItZDPpXwU/RnYlARZbQdapdpPUVXokcMs3veb67EwVAZMxNzF 9czHwy83LM8Rdp/+Dzz44g3oBSfe7/M+NnXjncqo8zMSf4xL3uhqpmJjxCZj9ZQP+S5P wxK6dIYi7ZFAW+TKWIKQZTPkkikn0SOvYLfC1THqtHMCVJuCM5cM04cKft8rMcSRZmb0 HupA== X-Gm-Message-State: AOAM530MSLc6nxLpM/96vbR3MWRlScVUxog3ihJNuyp6Sqolo0f6cCJu YpO/utthzQo55jPIxW43exHX8izjnPK/Z8PcB8W6gxQvJoUgBCk+VhqvbcX4eiuNUlbaB/CbDaX 77TduNNCPMduU8r4RwyKbTH33fDl/Nxv6XpS0Celg5MNTKPd5zbMX0v4dewQ1UFq58/DD1/DY92 vQ+WAkcUWDgmQp28lCA2qTm6Rhq1NzseeQ4rJ/ X-Google-Smtp-Source: ABdhPJyzVVZQsjPZ+6VTzi468isQMkXLrRicFqd4OmOmxA6IqULHLOYqLOU9SG2pjPoOWj8w2HHieA== X-Received: by 2002:a17:902:e984:b0:13f:17c2:8f0a with SMTP id f4-20020a170902e98400b0013f17c28f0amr19263293plb.74.1633983630678; Mon, 11 Oct 2021 13:20:30 -0700 (PDT) Received: from laptop280-ma-us.hitronhub.home (24-113-193-150.wavecable.com. [24.113.193.150]) by smtp.gmail.com with ESMTPSA id t9sm272944pjm.36.2021.10.11.13.20.26 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 11 Oct 2021 13:20:27 -0700 (PDT) From: Mark Dilger Message-Id: <44A67B65-28BF-4291-A0F0-806DD4309B18@enterprisedb.com> Content-Type: multipart/mixed; boundary="Apple-Mail=_EAD97AAE-607F-4263-B9CF-62C4A0019BBF" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.7\)) Subject: Re: BUG #17212: pg_amcheck fails on checking temporary relations Date: Mon, 11 Oct 2021 13:20:26 -0700 In-Reply-To: Cc: Robert Haas , Pavel Borisov , Alexander Lakhin , PostgreSQL-development To: Peter Geoghegan References: <17212-34dd4a1d6bba98bf@postgresql.org> <28B36E41-1E8F-4DC1-A924-711621BA4972@enterprisedb.com> <386F66EE-DEFD-42EA-9DB2-102BF3414669@enterprisedb.com> <63FF68F5-683D-423E-A667-9C00441101A5@enterprisedb.com> <561CE3DB-5CBC-44BF-9A3E-1D7FAEB123E0@enterprisedb.com> <543F3BF3-EDED-4C52-AF05-623921B6C409@enterprisedb.com> <55A2BFE7-AAAF-4A64-BD3A-5C98EA982267@enterprisedb.com> <07159C9A-40DA-47BF-B2F4-203F5AC00890@enterprisedb.com> <40DB088B-C96F-49B2-BC84-BDC8D35C7E97@enterprisedb.com> <0B7DA7A4-2872-485B-BFDD-BF8E9BB07B26@enterprisedb.com> X-Mailer: Apple Mail (2.3608.120.23.2.7) X-CLOUD-SEC-AV-Info: enterprisedb,google_mail,monitor X-CLOUD-SEC-AV-Sent: true X-Gm-Spam: 0 X-Gm-Phishy: 0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_EAD97AAE-607F-4263-B9CF-62C4A0019BBF Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On Oct 11, 2021, at 12:25 PM, Mark Dilger = wrote: >=20 > Your proposal sounds good. Let me try it and get back to you shortly. Ok, I went with this suggestion, and also your earlier suggestion to = have a in the pg_amcheck docs about using --parent-check = and/or --rootdescend against servers in recovery. --Apple-Mail=_EAD97AAE-607F-4263-B9CF-62C4A0019BBF Content-Disposition: attachment; filename=v3-0001-Fix-BUG-17212.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v3-0001-Fix-BUG-17212.patch" Content-Transfer-Encoding: quoted-printable =46rom=2048a11fb4436b58f25914b6726b589e6b34bc083b=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Mark=20Dilger=20=0A= Date:=20Sun,=203=20Oct=202021=2009:52:22=20-0700=0ASubject:=20[PATCH=20= v3]=20Fix=20BUG=20#17212=0A=0AStop=20attempting=20to=20check=20= inappropriate=20relations=20from=20pg_amcheck.=0ASpecifically,=20= temporary=20tables=20and=20indexes=20belonging=20to=20other=0Asessions,=20= and=20indexes=20marked=20invalid=20or=20not=20ready.=20=20The=20old=20= behavior=0Aprinted=20an=20error=20message=20for=20each=20of=20these=20= and=20ultimately=20exited=0Awith=20non-zero=20status.=20=20The=20tool=20= did=20not=20exit=20early,=20nor=20fail=20to=0Acheck=20relations=20which=20= were=20checkable,=20but=20users=20found=20the=20error=0Amessages=20and=20= exit=20status=20unfriendly.=0A=0AWhile=20at=20it,=20change=20amcheck's=20= verify_heapam=20function=20to=20skip=0Aunlogged=20tables=20with=20a=20= debug=20message=20when=20run=20against=20a=20server=20in=0Arecovery.=20=20= This=20solves=20the=20problem=20of=20pg_amcheck=20failing=20against=0A= such=20servers=20when=20they=20happen=20to=20contain=20unlogged=20= relations.=20=20We=20fix=0Ait=20here=20rather=20than=20in=20pg_amcheck=20= because=20non-pg_amcheck=20users=20will=0Apresumably=20want=20the=20same=20= behavior.=0A=0AChange=20verify_nbtree=20to=20issue=20DEBUG1=20rather=20= than=20NOTICE=20for=20unlogged=0Aindexes=20on=20servers=20in=20recovery.=20= =20This=20is=20the=20same=20issue=20as=20for=0Averify_heapam,=20except=20= that=20verify_nbtree=20was=20already=20handling=20this=0Aissue,=20just=20= with=20a=20higher=20ereport=20level.=0A---=0A=20= contrib/amcheck/verify_heapam.c=20=20=20=20=20=20=20=20=20|=20=2015=20++=0A= =20contrib/amcheck/verify_nbtree.c=20=20=20=20=20=20=20=20=20|=20=20=202=20= +-=0A=20doc/src/sgml/ref/pg_amcheck.sgml=20=20=20=20=20=20=20=20|=20=20=20= 7=20+=0A=20src/bin/pg_amcheck/pg_amcheck.c=20=20=20=20=20=20=20=20=20|=20= =2083=20++++++---=0A=20src/bin/pg_amcheck/t/006_bad_targets.pl=20|=20233=20= ++++++++++++++++++++++++=0A=205=20files=20changed,=20310=20= insertions(+),=2030=20deletions(-)=0A=20create=20mode=20100644=20= src/bin/pg_amcheck/t/006_bad_targets.pl=0A=0Adiff=20--git=20= a/contrib/amcheck/verify_heapam.c=20b/contrib/amcheck/verify_heapam.c=0A= index=2091ef09a8ca..0376cf6480=20100644=0A---=20= a/contrib/amcheck/verify_heapam.c=0A+++=20= b/contrib/amcheck/verify_heapam.c=0A@@=20-323,6=20+323,21=20@@=20= verify_heapam(PG_FUNCTION_ARGS)=0A=20=09=09=09=09= (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),=0A=20=09=09=09=09=20= errmsg("only=20heap=20AM=20is=20supported")));=0A=20=0A+=09/*=0A+=09=20*=20= Early=20exit=20for=20unlogged=20relations=20during=20recovery.=20=20= These=20will=20have=20no=0A+=09=20*=20relation=20fork,=20so=20there=20= won't=20be=20anything=20to=20check.=0A+=09=20*/=0A+=09if=20= (ctx.rel->rd_rel->relpersistence=20=3D=3D=20RELPERSISTENCE_UNLOGGED=20&&=0A= +=09=09RecoveryInProgress())=0A+=09{=0A+=09=09ereport(DEBUG1,=0A+=09=09=09= =09(errcode(ERRCODE_READ_ONLY_SQL_TRANSACTION),=0A+=09=09=09=09=20= errmsg("cannot=20verify=20unlogged=20relation=20\"%s\"=20during=20= recovery,=20skipping",=0A+=09=09=09=09=09=09= RelationGetRelationName(ctx.rel))));=0A+=09=09relation_close(ctx.rel,=20= AccessShareLock);=0A+=09=09PG_RETURN_NULL();=0A+=09}=0A+=0A=20=09/*=20= Early=20exit=20if=20the=20relation=20is=20empty=20*/=0A=20=09nblocks=20=3D= =20RelationGetNumberOfBlocks(ctx.rel);=0A=20=09if=20(!nblocks)=0Adiff=20= --git=20a/contrib/amcheck/verify_nbtree.c=20= b/contrib/amcheck/verify_nbtree.c=0Aindex=20d19f73127c..35a5c3ae98=20= 100644=0A---=20a/contrib/amcheck/verify_nbtree.c=0A+++=20= b/contrib/amcheck/verify_nbtree.c=0A@@=20-383,7=20+383,7=20@@=20= btree_index_mainfork_expected(Relation=20rel)=0A=20=09=09= !RecoveryInProgress())=0A=20=09=09return=20true;=0A=20=0A-=09= ereport(NOTICE,=0A+=09ereport(DEBUG1,=0A=20=09=09=09= (errcode(ERRCODE_READ_ONLY_SQL_TRANSACTION),=0A=20=09=09=09=20= errmsg("cannot=20verify=20unlogged=20index=20\"%s\"=20during=20recovery,=20= skipping",=0A=20=09=09=09=09=09RelationGetRelationName(rel))));=0Adiff=20= --git=20a/doc/src/sgml/ref/pg_amcheck.sgml=20= b/doc/src/sgml/ref/pg_amcheck.sgml=0Aindex=201fd0ecd911..54ec14ca37=20= 100644=0A---=20a/doc/src/sgml/ref/pg_amcheck.sgml=0A+++=20= b/doc/src/sgml/ref/pg_amcheck.sgml=0A@@=20-435,6=20+435,13=20@@=20= PostgreSQL=20documentation=0A=20=20=20=20=0A=20=20=20= =0A=20=0A+=20=20=0A+=20=20=20=0A+=20=20=20=20The=20= =20and=20=0A= +=20=20=20=20options=20will=20fail=20when=20executed=20against=20a=20= server=20in=20Hot=20Standby=20mode.=0A+=20=20=20=0A+=20=20= =0A+=0A=20=20=20=0A=20=20=20=20The=20following=20= command-line=20options=20control=20the=20connection=20to=20the=20server:=0A= =20=0Adiff=20--git=20a/src/bin/pg_amcheck/pg_amcheck.c=20= b/src/bin/pg_amcheck/pg_amcheck.c=0Aindex=20ec04b977de..d4a53c8e63=20= 100644=0A---=20a/src/bin/pg_amcheck/pg_amcheck.c=0A+++=20= b/src/bin/pg_amcheck/pg_amcheck.c=0A@@=20-816,6=20+816,9=20@@=20main(int=20= argc,=20char=20*argv[])=0A=20=20*=20names=20matching=20the=20= expectations=20of=20verify_heap_slot_handler,=20which=20will=0A=20=20*=20= receive=20and=20handle=20each=20row=20returned=20from=20the=20= verify_heapam()=20function.=0A=20=20*=0A+=20*=20The=20constructed=20SQL=20= command=20will=20silently=20skip=20temporary=20tables,=20as=20checking=0A= +=20*=20them=20would=20needlessly=20draw=20errors=20from=20the=20= underlying=20amcheck=20function.=0A+=20*=0A=20=20*=20sql:=20buffer=20= into=20which=20the=20heap=20table=20checking=20command=20will=20be=20= written=0A=20=20*=20rel:=20relation=20information=20for=20the=20heap=20= table=20to=20be=20checked=0A=20=20*=20conn:=20the=20connection=20to=20be=20= used,=20for=20string=20escaping=20purposes=0A@@=20-825,10=20+828,10=20@@=20= prepare_heap_command(PQExpBuffer=20sql,=20RelationInfo=20*rel,=20PGconn=20= *conn)=0A=20{=0A=20=09resetPQExpBuffer(sql);=0A=20=09= appendPQExpBuffer(sql,=0A-=09=09=09=09=09=20=20"SELECT=20blkno,=20= offnum,=20attnum,=20msg=20FROM=20%s.verify_heapam("=0A-=09=09=09=09=09=20= =20"\nrelation=20:=3D=20%u,=20on_error_stop=20:=3D=20%s,=20check_toast=20= :=3D=20%s,=20skip=20:=3D=20'%s'",=0A+=09=09=09=09=09=20=20"SELECT=20= v.blkno,=20v.offnum,=20v.attnum,=20v.msg=20"=0A+=09=09=09=09=09=20=20= "FROM=20pg_catalog.pg_class=20c,=20%s.verify_heapam("=0A+=09=09=09=09=09=20= =20"\nrelation=20:=3D=20c.oid,=20on_error_stop=20:=3D=20%s,=20= check_toast=20:=3D=20%s,=20skip=20:=3D=20'%s'",=0A=20=09=09=09=09=09=20=20= rel->datinfo->amcheck_schema,=0A-=09=09=09=09=09=20=20rel->reloid,=0A=20=09= =09=09=09=09=20=20opts.on_error_stop=20?=20"true"=20:=20"false",=0A=20=09= =09=09=09=09=20=20opts.reconcile_toast=20?=20"true"=20:=20"false",=0A=20=09= =09=09=09=09=20=20opts.skip);=0A@@=20-838,7=20+841,10=20@@=20= prepare_heap_command(PQExpBuffer=20sql,=20RelationInfo=20*rel,=20PGconn=20= *conn)=0A=20=09if=20(opts.endblock=20>=3D=200)=0A=20=09=09= appendPQExpBuffer(sql,=20",=20endblock=20:=3D=20"=20INT64_FORMAT,=20= opts.endblock);=0A=20=0A-=09appendPQExpBufferChar(sql,=20')');=0A+=09= appendPQExpBuffer(sql,=0A+=09=09=09=09=09=20=20"\n)=20v=20WHERE=20c.oid=20= =3D=20%u=20"=0A+=09=09=09=09=09=20=20"AND=20c.relpersistence=20!=3D=20= 't'",=0A+=09=09=09=09=09=20=20rel->reloid);=0A=20}=0A=20=0A=20/*=0A@@=20= -849,6=20+855,10=20@@=20prepare_heap_command(PQExpBuffer=20sql,=20= RelationInfo=20*rel,=20PGconn=20*conn)=0A=20=20*=20functions=20do=20not=20= return=20any,=20but=20rather=20return=20corruption=20information=20by=0A=20= =20*=20raising=20errors,=20which=20verify_btree_slot_handler=20expects.=0A= =20=20*=0A+=20*=20The=20constructed=20SQL=20command=20will=20silently=20= skip=20temporary=20indexes,=20and=0A+=20*=20indexes=20being=20reindexed=20= concurrently,=20as=20checking=20them=20would=20needlessly=20draw=0A+=20*=20= errors=20from=20the=20underlying=20amcheck=20functions.=0A+=20*=0A=20=20= *=20sql:=20buffer=20into=20which=20the=20heap=20table=20checking=20= command=20will=20be=20written=0A=20=20*=20rel:=20relation=20information=20= for=20the=20index=20to=20be=20checked=0A=20=20*=20conn:=20the=20= connection=20to=20be=20used,=20for=20string=20escaping=20purposes=0A@@=20= -858,27=20+868,31=20@@=20prepare_btree_command(PQExpBuffer=20sql,=20= RelationInfo=20*rel,=20PGconn=20*conn)=0A=20{=0A=20=09= resetPQExpBuffer(sql);=0A=20=0A-=09/*=0A-=09=20*=20Embed=20the=20= database,=20schema,=20and=20relation=20name=20in=20the=20query,=20so=20= if=20the=0A-=09=20*=20check=20throws=20an=20error,=20the=20user=20knows=20= which=20relation=20the=20error=20came=0A-=09=20*=20from.=0A-=09=20*/=0A=20= =09if=20(opts.parent_check)=0A=20=09=09appendPQExpBuffer(sql,=0A-=09=09=09= =09=09=09=20=20"SELECT=20*=20FROM=20%s.bt_index_parent_check("=0A-=09=09=09= =09=09=09=20=20"index=20:=3D=20'%u'::regclass,=20heapallindexed=20:=3D=20= %s,=20"=0A-=09=09=09=09=09=09=20=20"rootdescend=20:=3D=20%s)",=0A+=09=09=09= =09=09=09=20=20"SELECT=20%s.bt_index_parent_check("=0A+=09=09=09=09=09=09= =20=20"index=20:=3D=20c.oid,=20heapallindexed=20:=3D=20%s,=20rootdescend=20= :=3D=20%s)"=0A+=09=09=09=09=09=09=20=20"\nFROM=20pg_catalog.pg_class=20= c,=20pg_catalog.pg_index=20i=20"=0A+=09=09=09=09=09=09=20=20"WHERE=20= c.oid=20=3D=20%u=20"=0A+=09=09=09=09=09=09=20=20"AND=20c.oid=20=3D=20= i.indexrelid=20"=0A+=09=09=09=09=09=09=20=20"AND=20c.relpersistence=20!=3D= =20't'=20"=0A+=09=09=09=09=09=09=20=20"AND=20i.indisready=20AND=20= i.indisvalid=20AND=20i.indislive",=0A=20=09=09=09=09=09=09=20=20= rel->datinfo->amcheck_schema,=0A-=09=09=09=09=09=09=20=20rel->reloid,=0A=20= =09=09=09=09=09=09=20=20(opts.heapallindexed=20?=20"true"=20:=20= "false"),=0A-=09=09=09=09=09=09=20=20(opts.rootdescend=20?=20"true"=20:=20= "false"));=0A+=09=09=09=09=09=09=20=20(opts.rootdescend=20?=20"true"=20:=20= "false"),=0A+=09=09=09=09=09=09=20=20rel->reloid);=0A=20=09else=0A=20=09=09= appendPQExpBuffer(sql,=0A-=09=09=09=09=09=09=20=20"SELECT=20*=20FROM=20= %s.bt_index_check("=0A-=09=09=09=09=09=09=20=20"index=20:=3D=20= '%u'::regclass,=20heapallindexed=20:=3D=20%s)",=0A+=09=09=09=09=09=09=20=20= "SELECT=20%s.bt_index_check("=0A+=09=09=09=09=09=09=20=20"index=20:=3D=20= c.oid,=20heapallindexed=20:=3D=20%s)"=0A+=09=09=09=09=09=09=20=20"\nFROM=20= pg_catalog.pg_class=20c,=20pg_catalog.pg_index=20i=20"=0A+=09=09=09=09=09= =09=20=20"WHERE=20c.oid=20=3D=20%u=20"=0A+=09=09=09=09=09=09=20=20"AND=20= c.oid=20=3D=20i.indexrelid=20"=0A+=09=09=09=09=09=09=20=20"AND=20= c.relpersistence=20!=3D=20't'=20"=0A+=09=09=09=09=09=09=20=20"AND=20= i.indisready=20AND=20i.indisvalid=20AND=20i.indislive",=0A=20=09=09=09=09= =09=09=20=20rel->datinfo->amcheck_schema,=0A-=09=09=09=09=09=09=20=20= rel->reloid,=0A-=09=09=09=09=09=09=20=20(opts.heapallindexed=20?=20= "true"=20:=20"false"));=0A+=09=09=09=09=09=09=20=20(opts.heapallindexed=20= ?=20"true"=20:=20"false"),=0A+=09=09=09=09=09=09=20=20rel->reloid);=0A=20= }=0A=20=0A=20/*=0A@@=20-1086,15=20+1100,17=20@@=20= verify_btree_slot_handler(PGresult=20*res,=20PGconn=20*conn,=20void=20= *context)=0A=20=0A=20=09if=20(PQresultStatus(res)=20=3D=3D=20= PGRES_TUPLES_OK)=0A=20=09{=0A-=09=09int=09=09=09ntups=20=3D=20= PQntuples(res);=0A+=09=09int=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20ntups=20=3D=20PQntuples(res);=0A=20=0A-=09=09if=20= (ntups=20!=3D=201)=0A+=09=09if=20(ntups=20>=201)=0A=20=09=09{=0A=20=09=09= =09/*=0A=20=09=09=09=20*=20We=20expect=20the=20btree=20checking=20= functions=20to=20return=20one=20void=20row=0A-=09=09=09=20*=20each,=20so=20= we=20should=20output=20some=20sort=20of=20warning=20if=20we=20get=0A-=09=09= =09=20*=20anything=20else,=20not=20because=20it=20indicates=20= corruption,=20but=20because=0A-=09=09=09=20*=20it=20suggests=20a=20= mismatch=20between=20amcheck=20and=20pg_amcheck=20versions.=0A+=09=09=09=20= *=20each,=20or=20zero=20rows=20if=20the=20check=20was=20skipped=20due=20= to=20the=20object=0A+=09=09=09=20*=20being=20in=20the=20wrong=20state=20= to=20be=20checked,=20so=20we=20should=20output=20some=0A+=09=09=09=20*=20= sort=20of=20warning=20if=20we=20get=20anything=20more,=20not=20because=20= it=0A+=09=09=09=20*=20indicates=20corruption,=20but=20because=20it=20= suggests=20a=20mismatch=20between=0A+=09=09=09=20*=20amcheck=20and=20= pg_amcheck=20versions.=0A=20=09=09=09=20*=0A=20=09=09=09=20*=20In=20= conjunction=20with=20--progress,=20anything=20written=20to=20stderr=20at=0A= =20=09=09=09=20*=20this=20time=20would=20present=20strangely=20to=20the=20= user=20without=20an=20extra=0A@@=20-1889,10=20+1905,16=20@@=20= compile_relation_list_one_db(PGconn=20*conn,=20SimplePtrList=20= *relations,=0A=20=09=09=09=09=09=09=20=20"\nAND=20(c.relam=20=3D=20%u=20= OR=20NOT=20ep.btree_only=20OR=20ep.rel_regex=20IS=20NULL)",=0A=20=09=09=09= =09=09=09=20=20HEAP_TABLE_AM_OID,=20BTREE_AM_OID);=0A=20=0A+=09/*=0A+=09=20= *=20Exclude=20temporary=20tables=20and=20indexes,=20which=20must=20= necessarily=20belong=20to=0A+=09=20*=20other=20sessions.=20=20(We=20= don't=20create=20any=20ourselves.)=20=20We=20must=20ultimately=0A+=09=20= *=20exclude=20indexes=20marked=20invalid=20or=20not=20ready,=20but=20we=20= delay=20that=20decision=0A+=09=20*=20until=20firing=20off=20the=20= amcheck=20command,=20as=20the=20state=20of=20an=20index=20may=0A+=09=20*=20= change=20by=20then.=0A+=09=20*/=0A+=09appendPQExpBufferStr(&sql,=20= "\nWHERE=20c.relpersistence=20!=3D=20't'");=0A=20=09if=20= (opts.excludetbl=20||=20opts.excludeidx=20||=20opts.excludensp)=0A-=09=09= appendPQExpBufferStr(&sql,=20"\nWHERE=20ep.pattern_id=20IS=20NULL");=0A-=09= else=0A-=09=09appendPQExpBufferStr(&sql,=20"\nWHERE=20true");=0A+=09=09= appendPQExpBufferStr(&sql,=20"\nAND=20ep.pattern_id=20IS=20NULL");=0A=20=0A= =20=09/*=0A=20=09=20*=20We=20need=20to=20be=20careful=20not=20to=20break=20= the=20--no-dependent-toast=20and=0A@@=20-1944,7=20+1966,8=20@@=20= compile_relation_list_one_db(PGconn=20*conn,=20SimplePtrList=20= *relations,=0A=20=09=09=09=09=09=09=09=09=20"\nON=20('pg_toast'=20~=20= ep.nsp_regex=20OR=20ep.nsp_regex=20IS=20NULL)"=0A=20=09=09=09=09=09=09=09= =09=20"\nAND=20(t.relname=20~=20ep.rel_regex=20OR=20ep.rel_regex=20IS=20= NULL)"=0A=20=09=09=09=09=09=09=09=09=20"\nAND=20ep.heap_only"=0A-=09=09=09= =09=09=09=09=09=20"\nWHERE=20ep.pattern_id=20IS=20NULL");=0A+=09=09=09=09= =09=09=09=09=20"\nWHERE=20ep.pattern_id=20IS=20NULL"=0A+=09=09=09=09=09=09= =09=09=20"\nAND=20t.relpersistence=20!=3D=20't'");=0A=20=09=09= appendPQExpBufferStr(&sql,=0A=20=09=09=09=09=09=09=09=20"\n)");=0A=20=09= }=0A@@=20-1962,7=20+1985,8=20@@=20compile_relation_list_one_db(PGconn=20= *conn,=20SimplePtrList=20*relations,=0A=20=09=09=09=09=09=09=20=20= "\nINNER=20JOIN=20pg_catalog.pg_index=20i=20"=0A=20=09=09=09=09=09=09=20=20= "ON=20r.oid=20=3D=20i.indrelid=20"=0A=20=09=09=09=09=09=09=20=20"INNER=20= JOIN=20pg_catalog.pg_class=20c=20"=0A-=09=09=09=09=09=09=20=20"ON=20= i.indexrelid=20=3D=20c.oid");=0A+=09=09=09=09=09=09=20=20"ON=20= i.indexrelid=20=3D=20c.oid=20"=0A+=09=09=09=09=09=09=20=20"AND=20= c.relpersistence=20!=3D=20't'");=0A=20=09=09if=20(opts.excludeidx=20||=20= opts.excludensp)=0A=20=09=09=09appendPQExpBufferStr(&sql,=0A=20=09=09=09=09= =09=09=09=09=20"\nINNER=20JOIN=20pg_catalog.pg_namespace=20n=20"=0A@@=20= -2000,7=20+2024,8=20@@=20compile_relation_list_one_db(PGconn=20*conn,=20= SimplePtrList=20*relations,=0A=20=09=09=09=09=09=09=20=20"INNER=20JOIN=20= pg_catalog.pg_index=20i=20"=0A=20=09=09=09=09=09=09=20=20"ON=20t.oid=20=3D= =20i.indrelid"=0A=20=09=09=09=09=09=09=20=20"\nINNER=20JOIN=20= pg_catalog.pg_class=20c=20"=0A-=09=09=09=09=09=09=20=20"ON=20= i.indexrelid=20=3D=20c.oid");=0A+=09=09=09=09=09=09=20=20"ON=20= i.indexrelid=20=3D=20c.oid=20"=0A+=09=09=09=09=09=09=20=20"AND=20= c.relpersistence=20!=3D=20't'");=0A=20=09=09if=20(opts.excludeidx)=0A=20=09= =09=09appendPQExpBufferStr(&sql,=0A=20=09=09=09=09=09=09=09=09=20"\nLEFT=20= OUTER=20JOIN=20exclude_pat=20ep=20"=0Adiff=20--git=20= a/src/bin/pg_amcheck/t/006_bad_targets.pl=20= b/src/bin/pg_amcheck/t/006_bad_targets.pl=0Anew=20file=20mode=20100644=0A= index=200000000000..e89d7d1be8=0A---=20/dev/null=0A+++=20= b/src/bin/pg_amcheck/t/006_bad_targets.pl=0A@@=20-0,0=20+1,233=20@@=0A+=0A= +#=20Copyright=20(c)=202021,=20PostgreSQL=20Global=20Development=20Group=0A= +=0A+#=20This=20regression=20test=20checks=20the=20behavior=20of=20= pg_amcheck=20in=20the=20presence=20of=0A+#=20inappropriate=20target=20= relations.=0A+#=0A+use=20strict;=0A+use=20warnings;=0A+use=20= PostgresNode;=0A+use=20TestLib;=0A+use=20Test::More=20tests=20=3D>=2052;=0A= +=0A+#=20Establish=20a=20primary=20and=20standby=20server,=20with=20= temporary=20and=20unlogged=20tables.=0A+#=20The=20temporary=20tables=20= should=20not=20be=20checked=20on=20either=20system,=20as=20pg_amcheck's=0A= +#=20sessions=20won't=20be=20able=20to=20see=20their=20contents.=20=20= The=20unlogged=20tables=20should=20not=0A+#=20be=20checked=20on=20the=20= standby,=20as=20they=20won't=20have=20relation=20forks=20there.=0A+#=0A= +my=20$node_primary=20=3D=20PostgresNode->new('primary');=0A= +$node_primary->init(=0A+=09allows_streaming=20=3D>=201,=0A+=09= auth_extra=20=20=20=20=20=20=20=3D>=20[=20'--create-role',=20'repl_role'=20= ]);=0A+$node_primary->start;=0A+$node_primary->safe_psql('postgres',=20= qq(=0A+CREATE=20EXTENSION=20amcheck;=0A+CREATE=20UNLOGGED=20TABLE=20= unlogged_heap=20(i=20INTEGER[],=20b=20BOX);=0A+INSERT=20INTO=20= unlogged_heap=20(i,=20b)=20VALUES=20(ARRAY[1,2,3]::INTEGER[],=20= '((1,2),(3,4))'::BOX);=0A+CREATE=20INDEX=20unlogged_btree=20ON=20= unlogged_heap=20USING=20BTREE=20(i);=0A+CREATE=20INDEX=20unlogged_brin=20= ON=20unlogged_heap=20USING=20BRIN=20(b);=0A+CREATE=20INDEX=20= unlogged_gin=20ON=20unlogged_heap=20USING=20GIN=20(i);=0A+CREATE=20INDEX=20= unlogged_gist=20ON=20unlogged_heap=20USING=20GIST=20(b);=0A+CREATE=20= INDEX=20unlogged_hash=20ON=20unlogged_heap=20USING=20HASH=20(i);=0A+));=0A= +my=20$backup_name=20=3D=20'my_backup';=0A= +$node_primary->backup($backup_name);=0A+=0A+#=20Hold=20open=20a=20= session=20with=20temporary=20tables=20and=20indexes=20defined=0A+#=0A+my=20= $in=20=3D=20'';=0A+my=20$out=20=3D=20'';=0A+my=20$timer=20=3D=20= IPC::Run::timeout(180);=0A+my=20$h=20=3D=20= $node_primary->background_psql('postgres',=20\$in,=20\$out,=20$timer);=0A= +$in=20=3D=20qq(=0A+BEGIN;=0A+CREATE=20TEMPORARY=20TABLE=20= heap_temporary=20(i=20INTEGER[],=20b=20BOX)=20ON=20COMMIT=20PRESERVE=20= ROWS;=0A+INSERT=20INTO=20heap_temporary=20(i,=20b)=20VALUES=20= (ARRAY[1,2,3]::INTEGER[],=20'((1,2),(3,4))'::BOX);=0A+CREATE=20INDEX=20= btree_temporary=20ON=20heap_temporary=20USING=20BTREE=20(i);=0A+CREATE=20= INDEX=20brin_temporary=20ON=20heap_temporary=20USING=20BRIN=20(b);=0A= +CREATE=20INDEX=20gin_temporary=20ON=20heap_temporary=20USING=20GIN=20= (i);=0A+CREATE=20INDEX=20gist_temporary=20ON=20heap_temporary=20USING=20= GIST=20(b);=0A+CREATE=20INDEX=20hash_temporary=20ON=20heap_temporary=20= USING=20HASH=20(i);=0A+COMMIT;=0A+BEGIN;=0A+);=0A+$h->pump_nb;=0A+=0A+my=20= $node_standby=20=3D=20PostgresNode->new('standby');=0A= +$node_standby->init_from_backup($node_primary,=20$backup_name,=0A+=09= has_streaming=20=3D>=201);=0A+$node_standby->start;=0A= +$node_primary->wait_for_catchup($node_standby,=20'replay',=0A+=09= $node_primary->lsn('replay'));=0A+=0A+#=20Check=20that=20running=20= amcheck=20functions=20from=20SQL=20against=20inappropriate=20targets=0A= +#=20fails=20sensibly.=20=20This=20portion=20of=20the=20test=20arguably=20= belongs=20in=20contrib/amcheck=0A+#=20rather=20than=20here,=20but=20we=20= have=20already=20set=20up=20the=20necessary=20test=20environment,=0A+#=20= so=20check=20here=20rather=20than=20duplicating=20the=20environment=20= there.=0A+#=0A+my=20($stdout,=20$stderr);=0A+for=20my=20$test=20(=0A+=09= [=20$node_standby=20=3D>=20"SELECT=20*=20FROM=20= verify_heapam('unlogged_heap')",=0A+=09=20=20qr/^$/,=0A+=09=20"checking=20= unlogged=20table=20during=20recovery=20does=20not=20complain"=20],=0A+=0A= +=09[=20$node_standby=20=3D>=20"SELECT=20*=20FROM=20= bt_index_check('unlogged_btree')",=0A+=09=20=20qr/^$/,=0A+=09=20= "checking=20unlogged=20btree=20index=20during=20recovery=20does=20not=20= complain"=20],=0A+=0A+=09[=20$node_standby=20=3D>=20"SELECT=20*=20FROM=20= bt_index_check('unlogged_brin')",=0A+=09=20=20qr/ERROR:=20=20only=20= B-Tree=20indexes=20are=20supported=20as=20targets=20for=20= verification.*DETAIL:=20=20Relation=20"unlogged_brin"=20is=20not=20a=20= B-Tree=20index/s,=0A+=09=20"checking=20unlogged=20brin=20index=20during=20= recovery=20fails=20appropriately"=20],=0A+=0A+=09[=20$node_standby=20=3D>=20= "SELECT=20*=20FROM=20bt_index_check('unlogged_gin')",=0A+=09=20=20= qr/ERROR:=20=20only=20B-Tree=20indexes=20are=20supported=20as=20targets=20= for=20verification.*DETAIL:=20=20Relation=20"unlogged_gin"=20is=20not=20= a=20B-Tree=20index/s,=0A+=09=20"checking=20unlogged=20gin=20index=20= during=20recovery=20fails=20appropriately"=20],=0A+=0A+=09[=20= $node_standby=20=3D>=20"SELECT=20*=20FROM=20= bt_index_check('unlogged_gist')",=0A+=09=20=20qr/ERROR:=20=20only=20= B-Tree=20indexes=20are=20supported=20as=20targets=20for=20= verification.*DETAIL:=20=20Relation=20"unlogged_gist"=20is=20not=20a=20= B-Tree=20index/s,=0A+=09=20"checking=20unlogged=20gist=20index=20during=20= recovery=20fails=20appropriately"=20],=0A+=0A+=09[=20$node_standby=20=3D>=20= "SELECT=20*=20FROM=20bt_index_check('unlogged_hash')",=0A+=09=20=20= qr/ERROR:=20=20only=20B-Tree=20indexes=20are=20supported=20as=20targets=20= for=20verification.*DETAIL:=20=20Relation=20"unlogged_hash"=20is=20not=20= a=20B-Tree=20index/s,=0A+=09=20"checking=20unlogged=20hash=20index=20= during=20recovery=20fails=20appropriately"=20],=0A+=0A+=09)=0A+{=0A+=09= $test->[0]->psql('postgres',=20$test->[1],=0A+=09=09=09=09=09=20stdout=20= =3D>=20\$stdout,=20stderr=20=3D>=20\$stderr);=0A+=09like=20($stderr,=20= $test->[2],=20$test->[3]);=0A+}=0A+=0A+#=20Verify=20that=20--all=20= excludes=20the=20temporary=20relations=20and=20handles=20unlogged=0A+#=20= relations=20as=20appropriate,=20without=20raising=20any=20warnings=20or=20= exiting=20with=20a=0A+#=20non-zero=20status.=0A+#=0A= +$node_primary->command_checks_all(=0A+=09['pg_amcheck',=20'--all',=20= '-D',=20'template1'],=0A+=090,=0A+=09[=20qr/^$/=20],=0A+=09[=20qr/^$/=20= ],=0A+=09'pg_amcheck=20all=20objects=20on=20primary');=0A+=0A= +$node_standby->command_checks_all(=0A+=09['pg_amcheck',=20'--all',=20= '-D',=20'template1'],=0A+=090,=0A+=09[=20qr/^$/=20],=0A+=09[=20qr/^$/=20= ],=0A+=09'pg_amcheck=20all=20objects=20on=20standby');=0A+=0A+#=20Verify=20= that=20explicitly=20asking=20for=20unlogged=20relations=20to=20be=20= checked=20does=20not=0A+#=20raise=20any=20warnings=20or=20exit=20with=20= a=20non-zero=20exit=20status,=20even=20when=20they=20cannot=0A+#=20be=20= checked=20due=20to=20recovery=20being=20in=20progress.=0A+#=0A+#=20These=20= relations=20will=20have=20no=20relation=20fork=20during=20recovery,=20so=20= even=20without=0A+#=20checking=20them,=20we=20can=20say=20(by=20= definition)=20that=20they=20are=20not=20corrupt,=20because=0A+#=20it=20= is=20meaningless=20to=20declare=20a=20non-existent=20relation=20fork=20= corrupt.=0A+#=0A+$node_primary->command_checks_all(=0A+=09['pg_amcheck',=20= '--relation',=20'*unlogged*'],=0A+=090,=0A+=09[=20qr/^$/=20],=0A+=09[=20= qr/^$/=20],=0A+=09'pg_amcheck=20unlogged=20objects=20on=20primary');=0A+=0A= +$node_standby->command_checks_all(=0A+=09['pg_amcheck',=20'--relation',=20= '*unlogged*'],=0A+=090,=0A+=09[=20qr/^$/=20],=0A+=09[=20qr/^$/=20],=0A+=09= 'pg_amcheck=20unlogged=20objects=20on=20standby');=0A+=0A+#=20Verify=20= that=20the=20--heapallindexed=20check=20works=20on=20both=20primary=20= and=20standby.=0A+#=0A+$node_primary->command_checks_all(=0A+=09= ['pg_amcheck',=20'--all',=20'-D',=20'template1',=20'--heapallindexed'],=0A= +=090,=0A+=09[=20qr/^$/=20],=0A+=09[=20qr/^$/=20],=0A+=09'pg_amcheck=20= --helpallindexed=20on=20primary');=0A+=0A= +$node_standby->command_checks_all(=0A+=09['pg_amcheck',=20'--all',=20= '-D',=20'template1',=20'--heapallindexed'],=0A+=090,=0A+=09[=20qr/^$/=20= ],=0A+=09[=20qr/^$/=20],=0A+=09'pg_amcheck=20--helpallindexed=20on=20= standby');=0A+=0A+#=20Verify=20that=20the=20--parent-check=20and=20= --rootdescend=20options=20work=20on=20the=20primary.=0A+#=0A= +$node_primary->command_checks_all(=0A+=09['pg_amcheck',=20'--all',=20= '-D',=20'template1',=20'--rootdescend'],=0A+=090,=0A+=09[=20qr/^$/=20],=0A= +=09[=20qr/^$/=20],=0A+=09'pg_amcheck=20--rootdescend=20on=20primary');=0A= +=0A+$node_primary->command_checks_all(=0A+=09['pg_amcheck',=20'--all',=20= '-D',=20'template1',=20'--parent-check'],=0A+=090,=0A+=09[=20qr/^$/=20],=0A= +=09[=20qr/^$/=20],=0A+=09'pg_amcheck=20--parent-check=20on=20primary');=0A= +=0A+#=20Check=20that=20the=20failures=20on=20the=20standby=20from=20= using=20--parent-check=20and=0A+#=20--rootdescend=20are=20the=20failures=20= we=20expect=0A+#=0A+$node_standby->command_checks_all(=0A+=09= ['pg_amcheck',=20'--all',=20'-D',=20'template1',=20'--rootdescend'],=0A+=09= 2,=0A+=09[=20qr/ERROR:=20=20cannot=20acquire=20lock=20mode=20ShareLock=20= on=20database=20objects=20while=20recovery=20is=20in=20progress/,=0A+=09=20= =20qr/btree=20index=20"postgres\.pg_catalog\./,=0A+=09=20=20qr/btree=20= index=20"postgres\.pg_toast\./,=0A+=09],=0A+=09[=20qr/^$/=20],=0A+=09= 'pg_amcheck=20--rootdescend=20on=20standby');=0A+=0A= +$node_standby->command_checks_all(=0A+=09['pg_amcheck',=20'--all',=20= '-D',=20'template1',=20'--parent-check'],=0A+=092,=0A+=09[=20qr/ERROR:=20= =20cannot=20acquire=20lock=20mode=20ShareLock=20on=20database=20objects=20= while=20recovery=20is=20in=20progress/,=0A+=09=20=20qr/btree=20index=20= "postgres\.pg_catalog\./,=0A+=09=20=20qr/btree=20index=20= "postgres\.pg_toast\./,=0A+=09],=0A+=09[=20qr/^$/=20],=0A+=09'pg_amcheck=20= --parent-check=20on=20standby');=0A+=0A+#=20Bug=20#17212=0A+#=0A+#=20= Verify=20that=20explicitly=20asking=20for=20another=20session's=20= temporary=20relations=20to=20be=0A+#=20checked=20fails,=20but=20only=20= in=20the=20sense=20that=20nothing=20matched=20the=20parameter.=20=20We=0A= +#=20don't=20complain=20that=20they=20are=20uncheckable,=20only=20that=20= you=20gave=20a=20--relation=0A+#=20option=20and=20we=20didn't=20find=20= anything=20checkable=20matching=20the=20pattern.=0A+#=0A= +$node_primary->command_checks_all(=0A+=09['pg_amcheck',=20'--relation',=20= '*temporary*'],=0A+=091,=0A+=09[=20qr/^$/=20],=0A+=09[=20qr/error:=20no=20= relations=20to=20check=20matching=20"\*temporary\*"/=20],=0A+=09= 'pg_amcheck=20temporary=20objects=20on=20primary');=0A+=0A= +$node_standby->command_checks_all(=0A+=09['pg_amcheck',=20'--relation',=20= '*temporary*'],=0A+=091,=0A+=09[=20qr/^$/=20],=0A+=09[=20qr/error:=20no=20= relations=20to=20check=20matching=20"\*temporary\*"/=20],=0A+=09= 'pg_amcheck=20temporary=20objects=20on=20standby');=0A+=0A+#=20Verify=20= that=20a=20relation=20pattern=20which=20only=20matches=20temporary=20= relations=20draws=0A+#=20an=20error,=20even=20when=20other=20relation=20= patterns=20are=20ok.=20=20This=20differs=20from=20the=0A+#=20test=20= above=20in=20that=20the=20set=20of=20all=20relations=20to=20check=20is=20= not=20empty.=0A+#=0A+$node_primary->command_checks_all(=0A+=09= ['pg_amcheck',=20'--relation',=20'*temporary*',=20'--relation',=20= '*unlogged*'],=0A+=091,=0A+=09[=20qr/^$/=20],=0A+=09[=20qr/error:=20no=20= relations=20to=20check=20matching=20"\*temporary\*"/=20],=0A+=09= 'pg_amcheck=20temporary=20objects=20on=20primary');=0A+=0A= +$node_standby->command_checks_all(=0A+=09['pg_amcheck',=20'--relation',=20= '*temporary*',=20'--relation',=20'*unlogged*'],=0A+=091,=0A+=09[=20= qr/^$/=20],=0A+=09[=20qr/error:=20no=20relations=20to=20check=20matching=20= "\*temporary\*"/=20],=0A+=09'pg_amcheck=20temporary=20objects=20on=20= standby');=0A--=20=0A2.21.1=20(Apple=20Git-122.3)=0A=0A= --Apple-Mail=_EAD97AAE-607F-4263-B9CF-62C4A0019BBF Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 =E2=80=94 Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company --Apple-Mail=_EAD97AAE-607F-4263-B9CF-62C4A0019BBF--