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 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.