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 1mX9qQ-0003AA-PE for pgsql-hackers@arkaria.postgresql.org; Sun, 03 Oct 2021 22:20:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mX9qO-0004nQ-81 for pgsql-hackers@arkaria.postgresql.org; Sun, 03 Oct 2021 22:20:28 +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 1mX9qN-0004nI-Gx for pgsql-hackers@lists.postgresql.org; Sun, 03 Oct 2021 22:20:27 +0000 Received: from mail-pf1-x42a.google.com ([2607:f8b0:4864:20::42a]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mX9qK-0000jZ-1h for pgsql-hackers@postgresql.org; Sun, 03 Oct 2021 22:20:26 +0000 Received: by mail-pf1-x42a.google.com with SMTP id m5so132370pfk.7 for ; Sun, 03 Oct 2021 15:20:23 -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=2Gp6D4a7vnEnDvb8KXSbXJwQ60GLiYxp+QyYMkG1BzY=; b=k5wqJSI/KbZ4f3EO9c8jpDfs/lD7P5MR7Jmz+GKTjtYEySa2QfzK/I92IV9FcL0AaA 9RIswR84iP7k5vMxOeHTGMY582Q1K757AtooKZvZ3CiUsmDt2zfxV4N/x9sd7SuKXhLQ JzSU69/0s7CRHo6n3NTKOQvF4bVZaHth7ZRyB4V7m9dPzo3qwfyywGpe7pF8uIowY/94 4izk4O+zbhUIGkHnHANfI8W8kSj5UpHKn8kc1lJ3wVLdlhPHuIAqrOVyrGpNK4yEvOB3 eATQIA1I2PG264DHzZlD+hMecbVw8idH+QwJdNGIQ5m2G0dmG4EnxjWAOQdbSPoDWHMv 8YhQ== 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=2Gp6D4a7vnEnDvb8KXSbXJwQ60GLiYxp+QyYMkG1BzY=; b=OsPgmVgPJCqOLqgHyjG+UmlSVAIok0ow6SNiYKif9ycxZg6EZEbgreyh1oYWw1iP8/ ltP693y8NB5CAtgOYgtNL209jBtr+XvNGQ0AdeDRNbTruh4I99dGLgjX/y+kbjHZeChw 9MSFIlB6vQpDK7+nLi52PoEb324sgaw2+cqyqWvHyVwRFslaWhwsUhgqemguIEeMlxFl iYg2niSzparLu1m30c3gQ+eFzhIGrhewWOS7kXmJKas/Tysnw97CS7lLgrVgdZaHV7Ri NUHSst7e2tNpTk8Tr8GSgDknqTMae/AcRp9ZyeBH525YsQ7dHrN+aZTUzm/FWwjwSeQA GkCw== X-Gm-Message-State: AOAM533/cOz9IDnqvX433rlOiqOhuGFtynWF+yV7k6qWEkX179wOCHJt FnEQ/yaGNGHHehdFNjulMM/d4h9hPoys9kLKQRCJL2DNFXpbcGjAXeTv0yVXHjMwvB/kiJ3ivtG 1zfhs15lcaTVVrExndEnFgdvP4NSDux5iemuf30Olb1irRiFazMALDZRj0JUsqDJAyjxmdjG2jj kreZSH5vWs4thTpbNmYyvnOq3cTs+/D7RlEUX7G9qJjFoROSvRaoTW1FxXd/5egjz3Bf+6JSMxP jlvIxu536bbEUfzkVxTYOeamFssELhyABHm X-Google-Smtp-Source: ABdhPJzdJbkAyn9TZZ8KhzcTCxn8AmvYY8bVfCjrlnH+Pzk5Ez0jMNspAu5RQIgaToOp8qJAUVAzIA== X-Received: by 2002:a63:4c0e:: with SMTP id z14mr8038232pga.427.1633299622487; Sun, 03 Oct 2021 15:20:22 -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 l128sm12575465pfd.106.2021.10.03.15.20.21 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sun, 03 Oct 2021 15:20:21 -0700 (PDT) From: Mark Dilger Message-Id: Content-Type: multipart/mixed; boundary="Apple-Mail=_E8E6CBEC-4575-4E1C-AB4B-2545EA857AE7" 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: Sun, 3 Oct 2021 15:20:20 -0700 In-Reply-To: Cc: Alexander Lakhin , Robert Haas , Peter Geoghegan To: PostgreSQL-development References: <17212-34dd4a1d6bba98bf@postgresql.org> 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=_E8E6CBEC-4575-4E1C-AB4B-2545EA857AE7 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On Oct 3, 2021, at 10:04 AM, Mark Dilger = wrote: >=20 >> On Oct 2, 2021, at 10:32 AM, Peter Geoghegan wrote: >>=20 >> On Sat, Oct 2, 2021 at 4:49 AM PG Bug reporting form >> wrote: >>> Although you can add --exclude-relation=3D*.pg_temp*.*, this = behaviour differs >>> from the behaviour of pg_dump and friends, which skip such relations >>> silently. >>=20 >> I agree -- this behavior is a bug. >>=20 >> Can you propose a fix, Mark? >=20 > The attached patch includes a test case for this, which shows the = problems against the current pg_amcheck.c, and a new version of = pg_amcheck.c which fixes the bug. Could you review it? >=20 > Thanks for bringing this to my attention. Reposting to pgsql-hackers in preparation for making a commitfest entry. --Apple-Mail=_E8E6CBEC-4575-4E1C-AB4B-2545EA857AE7 Content-Disposition: attachment; filename=v1-0001-Bug-fix-do-not-check-temp-tables-from-pg_amcheck.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v1-0001-Bug-fix-do-not-check-temp-tables-from-pg_amcheck.patch" Content-Transfer-Encoding: quoted-printable =46rom=20fc2e4a14113f7b3d556707f900f1c6cf2a61c601=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= v1]=20Bug=20fix:=20do=20not=20check=20temp=20tables=20from=20pg_amcheck=0A= =0AFix=20BUG=20#17212:=20pg_amcheck=20fails=20on=20checking=20temporary=20= relations=0A=0ASince=20pg_amcheck=20does=20not=20create=20temporary=20= tables=20of=20its=20own,=20all=0Atemporary=20tables=20it=20encounters=20= must=20necessarily=20belong=20to=20other=0Asessions.=20=20Check=20= neither=20temporary=20tables=20nor=20indexes=20over=20such=0Atables.=0A= ---=0A=20src/bin/pg_amcheck/pg_amcheck.c=20=20=20|=2015=20+++++++-------=0A= =20src/bin/pg_amcheck/t/003_check.pl=20|=2033=20= +++++++++++++++++++++++++++++++=0A=202=20files=20changed,=2041=20= insertions(+),=207=20deletions(-)=0A=0Adiff=20--git=20= a/src/bin/pg_amcheck/pg_amcheck.c=20b/src/bin/pg_amcheck/pg_amcheck.c=0A= index=20ec04b977de..33a9965a60=20100644=0A---=20= a/src/bin/pg_amcheck/pg_amcheck.c=0A+++=20= b/src/bin/pg_amcheck/pg_amcheck.c=0A@@=20-1888,11=20+1888,9=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.heap_only=20OR=20ep.rel_regex=20IS=20NULL)"=0A=20=09=09=09=09= =09=09=20=20"\nAND=20(c.relam=20=3D=20%u=20OR=20NOT=20ep.btree_only=20OR=20= ep.rel_regex=20IS=20NULL)",=0A=20=09=09=09=09=09=09=20=20= HEAP_TABLE_AM_OID,=20BTREE_AM_OID);=0A-=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+1942,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+1961,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+2000,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/003_check.pl=20= b/src/bin/pg_amcheck/t/003_check.pl=0Aindex=20c26e5eda67..de7814089e=20= 100644=0A---=20a/src/bin/pg_amcheck/t/003_check.pl=0A+++=20= b/src/bin/pg_amcheck/t/003_check.pl=0A@@=20-337,6=20+337,32=20@@=20= $node->command_checks_all([=20@cmd,=20'-d',=20'db1',=20'-d',=20'db2',=20= '-d',=20'db3'=20],=0A=20#=0A=20perform_all_corruptions();=0A=20=0A+#=20= Hold=20open=20an=20unrelated=20session=20with=20temporary=20tables=20= defined,=20and=20indexes=0A+#=20over=20them=20defined,=20to=20verify=20= that=20pg_amcheck=20does=20not=20fail=20attempting=20to=0A+#=20check=20= them.=20=20Check=20both=20temporary=20objects=20whose=20creation=20has=20= been=20committed=0A+#=20and=20not.=0A+#=0A+#=20BUG=20#17212:=20= pg_amcheck=20fails=20on=20checking=20temporary=20relations=0A+#=0A+my=20= ($sql_1,=20$sql_2)=20=3D=20map=20{=20qq(=0A+CREATE=20TEMPORARY=20TABLE=20= empty_tmp$_=20(i=20INTEGER)=20ON=20COMMIT=20PRESERVE=20ROWS;=0A+CREATE=20= TEMPORARY=20TABLE=20data_tmp$_=20(i=20INTEGER[],=20b=20BOX)=20ON=20= COMMIT=20PRESERVE=20ROWS;=0A+INSERT=20INTO=20data_tmp$_=20(i,=20b)=20= VALUES=20(ARRAY[1,2,3]::INTEGER[],=20'((1,2),(3,4))'::BOX);=0A+CREATE=20= INDEX=20brin_tmp$_=20ON=20data_tmp$_=20USING=20BRIN=20(b);=0A+CREATE=20= INDEX=20btree_tmp$_=20ON=20data_tmp$_=20USING=20BTREE=20(i);=0A+CREATE=20= INDEX=20gin_tmp$_=20ON=20data_tmp$_=20USING=20GIN=20(i);=0A+CREATE=20= INDEX=20gist_tmp$_=20ON=20data_tmp$_=20USING=20GIST=20(b);=0A+CREATE=20= INDEX=20hash_tmp$_=20ON=20data_tmp$_=20USING=20HASH=20(i);=0A+)=20}=20= (1,=202);=0A+my=20$in=20=3D=20'';=0A+my=20$out=20=3D=20'';=0A+my=20= $timer=20=3D=20IPC::Run::timeout(180);=0A+my=20$h=20=3D=20= $node->background_psql('db1',=20\$in,=20\$out,=20$timer);=0A+$in=20.=3D=20= qq{=0A+BEGIN;=20$sql_1;=20COMMIT;=20--=20other=20sessions=20see=20these=20= in=20the=20catalogs=0A+BEGIN;=20$sql_2;=20--=20no=20commit,=20so=20these=20= objects=20invisible=20to=20other=20sessions=0A+};=0A+$h->pump_nb;=0A=20=0A= =20#=20Checking=20databases=20with=20amcheck=20installed=20and=20corrupt=20= relations,=20pg_amcheck=0A=20#=20command=20itself=20should=20return=20= exit=20status=20=3D=202,=20because=20tables=20and=20indexes=20are=0A@@=20= -517,3=20+543,10=20@@=20$node->command_checks_all(=0A=20=09[=20@cmd,=20= '-d',=20'db1',=20'-d',=20'db2',=20'-d',=20'db3',=20'-S',=20's*'=20],=0A=20= =090,=20[$no_output_re],=20[$no_output_re],=0A=20=09'pg_amcheck=20= excluding=20all=20corrupt=20schemas');=0A+=0A+#=20Finish=20the=20= unrelated=20session.=20=20We=20could=20let=20the=20test=20cleanup=20do=20= this=20for=20us,=0A+#=20but=20we'd=20like=20to=20verify=20that=20nothing=20= pg_amcheck=20did=20above=20breaks=20this=20other=0A+#=20session.=0A+$in=20= =3D=20'COMMIT;';=0A+$h->pump_nb;=0A+$h->finish;=0A--=20=0A2.21.1=20= (Apple=20Git-122.3)=0A=0A= --Apple-Mail=_E8E6CBEC-4575-4E1C-AB4B-2545EA857AE7 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=_E8E6CBEC-4575-4E1C-AB4B-2545EA857AE7--