Simon Riggs escribió:
> Seems like we don't need to mess with the deadlock checker itself.
>
> We can rely on the process at the head of the lock wait queue to sort
> this out for us. So all we need do is look at the isAutovacuum flag on
> the process that is holding the lock we're waiting on. If it isn't an
> autoANALYZE we can carry on with the main deadlock check. We just need a
> new kind of deadlock state to handle this, then let ProcSleep send
> SIGINT to the autoANALYZE and then go back to sleep, waiting to be
> reawoken when the auotANALYZE aborts.
Ok, I think this makes sense.
I can offer the following patch -- it makes it possible to determine
whether an autovacuum process is doing analyze or not, by comparing the
PGPROC of the running WorkerInfo list (the list has at most
max_autovacuum_workers entries, so this is better than trolling
ProcGlobal).
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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 4 Oct 2007 21:32:11 -0000
*************** typedef struct autovac_table
*** 182,188 ****
* wi_links entry into free list or running list
* 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_launchtime Time at which this worker was launched
* wi_cost_* Vacuum cost-based delay parameters current in this worker
*
--- 182,189 ----
* wi_links entry into free list or running list
* wi_dboid OID of the database this worker is supposed to work on
* wi_tableoid OID of the table currently being vacuumed
! * wi_proc pointer to PGPROC of the running worker, NULL if not 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 ****
* that worker itself).
*-------------
*/
typedef struct WorkerInfoData
{
SHM_QUEUE wi_links;
Oid wi_dboid;
Oid wi_tableoid;
! int wi_workerpid;
TimestampTz wi_launchtime;
int wi_cost_delay;
int wi_cost_limit;
--- 192,211 ----
* that worker itself).
*-------------
*/
+ typedef enum
+ {
+ AvActivityNone,
+ AvActivityVacuum,
+ AvActivityAnalyze
+ } AvActivity;
+
typedef struct WorkerInfoData
{
SHM_QUEUE wi_links;
Oid wi_dboid;
Oid wi_tableoid;
! PGPROC *wi_proc;
! AvActivity wi_activity;
TimestampTz wi_launchtime;
int wi_cost_delay;
int wi_cost_limit;
*************** AutoVacLauncherMain(int argc, char *argv
*** 694,700 ****
worker = (WorkerInfo) MAKE_PTR(AutoVacuumShmem->av_startingWorker);
worker->wi_dboid = InvalidOid;
worker->wi_tableoid = InvalidOid;
! worker->wi_workerpid = 0;
worker->wi_launchtime = 0;
worker->wi_links.next = AutoVacuumShmem->av_freeWorkers;
AutoVacuumShmem->av_freeWorkers = MAKE_OFFSET(worker);
--- 703,710 ----
worker = (WorkerInfo) MAKE_PTR(AutoVacuumShmem->av_startingWorker);
worker->wi_dboid = InvalidOid;
worker->wi_tableoid = InvalidOid;
! worker->wi_proc = NULL;
! 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)
*** 1198,1204 ****
AutoVacuumShmem->av_freeWorkers = worker->wi_links.next;
worker->wi_dboid = avdb->adw_datid;
! worker->wi_workerpid = 0;
worker->wi_launchtime = GetCurrentTimestamp();
AutoVacuumShmem->av_startingWorker = sworker;
--- 1208,1215 ----
AutoVacuumShmem->av_freeWorkers = worker->wi_links.next;
worker->wi_dboid = avdb->adw_datid;
! worker->wi_proc = NULL;
! worker->wi_activity = AvActivityNone;
worker->wi_launchtime = GetCurrentTimestamp();
AutoVacuumShmem->av_startingWorker = sworker;
*************** AutoVacWorkerMain(int argc, char *argv[]
*** 1542,1548 ****
{
MyWorkerInfo = (WorkerInfo) MAKE_PTR(AutoVacuumShmem->av_startingWorker);
dbid = MyWorkerInfo->wi_dboid;
! MyWorkerInfo->wi_workerpid = MyProcPid;
/* insert into the running list */
SHMQueueInsertBefore(&AutoVacuumShmem->av_runningWorkers,
--- 1553,1559 ----
{
MyWorkerInfo = (WorkerInfo) MAKE_PTR(AutoVacuumShmem->av_startingWorker);
dbid = MyWorkerInfo->wi_dboid;
! MyWorkerInfo->wi_proc = MyProc;
/* insert into the running list */
SHMQueueInsertBefore(&AutoVacuumShmem->av_runningWorkers,
*************** FreeWorkerInfo(int code, Datum arg)
*** 1637,1643 ****
MyWorkerInfo->wi_links.next = AutoVacuumShmem->av_freeWorkers;
MyWorkerInfo->wi_dboid = InvalidOid;
MyWorkerInfo->wi_tableoid = InvalidOid;
! MyWorkerInfo->wi_workerpid = 0;
MyWorkerInfo->wi_launchtime = 0;
MyWorkerInfo->wi_cost_delay = 0;
MyWorkerInfo->wi_cost_limit = 0;
--- 1648,1655 ----
MyWorkerInfo->wi_links.next = AutoVacuumShmem->av_freeWorkers;
MyWorkerInfo->wi_dboid = InvalidOid;
MyWorkerInfo->wi_tableoid = InvalidOid;
! MyWorkerInfo->wi_proc = NULL;
! MyWorkerInfo->wi_activity = AvActivityNone;
MyWorkerInfo->wi_launchtime = 0;
MyWorkerInfo->wi_cost_delay = 0;
MyWorkerInfo->wi_cost_limit = 0;
*************** autovac_balance_cost(void)
*** 1701,1707 ****
offsetof(WorkerInfoData, wi_links));
while (worker)
{
! if (worker->wi_workerpid != 0 &&
worker->wi_cost_limit_base > 0 && worker->wi_cost_delay > 0)
cost_total +=
(double) worker->wi_cost_limit_base / worker->wi_cost_delay;
--- 1713,1719 ----
offsetof(WorkerInfoData, wi_links));
while (worker)
{
! if (worker->wi_proc != NULL &&
worker->wi_cost_limit_base > 0 && worker->wi_cost_delay > 0)
cost_total +=
(double) worker->wi_cost_limit_base / worker->wi_cost_delay;
*************** autovac_balance_cost(void)
*** 1724,1730 ****
offsetof(WorkerInfoData, wi_links));
while (worker)
{
! if (worker->wi_workerpid != 0 &&
worker->wi_cost_limit_base > 0 && worker->wi_cost_delay > 0)
{
int limit = (int)
--- 1736,1742 ----
offsetof(WorkerInfoData, wi_links));
while (worker)
{
! if (worker->wi_proc != NULL &&
worker->wi_cost_limit_base > 0 && worker->wi_cost_delay > 0)
{
int limit = (int)
*************** autovac_balance_cost(void)
*** 1737,1743 ****
worker->wi_cost_limit = Max(Min(limit, worker->wi_cost_limit_base), 1);
elog(DEBUG2, "autovac_balance_cost(pid=%u db=%u, rel=%u, cost_limit=%d, cost_delay=%d)",
! worker->wi_workerpid, worker->wi_dboid,
worker->wi_tableoid, worker->wi_cost_limit, worker->wi_cost_delay);
}
--- 1749,1755 ----
worker->wi_cost_limit = Max(Min(limit, worker->wi_cost_limit_base), 1);
elog(DEBUG2, "autovac_balance_cost(pid=%u db=%u, rel=%u, cost_limit=%d, cost_delay=%d)",
! worker->wi_proc->pid, worker->wi_dboid,
worker->wi_tableoid, worker->wi_cost_limit, worker->wi_cost_delay);
}
*************** 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,2819 ----
pgstat_clear_snapshot();
}
+
+ /*
+ * avworker_is_analyze
+ *
+ * Determine whether an autovacuum worker (specified by PGPROC) is running
+ * ANALYZE.
+ */
+ bool
+ avworker_is_analyze(PGPROC *proc)
+ {
+ WorkerInfo worker;
+ bool answer = false;
+
+ Assert(proc->isAutovacuum);
+
+ LWLockAcquire(AutovacuumLock, LW_SHARED);
+ worker = (WorkerInfo) SHMQueueNext(&AutoVacuumShmem->av_runningWorkers,
+ &AutoVacuumShmem->av_runningWorkers,
+ offsetof(WorkerInfoData, wi_links));
+ while (worker)
+ {
+ if (worker->wi_proc == proc)
+ {
+ answer = worker->wi_activity == AvActivityAnalyze;
+ break;
+ }
+
+ worker = (WorkerInfo) SHMQueueNext(&AutoVacuumShmem->av_runningWorkers,
+ &worker->wi_links,
+ offsetof(WorkerInfoData, wi_links));
+ }
+ LWLockRelease(AutovacuumLock);
+
+ return answer;
+ }
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 4 Oct 2007 21:31:03 -0000
*************** extern int Log_autovacuum_min_duration;
*** 37,42 ****
--- 37,43 ----
extern bool AutoVacuumingActive(void);
extern bool IsAutoVacuumLauncherProcess(void);
extern bool IsAutoVacuumWorkerProcess(void);
+ extern bool avworker_is_analyze(PGPROC *proc);
/* Functions to start autovacuum process, called from postmaster */
extern void autovac_init(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
