Patch to remove/report orphaned files - Mailing list pgsql-patches

This patch removes old sort files on postmaster startup, and reports
orphaned database directory files during database vacuum.

I have not finished testing it yet, but it is almost ready.

I don't think it is necessary to check oid at snapshot time.  It is
enough to check the minimum oid at startup of each backend.  It doesn't
lock its oid checks because the number could be incremented with no
problems.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
Index: src/backend/access/transam/varsup.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/access/transam/varsup.c,v
retrieving revision 1.38
diff -c -r1.38 varsup.c
*** src/backend/access/transam/varsup.c    2001/03/22 03:59:17    1.38
--- src/backend/access/transam/varsup.c    2001/05/25 04:36:30
***************
*** 16,22 ****
--- 16,25 ----
  #include "access/transam.h"
  #include "access/xlog.h"
  #include "storage/proc.h"
+ #include "storage/sinval.h"
+ #include "storage/sinvaladt.h"

+ SISeg       *shmInvalBuffer;

  /* Number of XIDs and OIDs to prefetch (preallocate) per XLOG write */
  #define VAR_XID_PREFETCH        1024
***************
*** 143,145 ****
--- 146,189 ----

      SpinRelease(OidGenLockId);
  }
+
+ /*
+  * GetMinBackendOid -- returns lowest oid stored on startup of
+  * each backend.
+  */
+ Oid
+ GetMinStartupOid(void)
+ {
+     SISeg       *segP = shmInvalBuffer;
+     ProcState  *stateP = segP->procState;
+     int            index;
+     Oid            min_oid;
+
+     /* prime with current oid, no need for lock */
+     min_oid = ShmemVariableCache->nextOid;
+
+     SpinAcquire(SInvalLock);
+
+     for (index = 0; index < segP->lastBackend; index++)
+     {
+         SHMEM_OFFSET pOffset = stateP[index].procStruct;
+
+         if (pOffset != INVALID_OFFSET)
+         {
+             PROC       *proc = (PROC *) MAKE_PTR(pOffset);
+             Oid            proc_oid;
+
+             proc_oid = proc->startOid;    /* we don't use spin-locking in
+                                      * AbortTransaction() ! */
+             if (proc == MyProc || proc_oid <= BootstrapObjectIdData)
+                 continue;
+             if (proc_oid < min_oid)
+                 min_oid = proc_oid;
+         }
+     }
+
+     SpinRelease(SInvalLock);
+     return min_oid;
+ }
+
+
Index: src/backend/commands/vacuum.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.193
diff -c -r1.193 vacuum.c
*** src/backend/commands/vacuum.c    2001/05/18 21:24:18    1.193
--- src/backend/commands/vacuum.c    2001/05/25 04:36:32
***************
*** 16,24 ****
--- 16,27 ----

  #include <fcntl.h>
  #include <unistd.h>
+ #include <stdlib.h>
+ #include <limits.h>
  #include <time.h>
  #include <sys/time.h>
  #include <sys/types.h>
+ #include <dirent.h>
  #include <sys/file.h>
  #include <sys/stat.h>

***************
*** 30,35 ****
--- 33,39 ----

  #include "access/genam.h"
  #include "access/heapam.h"
+ #include "access/transam.h"
  #include "access/xlog.h"
  #include "catalog/catalog.h"
  #include "catalog/catname.h"
***************
*** 159,164 ****
--- 163,169 ----
  static bool enough_space(VacPage vacpage, Size len);
  static void init_rusage(VacRUsage *ru0);
  static char *show_rusage(VacRUsage *ru0);
+ static void report_orphans(void);


  /*
***************
*** 236,241 ****
--- 241,250 ----

      /* clean up */
      vacuum_shutdown();
