I came up with the following patch. What this does is cancel any
ANALYZE started by autovacuum, at the top of ALTER TABLE.
There is a new function relation_openrv_cav(). This is the same as
relation_openrv, except that it will also cancel analyzes. I'm still
wondering if I should merge the two and have a third boolean argument to
specify whether to do the cancel.
Comments?
--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"Ciencias políticas es la ciencia de entender por qué
los políticos actúan como lo hacen" (netfunny.com)
Index: src/backend/access/heap/heapam.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/access/heap/heapam.c,v
retrieving revision 1.242
diff -c -p -r1.242 heapam.c
*** src/backend/access/heap/heapam.c 21 Sep 2007 21:25:42 -0000 1.242
--- src/backend/access/heap/heapam.c 3 Oct 2007 22:12:34 -0000
*************** relation_openrv(const RangeVar *relation
*** 971,976 ****
--- 971,996 ----
return relation_open(relOid, lockmode);
}
+ /*
+ * relation_openrv_cav
+ *
+ * As above, but cancel any autovacuum-induced ANALYZE.
+ */
+ Relation
+ relation_openrv_cav(const RangeVar *relation, LOCKMODE lockmode)
+ {
+ Oid relOid;
+
+ if (lockmode != NoLock)
+ AcceptInvalidationMessages();
+
+ relOid = RangeVarGetRelid(relation, false);
+
+ autovac_cancel_analyze(relOid);
+
+ return relation_open(relOid, lockmode);
+ }
+
/* ----------------
* relation_close - close any relation
*
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.233
diff -c -p -r1.233 tablecmds.c
*** src/backend/commands/tablecmds.c 29 Sep 2007 17:18:58 -0000 1.233
--- src/backend/commands/tablecmds.c 3 Oct 2007 22:24:17 -0000
*************** renamerel(Oid myrelid, const char *newre
*** 1748,1754 ****
void
AlterTable(AlterTableStmt *stmt)
{
! Relation rel = relation_openrv(stmt->relation, AccessExclusiveLock);
int expected_refcnt;
/*
--- 1748,1754 ----
void
AlterTable(AlterTableStmt *stmt)
{
! Relation rel = relation_openrv_cav(stmt->relation, AccessExclusiveLock);
int expected_refcnt;
/*
Index: src/backend/postmaster/autovacuum.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.61
diff -c -p -r1.61 autovacuum.c
*** src/backend/postmaster/autovacuum.c 24 Sep 2007 04:12:01 -0000 1.61
--- src/backend/postmaster/autovacuum.c 3 Oct 2007 22:50:14 -0000
*************** typedef struct autovac_table
*** 183,188 ****
--- 183,189 ----
* wi_dboid OID of the database this worker is supposed to work on
* wi_tableoid OID of the table currently being vacuumed
* wi_workerpid PID of the running worker, 0 if not yet started
+ * wi_activity Type of task this worker is currently executing
* wi_launchtime Time at which this worker was launched
* wi_cost_* Vacuum cost-based delay parameters current in this worker
*
*************** typedef struct autovac_table
*** 191,202 ****
--- 192,211 ----
* that worker itself).
*-------------
*/
+ typedef enum
+ {
+ AvActivityNone,
+ AvActivityVacuum,
+ AvActivityAnalyze
+ } AvActivity;
+
typedef struct WorkerInfoData
{
SHM_QUEUE wi_links;
Oid wi_dboid;
Oid wi_tableoid;
int wi_workerpid;
+ AvActivity wi_activity;
TimestampTz wi_launchtime;
int wi_cost_delay;
int wi_cost_limit;
*************** AutoVacLauncherMain(int argc, char *argv
*** 695,700 ****
--- 704,710 ----
worker->wi_dboid = InvalidOid;
worker->wi_tableoid = InvalidOid;
worker->wi_workerpid = 0;
+ worker->wi_activity = AvActivityNone;
worker->wi_launchtime = 0;
worker->wi_links.next = AutoVacuumShmem->av_freeWorkers;
AutoVacuumShmem->av_freeWorkers = MAKE_OFFSET(worker);
*************** do_start_worker(void)
*** 1199,1204 ****
--- 1209,1215 ----
worker->wi_dboid = avdb->adw_datid;
worker->wi_workerpid = 0;
+ worker->wi_activity = AvActivityNone;
worker->wi_launchtime = GetCurrentTimestamp();
AutoVacuumShmem->av_startingWorker = sworker;
*************** FreeWorkerInfo(int code, Datum arg)
*** 1638,1643 ****
--- 1649,1655 ----
MyWorkerInfo->wi_dboid = InvalidOid;
MyWorkerInfo->wi_tableoid = InvalidOid;
MyWorkerInfo->wi_workerpid = 0;
+ MyWorkerInfo->wi_activity = AvActivityNone;
MyWorkerInfo->wi_launchtime = 0;
MyWorkerInfo->wi_cost_delay = 0;
MyWorkerInfo->wi_cost_limit = 0;
*************** next_worker:
*** 2062,2076 ****
VacuumCostDelay = tab->at_vacuum_cost_delay;
VacuumCostLimit = tab->at_vacuum_cost_limit;
! /*
! * Advertise my cost delay parameters for the balancing algorithm, and
! * do a balance
! */
LWLockAcquire(AutovacuumLock, LW_EXCLUSIVE);
MyWorkerInfo->wi_cost_delay = tab->at_vacuum_cost_delay;
MyWorkerInfo->wi_cost_limit = tab->at_vacuum_cost_limit;
MyWorkerInfo->wi_cost_limit_base = tab->at_vacuum_cost_limit;
autovac_balance_cost();
LWLockRelease(AutovacuumLock);
/* clean up memory before each iteration */
--- 2074,2095 ----
VacuumCostDelay = tab->at_vacuum_cost_delay;
VacuumCostLimit = tab->at_vacuum_cost_limit;
! /* Last fixups before actually starting to work */
LWLockAcquire(AutovacuumLock, LW_EXCLUSIVE);
+
+ /* advertise my cost delay parameters for the balancing algorithm */
MyWorkerInfo->wi_cost_delay = tab->at_vacuum_cost_delay;
MyWorkerInfo->wi_cost_limit = tab->at_vacuum_cost_limit;
MyWorkerInfo->wi_cost_limit_base = tab->at_vacuum_cost_limit;
+
+ /* do a balance */
autovac_balance_cost();
+
+ /* advertise my current activity */
+ MyWorkerInfo->wi_activity =
+ tab->at_dovacuum ? AvActivityVacuum : AvActivityAnalyze;
+
+ /* done */
LWLockRelease(AutovacuumLock);
/* clean up memory before each iteration */
*************** next_worker:
*** 2078,2086 ****
/*
* We will abort vacuuming the current table if we are interrupted, and
! * continue with the next one in schedule; but if anything else
! * happens, we will do our usual error handling which is to cause the
! * worker process to exit.
*/
PG_TRY();
{
--- 2097,2103 ----
/*
* We will abort vacuuming the current table if we are interrupted, and
! * continue with the next one in schedule.
*/
PG_TRY();
{
*************** next_worker:
*** 2100,2132 ****
errdata = CopyErrorData();
/*
! * If we errored out due to a cancel request, abort and restart the
! * transaction and go to the next table. Otherwise rethrow the
! * error so that the outermost handler deals with it.
*/
! if (errdata->sqlerrcode == ERRCODE_QUERY_CANCELED)
! {
! HOLD_INTERRUPTS();
! elog(LOG, "cancelling autovacuum of table \"%s.%s.%s\"",
! get_database_name(MyDatabaseId),
! get_namespace_name(get_rel_namespace(tab->at_relid)),
! get_rel_name(tab->at_relid));
!
! AbortOutOfAnyTransaction();
! FlushErrorState();
! MemoryContextResetAndDeleteChildren(PortalContext);
!
! /* restart our transaction for the following operations */
! StartTransactionCommand();
! RESUME_INTERRUPTS();
! }
! else
! PG_RE_THROW();
}
PG_END_TRY();
/* be tidy */
pfree(tab);
}
/*
--- 2117,2150 ----
errdata = CopyErrorData();
/*
! * Abort the transaction, restart a new one, and proceed with the
! * next table in our list.
*/
! HOLD_INTERRUPTS();
! ereport(LOG,
! (errmsg("cancelling autovacuum of table \"%s.%s.%s\"",
! get_database_name(MyDatabaseId),
! get_namespace_name(get_rel_namespace(tab->at_relid)),
! get_rel_name(tab->at_relid))));
!
! AbortOutOfAnyTransaction();
! FlushErrorState();
! MemoryContextResetAndDeleteChildren(PortalContext);
!
! /* restart our transaction for the following operations */
! StartTransactionCommand();
! RESUME_INTERRUPTS();
}
PG_END_TRY();
/* be tidy */
pfree(tab);
+
+ /* remove my info from shared memory */
+ LWLockAcquire(AutovacuumLock, LW_EXCLUSIVE);
+ MyWorkerInfo->wi_tableoid = InvalidOid;
+ MyWorkerInfo->wi_activity = AvActivityNone;
+ LWLockRelease(AutovacuumLock);
}
/*
*************** autovac_refresh_stats(void)
*** 2764,2766 ****
--- 2782,2828 ----
pgstat_clear_snapshot();
}
+
+ /*
+ * autovac_cancel_analyze
+ *
+ * Cancel any running autovacuum-induced ANALYZE in the given relation.
+ */
+ void
+ autovac_cancel_analyze(Oid relid)
+ {
+ WorkerInfo worker;
+
+ restart:
+ LWLockAcquire(AutovacuumLock, LW_SHARED);
+ worker = (WorkerInfo) SHMQueueNext(&AutoVacuumShmem->av_runningWorkers,
+ &AutoVacuumShmem->av_runningWorkers,
+ offsetof(WorkerInfoData, wi_links));
+ while (worker)
+ {
+ if (worker->wi_dboid != MyDatabaseId)
+ goto next_worker;
+ if (worker->wi_tableoid != relid)
+ goto next_worker;
+
+ if (worker->wi_activity == AvActivityAnalyze)
+ {
+ int pid;
+
+ pid = worker->wi_workerpid;
+
+ LWLockRelease(AutovacuumLock);
+
+ elog(NOTICE, "cancelling auto-analyze");
+ (void) kill(pid, SIGINT);
+ goto restart;
+ }
+
+ next_worker:
+ worker = (WorkerInfo) SHMQueueNext(&AutoVacuumShmem->av_runningWorkers,
+ &worker->wi_links,
+ offsetof(WorkerInfoData, wi_links));
+ }
+
+ LWLockRelease(AutovacuumLock);
+ }
Index: src/include/access/heapam.h
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/access/heapam.h,v
retrieving revision 1.127
diff -c -p -r1.127 heapam.h
*** src/include/access/heapam.h 20 Sep 2007 17:56:32 -0000 1.127
--- src/include/access/heapam.h 3 Oct 2007 22:13:17 -0000
*************** extern Relation relation_open(Oid relati
*** 132,137 ****
--- 132,138 ----
extern Relation try_relation_open(Oid relationId, LOCKMODE lockmode);
extern Relation relation_open_nowait(Oid relationId, LOCKMODE lockmode);
extern Relation relation_openrv(const RangeVar *relation, LOCKMODE lockmode);
+ extern Relation relation_openrv_cav(const RangeVar *relation, LOCKMODE lockmode);
extern void relation_close(Relation relation, LOCKMODE lockmode);
extern Relation heap_open(Oid relationId, LOCKMODE lockmode);
Index: src/include/postmaster/autovacuum.h
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/postmaster/autovacuum.h,v
retrieving revision 1.12
diff -c -p -r1.12 autovacuum.h
*** src/include/postmaster/autovacuum.h 24 Sep 2007 03:12:23 -0000 1.12
--- src/include/postmaster/autovacuum.h 3 Oct 2007 22:13:02 -0000
*************** extern int StartAutoVacLauncher(void);
*** 44,49 ****
--- 44,51 ----
extern int StartAutoVacWorker(void);
/* called from postmaster when a worker could not be forked */
extern void AutoVacWorkerFailed(void);
+ /* called from backends before they want to lock a table */
+ extern void autovac_cancel_analyze(Oid relid);
/* autovacuum cost-delay balancer */
extern void AutoVacuumUpdateDelay(void);
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
