Re: slow commits with heavy temp table usage in 8.4.0 - Mailing list pgsql-hackers

I wrote:
> As I said, my inclination for improving this area, if someone wanted
> to work on it, would be to find a way to do truncate-in-place on
> temp tables.  ISTM that in the case you're showing --- truncate that's
> not within a subtransaction, on a table that's drop-on-commit anyway
> --- we should not need to keep around the pre-truncation data.  So we
> could just do ftruncate instead of creating a new file, and we'd not
> need a new copy of the pg_class row either.  So that should make both
> the function time and the commit time a lot better.  But I'm not sure
> if the use-case is popular enough to deserve such a hack.

Actually, this is easier than I thought, because there is already
bookkeeping being done that (in effect) tracks whether a table has
already been truncated in the current transaction.  So we can rely
on that, and with only a very few lines of code added, ensure that
a situation like this does only one full-scale transaction-safe
truncation per transaction.  The attached prototype patch does this
and seems to fix the speed problem nicely.  It's not tremendously
well tested, but perhaps you'd like to test?  Should work in 8.4.

            regards, tom lane

Index: src/backend/catalog/heap.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/heap.c,v
retrieving revision 1.357
diff -c -r1.357 heap.c
*** src/backend/catalog/heap.c    2 Aug 2009 22:14:52 -0000    1.357
--- src/backend/catalog/heap.c    6 Aug 2009 06:15:05 -0000
***************
*** 2342,2359 ****
      {
          Oid            rid = lfirst_oid(cell);
          Relation    rel;
-         Oid            toastrelid;

          rel = heap_open(rid, AccessExclusiveLock);
          relations = lappend(relations, rel);
-
-         /* If there is a toast table, add it to the list too */
-         toastrelid = rel->rd_rel->reltoastrelid;
-         if (OidIsValid(toastrelid))
-         {
-             rel = heap_open(toastrelid, AccessExclusiveLock);
-             relations = lappend(relations, rel);
-         }
      }

      /* Don't allow truncate on tables that are referenced by foreign keys */
--- 2342,2350 ----
***************
*** 2364,2383 ****
      {
          Relation    rel = lfirst(cell);

!         /* Truncate the actual file (and discard buffers) */
!         RelationTruncate(rel, 0);

!         /* If this relation has indexes, truncate the indexes too */
!         RelationTruncateIndexes(rel);
!
!         /*
!          * Close the relation, but keep exclusive lock on it until commit.
!          */
          heap_close(rel, NoLock);
      }
  }

  /*
   * heap_truncate_check_FKs
   *        Check for foreign keys referencing a list of relations that
   *        are to be truncated, and raise error if there are any
--- 2355,2402 ----
      {
          Relation    rel = lfirst(cell);

!         /* Truncate the relation */
!         heap_truncate_one_rel(rel);

