Обсуждение: Patch to remove sort files, temp tables, unreferenced files

The following patch does full referential checking of sort files, temp
tables, and table files.  It checks references of every file and temp
table, and reports or removes it.  The code only removes files it is
certain about.

A typical output report is:

test=> VACUUM;
NOTICE:  Unusual file found in temporary sort directory.  This file is not
         normally created by PostgreSQL and can be removed by the
         administrator using 'rm':
         /usr/var/local/postgres/data/base/18617/pg_sorttemp/x
NOTICE:  Unusual file found in database directory.  This file is not
         normally created by PostgreSQL and can be removed by the
         administrator using 'rm':
         /usr/var/local/postgres/data/base/18617/x
NOTICE:  Core file found in database directory.  If you don't need it
         for debugging, the administrator can remove it using 'rm':
         /usr/var/local/postgres/data/base/18617/core
NOTICE:  Unreferenced table file found in database directory.  This
         could have been left from a database crash.  If no one was
         using the database during VACUUM, the file can be safely
         removed by the administrator using 'rm':
         /usr/var/local/postgres/data/base/18617/33333


Most of the work is done as part of a full database VACUUM.  Postmaster
startup also cleans all sort files.

It uses a SnapshotAny read of pg_class to find referenced files.  It
does not use Oid ranges anymore.

I also had to add ShmemPIDAdd() and redo ShmemPIDLookup() because the
ShmemPIDLookup() API was weird.

--
  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: doc/src/sgml/ref/vacuum.sgml
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/ref/vacuum.sgml,v
retrieving revision 1.16
diff -c -r1.16 vacuum.sgml
*** doc/src/sgml/ref/vacuum.sgml    2001/05/25 15:45:31    1.16
--- doc/src/sgml/ref/vacuum.sgml    2001/05/28 19:06:30
***************
*** 154,160 ****

    <para>
     With no parameter, <command>VACUUM</command> processes every table in the
!    current database.  With a parameter, <command>VACUUM</command> processes
     only that table.
    </para>

--- 154,161 ----

    <para>
     With no parameter, <command>VACUUM</command> processes every table in the
!    current database. It also detects extraneous files in the
!    database directory.  With a parameter, <command>VACUUM</command> processes
     only that table.
    </para>

Index: src/backend/commands/vacuum.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.195
diff -c -r1.195 vacuum.c
*** src/backend/commands/vacuum.c    2001/05/25 15:45:32    1.195
--- src/backend/commands/vacuum.c    2001/05/28 19:06:33
***************
*** 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,42 ****
--- 33,48 ----

  #include "access/genam.h"
  #include "access/heapam.h"
+ #include "access/transam.h"
  #include "access/xlog.h"
  #include "catalog/catalog.h"
  #include "catalog/catname.h"
+ #include "catalog/heap.h"
  #include "catalog/index.h"
  #include "commands/vacuum.h"
  #include "miscadmin.h"
  #include "nodes/execnodes.h"
+ #include "storage/fd.h"
  #include "storage/sinval.h"
  #include "storage/smgr.h"
  #include "tcop/tcopprot.h"
***************
*** 159,164 ****
--- 165,175 ----
  static bool enough_space(VacPage vacpage, Size len);
  static void init_rusage(VacRUsage *ru0);
  static char *show_rusage(VacRUsage *ru0);
+ static void remove_sorttemp_files(void);
+ static void remove_temp_tables(void);
+ static void report_orphaned_files(void);
+ static Oid *get_pgclass_relfilenodes(Size *num_relfilenodes);
+ static int vc_oidcmp(const void *a, const void* b);


  /*
***************
*** 236,241 ****
--- 247,260 ----

      /* clean up */
      vacuum_shutdown();
+
+     if (VacRelName == NULL)
+     {
+         remove_sorttemp_files();
+         report_orphaned_files();
+         remove_temp_tables();
+     }
+
  }

  /*
***************
*** 2645,2648 ****
--- 2664,2916 ----
               (int) (ru1.tv.tv_usec - ru0->tv.tv_usec) / 10000);

      return result;
+ }
+
+ /*
+  * remove_sorttemp_files
+  *
+  * Remove sorttemp files not referenced by any running backend.
+  * This could be caused by backend crash not cleaning up.
+  */
+ static void
+ remove_sorttemp_files(void)
+ {
+     DIR           *temp_dir;
+     struct dirent  *temp_de;
+     char         cwd[MAXPGPATH];
+     char        temp_path[MAXPGPATH];
+     int            pid;
+
+     getcwd(cwd,MAXPGPATH);
+     temp_dir = opendir(SORT_TEMP_DIR);
+     if (!temp_dir)    /* if there is no directory, nothing to do */
+         return;
+
+     /*
+      *    Cycle through directory and check each file against
+      *    pg_class.relfilenode.
+      */
+     while ((temp_de = readdir(temp_dir)) != NULL)
+     {
+         if (strcmp(temp_de->d_name,".") == 0 ||
+             strcmp(temp_de->d_name,"..") == 0)
+                 continue;
+         if (strspn(temp_de->d_name, "0123456789.") !=
+             strlen(temp_de->d_name))
+             /* Non-numeric file names */
+             elog(NOTICE,
+                 "Unusual file found in temporary sort directory.  This file is not\n"
+                 "\t normally created by PostgreSQL and can be removed by the\n"
+                 "\t administrator using 'rm':\n\t %s/%s/%s",
+                 cwd, SORT_TEMP_DIR, temp_de->d_name);
+         else
+         {
+             /* Numeric file names;  extents clip off decimal point */
+             pid = atoi(temp_de->d_name);
+             if (pid == 0)
+                 elog(NOTICE,
+                     "Unusual file found in temporary sort directory.  This file is not\n"
+                     "\t normally created by PostgreSQL and can be removed by the\n"
+                     "\t administrator using 'rm':\n\t %s/%s/%s",
+                     cwd, SORT_TEMP_DIR, temp_de->d_name);
+             else if (ShmemPIDLookup(pid) == NULL)
+             {
+                 snprintf(temp_path, MAXPGPATH,
+                     "%s/%s/%s", cwd, SORT_TEMP_DIR, temp_de->d_name);
+                 unlink(temp_path);
+             }
+         }
+     }
+     closedir(temp_dir);
+ }
+
+ /*
+  *    remove_temp_tables
+  *
+  *    Remove temporary tables not referenced by any running backend.
+  */
+ static void
+ remove_temp_tables(void)
+ {
+     Relation    rel;
+     TupleDesc    tupdesc;
+     HeapScanDesc scan;
+     HeapTuple    tuple;
+     Datum        d;
+     bool        n;
+     char        *relname;
+     char        relkind;
+     Oid            reloid;
+     int            pid;
+
+     rel = heap_openr(RelationRelationName, AccessShareLock);
+
+     tupdesc = RelationGetDescr(rel);
+
+     scan = heap_beginscan(rel, false, SnapshotNow, 0, (ScanKey) NULL);
+     while (HeapTupleIsValid(tuple = heap_getnext(scan, 0)))
+     {
+         d = heap_getattr(tuple, Anum_pg_class_relname, tupdesc, &n);
+         relname = (char *) DatumGetName(d);
+         d = heap_getattr(tuple, Anum_pg_class_relkind, tupdesc, &n);
+         relkind = DatumGetChar(d);
+         reloid = tuple->t_data->t_oid;
+
+         if (strncmp(relname,"pg_temp.", strlen("pg_temp.")) == 0)
+         {
+             if ((pid = atoi(&relname[8])) != 0 &&
+                 ShmemPIDLookup(pid) == NULL)
+             {
+                 if (relkind != RELKIND_INDEX)
+                     heap_drop_with_catalog(relname, true);
+                 else
+                     index_drop(reloid);
+             }
+             CommandCounterIncrement();
+         }
+     }
+     heap_endscan(scan);
+     heap_close(rel, AccessShareLock);
+ }
+
+
+ /*
+  *     report_orphaned_files
+  *
+  *     Report files that are not referenced by any pg_class.relfilenode.
+  *     This could be caused by backend crash not cleaning up.
+  */
+ static void
+ report_orphaned_files(void)
+ {
+     DIR           *db_dir;
+     struct dirent  *db_de;
+     Oid            dir_file_oid;
+     char         cwd[MAXPGPATH];
+     Size        num_relfilenodes = 0;
+     Oid            *relfilenodes = get_pgclass_relfilenodes(&num_relfilenodes);
+
+     getcwd(cwd,MAXPGPATH);
+     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 (strcmp(db_de->d_name,".") == 0 ||
+             strcmp(db_de->d_name,"..") == 0)
+                 continue;
+         if (strspn(db_de->d_name, "0123456789.") !=
+             strlen(db_de->d_name))
+         {
+             /* Non-numeric file names */
+             if (strcmp(db_de->d_name, "core") == 0)
+                 elog(NOTICE,
+                     "Core file found in database directory.  If you don't need it\n"
+                     "\t for debugging, the administrator can remove it using 'rm':\n\t %s/%s",
+                     cwd, db_de->d_name);
+             else if (strcmp(db_de->d_name, SORT_TEMP_DIR) != 0 &&
+                      strcmp(db_de->d_name, PG_VERSION_FILE) != 0 &&
+                      strcmp(db_de->d_name, RELCACHE_INIT_FILENAME) != 0)
+                 elog(NOTICE,
+                     "Unusual file found in database directory.  This file is not\n"
+                     "\t normally created by PostgreSQL and can be removed by the\n"
+                     "\t administrator using 'rm':\n\t %s/%s",
+                     cwd, db_de->d_name);
+         }
+         else
+         {
+             /* Numeric file names;  extents, clip off decimal point */
+             dir_file_oid = (Oid) strtoul((db_de->d_name), NULL, 10);
+
+             if (/* oid not found in pg_class */
+                 bsearch(&dir_file_oid, relfilenodes, num_relfilenodes,
+                 sizeof(Oid), vc_oidcmp) == NULL)
+                 elog(NOTICE,
+                     "Unreferenced table file found in database directory.  This\n"
+                     "\t could have been left from a database crash.  If no one was\n"
+                     "\t using the database during VACUUM, the file can be safely\n"
+                     "\t removed by the administrator using 'rm':\n\t %s/%s",
+                     cwd, db_de->d_name);
+                 /* Maybe one day we can unlink too.  bjm 2001-05-24 */
+         }
+     }
+     pfree(relfilenodes);
+     closedir(db_dir);
+ }
+
+
+ /*
+  *    get_pgclass_relfilenodes
+  *
+  *    Return sorted array of Oid's referenced by pg_class.relfilenodes.
+  */
+ static Oid *
+ get_pgclass_relfilenodes(Size *num_relfilenodes)
+ {
+     Relation    rel;
+     TupleDesc    tupdesc;
+     HeapScanDesc scan;
+     HeapTuple    tuple;
+     Datum        d;
+     bool        n;
+     Oid            *relfilenodes = NULL;
+     int            len = 0;
+     Oid            rel_file_oid;
+
+     rel = heap_openr(RelationRelationName, AccessShareLock);
+
+     tupdesc = RelationGetDescr(rel);
+
+     /*
+      *    Make all tuples visible.  This doesn't hurt.
+      *    If we miss an orphan now, we can report on it later.
+      */
+     scan = heap_beginscan(rel, false, SnapshotAny, 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 (*num_relfilenodes >= len)
+         {
+             if (len == 0)
+             {
+                 len = 100;
+                 relfilenodes = palloc(len * sizeof(Oid));
+             }
+             else
+             {
+                 len *= 2;
+                 relfilenodes = repalloc(relfilenodes, len * sizeof(Oid));
+             }
+         }
+         relfilenodes[*num_relfilenodes] = rel_file_oid;
+         (*num_relfilenodes)++;
+     }
+     heap_endscan(scan);
+     heap_close(rel, AccessShareLock);
+
+     qsort(relfilenodes, *num_relfilenodes, sizeof(Oid), vc_oidcmp);
+
+     return relfilenodes;
+ }
+
+
+ /*
+  *    vc_oidcmp
+  *
+  *    Used by qsort() above.
+  */
+ static int
+ vc_oidcmp(const void *a, const void* b)
+ {
+     if (*(Oid *)a > *(Oid *)b)
+         return 1;
+     else if (*(Oid *)a == *(Oid *)b)
+         return 0;
+     else
+         return -1;
  }
Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.214
diff -c -r1.214 postmaster.c
*** src/backend/postmaster/postmaster.c    2001/05/25 15:45:33    1.214
--- src/backend/postmaster/postmaster.c    2001/05/28 19:06:34
***************
*** 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,2506 ----

      fclose(fp);
      return true;
+ }
+
+
+ /*
+  * Remove old sort files
+  * This is done per database by VACUUM too.
+  */
+ 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.
+      */
+     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)
+                 {
+                     snprintf(rm_path, sizeof(temp_path),
+                         "%s/%s/%s/%s",
+                         db_path, db_de->d_name,
+                         SORT_TEMP_DIR, temp_de->d_name);
+                     if (strspn(temp_de->d_name, "0123456789.") ==
+                         strlen(temp_de->d_name))
+                         unlink(rm_path);
+                     else
+                         fprintf(stderr,"Unexpected file found while cleaning temporary sort directory:\n\t %s\n",
rm_path);
+                 }
+                 closedir(temp_dir);
+             }
+         }
+         closedir(db_dir);
+     }
  }
Index: src/backend/storage/file/fd.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/storage/file/fd.c,v
retrieving revision 1.78
diff -c -r1.78 fd.c
*** src/backend/storage/file/fd.c    2001/05/25 15:45:33    1.78
--- src/backend/storage/file/fd.c    2001/05/28 19:06:38
***************
*** 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/ipc/shmem.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/storage/ipc/shmem.c,v
retrieving revision 1.57
diff -c -r1.57 shmem.c
*** src/backend/storage/ipc/shmem.c    2001/03/22 03:59:45    1.57
--- src/backend/storage/ipc/shmem.c    2001/05/28 19:06:38
***************
*** 260,276 ****
  }

  /*
!  * ShmemPIDLookup -- lookup process data structure using process id
   *
!  * Returns: TRUE if no error.  locationPtr is initialized if PID is
!  *        found in the shmem index.
   *
-  * NOTES:
-  *        only information about success or failure is the value of
-  *        locationPtr.
   */
! bool
! ShmemPIDLookup(int pid, SHMEM_OFFSET *locationPtr)
  {
      ShmemIndexEnt *result,
                  item;
--- 260,272 ----
  }

  /*
!  * ShmemPIDLookup -- lookup process structure using process id
   *
!  * Returns shared memory pointer or NULL.
   *
   */
! SHMEM_OFFSET
! ShmemPIDLookup(int pid)
  {
      ShmemIndexEnt *result,
                  item;
***************
*** 283,305 ****
      SpinAcquire(ShmemIndexLock);

      result = (ShmemIndexEnt *)
!         hash_search(ShmemIndex, (char *) &item, HASH_ENTER, &found);

      if (!result)
      {
          SpinRelease(ShmemIndexLock);
          elog(ERROR, "ShmemInitPID: ShmemIndex corrupted");
-         return FALSE;
      }

      if (found)
!         *locationPtr = result->location;
      else
          result->location = *locationPtr;

      SpinRelease(ShmemIndexLock);
!     return TRUE;
  }

  /*
   * ShmemPIDDestroy -- destroy shmem index entry for process
--- 279,337 ----
      SpinAcquire(ShmemIndexLock);

      result = (ShmemIndexEnt *)
!         hash_search(ShmemIndex, (char *) &item, HASH_FIND, &found);

      if (!result)
      {
          SpinRelease(ShmemIndexLock);
          elog(ERROR, "ShmemInitPID: ShmemIndex corrupted");
      }

+     SpinRelease(ShmemIndexLock);
+
      if (found)
!         return result->location;
      else
+         return NULL;
+ }
+
+
+ /*
+  *     ShmemPIDAdd -- add process id to shared memory.
+  *
+  *     Returns boolean indicating success/failure.
+  *
+  */
+ bool
+ ShmemPIDAdd(int pid, SHMEM_OFFSET *locationPtr)
+ {
+     ShmemIndexEnt *result,
+                 item;
+     bool        found;
+
+     Assert(ShmemIndex);
+     MemSet(item.key, 0, SHMEM_INDEX_KEYSIZE);
+     sprintf(item.key, "PID %d", pid);
+
+     SpinAcquire(ShmemIndexLock);
+
+     result = (ShmemIndexEnt *)
+         hash_search(ShmemIndex, (char *) &item,
+         HASH_ENTER, &found);
+
+     if (!result)
+     {
+         SpinRelease(ShmemIndexLock);
+         elog(ERROR, "ShmemInitPID: ShmemIndex corrupted");
+     }
+
+     if (!found)
          result->location = *locationPtr;

      SpinRelease(ShmemIndexLock);
!     return !found;
  }
+

  /*
   * ShmemPIDDestroy -- destroy shmem index entry for process
Index: src/backend/storage/lmgr/lock.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/storage/lmgr/lock.c,v
retrieving revision 1.88
diff -c -r1.88 lock.c
*** src/backend/storage/lmgr/lock.c    2001/03/22 03:59:46    1.88
--- src/backend/storage/lmgr/lock.c    2001/05/28 19:06:40
***************
*** 1458,1465 ****
      int            lockmethod = DEFAULT_LOCKMETHOD;
      LOCKMETHODTABLE *lockMethodTable;

!     ShmemPIDLookup(MyProcPid, &location);
!     if (location == INVALID_OFFSET)
          return;
      proc = (PROC *) MAKE_PTR(location);
      if (proc != MyProc)
--- 1458,1465 ----
      int            lockmethod = DEFAULT_LOCKMETHOD;
      LOCKMETHODTABLE *lockMethodTable;

!     if ((location = ShmemPIDLookup(MyProcPid)) == NULL ||
!         location == INVALID_OFFSET)
          return;
      proc = (PROC *) MAKE_PTR(location);
      if (proc != MyProc)
***************
*** 1508,1515 ****
      HASH_SEQ_STATUS status;

      pid = getpid();
!     ShmemPIDLookup(pid, &location);
!     if (location == INVALID_OFFSET)
          return;
      proc = (PROC *) MAKE_PTR(location);
      if (proc != MyProc)
--- 1508,1515 ----
      HASH_SEQ_STATUS status;

      pid = getpid();
!     If ((location = ShmemPIDLookup(pid)) == NULL ||
!         location == INVALID_OFFSET)
          return;
      proc = (PROC *) MAKE_PTR(location);
      if (proc != MyProc)
Index: src/backend/storage/lmgr/proc.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/storage/lmgr/proc.c,v
retrieving revision 1.102
diff -c -r1.102 proc.c
*** src/backend/storage/lmgr/proc.c    2001/05/25 15:45:33    1.102
--- src/backend/storage/lmgr/proc.c    2001/05/28 19:06:40
***************
*** 276,283 ****
       * process to find us after any untimely exit.
       */
      location = MAKE_OFFSET(MyProc);
!     if ((!ShmemPIDLookup(MyProcPid, &location)) ||
!         (location != MAKE_OFFSET(MyProc)))
          elog(STOP, "InitProcess: ShmemPID table broken");

      /*
--- 276,283 ----
       * process to find us after any untimely exit.
       */
      location = MAKE_OFFSET(MyProc);
!     if (!ShmemPIDAdd(MyProcPid, &location) ||
!         location != MAKE_OFFSET(MyProc))
          elog(STOP, "InitProcess: ShmemPID table broken");

      /*
Index: src/backend/utils/cache/temprel.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/cache/temprel.c,v
retrieving revision 1.35
diff -c -r1.35 temprel.c
*** src/backend/utils/cache/temprel.c    2001/03/22 03:59:58    1.35
--- src/backend/utils/cache/temprel.c    2001/05/28 19:06:47
***************
*** 230,236 ****
          {
              char        relname[NAMEDATALEN];

!             /* safe from deallocation */
              strcpy(relname, NameStr(temp_rel->user_relname));
              heap_drop_with_catalog(relname, allowSystemTableMods);
          }
--- 230,236 ----
          {
              char        relname[NAMEDATALEN];

!             /* save from deallocation */
              strcpy(relname, NameStr(temp_rel->user_relname));
              heap_drop_with_catalog(relname, allowSystemTableMods);
          }
Index: src/backend/utils/init/miscinit.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/init/miscinit.c,v
retrieving revision 1.67
diff -c -r1.67 miscinit.c
*** src/backend/utils/init/miscinit.c    2001/05/18 17:49:52    1.67
--- src/backend/utils/init/miscinit.c    2001/05/28 19:06:47
***************
*** 824,830 ****
      if (*endptr == '.')
          my_minor = strtol(endptr + 1, NULL, 10);

!     snprintf(full_path, MAXPGPATH, "%s/PG_VERSION", path);

      file = AllocateFile(full_path, "r");
      if (!file)
--- 824,830 ----
      if (*endptr == '.')
          my_minor = strtol(endptr + 1, NULL, 10);

!     snprintf(full_path, MAXPGPATH, "%s/%s", path, PG_VERSION_FILE);

      file = AllocateFile(full_path, "r");
      if (!file)
Index: src/include/miscadmin.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/miscadmin.h,v
retrieving revision 1.85
diff -c -r1.85 miscadmin.h
*** src/include/miscadmin.h    2001/05/12 01:48:49    1.85
--- src/include/miscadmin.h    2001/05/28 19:06:48
***************
*** 132,137 ****
--- 132,139 ----

  extern int    DebugLvl;

