Removal of temp tables - Mailing list pgsql-patches

Here is a patch that allows people to delete the pg_temp* tables used as
temp tables.  They are left around after a backend crash and the only
way previously to remove them was to start postgres with the -O override
option.

I am wondering if pg_temp tables should even be seen as system tables by
IsSystemRelationName().  We have to call them pg_ so user applications
don't display them, but other than that they aren't like system tables.
Comments?

There are no tests to see if the table is actually in use.  I can add
them if people want it.

---------------------------------------------------------------------------

    test=> CREATE TEMP TABLE test (x int);
    CREATE
    test=> \dS
              List of relations
          Name      |  Type   |  Owner
    ----------------+---------+----------
    ...
     pg_temp.6682.0 | table   | postgres
    ...
    test=> DROP TABLE "pg_temp.6682.0";
    DROP

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
Index: src/backend/catalog/aclchk.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/catalog/aclchk.c,v
retrieving revision 1.50
diff -c -r1.50 aclchk.c
*** src/backend/catalog/aclchk.c    2001/06/09 23:21:54    1.50
--- src/backend/catalog/aclchk.c    2001/06/14 04:06:32
***************
*** 32,37 ****
--- 32,38 ----
  #include "parser/parse_func.h"
  #include "utils/acl.h"
  #include "utils/syscache.h"
+ #include "utils/temprel.h"

  static int32 aclcheck(Acl *acl, AclId id, AclIdType idtype, AclMode mode);

***************
*** 437,443 ****
       */
      if ((mode & (ACL_INSERT | ACL_UPDATE | ACL_DELETE)) &&
          !allowSystemTableMods && IsSystemRelationName(relname) &&
!         strncmp(relname, "pg_temp.", strlen("pg_temp.")) != 0 &&
          !((Form_pg_shadow) GETSTRUCT(tuple))->usecatupd)
      {
  #ifdef ACLDEBUG
--- 438,444 ----
       */
      if ((mode & (ACL_INSERT | ACL_UPDATE | ACL_DELETE)) &&
          !allowSystemTableMods && IsSystemRelationName(relname) &&
!         !is_temp_relname(relname) &&
          !((Form_pg_shadow) GETSTRUCT(tuple))->usecatupd)
      {
  #ifdef ACLDEBUG
Index: src/backend/catalog/heap.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/catalog/heap.c,v
retrieving revision 1.167
diff -c -r1.167 heap.c
*** src/backend/catalog/heap.c    2001/06/12 05:55:49    1.167
--- src/backend/catalog/heap.c    2001/06/14 04:06:38
***************
*** 281,288 ****
           * replace relname of caller with a unique name for a temp
           * relation
           */
!         snprintf(relname, NAMEDATALEN, "pg_temp.%d.%u",
!                  (int) MyProcPid, uniqueId++);
      }

      /*
--- 281,288 ----
           * replace relname of caller with a unique name for a temp
           * relation
           */
!         snprintf(relname, NAMEDATALEN, "%s.%d.%u",
!                 PG_TEMP_REL_PREFIX, (int) MyProcPid, uniqueId++);
      }

      /*
***************
*** 874,910 ****
  }


- /* ----------------------------------------------------------------
-  *        heap_drop_with_catalog    - removes all record of named relation from catalogs
-  *
-  *        1)    open relation, check for existence, etc.
-  *        2)    remove inheritance information
-  *        3)    remove indexes
-  *        4)    remove pg_class tuple
-  *        5)    remove pg_attribute tuples and related descriptions
-  *                6)        remove pg_description tuples
-  *        7)    remove pg_type tuples
-  *        8)    RemoveConstraints ()
-  *        9)    unlink relation
-  *
-  * old comments
-  *        Except for vital relations, removes relation from
-  *        relation catalog, and related attributes from
-  *        attribute catalog (needed?).  (Anything else?)
-  *
-  *        get proper relation from relation catalog (if not arg)
-  *        scan attribute catalog deleting attributes of reldesc
-  *                (necessary?)
-  *        delete relation from relation catalog
-  *        (How are the tuples of the relation discarded?)
-  *
-  *        XXX Must fix to work with indexes.
-  *        There may be a better order for doing things.
-  *        Problems with destroying a deleted database--cannot create
-  *        a struct reldesc without having an open file descriptor.
-  * ----------------------------------------------------------------
-  */
-
  /* --------------------------------
   *        RelationRemoveInheritance
   *
--- 874,879 ----
***************
*** 1334,1343 ****
      heap_close(pg_type_desc, RowExclusiveLock);
  }

! /* --------------------------------
!  *        heap_drop_with_catalog
   *
!  * --------------------------------
   */
  void
  heap_drop_with_catalog(const char *relname,
--- 1303,1337 ----
      heap_close(pg_type_desc, RowExclusiveLock);
  }

