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

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.

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