+ #define PG_VERSION_FILE "PG_VERSION"
+
  /* Date/Time Configuration
   *
   * Constants to pass info from runtime environment:
Index: src/include/storage/fd.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/storage/fd.h,v
retrieving revision 1.29
diff -c -r1.29 fd.h
*** src/include/storage/fd.h    2001/05/25 15:45:34    1.29
--- src/include/storage/fd.h    2001/05/28 19:06:49
***************
*** 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/shmem.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/storage/shmem.h,v
retrieving revision 1.28
diff -c -r1.28 shmem.h
*** src/include/storage/shmem.h    2001/03/22 04:01:09    1.28
--- src/include/storage/shmem.h    2001/05/28 19:06:49
***************
*** 71,77 ****
  extern bool ShmemIsValid(unsigned long addr);
  extern HTAB *ShmemInitHash(char *name, long init_size, long max_size,
                HASHCTL *infoP, int hash_flags);
! extern bool ShmemPIDLookup(int pid, SHMEM_OFFSET *locationPtr);
  extern SHMEM_OFFSET ShmemPIDDestroy(int pid);
  extern void *ShmemInitStruct(char *name, Size size, bool *foundPtr);

--- 71,78 ----
  extern bool ShmemIsValid(unsigned long addr);
  extern HTAB *ShmemInitHash(char *name, long init_size, long max_size,
                HASHCTL *infoP, int hash_flags);
! extern SHMEM_OFFSET ShmemPIDLookup(int pid);
! extern bool ShmemPIDAdd(int pid, SHMEM_OFFSET *locationPtr);
  extern SHMEM_OFFSET ShmemPIDDestroy(int pid);
  extern void *ShmemInitStruct(char *name, Size size, bool *foundPtr);

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> The following patch does full referential checking of sort files, temp
> tables, and table files.  It checks references of every file and temp
> table, and reports or removes it.  The code only removes files it is
> certain about.

Plus some that it's not certain about.

I still think that this whole business is dangerous and of unproven
usefulness.  Removing sorttemp files at postmaster start is OK ---
there's no question that they are temp, and there's no question (after
we have the postmaster lock) that no one needs them anymore.  Anything
else is just asking for trouble.

I am not comforted by the fact that the code doesn't remove data files
itself, but only recommends that the dbadmin do it; just how closely
do you think the average DBA will examine such recommendations?  The
first time someone removes a file he needed because "the system told me
to", your patch will have done damage outweighing the total positive
effect it could ever have anywhere.

Now, as to the lesser matter of exactly how many holes there are in this
particular version:

1. Removing sorttemp files during vacuum is not safe; you cannot know
that they don't belong to any running backend.  (No, the fact that you
looked in PROC and didn't find the PID doesn't prove anything; the same
PID could have been reassigned to a new backend since you looked.)

2. Removing temp tables during vacuum is not safe either, first because
of the fact that the PID check is unsafe, and second because it could
cause vacuum to fail entirely (suppose the owning backend terminates and
removes the temp table just before you do?).

3. Warning about relation nodes that you didn't find in a previous scan
of pg_class is obviously unsafe; they could have been created since you
looked in pg_class.

You can't really get around any of these problems by doing things in a
different order, either; that'd just shift the vulnerabilities.  The
only way to do it safely would be to acquire locks that would prevent
other backends from creating/deleting files while you make the checks.
That's not reasonable.

In short, the only parts of this patch that I think are acceptable are
the changes to move sorttemp files into their own directory and to
remove sorttemp files during postmaster start.

BTW, while I approve of moving sorttemp files into their own
subdirectory, it's sheer folly to give them names therein that look
so much like regular relation file names.  They should still be named
something like "sorttempNNN".

            regards, tom lane

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > The following patch does full referential checking of sort files, temp
> > tables, and table files.  It checks references of every file and temp
> > table, and reports or removes it.  The code only removes files it is
> > certain about.
>
> Plus some that it's not certain about.
>
> I still think that this whole business is dangerous and of unproven
> usefulness.  Removing sorttemp files at postmaster start is OK ---
> there's no question that they are temp, and there's no question (after
> we have the postmaster lock) that no one needs them anymore.  Anything
> else is just asking for trouble.

We can't just throw up our hands and say we can't account for this
left-over stuff.  We have to come up with some solution.

> I am not comforted by the fact that the code doesn't remove data files
> itself, but only recommends that the dbadmin do it; just how closely
> do you think the average DBA will examine such recommendations?  The
> first time someone removes a file he needed because "the system told me
> to", your patch will have done damage outweighing the total positive
> effect it could ever have anywhere.
>
> Now, as to the lesser matter of exactly how many holes there are in this
> particular version:
>
> 1. Removing sorttemp files during vacuum is not safe; you cannot know
> that they don't belong to any running backend.  (No, the fact that you
> looked in PROC and didn't find the PID doesn't prove anything; the same
> PID could have been reassigned to a new backend since you looked.)

But I have the directory entry before I look for the PID.  Seems safe.

> 2. Removing temp tables during vacuum is not safe either, first because
> of the fact that the PID check is unsafe, and second because it could
> cause vacuum to fail entirely (suppose the owning backend terminates and
> removes the temp table just before you do?).

Yes, I can't remove temp tables for running backends.  The table is
removed before the PID is removed from the hash.  I will have to do some
more checking to see that the pg_class entry actually points to a real
data file.

> 3. Warning about relation nodes that you didn't find in a previous scan
> of pg_class is obviously unsafe; they could have been created since you
> looked in pg_class.

Actually that is why I was doing the OID range checking but you didn't
like that either.  I had the code checking from the lowest OID on each
backend startup to the current OID counter and skipping those.  Should I
try adding that again.

> You can't really get around any of these problems by doing things in a
> different order, either; that'd just shift the vulnerabilities.  The
> only way to do it safely would be to acquire locks that would prevent
> other backends from creating/deleting files while you make the checks.
> That's not reasonable.
>
> In short, the only parts of this patch that I think are acceptable are
> the changes to move sorttemp files into their own directory and to
> remove sorttemp files during postmaster start.
>
> BTW, while I approve of moving sorttemp files into their own
> subdirectory, it's sheer folly to give them names therein that look
> so much like regular relation file names.  They should still be named
> something like "sorttempNNN".

They are in their own directory.  Naming as backend_pid.serial number seems
OK to me.

--
  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

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> We can't just throw up our hands and say we can't account for this
> left-over stuff.  We have to come up with some solution.

No we don't.  I'm not convinced that we have a problem at all anymore.
(Show me field reports from 7.1; older versions are irrelevant to this
discussion.)  But I am convinced that a half-baked solution would be
worse than not doing anything.


>> 1. Removing sorttemp files during vacuum is not safe; you cannot know
>> that they don't belong to any running backend.  (No, the fact that you
>> looked in PROC and didn't find the PID doesn't prove anything; the same
>> PID could have been reassigned to a new backend since you looked.)

> But I have the directory entry before I look for the PID.  Seems safe.

No.  PIDs wrap around, therefore sorttemp file names recycle.  You might
be able to establish that the backend that originally created the file
is gone, but it's still possible that by the time you actually do the
unlink, you are cutting the knees off a new backend that has re-used the
file name.

Basically, you cannot do any of this safely without a lock.  If you
think you can, then you don't understand the problem.

I don't think there are any suitable locks in the system at the moment,
and in any case I don't like the loss of concurrency that would occur
if we interlocked process start/stop, sorttemp file
creation/destruction, etc, to the extent that would be needed to make
the world safe for on-line temp file removal.  This cure looks much
worse than the disease to me...


>> BTW, while I approve of moving sorttemp files into their own
>> subdirectory, it's sheer folly to give them names therein that look
>> so much like regular relation file names.  They should still be named
>> something like "sorttempNNN".

> They are in their own directory.  Naming as backend_pid.serial number seems
> OK to me.

The code won't get confused, but humans might.  Keep in mind also that
the reason for having a separate directory is so that a DBA can symlink
the directory to someplace else.  What happens if he symlinks it to a
directory that also contains real data files?  Far better to make sure
that temp file names cannot conflict with relation file names, whether
they're in the same directory or not.

            regards, tom lane

> > BTW, while I approve of moving sorttemp files into their own
> > subdirectory, it's sheer folly to give them names therein that look
> > so much like regular relation file names.  They should still be named
> > something like "sorttempNNN".
>
> They are in their own directory.  Naming as backend_pid.serial number seems
> OK to me.

I know remember I was going to call the file pid_323.2 so people knew it
was a pid.  I will add that.

--
  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

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > We can't just throw up our hands and say we can't account for this
> > left-over stuff.  We have to come up with some solution.
>
> No we don't.  I'm not convinced that we have a problem at all anymore.
> (Show me field reports from 7.1; older versions are irrelevant to this
> discussion.)  But I am convinced that a half-baked solution would be
> worse than not doing anything.

We already know that crashes will leave these files around, and as we
start using file versioning for CLUSTER and DROP COLUMN, I expect those
orphaned files to continue.

Basically, if I can, I would like to harden our code to report or remove
this cruft.  Most commercial database have such tools.  Seems VACUUM is
the proper place for us to do it.

However, I agree if I can't make it 100% reliable, it is worse than
useless.

Let me keep trying and we can rip out parts that aren't 100%.


> >> 1. Removing sorttemp files during vacuum is not safe; you cannot know
> >> that they don't belong to any running backend.  (No, the fact that you
> >> looked in PROC and didn't find the PID doesn't prove anything; the same
> >> PID could have been reassigned to a new backend since you looked.)
>
> > But I have the directory entry before I look for the PID.  Seems safe.
>
> No.  PIDs wrap around, therefore sorttemp file names recycle.  You might
> be able to establish that the backend that originally created the file
> is gone, but it's still possible that by the time you actually do the
> unlink, you are cutting the knees off a new backend that has re-used the
> file name.
>
> Basically, you cannot do any of this safely without a lock.  If you
> think you can, then you don't understand the problem.

You are correct.  I didn't realized that after I check the shared memory
for the pid, another backend could start with the pid I was checking and
create a sort file.  The sort code doesn't so O_EXCL, so it would
succeed in creating it, and I would have removed it.

> I don't think there are any suitable locks in the system at the moment,
> and in any case I don't like the loss of concurrency that would occur
> if we interlocked process start/stop, sorttemp file
> creation/destruction, etc, to the extent that would be needed to make
> the world safe for on-line temp file removal.  This cure looks much
> worse than the disease to me...

OK, my solution was to do this:

            else if (ShmemPIDLookup(pid, true) == NULL)
            {
                snprintf(temp_path, MAXPGPATH,
                    "%s/%s/%s", cwd, SORT_TEMP_DIR, temp_de->d_name);
                /* Make sure no pid gets created and starts using this file */
                SpinAcquire(ShmemIndexLock);
                if (ShmemPIDLookup(pid, false) == NULL)
                    unlink(temp_path);
                SpinRelease(ShmemIndexLock);
            }

I added a boolean flag to ShmemPIDLookup() to control whether the
function does locking.  Basically, I do my own locking around the unlink
if I have found a orphaned file.  This prevents another backend from
being created under me with the pid I am checking.  This code only runs
if I have already found an orphan.

>
> >> BTW, while I approve of moving sorttemp files into their own
> >> subdirectory, it's sheer folly to give them names therein that look
> >> so much like regular relation file names.  They should still be named
> >> something like "sorttempNNN".
>
> > They are in their own directory.  Naming as backend_pid.serial number seems
> > OK to me.
>
> The code won't get confused, but humans might.  Keep in mind also that
> the reason for having a separate directory is so that a DBA can symlink
> the directory to someplace else.  What happens if he symlinks it to a
> directory that also contains real data files?  Far better to make sure
> that temp file names cannot conflict with relation file names, whether
> they're in the same directory or not.

See attached patch.  pid_###.###.

Also, I re-added the startOid/nextoid code to prevent me from looking at
any pg_class changes caused by running backends.

--
  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: doc/src/sgml/ref/vacuum.sgml
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/ref/vacuum.sgml,v
retrieving revision 1.16
diff -c -r1.16 vacuum.sgml
*** doc/src/sgml/ref/vacuum.sgml    2001/05/25 15:45:31    1.16
--- doc/src/sgml/ref/vacuum.sgml    2001/05/30 03:10:46
***************
*** 154,160 ****

    <para>
     With no parameter, <command>VACUUM</command> processes every table in the
!    current database.  With a parameter, <command>VACUUM</command> processes
     only that table.
    </para>

--- 154,161 ----

    <para>
     With no parameter, <command>VACUUM</command> processes every table in the
!    current database. It also detects extraneous files in the
!    database directory.  With a parameter, <command>VACUUM</command> processes
     only that table.
    </para>

Index: src/backend/access/transam/varsup.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/access/transam/varsup.c,v
retrieving revision 1.40
diff -c -r1.40 varsup.c
*** src/backend/access/transam/varsup.c    2001/05/25 15:45:32    1.40
--- src/backend/access/transam/varsup.c    2001/05/30 03:10:47
***************
*** 16,22 ****
--- 16,25 ----
  #include "access/transam.h"
  #include "access/xlog.h"
  #include "storage/proc.h"
+ #include "storage/sinval.h"
+ #include "storage/sinvaladt.h"

+ extern 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.195
diff -c -r1.195 vacuum.c
*** src/backend/commands/vacuum.c    2001/05/25 15:45:32    1.195
--- src/backend/commands/vacuum.c    2001/05/30 03:10:49
***************
*** 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,42 ****
--- 33,48 ----

  #include "access/genam.h"
  #include "access/heapam.h"
+ #include "access/transam.h"
  #include "access/xlog.h"
  #include "catalog/catalog.h"
  #include "catalog/catname.h"
+ #include "catalog/heap.h"
  #include "catalog/index.h"
  #include "commands/vacuum.h"
  #include "miscadmin.h"
  #include "nodes/execnodes.h"
+ #include "storage/fd.h"
  #include "storage/sinval.h"
  #include "storage/smgr.h"
  #include "tcop/tcopprot.h"
***************
*** 159,164 ****
--- 165,175 ----
  static bool enough_space(VacPage vacpage, Size len);
  static void init_rusage(VacRUsage *ru0);
  static char *show_rusage(VacRUsage *ru0);
+ static void remove_sorttemp_files(void);
+ static void remove_temp_tables(void);
+ static void report_orphaned_files(void);
+ static Oid *get_pgclass_relfilenodes(Size *num_relfilenodes);
+ static int vc_oidcmp(const void *a, const void* b);


  /*
***************
*** 236,241 ****
--- 247,260 ----

      /* clean up */
      vacuum_shutdown();
+
+     if (VacRelName == NULL)
+     {
+         remove_sorttemp_files();
+         report_orphaned_files();
+         remove_temp_tables();
+     }
+
  }

  /*
***************
*** 2645,2648 ****
--- 2664,2931 ----
               (int) (ru1.tv.tv_usec - ru0->tv.tv_usec) / 10000);

      return result;
+ }
+
+ /*
+  * remove_sorttemp_files
+  *
+  * Remove sorttemp files not referenced by any running backend.
+  * This could be caused by backend crash not cleaning up.
+  */
+ static void
+ remove_sorttemp_files(void)
+ {
+     DIR           *temp_dir;
+     struct dirent  *temp_de;
+     char         cwd[MAXPGPATH];
+     char        temp_path[MAXPGPATH];
+     int            pid;
+
+     getcwd(cwd,MAXPGPATH);
+     temp_dir = opendir(SORT_TEMP_DIR);
+     if (!temp_dir)    /* if there is no directory, nothing to do */
+         return;
+
+     /*
+      *    Cycle through directory and check each file against
+      *    pg_class.relfilenode.
+      */
+     while ((temp_de = readdir(temp_dir)) != NULL)
+     {
+         if (strcmp(temp_de->d_name,".") == 0 ||
+             strcmp(temp_de->d_name,"..") == 0)
+                 continue;
+         if (strncmp(temp_de->d_name,"pid_",strlen("pid_")) != 0)
+             /* Non-pid file name */
+             elog(NOTICE,
+                 "Unusual file found in temporary sort directory.  This file is not\n"
+                 "\t normally created by PostgreSQL and can be removed by the\n"
+                 "\t administrator using 'rm':\n\t %s/%s/%s",
+                 cwd, SORT_TEMP_DIR, temp_de->d_name);
+         else
+         {
+             /* Numeric file names;  extents clip off decimal point */
+             pid = atoi(&temp_de->d_name[4]);
+             if (pid == 0)
+                 elog(NOTICE,
+                     "Unusual file found in temporary sort directory.  This file\n"
+                     "\t is not normally created by PostgreSQL and can be removed\n"
+                     "\t by the administrator using 'rm':\n\t %s/%s/%s",
+                     cwd, SORT_TEMP_DIR, temp_de->d_name);
+             else if (ShmemPIDLookup(pid, true) == NULL)
+             {
+                 snprintf(temp_path, MAXPGPATH,
+                     "%s/%s/%s", cwd, SORT_TEMP_DIR, temp_de->d_name);
+                 /* Make sure no pid gets created and starts using this file */
+                 SpinAcquire(ShmemIndexLock);
+                 if (ShmemPIDLookup(pid, false) == NULL)
+                     unlink(temp_path);
+                 SpinRelease(ShmemIndexLock);
+
+             }
+         }
+     }
+     closedir(temp_dir);
+ }
+
+ /*
+  *    remove_temp_tables
+  *
+  *    Remove temporary tables not referenced by any running backend.
+  */
+ static void
+ remove_temp_tables(void)
+ {
+     Relation    rel;
+     TupleDesc    tupdesc;
+     HeapScanDesc scan;
+     HeapTuple    tuple;
+     Datum        d;
+     bool        n;
+     char        *relname;
+     char        relkind;
+     Oid            reloid;
+     int            pid;
+
+     rel = heap_openr(RelationRelationName, AccessShareLock);
+
+     tupdesc = RelationGetDescr(rel);
+
+     scan = heap_beginscan(rel, false, SnapshotNow, 0, (ScanKey) NULL);
+     while (HeapTupleIsValid(tuple = heap_getnext(scan, 0)))
+     {
+         d = heap_getattr(tuple, Anum_pg_class_relname, tupdesc, &n);
+         relname = (char *) DatumGetName(d);
+         d = heap_getattr(tuple, Anum_pg_class_relkind, tupdesc, &n);
+         relkind = DatumGetChar(d);
+         reloid = tuple->t_data->t_oid;
+
+         if (strncmp(relname,"pg_temp.", strlen("pg_temp.")) == 0)
+         {
+             if ((pid = atoi(&relname[8])) != 0 &&
+                 ShmemPIDLookup(pid, true) == NULL)
+             {
+                 if (relkind != RELKIND_INDEX)
+                     heap_drop_with_catalog(relname, true);
+                 else
+                     index_drop(reloid);
+             }
+             CommandCounterIncrement();
+         }
+     }
+     heap_endscan(scan);
+     heap_close(rel, AccessShareLock);
+ }
+
+
+ /*
+  *     report_orphaned_files
+  *
+  *     Report files that are not referenced by any pg_class.relfilenode.
+  *     This could be caused by backend crash not cleaning up.
+  */
+ static void
+ report_orphaned_files(void)
+ {
+     DIR           *db_dir;
+     struct dirent  *db_de;
+     Oid            dir_file_oid;
+     char         cwd[MAXPGPATH];
+     Oid            min_startup_oid = GetMinStartupOid();
+     Size        num_relfilenodes = 0;
+     Oid            *relfilenodes = get_pgclass_relfilenodes(&num_relfilenodes);
+
+     getcwd(cwd,MAXPGPATH);
+     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 (strcmp(db_de->d_name,".") == 0 ||
+             strcmp(db_de->d_name,"..") == 0)
+                 continue;
+         if (strspn(db_de->d_name, "0123456789.") !=
+             strlen(db_de->d_name))
+         {
+             /* Non-numeric file names */
+             if (strcmp(db_de->d_name, "core") == 0)
+                 elog(NOTICE,
+                     "Core file found in database directory.  If you don't need it\n"
+                     "\t for debugging, the administrator can remove it using 'rm':\n\t %s/%s",
+                     cwd, db_de->d_name);
+             else if (strcmp(db_de->d_name, SORT_TEMP_DIR) != 0 &&
+                      strcmp(db_de->d_name, PG_VERSION_FILE) != 0 &&
+                      strcmp(db_de->d_name, RELCACHE_INIT_FILENAME) != 0)
+                 elog(NOTICE,
+                     "Unusual file found in database directory.  This file is not\n"
+                     "\t normally created by PostgreSQL and can be removed by the\n"
+                     "\t administrator using 'rm':\n\t %s/%s",
+                     cwd, db_de->d_name);
+         }
+         else
+         {
+             /* Numeric file names;  extents, clip off decimal point */
+             dir_file_oid = (Oid) strtoul((db_de->d_name), NULL, 10);
+
+             if (/* oid not found in pg_class */
+                 bsearch(&dir_file_oid, relfilenodes, num_relfilenodes,
+                 sizeof(Oid), vc_oidcmp) == NULL &&
+                 /* not active backend */
+                 (dir_file_oid < min_startup_oid ||
+                  dir_file_oid > ShmemVariableCache->nextOid) &&
+                 /* no oid wrap */
+                 min_startup_oid <= ShmemVariableCache->nextOid)
+                 /*
+                  *  At this point, dir_file_oid is _not_ between
+                  *    MinStartupOid  and  nextOid.  We do this test
+                  *    here after we have checked pg_class in case the
+                  *    oid looped during the table scan.
+                  */
+                 elog(NOTICE,
+                     "Unreferenced table file found in database directory.  This\n"
+                     "\t could have been left from a database crash.  This file can\n"
+                     "\t be safely removed by the administrator using 'rm':\n\t %s/%s",
+                     cwd, db_de->d_name);
+                 /* Maybe one day we can unlink too.  bjm 2001-05-24 */
+         }
+     }
+     pfree(relfilenodes);
+     closedir(db_dir);
+ }
+
+
+ /*
+  *    get_pgclass_relfilenodes
+  *
+  *    Return sorted array of Oid's referenced by pg_class.relfilenodes.
+  */
+ static Oid *
+ get_pgclass_relfilenodes(Size *num_relfilenodes)
+ {
+     Relation    rel;
+     TupleDesc    tupdesc;
+     HeapScanDesc scan;
+     HeapTuple    tuple;
+     Datum        d;
+     bool        n;
+     Oid            *relfilenodes = NULL;
+     int            len = 0;
+     Oid            rel_file_oid;
+
+     rel = heap_openr(RelationRelationName, AccessShareLock);
+
+     tupdesc = RelationGetDescr(rel);
+
+     /*
+      *    Make all tuples visible.  This doesn't hurt.
+      *    If we miss an orphan now, we can report on it later.
+      */
+     scan = heap_beginscan(rel, false, SnapshotAny, 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 (*num_relfilenodes >= len)
+         {
+             if (len == 0)
+             {
+                 len = 100;
+                 relfilenodes = palloc(len * sizeof(Oid));
+             }
+             else
+             {
+                 len *= 2;
+                 relfilenodes = repalloc(relfilenodes, len * sizeof(Oid));
+             }
+         }
+         relfilenodes[*num_relfilenodes] = rel_file_oid;
+         (*num_relfilenodes)++;
+     }
+     heap_endscan(scan);
+     heap_close(rel, AccessShareLock);
+
+     qsort(relfilenodes, *num_relfilenodes, sizeof(Oid), vc_oidcmp);
+
+     return relfilenodes;
+ }
+
+
+ /*
+  *    vc_oidcmp
+  *
+  *    Used by qsort() above.
+  */
+ static int
+ vc_oidcmp(const void *a, const void* b)
+ {
+     if (*(Oid *)a > *(Oid *)b)
+         return 1;
+     else if (*(Oid *)a == *(Oid *)b)
+         return 0;
+     else
+         return -1;
  }
Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.214
diff -c -r1.214 postmaster.c
*** src/backend/postmaster/postmaster.c    2001/05/25 15:45:33    1.214
--- src/backend/postmaster/postmaster.c    2001/05/30 03:10:50
***************
*** 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,2506 ----

      fclose(fp);
      return true;