! /* ----------------------------------------------------------------
!  *        heap_drop_with_catalog    - removes all record of named relation from catalogs
   *
!  *        1)    open relation, check for existence, etc.
!  *        2)    remove inheritance information
!  *        3)    remove indexes
!  *        4)    remove pg_class tuple
!  *        5)    remove pg_attribute tuples and related descriptions
!  *                6)        remove pg_description tuples
!  *        7)    remove pg_type tuples
!  *        8)    RemoveConstraints ()
!  *        9)    unlink relation
!  *
!  * old comments
!  *        Except for vital relations, removes relation from
!  *        relation catalog, and related attributes from
!  *        attribute catalog (needed?).  (Anything else?)
!  *
!  *        get proper relation from relation catalog (if not arg)
!  *        scan attribute catalog deleting attributes of reldesc
!  *                (necessary?)
!  *        delete relation from relation catalog
!  *        (How are the tuples of the relation discarded?)
!  *
!  *        XXX Must fix to work with indexes.
!  *        There may be a better order for doing things.
!  *        Problems with destroying a deleted database--cannot create
!  *        a struct reldesc without having an open file descriptor.
!  * ----------------------------------------------------------------
   */
  void
  heap_drop_with_catalog(const char *relname,
***************
*** 1360,1367 ****
       * prevent deletion of system relations
       */
      /* allow temp of pg_class? Guess so. */
!     if (!istemp && !allow_system_table_mods &&
!         IsSystemRelationName(RelationGetRelationName(rel)))
          elog(ERROR, "System relation \"%s\" may not be dropped",
               RelationGetRelationName(rel));

--- 1354,1363 ----
       * prevent deletion of system relations
       */
      /* allow temp of pg_class? Guess so. */
!     if (!istemp &&
!         !allow_system_table_mods &&
!         IsSystemRelationName(RelationGetRelationName(rel)) &&
!         !is_temp_relname(RelationGetRelationName(rel)))
          elog(ERROR, "System relation \"%s\" may not be dropped",
               RelationGetRelationName(rel));

Index: src/backend/commands/vacuum.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.196
diff -c -r1.196 vacuum.c
*** src/backend/commands/vacuum.c    2001/06/13 21:44:40    1.196
--- src/backend/commands/vacuum.c    2001/06/14 04:06:40
***************
*** 491,497 ****
      vacuum_pages.num_pages = fraged_pages.num_pages = 0;
      scan_heap(vacrelstats, onerel, &vacuum_pages, &fraged_pages);
      if (IsIgnoringSystemIndexes() &&
!         IsSystemRelationName(RelationGetRelationName(onerel)))
          reindex = true;

      /* Now open indices */
--- 491,498 ----
      vacuum_pages.num_pages = fraged_pages.num_pages = 0;
      scan_heap(vacrelstats, onerel, &vacuum_pages, &fraged_pages);
      if (IsIgnoringSystemIndexes() &&
!         IsSystemRelationName(RelationGetRelationName(onerel)) &&
!         !is_temp_relname(RelationGetRelationName(onerel)))
          reindex = true;

      /* Now open indices */
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.113
diff -c -r1.113 utility.c
*** src/backend/tcop/utility.c    2001/06/09 23:21:54    1.113
--- src/backend/tcop/utility.c    2001/06/14 04:06:44
***************
*** 46,51 ****
--- 46,52 ----
  #include "utils/acl.h"
  #include "utils/ps_status.h"
  #include "utils/syscache.h"
+ #include "utils/temprel.h"
  #include "access/xlog.h"

  /*
***************
*** 120,126 ****
          elog(ERROR, "you do not own %s \"%s\"",
               rentry->name, name);

!     if (!allowSystemTableMods && IsSystemRelationName(name))
          elog(ERROR, "%s \"%s\" is a system %s",
               rentry->name, name, rentry->name);

--- 121,128 ----
          elog(ERROR, "you do not own %s \"%s\"",
               rentry->name, name);

!     if (!allowSystemTableMods && IsSystemRelationName(name) &&
!         !is_temp_relname(name))
          elog(ERROR, "%s \"%s\" is a system %s",
               rentry->name, name, rentry->name);

Index: src/include/utils/temprel.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/utils/temprel.h,v
retrieving revision 1.15
diff -c -r1.15 temprel.h
*** src/include/utils/temprel.h    2001/03/22 04:01:14    1.15
--- src/include/utils/temprel.h    2001/06/14 04:06:47
***************
*** 16,21 ****
--- 16,26 ----

  #include "access/htup.h"

+ #define PG_TEMP_REL_PREFIX "pg_temp"
+
+ #define is_temp_relname(relname) \
+         (!strncmp(relname, PG_TEMP_REL_PREFIX, strlen(PG_TEMP_REL_PREFIX)))
+
  extern void create_temp_relation(const char *relname,
                       HeapTuple pg_class_tuple);
  extern void remove_temp_rel_by_relid(Oid relid);