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
Fri, 20 Aug 1999 10:02:30 -0700
"G. Anthony Reina" <reina@nsi.edu>
RE: [GENERAL] Error during 'vacuum analyze'
I assume we addressed this in our current tree, right?

> 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();
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Mon, 27 Sep 1999 14:52:06 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [GENERAL] Error during 'vacuum analyze'