+ }
+
+
+ /*
+  * Remove old sort files
+  * This is done per database by VACUUM too.
+  */
+ 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.
+      */
+     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)
+                 {
+                     snprintf(rm_path, sizeof(temp_path),
+                         "%s/%s/%s/%s",
+                         db_path, db_de->d_name,
+                         SORT_TEMP_DIR, temp_de->d_name);
+                     if (strspn(temp_de->d_name, "0123456789.") ==
+                         strlen(temp_de->d_name))
+                         unlink(rm_path);
+                     else
+                         fprintf(stderr,"Unexpected file found while cleaning temporary sort directory:\n\t %s\n",
rm_path);
+                 }
+                 closedir(temp_dir);
+             }
+         }
+         closedir(db_dir);
+     }
  }
Index: src/backend/storage/file/fd.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/storage/file/fd.c,v
retrieving revision 1.78
diff -c -r1.78 fd.c
*** src/backend/storage/file/fd.c    2001/05/25 15:45:33    1.78
--- src/backend/storage/file/fd.c    2001/05/30 03:10:51
***************
*** 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/pid_%d.%ld", SORT_TEMP_DIR, 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/ipc/shmem.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/storage/ipc/shmem.c,v
retrieving revision 1.57
diff -c -r1.57 shmem.c
*** src/backend/storage/ipc/shmem.c    2001/03/22 03:59:45    1.57
--- src/backend/storage/ipc/shmem.c    2001/05/30 03:10:52
***************
*** 260,276 ****
  }

  /*
!  * ShmemPIDLookup -- lookup process data structure using process id
   *
!  * Returns: TRUE if no error.  locationPtr is initialized if PID is
!  *        found in the shmem index.
   *
-  * NOTES:
-  *        only information about success or failure is the value of
-  *        locationPtr.
   */
! bool
! ShmemPIDLookup(int pid, SHMEM_OFFSET *locationPtr)
  {
      ShmemIndexEnt *result,
                  item;
--- 260,272 ----
  }

  /*
!  * ShmemPIDLookup -- lookup process structure using process id
   *
!  * Returns shared memory pointer or NULL.
   *
   */
! SHMEM_OFFSET
! ShmemPIDLookup(int pid, bool do_locking)
  {
      ShmemIndexEnt *result,
                  item;
***************
*** 280,305 ****
      MemSet(item.key, 0, SHMEM_INDEX_KEYSIZE);
      sprintf(item.key, "PID %d", pid);

!     SpinAcquire(ShmemIndexLock);

      result = (ShmemIndexEnt *)
!         hash_search(ShmemIndex, (char *) &item, HASH_ENTER, &found);

      if (!result)
      {
          SpinRelease(ShmemIndexLock);
          elog(ERROR, "ShmemInitPID: ShmemIndex corrupted");
-         return FALSE;
      }

      if (found)
!         *locationPtr = result->location;
      else
          result->location = *locationPtr;

      SpinRelease(ShmemIndexLock);
!     return TRUE;
  }

  /*
   * ShmemPIDDestroy -- destroy shmem index entry for process
--- 276,339 ----
      MemSet(item.key, 0, SHMEM_INDEX_KEYSIZE);
      sprintf(item.key, "PID %d", pid);

!     if (do_locking)
!         SpinAcquire(ShmemIndexLock);

      result = (ShmemIndexEnt *)
!         hash_search(ShmemIndex, (char *) &item, HASH_FIND, &found);

      if (!result)
      {
          SpinRelease(ShmemIndexLock);
          elog(ERROR, "ShmemInitPID: ShmemIndex corrupted");
      }

+     if (do_locking)
+         SpinRelease(ShmemIndexLock);
+
      if (found)
!         return result->location;
      else
+         return NULL;
+ }
+
+
+ /*
+  *     ShmemPIDAdd -- add process id to shared memory.
+  *
+  *     Returns boolean indicating success/failure.
+  *
+  */
+ bool
+ ShmemPIDAdd(int pid, SHMEM_OFFSET *locationPtr)
+ {
+     ShmemIndexEnt *result,
+                 item;
+     bool        found;
+
+     Assert(ShmemIndex);
+     MemSet(item.key, 0, SHMEM_INDEX_KEYSIZE);
+     sprintf(item.key, "PID %d", pid);
+
+     SpinAcquire(ShmemIndexLock);
+
+     result = (ShmemIndexEnt *)
+         hash_search(ShmemIndex, (char *) &item,
+         HASH_ENTER, &found);
+
+     if (!result)
+     {
+         SpinRelease(ShmemIndexLock);
+         elog(ERROR, "ShmemInitPID: ShmemIndex corrupted");
+     }
+
+     if (!found)
          result->location = *locationPtr;

      SpinRelease(ShmemIndexLock);
!     return !found;
  }
+

  /*
   * ShmemPIDDestroy -- destroy shmem index entry for process
Index: src/backend/storage/lmgr/lock.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/storage/lmgr/lock.c,v
retrieving revision 1.88
diff -c -r1.88 lock.c
*** src/backend/storage/lmgr/lock.c    2001/03/22 03:59:46    1.88
--- src/backend/storage/lmgr/lock.c    2001/05/30 03:10:52
***************
*** 1458,1465 ****
      int            lockmethod = DEFAULT_LOCKMETHOD;
      LOCKMETHODTABLE *lockMethodTable;

!     ShmemPIDLookup(MyProcPid, &location);
!     if (location == INVALID_OFFSET)
          return;
      proc = (PROC *) MAKE_PTR(location);
      if (proc != MyProc)
--- 1458,1465 ----
      int            lockmethod = DEFAULT_LOCKMETHOD;
      LOCKMETHODTABLE *lockMethodTable;

!     if ((location = ShmemPIDLookup(MyProcPid, true)) == NULL ||
!         location == INVALID_OFFSET)
          return;
      proc = (PROC *) MAKE_PTR(location);
      if (proc != MyProc)
***************
*** 1508,1515 ****
      HASH_SEQ_STATUS status;

      pid = getpid();
!     ShmemPIDLookup(pid, &location);
!     if (location == INVALID_OFFSET)
          return;
      proc = (PROC *) MAKE_PTR(location);
      if (proc != MyProc)
--- 1508,1515 ----
      HASH_SEQ_STATUS status;

      pid = getpid();
!     If ((location = ShmemPIDLookup(pid, true)) == NULL ||
!         location == INVALID_OFFSET)
          return;
      proc = (PROC *) MAKE_PTR(location);
      if (proc != MyProc)
Index: src/backend/storage/lmgr/proc.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/storage/lmgr/proc.c,v
retrieving revision 1.102
diff -c -r1.102 proc.c
*** src/backend/storage/lmgr/proc.c    2001/05/25 15:45:33    1.102
--- src/backend/storage/lmgr/proc.c    2001/05/30 03:10:53
***************
*** 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));
***************
*** 276,283 ****
       * process to find us after any untimely exit.
       */
      location = MAKE_OFFSET(MyProc);
!     if ((!ShmemPIDLookup(MyProcPid, &location)) ||
!         (location != MAKE_OFFSET(MyProc)))
          elog(STOP, "InitProcess: ShmemPID table broken");

      /*
--- 277,284 ----
       * process to find us after any untimely exit.
       */
      location = MAKE_OFFSET(MyProc);
!     if (!ShmemPIDAdd(MyProcPid, &location) ||
!         location != MAKE_OFFSET(MyProc))
          elog(STOP, "InitProcess: ShmemPID table broken");

      /*
Index: src/backend/utils/cache/temprel.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/cache/temprel.c,v
retrieving revision 1.35
diff -c -r1.35 temprel.c
*** src/backend/utils/cache/temprel.c    2001/03/22 03:59:58    1.35
--- src/backend/utils/cache/temprel.c    2001/05/30 03:10:58
***************
*** 230,236 ****
          {
              char        relname[NAMEDATALEN];

!             /* safe from deallocation */
              strcpy(relname, NameStr(temp_rel->user_relname));
              heap_drop_with_catalog(relname, allowSystemTableMods);
          }
--- 230,236 ----
          {
              char        relname[NAMEDATALEN];

!             /* save from deallocation */
              strcpy(relname, NameStr(temp_rel->user_relname));
              heap_drop_with_catalog(relname, allowSystemTableMods);
          }
Index: src/backend/utils/init/miscinit.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/init/miscinit.c,v
retrieving revision 1.67
diff -c -r1.67 miscinit.c
*** src/backend/utils/init/miscinit.c    2001/05/18 17:49:52    1.67
--- src/backend/utils/init/miscinit.c    2001/05/30 03:10:58
***************
*** 824,830 ****
      if (*endptr == '.')
          my_minor = strtol(endptr + 1, NULL, 10);

!     snprintf(full_path, MAXPGPATH, "%s/PG_VERSION", path);

      file = AllocateFile(full_path, "r");
      if (!file)
--- 824,830 ----
      if (*endptr == '.')
          my_minor = strtol(endptr + 1, NULL, 10);

!     snprintf(full_path, MAXPGPATH, "%s/%s", path, PG_VERSION_FILE);

      file = AllocateFile(full_path, "r");
      if (!file)
