Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ips9C-0001Kd-6u for pgsql-hackers@arkaria.postgresql.org; Fri, 10 Jan 2020 11:08:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ips9A-0002vM-Vz for pgsql-hackers@arkaria.postgresql.org; Fri, 10 Jan 2020 11:08:08 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ips99-0002uP-DE for pgsql-hackers@lists.postgresql.org; Fri, 10 Jan 2020 11:08:08 +0000 Received: from [66.111.4.26] (helo=out2-smtp.messagingengine.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ips96-0000l1-3p for pgsql-hackers@lists.postgresql.org; Fri, 10 Jan 2020 11:08:06 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.nyi.internal (Postfix) with ESMTP id 9160121FB5; Fri, 10 Jan 2020 06:07:52 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute4.internal (MEProxy); Fri, 10 Jan 2020 06:07:52 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= date:from:to:cc:subject:message-id:references:mime-version :content-type:in-reply-to; s=fm3; bh=yrCiHnuHidXCfvH83PrR3FRiHoJ HiKbrm9xQHKHC82k=; b=XbbxalTxAmtp3EJoEudGizSc5EI65SWihCiO+6ySjFr CISNc/6a446hnxmbv1WhX92pzF5LHMnyRPS6wMGoNm3eCAV0rv+oWEasJWjw8b9X 4d3nxRPcBAMVQ9TRndNbfCCypU2lZz4SPnaS7NqIofuOIHVsCLpENxgX6M5ltrUa 8NTDB2DqzudaS04kionZNZkdFq8muejzaJaB1XYCkTc9IkEs/7M+btXWxdjXL4CE g4IMLnmHEOblPpmMJGOJ/cBdTBoFBME1gRXKayUznQ/NPNrXQMcmKcuLi//vl80K TyZjJNqvfprLmkEbzKPzPeHZk1Gdq2UnRJeeVq4t2+w== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; bh=yrCiHn uHidXCfvH83PrR3FRiHoJHiKbrm9xQHKHC82k=; b=OUIZkUoS1r5Lo0yiGzjBgu In/8V/iqzrFAcAR6KRPDxzG8gu+IcouZudBoyDwbiW70ONWGPeAWD5/Y7f5rowBf 1P70wtAiiewiQt+1fWO3IV1wbuX9q7SVjoDdyEiEgTzFEHSUsRB812h+SDITRcYV NYmG2QauXpL0COfjcj/w4KSEXJK4ZuMj6XO1etfOFMgcpDkk+ruVvOqTMzqZ1Bq+ RSjQbpNHysOK4XwlvvWCNwq79t2CDhTSqKx3kI2QhOJcmK5J+6nwEHjNBR7ISOcr EP1FUamXR+d10sVn+0tlMGKXFzU91OQhUdZzZbmTP0FUSBdN5L4KeYbHUndiHYXw == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedufedrvdeifedgvdduucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne gfrhhlucfvnfffucdljedtmdenucfjughrpeffhffvuffkfhggtggujgesghdtreertddt vdenucfhrhhomhepofhitghhrggvlhcurfgrqhhuihgvrhcuoehmihgthhgrvghlsehprg hquhhivghrrdighiiiqeenucfkphepudduuddruddtvddrudektddrudekheenucfrrghr rghmpehmrghilhhfrhhomhepmhhitghhrggvlhesphgrqhhuihgvrhdrgiihiienucevlh hushhtvghrufhiiigvpedt X-ME-Proxy: Received: from paquier.xyz (ee0822lan1.rev.em-net.ne.jp [111.102.180.185]) by mail.messagingengine.com (Postfix) with ESMTPA id AE28E30607CD; Fri, 10 Jan 2020 06:07:50 -0500 (EST) Date: Fri, 10 Jan 2020 20:07:48 +0900 From: Michael Paquier To: Mahendra Singh Thalor Cc: Tom Lane , Robert Haas , Kyotaro Horiguchi , PostgreSQL Hackers Subject: Re: Assert failure due to "drop schema pg_temp_3 cascade" for temporary tables and \d+ is not showing any info after drooping temp table schema Message-ID: <20200110110748.GD250447@paquier.xyz> References: <31319.1578332027@sss.pgh.pa.us> <20200107002216.GA2386@paquier.xyz> <14868.1578420368@sss.pgh.pa.us> <20200108004409.GA3413@paquier.xyz> <5602.1578444917@sss.pgh.pa.us> <20200108015601.GB3413@paquier.xyz> <20200109040612.GG2251@paquier.xyz> <20200110080125.GA250447@paquier.xyz> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="DO5DiztRLs659m5i" Content-Disposition: inline In-Reply-To: <20200110080125.GA250447@paquier.xyz> X-Host-Lookup-Failed: Reverse DNS lookup failed for 66.111.4.26 (deferred) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --DO5DiztRLs659m5i Content-Type: multipart/mixed; boundary="fWddYNRDgTk9wQGZ" Content-Disposition: inline --fWddYNRDgTk9wQGZ Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Fri, Jan 10, 2020 at 05:01:25PM +0900, Michael Paquier wrote: > This makes me wonder how much we should try to outsmart somebody which > puts the catalogs in such a inconsistent state. Hmm. Perhaps at the > end autovacuum should just ignore such entries and just don't help the > user at all as this also comes with its own issues with the storage > level as well as smgr.c uses rd_backend. And if the user plays with > temporary namespaces like that with superuser rights, he likely knows > what he is doing. Perhaps not :D, in which case autovacuum may not be > the best thing to decide that. I still think we should make the log > of autovacuum.c for orphaned relations more careful with its coding > though, and fix it with the previous patch. The documentation of > isTempNamespaceInUse() could gain in clarity, just a nit from me while > looking at the surroundings. And actually I found an issue with its > logic, as the routine would not consider a temp namespace in use for a > session's own MyBackendId. As that's only used for autovacuum, this > has no consequence, but let's be correct in hte long run. >=20 > And this gives the attached after a closer lookup. Thoughts? Thinking more about it, this has a race condition if a temporary schema is removed after collecting the OIDs in the drop phase. So the updated attached is actually much more conservative and does not need an update of the log message, without giving up on the improvements done in v11~. In 9.4~10, the code of the second phase relies on GetTempNamespaceBackendId() which causes an orphaned relation to not be dropped in the event of a missing namespace. I'll just leave that alone for a couple of days now.. -- Michael --fWddYNRDgTk9wQGZ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="drop-temp-schema-adjust-v5.patch" Content-Transfer-Encoding: quoted-printable diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespac= e.c index c82f9fc4b5..0987986a8f 100644 --- a/src/backend/catalog/namespace.c +++ b/src/backend/catalog/namespace.c @@ -3235,10 +3235,14 @@ isTempNamespaceInUse(Oid namespaceId) =20 backendId =3D GetTempNamespaceBackendId(namespaceId); =20 - if (backendId =3D=3D InvalidBackendId || - backendId =3D=3D MyBackendId) + /* No such temporary namespace? */ + if (backendId =3D=3D InvalidBackendId) return false; =20 + /* Is the namespace used by this backend? */ + if (backendId =3D=3D MyBackendId) + return true; + /* Is the backend alive? */ proc =3D BackendIdGetProc(backendId); if (proc =3D=3D NULL) diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/a= utovacuum.c index f0e40e36af..9eb8132a37 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -2071,9 +2071,11 @@ do_autovacuum(void) { /* * We just ignore it if the owning backend is still active and - * using the temporary schema. + * using the temporary schema. If the namespace does not exist + * ignore the entry. */ - if (!isTempNamespaceInUse(classForm->relnamespace)) + if (!isTempNamespaceInUse(classForm->relnamespace) && + get_namespace_name(classForm->relnamespace) !=3D NULL) { /* * The table seems to be orphaned -- although it might be that @@ -2202,6 +2204,7 @@ do_autovacuum(void) Oid relid =3D lfirst_oid(cell); Form_pg_class classForm; ObjectAddress object; + char *nspname; =20 /* * Check for user-requested abort. @@ -2243,7 +2246,15 @@ do_autovacuum(void) continue; } =20 - if (isTempNamespaceInUse(classForm->relnamespace)) + nspname =3D get_namespace_name(classForm->relnamespace); + + /* + * Nothing to do for a relation with a missing namespace. This + * check is the same as above when building the list of orphan + * relations. + */ + if (isTempNamespaceInUse(classForm->relnamespace) || + nspname =3D=3D NULL) { UnlockRelationOid(relid, AccessExclusiveLock); continue; @@ -2253,8 +2264,7 @@ do_autovacuum(void) ereport(LOG, (errmsg("autovacuum: dropping orphan temp table \"%s.%s.%s\"", get_database_name(MyDatabaseId), - get_namespace_name(classForm->relnamespace), - NameStr(classForm->relname)))); + nspname, NameStr(classForm->relname)))); =20 object.classId =3D RelationRelationId; object.objectId =3D relid; --fWddYNRDgTk9wQGZ-- --DO5DiztRLs659m5i Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAl4YWwMACgkQnvQgOdby QH2U8Q//Uy85/hvxPsBfnkB2v9bjfdzd1fnLr6H+gGf4mTJ4sQevP7CNanPOmYzi m0sCO8LZwvKSZyshXsH8m0rPowyekX6V6oXmteidlwewdZBY12PAa+0JGB6LfCyR HKfn3KaFt1P5bCqnF4z684J545GSkGNLOtOZEti39PiiogpymiT4LaQACOCh/i/f Z1/q3G9ZIZVPcAIAAQzmf8QlwqQVHlA8UjCk0z0MDU5kF85GFn/jQTOzH6T8BuhV vNwmhGl1pOGhPpWLiZcpsillEGokTYk6fL5PFXKfNfsJaUV/EnZ6kPHN8LQJWjWQ cBeQU/CYKpY4nqFCcWKS4dKxKh6juAKsSi906LHGMx3jmJWoeqUqyQR6j2qhq7zR gUolQkoVB3JtbGAk9dg4j3gJ1z8ucD5SfcMn5F3FhuKTlFYhKZmlZH0HedlD1868 ZxM/PKd9TWoHe5VCsK1ovtGH0vcuXr5yXESHpoBFCOOZp5wBwoOE++vXdd5XLie2 vxhdA6NZS+ohZKH0JZW959rG4EeKrKfREEq28s8HdmT3+5GGn4qNG7d1s1UnjipE Kur/KRhE8NTD+EwRk0DaAyBGW4bLYaGczx4kdF84roni4Y2t6XoxAvIvC7x5irLG PXIZtDwEr5UDXSDzemBaSOKVw4+TBj5exRrxmi9bAiBWJTQOol0= =ce3r -----END PGP SIGNATURE----- --DO5DiztRLs659m5i--