Re: can't stop autovacuum by HUP'ing the server - Mailing list pgsql-hackers

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
conditionwith new entries being    * inserted by CREATE DATABASE.  Any such entry will have a copy of some    *
existingDB's datfrozenxid, and that source DB cannot be ours because    * of the interlock against copying a DB
containingan active backend.    * Hence the new entry will not reduce the minimum.  Also, if two VACUUMs    *
concurrentlymodify the datfrozenxid's of different databases, the    * worst possible outcome is that pg_clog is not
truncatedas 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.