Index: src/include/miscadmin.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/miscadmin.h,v
retrieving revision 1.85
diff -c -r1.85 miscadmin.h
*** src/include/miscadmin.h    2001/05/12 01:48:49    1.85
--- src/include/miscadmin.h    2001/05/30 03:10:59
***************
*** 132,137 ****
--- 132,139 ----

  extern int    DebugLvl;

+ #define PG_VERSION_FILE "PG_VERSION"
+
  /* Date/Time Configuration
   *
   * Constants to pass info from runtime environment:
Index: src/include/access/transam.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/access/transam.h,v
retrieving revision 1.35
diff -c -r1.35 transam.h
*** src/include/access/transam.h    2001/05/25 15:45:33    1.35
--- src/include/access/transam.h    2001/05/30 03:10:59
***************
*** 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.29
diff -c -r1.29 fd.h
*** src/include/storage/fd.h    2001/05/25 15:45:34    1.29
--- src/include/storage/fd.h    2001/05/30 03:11:00
***************
*** 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.43
diff -c -r1.43 proc.h
*** src/include/storage/proc.h    2001/05/25 15:45:34    1.43
--- src/include/storage/proc.h    2001/05/30 03:11:00
***************
*** 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
Index: src/include/storage/shmem.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/storage/shmem.h,v
retrieving revision 1.28
diff -c -r1.28 shmem.h
*** src/include/storage/shmem.h    2001/03/22 04:01:09    1.28
--- src/include/storage/shmem.h    2001/05/30 03:11:00
***************
*** 71,77 ****
  extern bool ShmemIsValid(unsigned long addr);
  extern HTAB *ShmemInitHash(char *name, long init_size, long max_size,
                HASHCTL *infoP, int hash_flags);
! extern bool ShmemPIDLookup(int pid, SHMEM_OFFSET *locationPtr);
  extern SHMEM_OFFSET ShmemPIDDestroy(int pid);
  extern void *ShmemInitStruct(char *name, Size size, bool *foundPtr);

--- 71,78 ----
  extern bool ShmemIsValid(unsigned long addr);
  extern HTAB *ShmemInitHash(char *name, long init_size, long max_size,
                HASHCTL *infoP, int hash_flags);
! extern SHMEM_OFFSET ShmemPIDLookup(int pid, bool do_locking);
! extern bool ShmemPIDAdd(int pid, SHMEM_OFFSET *locationPtr);
  extern SHMEM_OFFSET ShmemPIDDestroy(int pid);
  extern void *ShmemInitStruct(char *name, Size size, bool *foundPtr);

> NOTICE:  Unreferenced table file found in database directory.  This
>          could have been left from a database crash.  If no one was
>          using the database during VACUUM, the file can be safely
>          removed by the administrator using 'rm':
>          /usr/var/local/postgres/data/base/18617/33333

I think this wording is overly cautious.  I will change it to:

> NOTICE:  Unreferenced table file found in database directory.  This
>          could have been left from a database crash.  This file can be
>          safely removed by the administrator using 'rm':
>          /usr/var/local/postgres/data/base/18617/33333


--
  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

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> No we don't.  I'm not convinced that we have a problem at all anymore.

> We already know that crashes will leave these files around, and as we
> start using file versioning for CLUSTER and DROP COLUMN, I expect those
> orphaned files to continue.

I don't.  The intention (not yet implemented, but Vadim's referred to it
repeatedly) is that relation file creation/deletion will be logged in
WAL, and so it can be redone or undone after a crash.  That seems a much
more secure approach than anything you've proposed in this thread.

Temp files won't be logged in WAL, but a startup-time cleanup seems
sufficient to deal with them.

> You are correct.  I didn't realized that after I check the shared memory
> for the pid, another backend could start with the pid I was checking and
> create a sort file.  The sort code doesn't do O_EXCL,

... quite deliberately ... you might want to add a comment to that
effect in OpenTemporaryFile.

            regards, tom lane

> > You are correct.  I didn't realized that after I check the shared memory
> > for the pid, another backend could start with the pid I was checking and
> > create a sort file.  The sort code doesn't do O_EXCL,
>
> ... quite deliberately ... you might want to add a comment to that
> effect in OpenTemporaryFile.

Done.

--
  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

OK, Tom doesn't like the majority of my patch.  I think it has value.
Would others like to comment?


> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> No we don't.  I'm not convinced that we have a problem at all anymore.
>
> > We already know that crashes will leave these files around, and as we
> > start using file versioning for CLUSTER and DROP COLUMN, I expect those
> > orphaned files to continue.
>
> I don't.  The intention (not yet implemented, but Vadim's referred to it
> repeatedly) is that relation file creation/deletion will be logged in
> WAL, and so it can be redone or undone after a crash.  That seems a much
> more secure approach than anything you've proposed in this thread.
>
> Temp files won't be logged in WAL, but a startup-time cleanup seems
> sufficient to deal with them.
>
> > You are correct.  I didn't realized that after I check the shared memory
> > for the pid, another backend could start with the pid I was checking and
> > create a sort file.  The sort code doesn't do O_EXCL,
>
> ... quite deliberately ... you might want to add a comment to that
> effect in OpenTemporaryFile.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>

--
  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

I agree with Tom's arguments ...

On Wed, 30 May 2001, Bruce Momjian wrote:

>
> OK, Tom doesn't like the majority of my patch.  I think it has value.
> Would others like to comment?
>
>
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > >> No we don't.  I'm not convinced that we have a problem at all anymore.
> >
> > > We already know that crashes will leave these files around, and as we
> > > start using file versioning for CLUSTER and DROP COLUMN, I expect those
> > > orphaned files to continue.
> >
> > I don't.  The intention (not yet implemented, but Vadim's referred to it
> > repeatedly) is that relation file creation/deletion will be logged in
> > WAL, and so it can be redone or undone after a crash.  That seems a much
> > more secure approach than anything you've proposed in this thread.
> >
> > Temp files won't be logged in WAL, but a startup-time cleanup seems
> > sufficient to deal with them.
> >
> > > You are correct.  I didn't realized that after I check the shared memory
> > > for the pid, another backend could start with the pid I was checking and
> > > create a sort file.  The sort code doesn't do O_EXCL,
> >
> > ... quite deliberately ... you might want to add a comment to that
> > effect in OpenTemporaryFile.
> >
> >             regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://www.postgresql.org/search.mpl
> >
>
> --
>   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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> No we don't.  I'm not convinced that we have a problem at all anymore.
>
> > We already know that crashes will leave these files around, and as we
> > start using file versioning for CLUSTER and DROP COLUMN, I expect those
> > orphaned files to continue.
>
> I don't.  The intention (not yet implemented, but Vadim's referred to it
> repeatedly) is that relation file creation/deletion will be logged in
> WAL, and so it can be redone or undone after a crash.  That seems a much
> more secure approach than anything you've proposed in this thread.

I prefer a WAL cleanup myself, especially if it can be done for 7.2, but
how do we do that without fsync'ing the WAL every time we create a file?

--
  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

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> I don't.  The intention (not yet implemented, but Vadim's referred to it
>> repeatedly) is that relation file creation/deletion will be logged in
>> WAL, and so it can be redone or undone after a crash.  That seems a much
>> more secure approach than anything you've proposed in this thread.

> I prefer a WAL cleanup myself, especially if it can be done for 7.2, but
> how do we do that without fsync'ing the WAL every time we create a file?

Yes, we'd need to fsync WAL at the file creation/deletion points.  So?

            regards, tom lane

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> I don't.  The intention (not yet implemented, but Vadim's referred to it
> >> repeatedly) is that relation file creation/deletion will be logged in
> >> WAL, and so it can be redone or undone after a crash.  That seems a much
> >> more secure approach than anything you've proposed in this thread.
>
> > I prefer a WAL cleanup myself, especially if it can be done for 7.2, but
> > how do we do that without fsync'ing the WAL every time we create a file?
>
> Yes, we'd need to fsync WAL at the file creation/deletion points.  So?

I guess we don't do creation/deletion too much.

--
  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

OK, seems I am the only one that is concerned about orphaned files, so,
Tom, would you let me know which parts of the patch you want applied or
apply the parts you want yourself.  Thanks.


> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> I don't.  The intention (not yet implemented, but Vadim's referred to it
> >> repeatedly) is that relation file creation/deletion will be logged in
> >> WAL, and so it can be redone or undone after a crash.  That seems a much
> >> more secure approach than anything you've proposed in this thread.
>
> > I prefer a WAL cleanup myself, especially if it can be done for 7.2, but
> > how do we do that without fsync'ing the WAL every time we create a file?
>
> Yes, we'd need to fsync WAL at the file creation/deletion points.  So?
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
  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: doc/src/sgml/ref/vacuum.sgml
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/ref/vacuum.sgml,v
retrieving revision 1.16
diff -c -r1.16 vacuum.sgml
*** doc/src/sgml/ref/vacuum.sgml    2001/05/25 15:45:31    1.16
--- doc/src/sgml/ref/vacuum.sgml    2001/05/30 16:27:07
***************
*** 154,160 ****

    <para>
     With no parameter, <command>VACUUM</command> processes every table in the
!    current database.  With a parameter, <command>VACUUM</command> processes
     only that table.
    </para>

--- 154,161 ----

    <para>
     With no parameter, <command>VACUUM</command> processes every table in the
!    current database. It also detects extraneous files in the
!    database directory.  With a parameter, <command>VACUUM</command> processes
     only that table.
    </para>

Index: src/backend/access/transam/varsup.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/access/transam/varsup.c,v
retrieving revision 1.40
diff -c -r1.40 varsup.c
*** src/backend/access/transam/varsup.c    2001/05/25 15:45:32    1.40
--- src/backend/access/transam/varsup.c    2001/05/30 16:27:08
***************
*** 16,22 ****
--- 16,25 ----
  #include "access/transam.h"
  #include "access/xlog.h"
  #include "storage/proc.h"
+ #include "storage/sinval.h"
+ #include "storage/sinvaladt.h"

+ extern 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.195
diff -c -r1.195 vacuum.c
*** src/backend/commands/vacuum.c    2001/05/25 15:45:32    1.195
--- src/backend/commands/vacuum.c    2001/05/30 16:27:21
***************
*** 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,42 ****
--- 33,48 ----

  #include "access/genam.h"
  #include "access/heapam.h"
+ #include "access/transam.h"
  #include "access/xlog.h"
  #include "catalog/catalog.h"
  #include "catalog/catname.h"
+ #include "catalog/heap.h"
  #include "catalog/index.h"
  #include "commands/vacuum.h"
  #include "miscadmin.h"
  #include "nodes/execnodes.h"
+ #include "storage/fd.h"
  #include "storage/sinval.h"
  #include "storage/smgr.h"
  #include "tcop/tcopprot.h"
***************
*** 159,164 ****
--- 165,175 ----
  static bool enough_space(VacPage vacpage, Size len);
  static void init_rusage(VacRUsage *ru0);
  static char *show_rusage(VacRUsage *ru0);
+ static void remove_sorttemp_files(void);
+ static void remove_temp_tables(void);
+ static void report_orphaned_files(void);
+ static Oid *get_pgclass_relfilenodes(Size *num_relfilenodes);
+ static int vc_oidcmp(const void *a, const void* b);


  /*
***************
*** 236,241 ****
--- 247,260 ----

      /* clean up */
      vacuum_shutdown();
