Hello hackers,
In some cases if PostgreSQL encounters with wraparound PostgreSQL might
leave created temporary tables even after shutdown.
This orphan temporary tables prevent VACUUM to fix wraparound. It is
because in single mode VACUUM considers orphan temp tables as temp
tables of other backends.
Grigory reported that one of our client did stuck with fixing wraparound
by because he didn't know that he has orphaned temp tables left by a
backend after wraparound.
This patch fixes the issue. With it VACUUM deletes orphaned tables in
single mode.
See also thread in general (I'm not sure that orphan temp tables were
cause here though):
https://www.postgresql.org/message-id/CADU5SwN6u4radqQgUY2VjEyqXF0KJ6A09PYuJjT%3Do9d7vzM%3DCg%40mail.gmail.com
If the patch is interesting I'll add it to the next commitfest and label
it as 'v13'.
--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 1c4fc40489..46f3848038 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -705,6 +705,35 @@ HINT: Stop the postmaster and vacuum that database in single-user mode.
nominally disabled.
</para>
</sect3>
+
+ <sect3 id="temporary-tables-and-wraparound">
+ <title>Temporary Tables and Wraparound</title>
+
+ <para>
+ In long sessions, having multiple temporary tables that are not properly
+ cleaned up can cause wraparound. To avoid this issue, you can manually
+ remove temporary tables that are no longer required.
+ </para>
+ <para>
+ If the wraparound has already occurred and the server has stopped accepting
+ commands, you can do the following:
+ <orderedlist>
+ <listitem>
+ <para>
+ Stop the server and launch it in the
+ <link linkend="app-postgres-single-user">single-user mode</link>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Run the <command>VACUUM</command> command to remove orphaned temporary
+ tables.
+ </para>
+ </listitem>
+ </orderedlist>
+ Once this operation completes, you can restart the server as usual.
+ </para>
+ </sect3>
</sect2>
<sect2 id="autovacuum">
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index e91df2171e..e4eac7eccd 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -30,11 +30,13 @@
#include "access/multixact.h"
#include "access/transam.h"
#include "access/xact.h"
+#include "catalog/dependency.h"
#include "catalog/namespace.h"
#include "catalog/pg_database.h"
#include "catalog/pg_inherits.h"
#include "catalog/pg_namespace.h"
#include "commands/cluster.h"
+#include "commands/dbcommands.h"
#include "commands/vacuum.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
@@ -47,6 +49,7 @@
#include "utils/acl.h"
#include "utils/fmgroids.h"
#include "utils/guc.h"
+#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/snapmgr.h"
#include "utils/syscache.h"
@@ -1638,7 +1641,33 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params)
*/
if (RELATION_IS_OTHER_TEMP(onerel))
{
- relation_close(onerel, lmode);
+ /*
+ * But in single-user mode this is an orphan temp table. We should drop
+ * it to resolve possible wraparound.
+ */
+ if (!IsUnderPostmaster && MyAuxProcType == NotAnAuxProcess)
+ {
+ ObjectAddress object;
+
+ ereport(LOG,
+ (errmsg("dropping orphan temp table \"%s.%s.%s\"",
+ get_database_name(MyDatabaseId),
+ get_namespace_name(onerel->rd_rel->relnamespace),
+ NameStr(onerel->rd_rel->relname))));
+
+ relation_close(onerel, lmode);
+
+ object.classId = RelationRelationId;
+ object.objectId = relid;
+ object.objectSubId = 0;
+ performDeletion(&object, DROP_CASCADE,
+ PERFORM_DELETION_INTERNAL |
+ PERFORM_DELETION_QUIETLY |
+ PERFORM_DELETION_SKIP_EXTENSIONS);
+ }
+ else
+ relation_close(onerel, lmode);
+
PopActiveSnapshot();
CommitTransactionCommand();
return false;
