Date: Fri, 14 Dec 2018 14:26:58 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: removal of dangling temp tables
Message-ID: <20181214172658.3b7esbo3dm3gputq@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="z7adugs2f544eilm"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CA+TgmoYdgL7pRBAkucZoZriVExJvS+TqGZ7yZNF0roi7FiHwLA@mail.gmail.com>
User-Agent: NeoMutt/20180716
Precedence: bulk


--z7adugs2f544eilm
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On 2018-Dec-14, Robert Haas wrote:

> On Fri, Dec 14, 2018 at 11:29 AM Alvaro Herrera
> <alvherre@2ndquadrant.com> wrote:
> > I think the best way to fix this is to call RemoveTempRelations()
> > unconditionally at session start (without doing the rest of the temp
> > table setup, just the removal.)
> 
> That would certainly simplify things.  I think I thought about that as
> far back as a734fd5d1c309cc553b7c8c79fba96218af090f7 but it seemed
> like a significant behavior change and I wasn't sure that everyone
> would like it.  In particular, it adds overhead to backend startup
> that, in the case of a large temp schema, could be fairly long.

Hmm, I think in the case covered by your commit, that is a session that
crashes with a few thousands of temp tables, this new patch might cause
a failure to open a new session altogether.

Maybe it'd be better to change temp table removal to always drop
max_locks_per_transaction objects at a time (ie. commit/start a new
transaction every so many objects).

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--z7adugs2f544eilm
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="clean-temp-ns-startup.patch"

diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c
index 13a24631fc..003bbabf1f 100644
--- a/src/backend/catalog/namespace.c
+++ b/src/backend/catalog/namespace.c
@@ -4118,6 +4118,26 @@ RemoveTempRelations(Oid tempNamespaceId)
 }
 
 /*
+ * Remove temp tables, without relying on myTempNamespace being set, and
+ * without setting it.  This is used at session start.
+ */
+void
+InitRemoveTempRelations(void)
+{
+	Oid			namespaceOid;
+	char		namespaceName[NAMEDATALEN];
+
+	Assert(MyBackendId != InvalidBackendId);
+
+	snprintf(namespaceName, sizeof(namespaceName), "pg_temp_%d", MyBackendId);
+
+	namespaceOid = get_namespace_oid(namespaceName, true);
+
+	if (OidIsValid(namespaceOid))
+		RemoveTempRelations(namespaceOid);
+}
+
+/*
  * Callback to remove temp relations at backend exit.
  */
 static void
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index b636b1e262..b41b47222c 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -1074,6 +1074,10 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username,
 	if (!bootstrap)
 		pgstat_bestart();
 
+	/* Remove any temp tables that might have leaked previously */
+	if (!bootstrap)
+		InitRemoveTempRelations();
+
 	/* close the transaction we started above */
 	if (!bootstrap)
 		CommitTransactionCommand();
diff --git a/src/include/catalog/namespace.h b/src/include/catalog/namespace.h
index 0e202372d5..f90eeff1b7 100644
--- a/src/include/catalog/namespace.h
+++ b/src/include/catalog/namespace.h
@@ -144,6 +144,7 @@ extern void GetTempNamespaceState(Oid *tempNamespaceId,
 					  Oid *tempToastNamespaceId);
 extern void SetTempNamespaceState(Oid tempNamespaceId,
 					  Oid tempToastNamespaceId);
+extern void InitRemoveTempRelations(void);
 extern void ResetTempTableNamespace(void);
 
 extern OverrideSearchPath *GetOverrideSearchPath(MemoryContext context);

--z7adugs2f544eilm--