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 1mZyZH-0001kO-PG for pgsql-hackers@arkaria.postgresql.org; Mon, 11 Oct 2021 16:54:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mZyYl-0004kY-5m for pgsql-hackers@arkaria.postgresql.org; Mon, 11 Oct 2021 16:53:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mZyYk-0004kP-FK for pgsql-hackers@lists.postgresql.org; Mon, 11 Oct 2021 16:53:54 +0000 Received: from mail-pf1-x434.google.com ([2607:f8b0:4864:20::434]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mZyYf-0004Kx-QL for pgsql-hackers@postgresql.org; Mon, 11 Oct 2021 16:53:53 +0000 Received: by mail-pf1-x434.google.com with SMTP id w6so3785374pfd.11 for ; Mon, 11 Oct 2021 09:53:49 -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=cOVxs7y32jfJSKxPlXoxinfd2cvXULRzU62NeqywmAk=; b=eX0qoUPKe+Z1Qya9SOBejFXadQWT/v78VPLeYLOkyBGGMgFA4XQ98fg4/wIPqDoAJI pVFNP+QXQM3LdCnhhMBkG+U3rDhCvYWEqAIcUQntpovK7Fjhl/sFs0cOBqH/qcIXtF/4 t9u3Vpm6e9TIPdbCmKdPESVkgDP3mgIbECknC9+urNkIL3/A6HoWMq8JB/1RYr1WbO8Q rsdwyMBgHW1PqamvqNR69JCZ6sY/lcRoSEln822Iqdl5sraDG/m75qopmN4PNq0Lzqzg /Wlu0CT4Dq/cJwjtL0WwyaSbwVrCyuSuoNRPwYIsPkfSnIOYxJLwr4snzop3lldsvXVr +xZA== 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=cOVxs7y32jfJSKxPlXoxinfd2cvXULRzU62NeqywmAk=; b=M7nxfd0WSmnyA9s5E5G4LbyoRe8tkzIxDZKANvIkp1wXJZ++P5DLHgLZ8FQEu7oD3m tgH2CMfPaIZgLVYqBC9HRI8oWzh/3kQRvHz688hkxXZpVnbYVGGfP/FN3a4mbj48AI6o T8w4RaERQncPEwdpoDgrUcFWjfEU4gT1nUauXr0+vjdbP/OqDHM1s0CWllihmWS3JUS2 EifTS2g/X/RKRIHzxrk7d1QcDvDfcQVuXVe8VrVWx+GMrAkmHKcwTSCw9wSzODeYJOP9 vRLeeroC1BJy3LyFPynure/ujAC271cZupKbdzLOzlJvT3vaRhJ1Y4TFULjg2uqkdUjE 4sGw== X-Gm-Message-State: AOAM532DquUp1LIjo/mtYSk5M/ATTL6ULHETcfPg3ZoR0qU57a39rwVL v7zMIb3wQo0ITef2TJcRXCevB9pQjsnMQIlWu3+/Fh+GefyJ1v5BcPerFluYmbQN4t6GGdsuGLC fc/bGgvGrjwK7L+11T/y9PCE+b7iPop7p+hyiRWfoJZrEmS3sZ9P15PiE7/vF/VExCQh4cW+KLs g6bEIUuvcV/jv7cYCtRFP/1S3KdibKv7V3tH4D X-Google-Smtp-Source: ABdhPJzd3w7Wo093kmTEZykGRb0BxWKPpS2EJ/AE+I3TRE+sUmnO9Iw911hr44TArAG1/s7MsyWBuQ== X-Received: by 2002:a62:6203:0:b0:44c:7649:90b0 with SMTP id w3-20020a626203000000b0044c764990b0mr27653034pfb.21.1633971227288; Mon, 11 Oct 2021 09:53:47 -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 l18sm8608292pfu.202.2021.10.11.09.53.46 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 11 Oct 2021 09:53:46 -0700 (PDT) From: Mark Dilger Message-Id: <07159C9A-40DA-47BF-B2F4-203F5AC00890@enterprisedb.com> Content-Type: multipart/mixed; boundary="Apple-Mail=_2EC3B1C0-5011-48DD-9394-77309E3BD4C8" 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 09:53:45 -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> 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=_2EC3B1C0-5011-48DD-9394-77309E3BD4C8 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On Oct 6, 2021, at 4:12 PM, Peter Geoghegan wrote: >=20 >>=20 >> Ok, excellent, that was probably the only thing that had me really = hung up. I thought you were still asking for pg_amcheck to filter out = the --parent-check option when in recovery, but if you're not asking for = that, then I might have enough to go on now. >=20 > Sorry about that. I realized my mistake (not specifically addressing > pg_is_in_recovery()) after I hit "send", and should have corrected the > record sooner. >=20 >> I was using "downgrading" to mean downgrading from = bt_index_parent_check() to bt_index_check() when pg_is_in_recovery() is = true, but you've clarified that you're not requesting that downgrade, so = I think we've now gotten past the last sticking point about that whole = issue. >=20 > Right. I never meant anything like making a would-be > bt_index_parent_check() call into a bt_index_check() call, just > because of the state of the system (e.g., it's in recovery). That > seems awful, in fact. Please find attached the latest version of the patch which includes the = changes we discussed. --Apple-Mail=_2EC3B1C0-5011-48DD-9394-77309E3BD4C8 Content-Disposition: attachment; filename=v2-0001-Fix-BUG-17212.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v2-0001-Fix-BUG-17212.patch" Content-Transfer-Encoding: quoted-printable =46rom=20c4df4c9a90276097607d34ad5712fa1685dfb064=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= v2]=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= unlogged=20tables=20and=20indexes=20on=20systems=20that=20are=20in=0A= recovery,=20and=20indexes=20marked=20invalid=20or=20not=20ready.=20=20= The=20old=20behavior=0Aprinted=20an=20error=20message=20for=20each=20of=20= these=20and=20ultimately=20exited=0Awith=20non-zero=20status.=20=20The=20= tool=20did=20not=20exit=20early,=20nor=20fail=20to=0Acheck=20relations=20= which=20were=20checkable,=20but=20users=20found=20the=20error=0Amessages=20= and=20exit=20status=20unfriendly.=0A=0AWhile=20at=20it,=20also=20change=20= amcheck's=20verify_heapam=20function=20to=20skip=0Aunlogged=20tables=20= with=20a=20notice=20message=20while=20in=20recovery,=20in=20case=0A= callers=20other=20than=20pg_amcheck=20attempt=20to=20check=20them.=0A---=0A= =20contrib/amcheck/verify_heapam.c=20=20=20=20=20=20=20=20=20|=20=2015=20= ++=0A=20doc/src/sgml/ref/pg_amcheck.sgml=20=20=20=20=20=20=20=20|=20=20=20= 8=20+=0A=20src/bin/pg_amcheck/pg_amcheck.c=20=20=20=20=20=20=20=20=20|=20= =2090=20+++++++---=0A=20src/bin/pg_amcheck/t/006_bad_targets.pl=20|=20= 225=20++++++++++++++++++++++++=0A=204=20files=20changed,=20309=20= insertions(+),=2029=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..cd9b946eb2=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(NOTICE,=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/doc/src/sgml/ref/pg_amcheck.sgml=20= b/doc/src/sgml/ref/pg_amcheck.sgml=0Aindex=201fd0ecd911..a8af64ceb5=20= 100644=0A---=20a/doc/src/sgml/ref/pg_amcheck.sgml=0A+++=20= b/doc/src/sgml/ref/pg_amcheck.sgml=0A@@=20-408,6=20+408,10=20@@=20= PostgreSQL=20documentation=0A=20=20=20=20=20=20=20=20= =20option=20implicitly=20selects=0A=20=20=20= =20=20=20=20=20bt_index_parent_check.=0A=20=20=20=20= =20=20=20=0A+=20=20=20=20=20=20=0A+=20=20=20=20=20=20=20= Note=20that=20=20cannot=20be=20used=20= when=20Hot=0A+=20=20=20=20=20=20=20Standby=20mode=20is=20enabled=20= (i.e.,=20on=20read-only=20physical=20replicas).=0A+=20=20=20=20=20=20= =0A=20=20=20=20=20=20=0A=20=20=20=20=20=0A= =20=0A@@=20-430,6=20+434,10=20@@=20PostgreSQL=20documentation=0A=20=20=20= =20=20=20=20=20practice.=20=20It=20may=20also=20cause=20corruption=20= checking=20to=20take=20considerably=0A=20=20=20=20=20=20=20=20longer=20= and=20consume=20considerably=20more=20resources=20on=20the=20server.=0A=20= =20=20=20=20=20=20=0A+=20=20=20=20=20=20=0A+=20=20=20=20=20=20= =20Note=20that=20=20cannot=20be=20used=20= when=20Hot=20Standby=0A+=20=20=20=20=20=20=20mode=20is=20enabled=20= (i.e.,=20on=20read-only=20physical=20replicas).=0A+=20=20=20=20=20=20= =0A=20=20=20=20=20=20=0A=20=20=20=20=20=0A= =20=20=20=20=0Adiff=20--git=20= a/src/bin/pg_amcheck/pg_amcheck.c=20b/src/bin/pg_amcheck/pg_amcheck.c=0A= index=20ec04b977de..66d18b09fb=20100644=0A---=20= a/src/bin/pg_amcheck/pg_amcheck.c=0A+++=20= b/src/bin/pg_amcheck/pg_amcheck.c=0A@@=20-816,6=20+816,10=20@@=20= main(int=20argc,=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=20unlogged=20tables=20when=20in=0A+=20*=20= recovery=20and=20temporary=20tables,=20as=20checking=20them=20would=20= needlessly=20draw=20errors=0A+=20*=20from=20the=20underlying=20amcheck=20= function.=0A+=20*=0A=20=20*=20sql:=20buffer=20into=20which=20the=20heap=20= table=20checking=20command=20will=20be=20written=0A=20=20*=20rel:=20= relation=20information=20for=20the=20heap=20table=20to=20be=20checked=0A=20= =20*=20conn:=20the=20connection=20to=20be=20used,=20for=20string=20= escaping=20purposes=0A@@=20-825,10=20+829,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+842,11=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'=20"=0A+=09=09=09=09=09=20=20"AND=20(c.relpersistence=20!=3D=20'u'=20= OR=20NOT=20pg_is_in_recovery())",=0A+=09=09=09=09=09=20=20rel->reloid);=0A= =20}=0A=20=0A=20/*=0A@@=20-849,6=20+857,11=20@@=20= prepare_heap_command(PQExpBuffer=20sql,=20RelationInfo=20*rel,=20PGconn=20= *conn)=0A=20=20*=20functions=20do=20not=20return=20any,=20but=20rather=20= return=20corruption=20information=20by=0A=20=20*=20raising=20errors,=20= which=20verify_btree_slot_handler=20expects.=0A=20=20*=0A+=20*=20The=20= constructed=20SQL=20command=20will=20silently=20skip=20unlogged=20= indexed=20when=20in=0A+=20*=20recovery,=20temporary=20indexes,=20and=20= indexes=20being=20reindexed=20concurrently,=20as=0A+=20*=20checking=20= them=20would=20needlessly=20draw=20errors=20from=20the=20underlying=20= amcheck=0A+=20*=20functions.=0A+=20*=0A=20=20*=20sql:=20buffer=20into=20= which=20the=20heap=20table=20checking=20command=20will=20be=20written=0A=20= =20*=20rel:=20relation=20information=20for=20the=20index=20to=20be=20= checked=0A=20=20*=20conn:=20the=20connection=20to=20be=20used,=20for=20= string=20escaping=20purposes=0A@@=20-858,27=20+871,33=20@@=20= prepare_btree_command(PQExpBuffer=20sql,=20RelationInfo=20*rel,=20PGconn=20= *conn)=0A=20{=0A=20=09resetPQExpBuffer(sql);=0A=20=0A-=09/*=0A-=09=20*=20= Embed=20the=20database,=20schema,=20and=20relation=20name=20in=20the=20= query,=20so=20if=20the=0A-=09=20*=20check=20throws=20an=20error,=20the=20= user=20knows=20which=20relation=20the=20error=20came=0A-=09=20*=20from.=0A= -=09=20*/=0A=20=09if=20(opts.parent_check)=0A=20=09=09= appendPQExpBuffer(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=20= c.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=20c,=20= pg_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=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=20(c.relpersistence=20!=3D=20= 'u'=20OR=20NOT=20pg_is_in_recovery())=20"=0A+=09=09=09=09=09=09=20=20= "AND=20i.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=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=09= else=0A=20=09=09appendPQExpBuffer(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=20c.oid,=20heapallindexed=20:=3D=20%s)"=0A+=09=09=09=09=09=09= =20=20"\nFROM=20pg_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=20c.oid=20=3D=20i.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=20= (c.relpersistence=20!=3D=20'u'=20OR=20NOT=20pg_is_in_recovery())=20"=0A+=09= =09=09=09=09=09=20=20"AND=20i.indisready=20AND=20i.indisvalid=20AND=20= i.indislive",=0A=20=09=09=09=09=09=09=20=20rel->datinfo->amcheck_schema,=0A= -=09=09=09=09=09=09=20=20rel->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+1105,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+1910,18=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=20unlogged=20tables=20and=20indexes=20if=20running=20in=20= recovery,=20as=20they=0A+=09=20*=20won't=20have=20relation=20forks=20to=20= check,=20and=20indexes=20marked=20invalid=20or=20not=0A+=09=20*=20ready,=20= but=20we=20delay=20that=20decision=20until=20firing=20off=20the=20= amcheck=20command,=0A+=09=20*=20as=20the=20system=20may=20exit=20= recovery=20or=20indexes=20may=20become=20valid=20by=20that=0A+=09=20*=20= time.=0A+=09=20*/=0A+=09appendPQExpBufferStr(&sql,=20"\nWHERE=20= c.relpersistence=20!=3D=20't'");=0A=20=09if=20(opts.excludetbl=20||=20= opts.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+1973,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+1992,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+2031,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..d711d46298=0A---=20/dev/null=0A+++=20= b/src/bin/pg_amcheck/t/006_bad_targets.pl=0A@@=20-0,0=20+1,225=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=20checking=20= inappropriate=20targets=20from=20SQL=20fails=20sensibly=0A+my=20= ($stdout,=20$stderr);=0A+=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/NOTICE:=20=20cannot=20= verify=20unlogged=20relation=20"unlogged_heap"=20during=20recovery,=20= skipping/,=0A+=09=20"checking=20unlogged=20table=20during=20recovery=20= fails=20appropriately"=20],=0A+=0A+=09[=20$node_standby=20=3D>=20"SELECT=20= *=20FROM=20bt_index_check('unlogged_btree')",=0A+=09=20=20qr/NOTICE:=20=20= cannot=20verify=20unlogged=20index=20"unlogged_btree"=20during=20= recovery,=20skipping/,=0A+=09=20"checking=20unlogged=20btree=20index=20= during=20recovery=20fails=20appropriately"=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=20and=20unlogged=20tables=20as=20appropriate,=0A= +#=20without=20raising=20any=20warnings=20or=20exiting=20with=20a=20= non-zero=20status=0A+#=0A+$node_primary->command_checks_all(=0A+=09= ['pg_amcheck',=20'--all',=20'-D',=20'template1'],=0A+=090,=0A+=09[=20= qr/^$/=20],=0A+=09[=20qr/^$/=20],=0A+=09'pg_amcheck=20all=20objects=20on=20= primary');=0A+=0A+$node_standby->command_checks_all(=0A+=09= ['pg_amcheck',=20'--all',=20'-D',=20'template1'],=0A+=090,=0A+=09[=20= qr/^$/=20],=0A+=09[=20qr/^$/=20],=0A+=09'pg_amcheck=20all=20objects=20on=20= standby');=0A+=0A+#=20Verify=20that=20explicitly=20asking=20for=20= unlogged=20relations=20to=20be=20checked=20does=20not=0A+#=20raise=20any=20= warnings=20or=20exit=20with=20a=20non-zero=20exit=20status,=20even=20= when=20they=20cannot=0A+#=20be=20checked=20due=20to=20recovery=20being=20= in=20progress.=0A+#=0A+#=20These=20relations=20will=20have=20no=20= relation=20fork=20during=20recovery,=20so=20even=20without=0A+#=20= checking=20them,=20we=20can=20say=20(by=20definition)=20that=20they=20= are=20not=20corrupt,=20because=0A+#=20it=20is=20meaningless=20to=20= declare=20a=20non-existent=20relation=20fork=20corrupt=0A+#=0A= +$node_primary->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=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,=20--rootdescend,=20and=20= --parent-check=0A+#=20do=20not=20cause=20failures=20in=20these=20modes.=20= =20At=20least=20--parent-check=20must=20be=0A+#=20dropped=20when=20in=20= recovery,=20but=20just=20check=20them=20all.=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=20= primary');=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=20standby');=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_standby->command_checks_all(=0A+=09['pg_amcheck',=20'--all',=20= '-D',=20'template1',=20'--rootdescend'],=0A+=092,=0A+=09[=20qr/ERROR:=20=20= cannot=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= --rootdescend=20on=20standby');=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+$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+#=20Unlike=20= above,=20when=20we=20ask=20both=20for=20the=20temporary=20relations=20= and=20the=20unlogged=0A+#=20relations,=20we=20do=20have=20at=20least=20= something=20(the=20unlogged=20ones)=20to=20check.=0A+#=0A= +$node_primary->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= primary');=0A+=0A+$node_standby->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=20standby');=0A--=20=0A2.21.1=20= (Apple=20Git-122.3)=0A=0A= --Apple-Mail=_2EC3B1C0-5011-48DD-9394-77309E3BD4C8 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=_2EC3B1C0-5011-48DD-9394-77309E3BD4C8--