RE: [GENERAL] Error during 'vacuum analyze'

Mark,

    The error you are experiencing was discussed on the hacker's list
last week. Oliver Elphick and Tom Good worked out a patch for SQL to
solve the problem. You're right that the pg_vlock is getting created and
then getting deleted during the vacuuming of the last table. This ends
up boinking the vacuum. I've attached the patch that Oliver sent to me.
The patch won't go through all of the way so you'll have to go through
file by file and just make the changes yourself (just deleting a few
lines or adding a few, nothing big). That has solved the problem on my
machine.

-Tony






Index: include/access/nbtree.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/include/access/nbtree.h,v
retrieving revision 1.27
retrieving revision 1.27.2.1
diff -c -r1.27 -r1.27.2.1
*** include/access/nbtree.h     1999/05/25 22:04:55     1.27
--- include/access/nbtree.h     1999/08/08 20:24:09     1.27.2.1
***************
*** 255,260 ****
--- 255,261 ----
  extern void _bt_regscan(IndexScanDesc scan);
  extern void _bt_dropscan(IndexScanDesc scan);
  extern void _bt_adjscans(Relation rel, ItemPointer tid);
+ extern void AtEOXact_nbtree(void);

  /*
   * prototypes for functions in nbtsearch.c
Index: backend/access/nbtree/nbtscan.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/access/nbtree/nbtscan.c,v
retrieving revision 1.23.2.1
retrieving revision 1.23.2.2
diff -c -r1.23.2.1 -r1.23.2.2
*** backend/access/nbtree/nbtscan.c     1999/08/02 05:24:41     1.23.2.1
--- backend/access/nbtree/nbtscan.c     1999/08/08 20:24:10     1.23.2.2
***************
*** 42,47 ****
--- 42,69 ----
  static BTScanList BTScans = (BTScanList) NULL;

  static void _bt_scandel(IndexScanDesc scan, BlockNumber blkno, OffsetNumber offno);
+
+ /*
+  * AtEOXact_nbtree() --- clean up nbtree subsystem at xact abort or commit.
+  *
+  * This is here because it needs to touch this module's static var BTScans.
+  */
+ void
+ AtEOXact_nbtree(void)
+ {
+       /* Note: these actions should only be necessary during xact abort;
+        * but they can't hurt during a commit.
+        */
+
+       /* Reset the active-scans list to empty.
+        * We do not need to free the list elements, because they're all
+        * palloc()'d, so they'll go away at end of transaction anyway.
+        */
+       BTScans = NULL;
+
+       /* If we were building a btree, we ain't anymore. */
+       BuildingBtree = false;
+ }

  /*
   *    _bt_regscan() -- register a new scan.
Index: backend/access/transam/transam.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/access/transam/transam.c,v
retrieving revision 1.27.2.1
retrieving revision 1.27.2.2
diff -c -r1.27.2.1 -r1.27.2.2
*** backend/access/transam/transam.c    1999/08/02 05:56:46     1.27.2.1
--- backend/access/transam/transam.c    1999/08/08 20:24:12     1.27.2.2
***************
*** 20,26 ****

  #include "access/heapam.h"
  #include "catalog/catname.h"
- #include "commands/vacuum.h"

  static int    RecoveryCheckingEnabled(void);
  static void TransRecover(Relation logRelation);
--- 20,25 ----
***************
*** 83,95 ****
   */
  extern int    OidGenLockId;

- /* ----------------
-  *            globals that must be reset at abort
-  * ----------------
-  */
- extern bool BuildingBtree;

-
  /* ----------------
   *            recovery checking accessors
   * ----------------
--- 82,88 ----
***************
*** 568,578 ****
  void
  TransactionIdAbort(TransactionId transactionId)
  {
-       BuildingBtree = false;
-
-       if (VacuumRunning)
-               vc_abort();
-
        if (AMI_OVERRIDE)
                return;

--- 561,566 ----
Index: backend/access/transam/xact.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/access/transam/xact.c,v
retrieving revision 1.42.2.1
retrieving revision 1.42.2.2
diff -c -r1.42.2.1 -r1.42.2.2
*** backend/access/transam/xact.c       1999/08/02 05:56:48     1.42.2.1
--- backend/access/transam/xact.c       1999/08/08 20:24:12     1.42.2.2
***************
*** 144,152 ****
--- 144,154 ----
   */
  #include "postgres.h"

+ #include "access/nbtree.h"
  #include "catalog/heap.h"
  #include "commands/async.h"
  #include "commands/sequence.h"
+ #include "commands/vacuum.h"
  #include "libpq/be-fsstubs.h"
  #include "storage/proc.h"
  #include "utils/inval.h"
***************
*** 952,957 ****
--- 954,960 ----
        }

        RelationPurgeLocalRelation(true);
+       AtEOXact_nbtree();
        AtCommit_Cache();
        AtCommit_Locks();
        AtCommit_Memory();
***************
*** 1013,1021 ****
--- 1016,1027 ----
        AtAbort_Notify();
        CloseSequences();
        AtEOXact_portals();
+       if (VacuumRunning)
+               vc_abort();
        RecordTransactionAbort();
        RelationPurgeLocalRelation(false);
        DestroyNoNameRels();
+       AtEOXact_nbtree();
        AtAbort_Cache();
        AtAbort_Locks();
        AtAbort_Memory();