+
+     if (VacRelName == NULL)
+     {
+         remove_sorttemp_files();
+         report_orphaned_files();
+         remove_temp_tables();
+     }
+
  }

  /*
***************
*** 2645,2648 ****
--- 2664,2930 ----
               (int) (ru1.tv.tv_usec - ru0->tv.tv_usec) / 10000);

      return result;
+ }
+
+ /*
+  * remove_sorttemp_files
+  *
+  * Remove sorttemp files not referenced by any running backend.
+  * This could be caused by backend crash not cleaning up.
+  */
+ static void
+ remove_sorttemp_files(void)
+ {
+     DIR           *temp_dir;
+     struct dirent  *temp_de;
+     char         cwd[MAXPGPATH];
+     char        temp_path[MAXPGPATH];
+     int            pid;
+
+     getcwd(cwd,MAXPGPATH);
+     temp_dir = opendir(SORT_TEMP_DIR);
+     if (!temp_dir)    /* if there is no directory, nothing to do */
+         return;
+
+     /*
+      *    Cycle through directory and check each file against
+      *    pg_class.relfilenode.
+      */
+     while ((temp_de = readdir(temp_dir)) != NULL)
+     {
+         if (strcmp(temp_de->d_name,".") == 0 ||
+             strcmp(temp_de->d_name,"..") == 0)
+                 continue;
+         if (strncmp(temp_de->d_name,"pid_",strlen("pid_")) != 0)
+             /* Non-pid file name */
+             elog(NOTICE,
+                 "Unusual file found in temporary sort directory.  This file is not\n"
+                 "\t normally created by PostgreSQL and can be removed by the\n"
+                 "\t administrator using 'rm':\n\t %s/%s/%s",
+                 cwd, SORT_TEMP_DIR, temp_de->d_name);
+         else
+         {
+             /* Numeric file names;  extents clip off decimal point */
+             pid = atoi(&temp_de->d_name[4]);
+             if (pid == 0)
+                 elog(NOTICE,
+                     "Unusual file found in temporary sort directory.  This file\n"
+                     "\t is not normally created by PostgreSQL and can be removed\n"
+                     "\t by the administrator using 'rm':\n\t %s/%s/%s",
+                     cwd, SORT_TEMP_DIR, temp_de->d_name);
+             else
+             {
+                 snprintf(temp_path, MAXPGPATH,
+                     "%s/%s/%s", cwd, SORT_TEMP_DIR, temp_de->d_name);
+                 /* Make sure no pid gets created and starts using this file */
+                 SpinAcquire(ShmemIndexLock);
+                 if (ShmemPIDLookup(pid, false) == NULL)
+                     unlink(temp_path);
+                 SpinRelease(ShmemIndexLock);
+             }
+         }
+     }
+     closedir(temp_dir);
+ }
+
+ /*
+  *    remove_temp_tables
+  *
+  *    Remove temporary tables not referenced by any running backend.
+  */
+ static void
+ remove_temp_tables(void)
+ {
+     Relation    rel;
+     TupleDesc    tupdesc;
+     HeapScanDesc scan;
+     HeapTuple    tuple;
+     Datum        d;
+     bool        n;
+     char        *relname;
+     char        relkind;
+     Oid            reloid;
+     int            pid;
+
+     rel = heap_openr(RelationRelationName, AccessShareLock);
+
+     tupdesc = RelationGetDescr(rel);
+
+     scan = heap_beginscan(rel, false, SnapshotNow, 0, (ScanKey) NULL);
+     while (HeapTupleIsValid(tuple = heap_getnext(scan, 0)))
+     {
+         d = heap_getattr(tuple, Anum_pg_class_relname, tupdesc, &n);
+         relname = (char *) DatumGetName(d);
+         d = heap_getattr(tuple, Anum_pg_class_relkind, tupdesc, &n);
+         relkind = DatumGetChar(d);
+         reloid = tuple->t_data->t_oid;
+
+         if (strncmp(relname,"pg_temp.", strlen("pg_temp.")) == 0)
+         {
+             if ((pid = atoi(&relname[8])) != 0 &&
+                 ShmemPIDLookup(pid, true) == NULL)
+             {
+                 if (relkind != RELKIND_INDEX)
+                     heap_drop_with_catalog(relname, true);
+                 else
+                     index_drop(reloid);
+             }
+             CommandCounterIncrement();
+         }
+     }
+     heap_endscan(scan);
+     heap_close(rel, AccessShareLock);
+ }
+
+
+ /*
+  *     report_orphaned_files
+  *
+  *     Report files that are not referenced by any pg_class.relfilenode.
+  *     This could be caused by backend crash not cleaning up.
+  */
+ static void
+ report_orphaned_files(void)
+ {
+     DIR           *db_dir;
+     struct dirent  *db_de;
+     Oid            dir_file_oid;
+     char         cwd[MAXPGPATH];
+     Oid            min_startup_oid = GetMinStartupOid();
+     Size        num_relfilenodes = 0;
+     Oid            *relfilenodes = get_pgclass_relfilenodes(&num_relfilenodes);
+
+     getcwd(cwd,MAXPGPATH);
+     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 (strcmp(db_de->d_name,".") == 0 ||
+             strcmp(db_de->d_name,"..") == 0)
+                 continue;
+         if (strspn(db_de->d_name, "0123456789.") !=
+             strlen(db_de->d_name))
+         {
+             /* Non-numeric file names */
+             if (strcmp(db_de->d_name, "core") == 0)
+                 elog(NOTICE,
+                     "Core file found in database directory.  If you don't need it\n"
+                     "\t for debugging, the administrator can remove it using 'rm':\n\t %s/%s",
+                     cwd, db_de->d_name);
+             else if (strcmp(db_de->d_name, SORT_TEMP_DIR) != 0 &&
+                      strcmp(db_de->d_name, PG_VERSION_FILE) != 0 &&
+                      strcmp(db_de->d_name, RELCACHE_INIT_FILENAME) != 0)
+                 elog(NOTICE,
+                     "Unusual file found in database directory.  This file is not\n"
+                     "\t normally created by PostgreSQL and can be removed by the\n"
+                     "\t administrator using 'rm':\n\t %s/%s",
+                     cwd, db_de->d_name);
+         }
+         else
+         {
+             /* Numeric file names;  extents, clip off decimal point */
+             dir_file_oid = (Oid) strtoul((db_de->d_name), NULL, 10);
+
+             if (/* oid not found in pg_class */
+                 bsearch(&dir_file_oid, relfilenodes, num_relfilenodes,
+                 sizeof(Oid), vc_oidcmp) == NULL &&
+                 /* not active backend */
+                 (dir_file_oid < min_startup_oid ||
+                  dir_file_oid > ShmemVariableCache->nextOid) &&
+                 /* no oid wrap */
+                 min_startup_oid <= ShmemVariableCache->nextOid)
+                 /*
+                  *  At this point, dir_file_oid is _not_ between
+                  *    MinStartupOid  and  nextOid.  We do this test
+                  *    here after we have checked pg_class in case the
+                  *    oid looped during the table scan.
+                  */
+                 elog(NOTICE,
+                     "Unreferenced table file found in database directory.  This\n"
+                     "\t could have been left from a database crash.  This file can\n"
+                     "\t be safely removed by the administrator using 'rm':\n\t %s/%s",
+                     cwd, db_de->d_name);
+                 /* Maybe one day we can unlink too.  bjm 2001-05-24 */
+         }
+     }
+     pfree(relfilenodes);
+     closedir(db_dir);
+ }
+
+
+ /*
+  *    get_pgclass_relfilenodes
+  *
+  *    Return sorted array of Oid's referenced by pg_class.relfilenodes.
+  */
+ static Oid *
+ get_pgclass_relfilenodes(Size *num_relfilenodes)
+ {
+     Relation    rel;
+     TupleDesc    tupdesc;
+     HeapScanDesc scan;
+     HeapTuple    tuple;
+     Datum        d;
+     bool        n;
+     Oid            *relfilenodes = NULL;
+     int            len = 0;
+     Oid            rel_file_oid;
+
+     rel = heap_openr(RelationRelationName, AccessShareLock);
+
+     tupdesc = RelationGetDescr(rel);
+
+     /*
+      *    Make all tuples visible.  This doesn't hurt.
+      *    If we miss an orphan now, we can report on it later.
+      */
+     scan = heap_beginscan(rel, false, SnapshotAny, 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 (*num_relfilenodes >= len)
+         {
+             if (len == 0)
+             {
+                 len = 100;
+                 relfilenodes = palloc(len * sizeof(Oid));
+             }
+             else
+             {
+                 len *= 2;
+                 relfilenodes = repalloc(relfilenodes, len * sizeof(Oid));
+             }
+         }
+         relfilenodes[*num_relfilenodes] = rel_file_oid;
+         (*num_relfilenodes)++;
+     }
+     heap_endscan(scan);
+     heap_close(rel, AccessShareLock);
+
+     qsort(relfilenodes, *num_relfilenodes, sizeof(Oid), vc_oidcmp);
+
+     return relfilenodes;
+ }
+
+
+ /*
+  *    vc_oidcmp
+  *
+  *    Used by qsort() above.
+  */
+ static int
+ vc_oidcmp(const void *a, const void* b)
+ {
+     if (*(Oid *)a > *(Oid *)b)
+         return 1;
+     else if (*(Oid *)a == *(Oid *)b)
+         return 0;
+     else
+         return -1;
  }
Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.215
diff -c -r1.215 postmaster.c
*** src/backend/postmaster/postmaster.c    2001/05/30 14:15:26    1.215
--- src/backend/postmaster/postmaster.c    2001/05/30 16:27: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);

***************
*** 594,599 ****
--- 596,604 ----
      if (!CreateDataDirLockFile(DataDir, true))
          ExitPostmaster(1);

+     /* Remove old sort files */
+     RemovePgSorttemp();
+
      /*
       * Establish input sockets.
       */
***************
*** 2448,2451 ****
--- 2453,2505 ----

      fclose(fp);
      return true;
+ }
+
+
+ /*
+  * Remove old sort files
+  * This is done per database by VACUUM too.
+  */
+ 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.
+      */
+     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)
+                 {
+                     snprintf(rm_path, sizeof(temp_path),
+                         "%s/%s/%s/%s",
+                         db_path, db_de->d_name,
+                         SORT_TEMP_DIR, temp_de->d_name);
+                     if (strspn(temp_de->d_name, "0123456789.") ==
+                         strlen(temp_de->d_name))
+                         unlink(rm_path);
+                     else
+                         fprintf(stderr,"Unexpected file found while cleaning temporary sort directory:\n\t %s\n",
rm_path);
+                 }
+                 closedir(temp_dir);
+             }
+         }
+         closedir(db_dir);
+     }
  }
