The attached patch allows VACUUMS's on small relations to clean up dead
tuples while VACUUM or ANALYSE is running for a long time on some big
table.
This is done by adding a "bool inVacuum" to PGPROC and then making use
of it in GetOldestXmin.
This patch is against current CVS head, but should also apply to 8.0.2
with minorpach warnings.
--
Hannu Krosing <[EMAIL PROTECTED]>
Index: src/backend/access/transam/xact.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/xact.c,v
retrieving revision 1.200
diff -c -r1.200 xact.c
*** src/backend/access/transam/xact.c 28 Apr 2005 21:47:10 -0000 1.200
--- src/backend/access/transam/xact.c 17 May 2005 22:06:34 -0000
***************
*** 1411,1416 ****
--- 1411,1424 ----
AfterTriggerBeginXact();
/*
+ * mark the transaction as not VACUUM (vacuum_rel will set isVacuum to true
+ * directly after calling BeginTransactionCommand() )
+ */
+ if (MyProc != NULL)
+ {
+ MyProc->inVacuum = false;
+ }
+ /*
* done with start processing, set current transaction state to "in
* progress"
*/
Index: src/backend/commands/vacuum.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.308
diff -c -r1.308 vacuum.c
*** src/backend/commands/vacuum.c 6 May 2005 17:24:53 -0000 1.308
--- src/backend/commands/vacuum.c 17 May 2005 22:06:35 -0000
***************
*** 36,41 ****
--- 36,42 ----
#include "executor/executor.h"
#include "miscadmin.h"
#include "storage/freespace.h"
+ #include "storage/proc.h"
#include "storage/sinval.h"
#include "storage/smgr.h"
#include "tcop/pquery.h"
***************
*** 343,350 ****
* would be problematic.)
*
* For ANALYZE (no VACUUM): if inside a transaction block, we cannot
! * start/commit our own transactions. Also, there's no need to do so
! * if only processing one relation. For multiple relations when not
* within a transaction block, use own transactions so we can release
* locks sooner.
*/
--- 344,350 ----
* would be problematic.)
*
* For ANALYZE (no VACUUM): if inside a transaction block, we cannot
! * start/commit our own transactions. For multiple relations when not
* within a transaction block, use own transactions so we can release
* locks sooner.
*/
***************
*** 355,364 ****
Assert(vacstmt->analyze);
if (in_outer_xact)
use_own_xacts = false;
- else if (list_length(relations) > 1)
- use_own_xacts = true;
else
! use_own_xacts = false;
}
/*
--- 355,362 ----
Assert(vacstmt->analyze);
if (in_outer_xact)
use_own_xacts = false;
else
! use_own_xacts = true;
}
/*
***************
*** 420,425 ****
--- 418,428 ----
if (use_own_xacts)
{
StartTransactionCommand();
+ if (MyProc != NULL) /* is this needed here ? */
+ {
+ /* so other vacuums don't look at our xid/xmin in GetOldestXmin() */
+ MyProc->inVacuum = true;
+ }
/* functions in indexes may want a snapshot set */
ActiveSnapshot = CopySnapshot(GetTransactionSnapshot());
}
***************
*** 905,910 ****
--- 908,920 ----
/* Begin a transaction for vacuuming this relation */
StartTransactionCommand();
+
+ if (MyProc != NULL) /* is this needed here ? */
+ {
+ /* so other vacuums don't look at our xid/xmin in GetOldestXmin() */
+ MyProc->inVacuum = true;
+ }
+
/* functions in indexes may want a snapshot set */
ActiveSnapshot = CopySnapshot(GetTransactionSnapshot());
Index: src/backend/storage/ipc/sinval.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/storage/ipc/sinval.c,v
retrieving revision 1.75
diff -c -r1.75 sinval.c
*** src/backend/storage/ipc/sinval.c 31 Dec 2004 22:00:56 -0000 1.75
--- src/backend/storage/ipc/sinval.c 17 May 2005 22:06:36 -0000
***************
*** 697,703 ****
{
PGPROC *proc = (PGPROC *) MAKE_PTR(pOffset);
! if (allDbs || proc->databaseId == MyDatabaseId)
{
/* Fetch xid just once - see GetNewTransactionId */
TransactionId xid = proc->xid;
--- 697,703 ----
{
PGPROC *proc = (PGPROC *) MAKE_PTR(pOffset);
! if ((proc->inVacuum == false) && (allDbs || proc->databaseId == MyDatabaseId))
{
/* Fetch xid just once - see GetNewTransactionId */
TransactionId xid = proc->xid;
***************
*** 845,854 ****
* them as running anyway. We also assume that such xacts
* can't compute an xmin older than ours, so they needn't be
* considered in computing globalxmin.
*/
if (proc == MyProc ||
!TransactionIdIsNormal(xid) ||
! TransactionIdFollowsOrEquals(xid, xmax))
continue;
if (TransactionIdPrecedes(xid, xmin))
--- 845,858 ----
* them as running anyway. We also assume that such xacts
* can't compute an xmin older than ours, so they needn't be
* considered in computing globalxmin.
+ *
+ * there is also no need to consider transaxtions runnibg the
+ * vacuum command as it will not affect tuple visibility
*/
if (proc == MyProc ||
!TransactionIdIsNormal(xid) ||
! TransactionIdFollowsOrEquals(xid, xmax) ||
! proc->inVacuum == true )
continue;
if (TransactionIdPrecedes(xid, xmin))
Index: src/include/storage/proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/storage/proc.h,v
retrieving revision 1.77
diff -c -r1.77 proc.h
*** src/include/storage/proc.h 31 Dec 2004 22:03:42 -0000 1.77
--- src/include/storage/proc.h 17 May 2005 22:06:36 -0000
***************
*** 63,68 ****
--- 63,73 ----
* were starting our xact: vacuum must not
* remove tuples deleted by xid >= xmin ! */
+ bool inVacuum; /* true if current command is vacuum.
+ * xid or xmin of other vacuum commands
+ * need not be used when
+ * finding global xmin for removing tuples */
+
int pid; /* This backend's process id */
Oid databaseId; /* OID of database this backend is using */
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