+
+     if (VacRelName == NULL)
+         report_orphans();
+
  }

  /*
***************
*** 2646,2648 ****
--- 2655,2723 ----

      return result;
  }
+
+ /*
+  * report_orphans
+  *
+  * Report files that are not referenced by any pg_class.relfilenode.
+  * Could be caused by backend crash no cleaning up.
+  */
+ static void
+ report_orphans(void)
+ {
+     DIR           *db_dir;
+     struct dirent  *db_de;
+     Relation    rel;
+     TupleDesc    tupdesc;
+     HeapScanDesc scan;
+     HeapTuple    tuple;
+     Oid            dir_file_oid;
+     Oid            rel_file_oid;
+     Datum        d;
+     bool        n;
+     bool        match_found;
+
+     rel = heap_openr(RelationRelationName, AccessShareLock);
+     db_dir = opendir(".");
+     Assert(db_dir);
+
+     /*
+      * Cycle through directory and check each file against
+      * pg_class.relfilenode.
+      */
+     while ((db_de = readdir(db_dir)) != NULL)
+     {
+         if (strspn(db_de->d_name, "0123456789") ==
+             strlen(db_de->d_name))
+         {
+             dir_file_oid = (Oid) strtoul((db_de->d_name), NULL, 10);
+
+             if (dir_file_oid >= GetMinStartupOid() ||
+                 dir_file_oid <= BootstrapObjectIdData)
+                 continue;
+
+             tupdesc = RelationGetDescr(rel);
+
+             match_found = false;
+             scan = heap_beginscan(rel, false, SnapshotNow, 0, (ScanKey) NULL);
+             while (HeapTupleIsValid(tuple = heap_getnext(scan, 0)))
+             {
+                 d = heap_getattr(tuple, Anum_pg_class_relfilenode, tupdesc, &n);
+                 rel_file_oid = DatumGetObjectId(d);
+                 if (dir_file_oid == rel_file_oid)
+                 {
+                     match_found = true;
+                     break;
+                 }
+             }
+             heap_endscan(scan);
+             if (!match_found)
+                 elog(NOTICE, "Unreferenced file found:  %s", db_de->d_name);
+             /* Maybe one day we can unlink too.  bjm 2001-05-24 */
+         }
+     }
+
+     heap_close(rel, AccessShareLock);
+ }
+
+
Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.212
diff -c -r1.212 postmaster.c
*** src/backend/postmaster/postmaster.c    2001/04/19 19:09:23    1.212
--- src/backend/postmaster/postmaster.c    2001/05/25 04:36:33
***************
*** 58,63 ****
--- 58,64 ----
  #include <ctype.h>
  #include <sys/types.h>
  #include <sys/stat.h>
+ #include <dirent.h>
  #include <sys/time.h>
  #include <sys/socket.h>
  #include <errno.h>
***************
*** 243,248 ****
--- 244,250 ----
  static void SignalChildren(int signal);
  static int    CountChildren(void);
  static bool CreateOptsFile(int argc, char *argv[]);
+ static void RemovePgSorttemp(void);

  static pid_t SSDataBase(int xlop);

***************
*** 595,600 ****
--- 597,605 ----
      if (!CreateDataDirLockFile(DataDir, true))
          ExitPostmaster(1);

+     /* Remove old sort files */
+     RemovePgSorttemp();
+
      /*
       * Establish input sockets.
       */
***************
*** 2449,2452 ****
--- 2454,2499 ----

      fclose(fp);
      return true;
+ }
+
+
+ /*
+  * Remove old sort files
+  */
+ static void
+ RemovePgSorttemp(void)
+ {
+     char         db_path[MAXPGPATH];
+     char         temp_path[MAXPGPATH];
+     char         rm_path[MAXPGPATH];
+     DIR           *db_dir;
+     DIR           *temp_dir;
+     struct dirent  *db_de;
+     struct dirent  *temp_de;
+
+     /*
+      * Cycle through pg_tempsort for all databases and
+      * and remove old sort files.
+      */
+     /* trailing slash forces symlink following */
+     snprintf(db_path, sizeof(db_path), "%s/base/",    DataDir);
+     if ((db_dir = opendir(db_path)) != NULL)
+         while ((db_de = readdir(db_dir)) != NULL)
+         {
+             snprintf(temp_path, sizeof(temp_path),
+                 "%s/%s/%s/",    db_path, db_de->d_name, SORT_TEMP_DIR);
+             if ((temp_dir = opendir(temp_path)) != NULL)
+                 while ((temp_de = readdir(temp_dir)) != NULL)
+                 {
+                     if (strspn(temp_de->d_name, "0123456789.") ==
+                         strlen(temp_de->d_name))
+                     {
+                         snprintf(rm_path, sizeof(temp_path),
+                             "%s/%s/%s/%s",
+                             db_path, db_de->d_name,
+                             SORT_TEMP_DIR, temp_de->d_name);
+                         unlink(rm_path);
+                     }
+                 }
+         }
  }