Index: src/backend/storage/file/fd.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/storage/file/fd.c,v
retrieving revision 1.79
diff -c -r1.79 fd.c
*** src/backend/storage/file/fd.c    2001/05/30 14:15:26    1.79
--- src/backend/storage/file/fd.c    2001/05/30 16:27:34
***************
*** 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,774 ----
  File
  OpenTemporaryFile(void)
  {
!     char        tempfilepath[128];
      File        file;

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

!     /*
!      *    Open the file -
!      *    Don't use O_EXCL in case there is an orphaned sort file that
!      *    can be reused.
!      */
!     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/ipc/shmem.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/storage/ipc/shmem.c,v
retrieving revision 1.57
diff -c -r1.57 shmem.c
*** src/backend/storage/ipc/shmem.c    2001/03/22 03:59:45    1.57
--- src/backend/storage/ipc/shmem.c    2001/05/30 16:27:35
***************
*** 260,276 ****
  }

  /*
!  * ShmemPIDLookup -- lookup process data structure using process id
   *
!  * Returns: TRUE if no error.  locationPtr is initialized if PID is
!  *        found in the shmem index.
   *
-  * NOTES:
-  *        only information about success or failure is the value of
-  *        locationPtr.
   */
! bool
! ShmemPIDLookup(int pid, SHMEM_OFFSET *locationPtr)
  {
      ShmemIndexEnt *result,
                  item;
--- 260,272 ----
  }

  /*
!  * ShmemPIDLookup -- lookup process structure using process id
   *
!  * Returns shared memory pointer or NULL.
   *
   */
! SHMEM_OFFSET
! ShmemPIDLookup(int pid, bool do_locking)
  {
      ShmemIndexEnt *result,
                  item;
***************
*** 280,305 ****
      MemSet(item.key, 0, SHMEM_INDEX_KEYSIZE);
      sprintf(item.key, "PID %d", pid);

!     SpinAcquire(ShmemIndexLock);

      result = (ShmemIndexEnt *)
!         hash_search(ShmemIndex, (char *) &item, HASH_ENTER, &found);

      if (!result)
      {
          SpinRelease(ShmemIndexLock);
          elog(ERROR, "ShmemInitPID: ShmemIndex corrupted");
-         return FALSE;
      }

      if (found)
!         *locationPtr = result->location;
      else
          result->location = *locationPtr;

      SpinRelease(ShmemIndexLock);
!     return TRUE;
  }

  /*
   * ShmemPIDDestroy -- destroy shmem index entry for process
--- 276,339 ----
      MemSet(item.key, 0, SHMEM_INDEX_KEYSIZE);
      sprintf(item.key, "PID %d", pid);

!     if (do_locking)
!         SpinAcquire(ShmemIndexLock);

      result = (ShmemIndexEnt *)
!         hash_search(ShmemIndex, (char *) &item, HASH_FIND, &found);

      if (!result)
      {
          SpinRelease(ShmemIndexLock);
          elog(ERROR, "ShmemInitPID: ShmemIndex corrupted");
      }

+     if (do_locking)
+         SpinRelease(ShmemIndexLock);
+
      if (found)
!         return result->location;
      else
+         return NULL;
+ }
+
+
+ /*
+  *     ShmemPIDAdd -- add process id to shared memory.
+  *
+  *     Returns boolean indicating success/failure.
+  *
+  */
+ bool
+ ShmemPIDAdd(int pid, SHMEM_OFFSET *locationPtr)
+ {
+     ShmemIndexEnt *result,
+                 item;
+     bool        found;
+
+     Assert(ShmemIndex);
+     MemSet(item.key, 0, SHMEM_INDEX_KEYSIZE);
+     sprintf(item.key, "PID %d", pid);
+
+     SpinAcquire(ShmemIndexLock);
+
+     result = (ShmemIndexEnt *)
+         hash_search(ShmemIndex, (char *) &item,
+         HASH_ENTER, &found);
+
+     if (!result)
+     {
+         SpinRelease(ShmemIndexLock);
+         elog(ERROR, "ShmemInitPID: ShmemIndex corrupted");
+     }
+
+     if (!found)
          result->location = *locationPtr;

      SpinRelease(ShmemIndexLock);
!     return !found;
  }
+

  /*
   * ShmemPIDDestroy -- destroy shmem index entry for process
Index: src/backend/storage/lmgr/lock.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/storage/lmgr/lock.c,v
retrieving revision 1.88
diff -c -r1.88 lock.c
*** src/backend/storage/lmgr/lock.c    2001/03/22 03:59:46    1.88
--- src/backend/storage/lmgr/lock.c    2001/05/30 16:27:43
***************
*** 1458,1465 ****
      int            lockmethod = DEFAULT_LOCKMETHOD;
      LOCKMETHODTABLE *lockMethodTable;

!     ShmemPIDLookup(MyProcPid, &location);
!     if (location == INVALID_OFFSET)
          return;
      proc = (PROC *) MAKE_PTR(location);
      if (proc != MyProc)
--- 1458,1465 ----
      int            lockmethod = DEFAULT_LOCKMETHOD;
      LOCKMETHODTABLE *lockMethodTable;

!     if ((location = ShmemPIDLookup(MyProcPid, true)) == NULL ||
!         location == INVALID_OFFSET)
          return;
      proc = (PROC *) MAKE_PTR(location);
      if (proc != MyProc)
***************
*** 1508,1515 ****
      HASH_SEQ_STATUS status;

      pid = getpid();
!     ShmemPIDLookup(pid, &location);
!     if (location == INVALID_OFFSET)
          return;
      proc = (PROC *) MAKE_PTR(location);
      if (proc != MyProc)
--- 1508,1515 ----
      HASH_SEQ_STATUS status;

      pid = getpid();
!     If ((location = ShmemPIDLookup(pid, true)) == NULL ||
!         location == INVALID_OFFSET)
          return;
      proc = (PROC *) MAKE_PTR(location);
      if (proc != MyProc)
Index: src/backend/storage/lmgr/proc.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/storage/lmgr/proc.c,v
retrieving revision 1.102
diff -c -r1.102 proc.c
*** src/backend/storage/lmgr/proc.c    2001/05/25 15:45:33    1.102
--- src/backend/storage/lmgr/proc.c    2001/05/30 16:27:48
***************
*** 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));
***************
*** 276,283 ****
       * process to find us after any untimely exit.
       */
      location = MAKE_OFFSET(MyProc);
!     if ((!ShmemPIDLookup(MyProcPid, &location)) ||
!         (location != MAKE_OFFSET(MyProc)))
          elog(STOP, "InitProcess: ShmemPID table broken");

      /*
--- 277,284 ----
       * process to find us after any untimely exit.
       */
      location = MAKE_OFFSET(MyProc);
!     if (!ShmemPIDAdd(MyProcPid, &location) ||
!         location != MAKE_OFFSET(MyProc))
          elog(STOP, "InitProcess: ShmemPID table broken");

      /*
Index: src/backend/utils/cache/temprel.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/cache/temprel.c,v
retrieving revision 1.35
diff -c -r1.35 temprel.c
*** src/backend/utils/cache/temprel.c    2001/03/22 03:59:58    1.35
--- src/backend/utils/cache/temprel.c    2001/05/30 16:27:50
***************
*** 230,236 ****
          {
              char        relname[NAMEDATALEN];

!             /* safe from deallocation */
              strcpy(relname, NameStr(temp_rel->user_relname));
              heap_drop_with_catalog(relname, allowSystemTableMods);
          }
--- 230,236 ----
          {
              char        relname[NAMEDATALEN];

!             /* save from deallocation */
              strcpy(relname, NameStr(temp_rel->user_relname));
              heap_drop_with_catalog(relname, allowSystemTableMods);
          }
Index: src/backend/utils/init/miscinit.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/init/miscinit.c,v
retrieving revision 1.67
diff -c -r1.67 miscinit.c
*** src/backend/utils/init/miscinit.c    2001/05/18 17:49:52    1.67
--- src/backend/utils/init/miscinit.c    2001/05/30 16:27:53
***************
*** 824,830 ****
      if (*endptr == '.')
          my_minor = strtol(endptr + 1, NULL, 10);

!     snprintf(full_path, MAXPGPATH, "%s/PG_VERSION", path);

      file = AllocateFile(full_path, "r");
      if (!file)
--- 824,830 ----
      if (*endptr == '.')
          my_minor = strtol(endptr + 1, NULL, 10);

!     snprintf(full_path, MAXPGPATH, "%s/%s", path, PG_VERSION_FILE);

      file = AllocateFile(full_path, "r");
      if (!file)
Index: src/include/miscadmin.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/miscadmin.h,v
retrieving revision 1.85
diff -c -r1.85 miscadmin.h
*** src/include/miscadmin.h    2001/05/12 01:48:49    1.85
--- src/include/miscadmin.h    2001/05/30 16:27:54
***************
*** 132,137 ****
--- 132,139 ----

  extern int    DebugLvl;

+ #define PG_VERSION_FILE "PG_VERSION"
+
  /* Date/Time Configuration
   *
   * Constants to pass info from runtime environment:
Index: src/include/access/transam.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/access/transam.h,v
retrieving revision 1.35
diff -c -r1.35 transam.h
*** src/include/access/transam.h    2001/05/25 15:45:33    1.35
--- src/include/access/transam.h    2001/05/30 16:27:55
***************
*** 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.29
diff -c -r1.29 fd.h
*** src/include/storage/fd.h    2001/05/25 15:45:34    1.29
--- src/include/storage/fd.h    2001/05/30 16:27:55
***************
*** 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.43
diff -c -r1.43 proc.h
*** src/include/storage/proc.h    2001/05/25 15:45:34    1.43
--- src/include/storage/proc.h    2001/05/30 16:27:56
***************
*** 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
Index: src/include/storage/shmem.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/storage/shmem.h,v
retrieving revision 1.28
diff -c -r1.28 shmem.h
*** src/include/storage/shmem.h    2001/03/22 04:01:09    1.28
--- src/include/storage/shmem.h    2001/05/30 16:27:57
***************
*** 71,77 ****
  extern bool ShmemIsValid(unsigned long addr);
  extern HTAB *ShmemInitHash(char *name, long init_size, long max_size,
                HASHCTL *infoP, int hash_flags);
! extern bool ShmemPIDLookup(int pid, SHMEM_OFFSET *locationPtr);
  extern SHMEM_OFFSET ShmemPIDDestroy(int pid);
  extern void *ShmemInitStruct(char *name, Size size, bool *foundPtr);

--- 71,78 ----
  extern bool ShmemIsValid(unsigned long addr);
  extern HTAB *ShmemInitHash(char *name, long init_size, long max_size,
                HASHCTL *infoP, int hash_flags);
! extern SHMEM_OFFSET ShmemPIDLookup(int pid, bool do_locking);
! extern bool ShmemPIDAdd(int pid, SHMEM_OFFSET *locationPtr);
  extern SHMEM_OFFSET ShmemPIDDestroy(int pid);
  extern void *ShmemInitStruct(char *name, Size size, bool *foundPtr);