Skip site navigation (1) Skip section navigation (2)

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

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Dave Cramer <pg(at)fastcrypt(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: can't stop autovacuum by HUP'ing the server
Date: 2008-08-26 16:50:00
Message-ID: 20080826165000.GL4920@alvh.no-ip.org (view raw or flat)
Thread:
Lists: 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.

In response to

Responses

pgsql-hackers by date

Next:From: Dave CramerDate: 2008-08-26 16:58:59
Subject: Re: can't stop autovacuum by HUP'ing the server
Previous:From: Tom LaneDate: 2008-08-26 16:48:22
Subject: Re: Split up the wiki TODO page?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group