Index: src/backend/storage/file/fd.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/storage/file/fd.c,v
retrieving revision 1.76
diff -c -r1.76 fd.c
*** src/backend/storage/file/fd.c    2001/04/03 04:07:02    1.76
--- src/backend/storage/file/fd.c    2001/05/25 04:36:40
***************
*** 742,762 ****
  File
  OpenTemporaryFile(void)
  {
!     char        tempfilename[64];
      File        file;

      /*
       * Generate a tempfile name that's unique within the current
       * transaction
       */
!     snprintf(tempfilename, sizeof(tempfilename),
!              "pg_sorttemp%d.%ld", MyProcPid, tempFileCounter++);

      /* Open the file */
!     file = FileNameOpenFile(tempfilename,
                              O_RDWR | O_CREAT | O_TRUNC | PG_BINARY, 0600);
      if (file <= 0)
!         elog(ERROR, "Failed to create temporary file %s", tempfilename);

      /* Mark it for deletion at close or EOXact */
      VfdCache[file].fdstate |= FD_TEMPORARY;
--- 742,770 ----
  File
  OpenTemporaryFile(void)
  {
!     char        tempfilepath[128];
      File        file;

      /*
       * Generate a tempfile name that's unique within the current
       * transaction
       */
!     snprintf(tempfilepath, sizeof(tempfilepath),
!              "%s%c%d.%ld", SORT_TEMP_DIR, SEP_CHAR, MyProcPid,
!              tempFileCounter++);

      /* Open the file */
!     file = FileNameOpenFile(tempfilepath,
                              O_RDWR | O_CREAT | O_TRUNC | PG_BINARY, 0600);
      if (file <= 0)
!     {
!         /* mkdir could fail if some one else already created it */
!         mkdir(SORT_TEMP_DIR, S_IRWXU);
!         file = FileNameOpenFile(tempfilepath,
!                             O_RDWR | O_CREAT | O_TRUNC | PG_BINARY, 0600);
!         if (file <= 0)
!             elog(ERROR, "Failed to create temporary file %s", tempfilepath);
!     }

      /* Mark it for deletion at close or EOXact */
      VfdCache[file].fdstate |= FD_TEMPORARY;
Index: src/backend/storage/lmgr/proc.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/storage/lmgr/proc.c,v
retrieving revision 1.100
diff -c -r1.100 proc.c
*** src/backend/storage/lmgr/proc.c    2001/03/22 06:16:17    1.100
--- src/backend/storage/lmgr/proc.c    2001/05/25 04:36:40
***************
*** 261,266 ****
--- 261,267 ----
      MyProc->databaseId = MyDatabaseId;
      MyProc->xid = InvalidTransactionId;
      MyProc->xmin = InvalidTransactionId;
+     MyProc->startOid = ShmemVariableCache->nextOid;
      MyProc->waitLock = NULL;
      MyProc->waitHolder = NULL;
      SHMQueueInit(&(MyProc->procHolders));
Index: src/include/access/transam.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/access/transam.h,v
retrieving revision 1.33
diff -c -r1.33 transam.h
*** src/include/access/transam.h    2001/05/14 20:30:21    1.33
--- src/include/access/transam.h    2001/05/25 04:36:43
***************
*** 133,138 ****
--- 133,139 ----
  extern void ReadNewTransactionId(TransactionId *xid);
  extern void GetNewObjectId(Oid *oid_return);
  extern void CheckMaxObjectId(Oid assigned_oid);
+ extern Oid GetMinStartupOid(void);

  /* ----------------
   *        global variable extern declarations
Index: src/include/storage/fd.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/storage/fd.h,v
retrieving revision 1.27
diff -c -r1.27 fd.h
*** src/include/storage/fd.h    2001/02/18 04:39:42    1.27
--- src/include/storage/fd.h    2001/05/25 04:36:43
***************
*** 39,44 ****
--- 39,46 ----
   * FileSeek uses the standard UNIX lseek(2) flags.
   */

+ #define SORT_TEMP_DIR "pg_sorttemp"
+
  typedef char *FileName;

  typedef int File;
Index: src/include/storage/proc.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/storage/proc.h,v
retrieving revision 1.41
diff -c -r1.41 proc.h
*** src/include/storage/proc.h    2001/03/22 04:01:08    1.41
--- src/include/storage/proc.h    2001/05/25 04:36:43
***************
*** 50,55 ****
--- 50,58 ----
                                   * were starting our xact: vacuum must not
                                   * remove tuples deleted by xid >= xmin ! */

+     Oid            startOid;        /* oid at startup, used by vacuum to find
+                                  * orphaned files.
+                                  */
      /*
       * XLOG location of first XLOG record written by this backend's
       * current transaction.  If backend is not in a transaction or hasn't