!         /* Close the relation, but keep exclusive lock on it until commit */
          heap_close(rel, NoLock);
      }
  }

  /*
+  *     heap_truncate_one_rel
+  *
+  *     This routine deletes all data within the specified relation.
+  *
+  * This is not transaction-safe, because the truncation is done immediately
+  * and cannot be rolled back later.  Caller is responsible for having
+  * checked permissions etc, and must have obtained AccessExclusiveLock.
+  */
+ void
+ heap_truncate_one_rel(Relation rel)
+ {
+     Oid            toastrelid;
+
+     /* Truncate the actual file (and discard buffers) */
+     RelationTruncate(rel, 0);
+
+     /* If the relation has indexes, truncate the indexes too */
+     RelationTruncateIndexes(rel);
+
+     /* If there is a toast table, truncate that too */
+     toastrelid = rel->rd_rel->reltoastrelid;
+     if (OidIsValid(toastrelid))
+     {
+         Relation    toastrel = heap_open(toastrelid, AccessExclusiveLock);
+
+         RelationTruncate(toastrel, 0);
+         RelationTruncateIndexes(toastrel);
+         /* keep the lock... */
+         heap_close(toastrel, NoLock);
+     }
+ }
+
+ /*
   * heap_truncate_check_FKs
   *        Check for foreign keys referencing a list of relations that
   *        are to be truncated, and raise error if there are any
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.295
diff -c -r1.295 tablecmds.c
*** src/backend/commands/tablecmds.c    2 Aug 2009 22:14:52 -0000    1.295
--- src/backend/commands/tablecmds.c    6 Aug 2009 06:15:06 -0000
***************
*** 775,780 ****
--- 775,781 ----
      EState       *estate;
      ResultRelInfo *resultRelInfos;
      ResultRelInfo *resultRelInfo;
+     SubTransactionId mySubid;
      ListCell   *cell;

      /*
***************
*** 944,979 ****
      /*
       * OK, truncate each table.
       */
      foreach(cell, rels)
      {
          Relation    rel = (Relation) lfirst(cell);
-         Oid            heap_relid;
-         Oid            toast_relid;

          /*
!          * Create a new empty storage file for the relation, and assign it as
!          * the relfilenode value.    The old storage file is scheduled for
!          * deletion at commit.
           */
!         setNewRelfilenode(rel, RecentXmin);
!
!         heap_relid = RelationGetRelid(rel);
!         toast_relid = rel->rd_rel->reltoastrelid;
!
!         /*
!          * The same for the toast table, if any.
!          */
!         if (OidIsValid(toast_relid))
          {
!             rel = relation_open(toast_relid, AccessExclusiveLock);
!             setNewRelfilenode(rel, RecentXmin);
!             heap_close(rel, NoLock);
          }

!         /*
!          * Reconstruct the indexes to match, and we're done.
!          */
!         reindex_relation(heap_relid, true);
      }

      /*
--- 945,1002 ----
      /*
       * OK, truncate each table.
       */
+     mySubid = GetCurrentSubTransactionId();
+
      foreach(cell, rels)
      {
          Relation    rel = (Relation) lfirst(cell);

          /*
!          * Normally, we need a transaction-safe truncation here.  However,
!          * if the table was either created in the current (sub)transaction
!          * or has a new relfilenode in the current (sub)transaction, then
!          * we can just truncate it in-place, because a rollback would
!          * cause the whole table or the current physical file to be
!          * thrown away anyway.
           */
!         if (rel->rd_createSubid == mySubid ||
!             rel->rd_newRelfilenodeSubid == mySubid)
          {
!             /* Immediate, non-rollbackable truncation is OK */
!             heap_truncate_one_rel(rel);
          }
+         else
+         {
+             Oid            heap_relid;
+             Oid            toast_relid;

!             /*
!              * Need the full transaction-safe pushups.
!              *
!              * Create a new empty storage file for the relation, and assign it
!              * as the relfilenode value. The old storage file is scheduled for
!              * deletion at commit.
!              */
!             setNewRelfilenode(rel, RecentXmin);
!
!             heap_relid = RelationGetRelid(rel);
!             toast_relid = rel->rd_rel->reltoastrelid;
!
!             /*
!              * The same for the toast table, if any.
!              */
!             if (OidIsValid(toast_relid))
!             {
!                 rel = relation_open(toast_relid, AccessExclusiveLock);
!                 setNewRelfilenode(rel, RecentXmin);
!                 heap_close(rel, NoLock);
!             }
!
!             /*
!              * Reconstruct the indexes to match, and we're done.
!              */
!             reindex_relation(heap_relid, true);
!         }
      }

      /*
Index: src/include/catalog/heap.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/heap.h,v
retrieving revision 1.91
diff -c -r1.91 heap.h
*** src/include/catalog/heap.h    11 Jun 2009 14:49:09 -0000    1.91
--- src/include/catalog/heap.h    6 Aug 2009 06:15:06 -0000
***************
*** 62,67 ****
--- 62,69 ----

  extern void heap_truncate(List *relids);

+ extern void heap_truncate_one_rel(Relation rel);
+
  extern void heap_truncate_check_FKs(List *relations, bool tempTables);

  extern List *heap_truncate_find_FKs(List *relationIds);