Here is a cleaned-up version of the unreference file patch that was discussed extensively in May of 2005. I want to get it into the archives in case someone else want to work on it.
Here is a reference to the work still needed on the patch: http://archives.postgresql.org/pgsql-patches/2005-05/msg00024.php -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
/*------------------------------------------------------------------------- * * checkfiles.c * check for stale relation files during crash recovery * * If a backend crashes while in a transaction that has created or * deleted a relfilenode, a stale file can be left over in the data * directory. This file contains routines to clean up those stale * files on recovery. * * This adds a 17% increase in startup cost for 100 empty databases. bjm * One optimization would be to create a 'dirty' file on a postmaster recovery * and remove the dirty flag only when a clean startup detects no unreferenced * files, and use the 'dirty' flag to determine if we should run this on * a clean startup. * * $PostgreSQL: pgsql/src/backend/utils/init/checkfiles.c,v 1.2 2005/05/05 22:18:27 tgl Exp $ * *------------------------------------------------------------------------- */ #include "postgres.h" #include "access/heapam.h" #include "access/relscan.h" #include "access/skey.h" #include "catalog/catalog.h" #include "catalog/pg_tablespace.h" #include "miscadmin.h" #include "storage/fd.h" #include "utils/flatfiles.h" #include "utils/fmgroids.h" #include "utils/resowner.h" static void CheckStaleRelFilesFrom(Oid tablespaceoid, Oid dboid); static void CheckStaleRelFilesFromTablespace(Oid tablespaceoid); /* Like AllocateDir, but ereports on failure */ static DIR * AllocateDirChecked(char *path) { DIR *dirdesc = AllocateDir(path); if (dirdesc == NULL) ereport(ERROR, (errcode_for_file_access(), errmsg("could not open directory \"%s\": %m", path))); return dirdesc; } /* * Scan through all tablespaces for relations left over * by aborted transactions. */ void CheckStaleRelFiles(void) { DIR *dirdesc; struct dirent *de; char *path; int pathlen; pathlen = strlen(DataDir) + 11 + 1; path = (char *) palloc(pathlen); snprintf(path, pathlen, "%s/pg_tblspc/", DataDir); dirdesc = AllocateDirChecked(path); while ((de = readdir(dirdesc)) != NULL) { char *invalid; Oid tablespaceoid; /* Check that the directory name looks like valid tablespace link. */ tablespaceoid = (Oid) strtol(de->d_name, &invalid, 10); if (invalid[0] == '\0') CheckStaleRelFilesFromTablespace(tablespaceoid); } FreeDir(dirdesc); pfree(path); CheckStaleRelFilesFromTablespace(DEFAULTTABLESPACE_OID); } /* Scan a specific tablespace for stale relations */ static void CheckStaleRelFilesFromTablespace(Oid tablespaceoid) { DIR *dirdesc; struct dirent *de; char *path; path = GetTablespacePath(tablespaceoid); dirdesc = AllocateDirChecked(path); while ((de = readdir(dirdesc)) != NULL) { char *invalid; Oid dboid; dboid = (Oid) strtol(de->d_name, &invalid, 10); if (invalid[0] == '\0') CheckStaleRelFilesFrom(tablespaceoid, dboid); } FreeDir(dirdesc); pfree(path); } /* Scan a specific database in a specific tablespace for stale relations. * * First, pg_class for the database is opened, and the relfilenodes of all * relations mentioned there are stored in a hash table. * * Then the directory is scanned. Every file in the directory that's not * found in pg_class (the hash table) is logged. */ static void CheckStaleRelFilesFrom(Oid tablespaceoid, Oid dboid) { DIR *dirdesc; struct dirent *de; HASHCTL hashctl; HTAB *relfilenodeHash; RelFileNode rnode; char *path; /* * The entry contents is not used for anything, we just check if an oid is * in the hash table or not. */ hashctl.keysize = sizeof(Oid); hashctl.entrysize = sizeof(Oid); hashctl.hash = tag_hash; relfilenodeHash = hash_create("relfilenodeHash", 100, &hashctl, HASH_FUNCTION | HASH_ELEM); /* Read all relfilenodes from pg_class into the hash table */ { ResourceOwner owner, oldowner; Relation rel; HeapScanDesc scan; HeapTuple tuple; /* Need a resowner to keep the heapam and buffer code happy */ owner = ResourceOwnerCreate(NULL, "CheckStaleRelFiles"); oldowner = CurrentResourceOwner; CurrentResourceOwner = owner; rnode.spcNode = tablespaceoid; rnode.dbNode = dboid; rnode.relNode = RelationRelationId; rel = XLogOpenRelation(true, 0, rnode); scan = heap_beginscan(rel, SnapshotNow, 0, NULL); while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL) { Form_pg_class classform = (Form_pg_class) GETSTRUCT(tuple); hash_search(relfilenodeHash, &classform->relfilenode, HASH_ENTER, NULL); } heap_endscan(scan); XLogCloseRelation(rnode); CurrentResourceOwner = oldowner; ResourceOwnerDelete(owner); } /* Scan the directory */ path = GetDatabasePath(dboid, tablespaceoid); dirdesc = AllocateDirChecked(path); while ((de = readdir(dirdesc)) != NULL) { char *invalid; Oid relfilenode; relfilenode = strtol(de->d_name, &invalid, 10); if (invalid[0] == '\0') { /* * Filename was a valid number, check if pg_class knows about it */ if (hash_search(relfilenodeHash, &relfilenode, HASH_FIND, NULL) == NULL) { char *filepath; rnode.spcNode = tablespaceoid; rnode.dbNode = dboid; rnode.relNode = relfilenode; filepath = relpath(rnode); ereport(LOG, (errcode_for_file_access(), errmsg("table or index file \"%s\" is stale and can safely be removed", filepath))); pfree(filepath); } } } FreeDir(dirdesc); pfree(path); hash_destroy(relfilenodeHash); }
Index: doc/src/sgml/maintenance.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v retrieving revision 1.41 retrieving revision 1.42 diff -c -r1.41 -r1.42 *** doc/src/sgml/maintenance.sgml 20 Feb 2005 02:21:26 -0000 1.41 --- doc/src/sgml/maintenance.sgml 2 May 2005 18:26:52 -0000 1.42 *************** *** 474,479 **** --- 474,496 ---- </para> </sect1> + <sect1 id="check-files-after-crash"> + <title>Check files after crash</title> + + <indexterm zone="check-files-after-crash"> + <primary>stale file</primary> + </indexterm> + + <para> + <productname>PostgreSQL</productname> recovers automatically after crash + using the write-ahead log (see <xref linkend="wal">) and no manual + operations are normally needed. However, if there was a transaction running + when the crash occured that created or dropped a relation, the + transaction might have left a stale file in the data directory. If this + happens, you will get a notice in the log file stating which files can be + deleted. + </para> + </sect1> <sect1 id="logfile-maintenance"> <title>Log File Maintenance</title> Index: src/backend/access/transam/xlog.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/access/transam/xlog.c,v retrieving revision 1.189 retrieving revision 1.190 diff -c -r1.189 -r1.190 *** src/backend/access/transam/xlog.c 28 Apr 2005 21:47:10 -0000 1.189 --- src/backend/access/transam/xlog.c 2 May 2005 18:26:52 -0000 1.190 *************** *** 43,48 **** --- 43,49 ---- #include "utils/builtins.h" #include "utils/guc.h" #include "utils/relcache.h" + #include "utils/flatfiles.h" /* *************** *** 4525,4530 **** --- 4526,4533 ---- CreateCheckPoint(true, true); + CheckStaleRelFiles(); + /* * Close down recovery environment */ *************** *** 4536,4541 **** --- 4539,4550 ---- */ remove_backup_label(); } + else + { + XLogInitRelationCache(); + CheckStaleRelFiles(); + XLogCloseRelationCache(); + } /* * Preallocate additional log files, if wanted. Index: src/backend/catalog/catalog.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/catalog/catalog.c,v retrieving revision 1.59 retrieving revision 1.60 diff -c -r1.59 -r1.60 *** src/backend/catalog/catalog.c 14 Apr 2005 20:03:23 -0000 1.59 --- src/backend/catalog/catalog.c 2 May 2005 18:26:53 -0000 1.60 *************** *** 106,111 **** --- 106,144 ---- return path; } + /* + * GetTablespacePath - construct path to a tablespace symbolic link + * + * Result is a palloc'd string. + * + * XXX this must agree with relpath and GetDatabasePath! + */ + char * + GetTablespacePath(Oid spcNode) + { + int pathlen; + char *path; + + Assert(spcNode != GLOBALTABLESPACE_OID); + + if (spcNode == DEFAULTTABLESPACE_OID) + { + /* The default tablespace is {datadir}/base */ + pathlen = strlen(DataDir) + 5 + 1; + path = (char *) palloc(pathlen); + snprintf(path, pathlen, "%s/base", + DataDir); + } + else + { + /* All other tablespaces have symlinks in pg_tblspc */ + pathlen = strlen(DataDir) + 11 + OIDCHARS + 1; + path = (char *) palloc(pathlen); + snprintf(path, pathlen, "%s/pg_tblspc/%u", + DataDir, spcNode); + } + return path; + } /* * IsSystemRelation Index: src/backend/commands/tablespace.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/commands/tablespace.c,v retrieving revision 1.17 retrieving revision 1.18 diff -c -r1.17 -r1.18 *** src/backend/commands/tablespace.c 14 Apr 2005 20:03:24 -0000 1.17 --- src/backend/commands/tablespace.c 2 May 2005 18:26:53 -0000 1.18 *************** *** 341,348 **** /* * All seems well, create the symlink */ ! linkloc = (char *) palloc(strlen(DataDir) + 11 + 10 + 1); ! sprintf(linkloc, "%s/pg_tblspc/%u", DataDir, tablespaceoid); if (symlink(location, linkloc) < 0) ereport(ERROR, --- 341,347 ---- /* * All seems well, create the symlink */ ! linkloc = GetTablespacePath(tablespaceoid); if (symlink(location, linkloc) < 0) ereport(ERROR, *************** *** 495,502 **** char *subfile; struct stat st; ! location = (char *) palloc(strlen(DataDir) + 11 + 10 + 1); ! sprintf(location, "%s/pg_tblspc/%u", DataDir, tablespaceoid); /* * Check if the tablespace still contains any files. We try to rmdir --- 494,500 ---- char *subfile; struct stat st; ! location = GetTablespacePath(tablespaceoid); /* * Check if the tablespace still contains any files. We try to rmdir *************** *** 1036,1043 **** set_short_version(location); /* Create the symlink if not already present */ ! linkloc = (char *) palloc(strlen(DataDir) + 11 + 10 + 1); ! sprintf(linkloc, "%s/pg_tblspc/%u", DataDir, xlrec->ts_id); if (symlink(location, linkloc) < 0) { --- 1034,1040 ---- set_short_version(location); /* Create the symlink if not already present */ ! linkloc = GetTablespacePath(xlrec->ts_id); if (symlink(location, linkloc) < 0) { Index: src/backend/utils/adt/misc.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/utils/adt/misc.c,v retrieving revision 1.40 retrieving revision 1.41 diff -c -r1.40 -r1.41 *** src/backend/utils/adt/misc.c 31 Dec 2004 22:01:22 -0000 1.40 --- src/backend/utils/adt/misc.c 2 May 2005 18:26:53 -0000 1.41 *************** *** 26,31 **** --- 26,32 ---- #include "funcapi.h" #include "catalog/pg_type.h" #include "catalog/pg_tablespace.h" + #include "catalog/catalog.h" #define atooid(x) ((Oid) strtoul((x), NULL, 10)) *************** *** 144,154 **** fctx = palloc(sizeof(ts_db_fctx)); - /* - * size = path length + tablespace dirname length + 2 dir sep - * chars + oid + terminator - */ - fctx->location = (char *) palloc(strlen(DataDir) + 11 + 10 + 1); if (tablespaceOid == GLOBALTABLESPACE_OID) { fctx->dirdesc = NULL; --- 145,150 ---- *************** *** 157,168 **** } else { ! if (tablespaceOid == DEFAULTTABLESPACE_OID) ! sprintf(fctx->location, "%s/base", DataDir); ! else ! sprintf(fctx->location, "%s/pg_tblspc/%u", DataDir, ! tablespaceOid); ! fctx->dirdesc = AllocateDir(fctx->location); if (!fctx->dirdesc) --- 153,159 ---- } else { ! fctx->location = GetTablespacePath(tablespaceOid); fctx->dirdesc = AllocateDir(fctx->location); if (!fctx->dirdesc) Index: src/backend/utils/init/Makefile =================================================================== RCS file: /cvsroot/pgsql/src/backend/utils/init/Makefile,v retrieving revision 1.18 retrieving revision 1.19 diff -c -r1.18 -r1.19 *** src/backend/utils/init/Makefile 20 Feb 2005 02:22:00 -0000 1.18 --- src/backend/utils/init/Makefile 2 May 2005 18:26:53 -0000 1.19 *************** *** 12,18 **** top_builddir = ../../../.. include $(top_builddir)/src/Makefile.global ! OBJS = flatfiles.o globals.o miscinit.o postinit.o all: SUBSYS.o --- 12,18 ---- top_builddir = ../../../.. include $(top_builddir)/src/Makefile.global ! OBJS = flatfiles.o globals.o miscinit.o postinit.o checkfiles.o all: SUBSYS.o Index: src/include/catalog/catalog.h =================================================================== RCS file: /cvsroot/pgsql/src/include/catalog/catalog.h,v retrieving revision 1.30 retrieving revision 1.31 diff -c -r1.30 -r1.31 *** src/include/catalog/catalog.h 31 Dec 2004 22:03:24 -0000 1.30 --- src/include/catalog/catalog.h 2 May 2005 18:26:54 -0000 1.31 *************** *** 19,24 **** --- 19,25 ---- extern char *relpath(RelFileNode rnode); extern char *GetDatabasePath(Oid dbNode, Oid spcNode); + extern char *GetTablespacePath(Oid spcNode); extern bool IsSystemRelation(Relation relation); extern bool IsToastRelation(Relation relation); Index: src/include/utils/flatfiles.h =================================================================== RCS file: /cvsroot/pgsql/src/include/utils/flatfiles.h,v retrieving revision 1.1 retrieving revision 1.2 diff -c -r1.1 -r1.2 *** src/include/utils/flatfiles.h 20 Feb 2005 02:22:07 -0000 1.1 --- src/include/utils/flatfiles.h 2 May 2005 18:26:54 -0000 1.2 *************** *** 30,33 **** --- 30,36 ---- extern Datum flatfile_update_trigger(PG_FUNCTION_ARGS); + /* from checkfiles.c */ + extern void CheckStaleRelFiles(void); + #endif /* FLATFILES_H */
---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq