Re: can't stop autovacuum by HUP'ing the server

On Tue, Aug 26, 2008 at 12:50 PM, Alvaro Herrera <alvherre(at)commandprompt(dot)com
> wrote:

> Dave Cramer wrote:
>
> > Yes
> >
> > select * from pg_database where datname='template0';
> > datname | datdba | encoding | datistemplate | datallowconn |
> datconnlimit
> > | datlastsysoid | datfrozenxid | dattablespace | datconfig |
> > datacl
> >
> -----------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+-----------+-------------------------------------
> > template0 | 10 | 6 | t | f |
> -1
> > | 11510 | 201850617 | 1663 | |
> > {=c/postgres,postgres=CTc/postgres}
> >
> > So how to fix ?
>
> I think I see the problem -- vac_truncate_clog is not ignoring these
> databases when passing the new frozen value to SetTransactionIdLimit.
>
> /*
> * Scan pg_database to compute the minimum datfrozenxid
> *
> * Note: we need not worry about a race condition with new entries being
> * inserted by CREATE DATABASE. Any such entry will have a copy of some
> * existing DB's datfrozenxid, and that source DB cannot be ours because
> * of the interlock against copying a DB containing an active backend.
> * Hence the new entry will not reduce the minimum. Also, if two
> VACUUMs
> * concurrently modify the datfrozenxid's of different databases, the
> * worst possible outcome is that pg_clog is not truncated as
> aggressively
> * as it could be.
> */
> relation = heap_open(DatabaseRelationId, AccessShareLock);
>
> scan = heap_beginscan(relation, SnapshotNow, 0, NULL);
>
> while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
> {
> Form_pg_database dbform = (Form_pg_database) GETSTRUCT(tuple);
>
> Assert(TransactionIdIsNormal(dbform->datfrozenxid));
>
> if (TransactionIdPrecedes(myXID, dbform->datfrozenxid))
> frozenAlreadyWrapped = true;
> else if (TransactionIdPrecedes(dbform->datfrozenxid, frozenXID))
> {
> frozenXID = dbform->datfrozenxid;
> namecpy(&oldest_datname, &dbform->datname);
> }
> }
>
> ...
>
> /*
> * Update the wrap limit for GetNewTransactionId. Note: this function
> * will also signal the postmaster for an(other) autovac cycle if
> needed.
> */
> SetTransactionIdLimit(frozenXID, &oldest_datname);
>
>
> If it doesn't ignore them, then it should be properly vacuuming
> template0 as any other database. We've changed autovac's behavior on
> this area back and forth so I may be misremembering what's our rationale
> du jour.
>

Well, I'm willing to help debug this, however this is a busy production
database and I need to be able to turn it off for a few hours a day. Would
changing autovacuum_freeze_max_age be a solution ?

Dave

>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>