Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 14E8C9FB35F for ; Thu, 28 Sep 2006 15:58:18 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 07194-01 for ; Thu, 28 Sep 2006 15:58:14 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.232]) by postgresql.org (Postfix) with ESMTP id CA9E89FB35B for ; Thu, 28 Sep 2006 15:58:13 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id t13so696131wxc for ; Thu, 28 Sep 2006 11:58:12 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=SJvfndceB1GjawHoTwsQbo7dmrbf7bzQn4+aBDff4R2Q9MyXjkcb5GN3aubePbmWq6DWznLBzpVKR0gGeM1Fb02Qgd8JWcSt8zBcBehPirt7E8IaJtXmNfDh5AXHWzU0RlA9lJMRy13kcYgoE1OqhODqtBODAhHtvpjs89ko0G8= Received: by 10.70.129.6 with SMTP id b6mr799708wxd; Thu, 28 Sep 2006 11:58:12 -0700 (PDT) Received: by 10.70.71.18 with HTTP; Thu, 28 Sep 2006 11:58:12 -0700 (PDT) Message-ID: Date: Thu, 28 Sep 2006 11:58:12 -0700 From: "Talha Khan" To: "Sriram Dandapani" Subject: Re: transaction id wraparound Cc: pgsql-admin@postgresql.org In-Reply-To: <6992E470F12A444BB787B5C937B9D4DF060E5A57@ca-mail1.cis.local> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_76053_7622401.1159469892442" References: <6992E470F12A444BB787B5C937B9D4DF060E5A57@ca-mail1.cis.local> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.518 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/339 X-Sequence-Number: 23253 ------=_Part_76053_7622401.1159469892442 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi sriram, datfrozenxid column of pg_database row is updated at the completion of any database wide vacuum operation. The value store here is the cuttoff xid used by the vacuum operation all xid's older then this cutoffxid are replaced by theis xid so i think the behaviour being shown by your database is quite normal. Regards Talha Khan The datfrozenxid column of a database's pg_database row is updated at the completion of any database-wide VACUUM operation (i.e., VACUUM that does not name a specific table). The value stored in this field is the freeze cutoff XID that was used by that VACUUM command. All normal XIDs older than this cutoff XID are guaranteed to have been replaced by FrozenXID within that database On 9/28/06, Sriram Dandapani wrote: > > When I run this query > > > > fwdb01=# select current_timestamp,datname,age(datfrozenxid) from > pg_database; > > now | datname | age > > -------------------------------+-----------+------------ > > 2006-09-28 18:04:24.489935+00 | postgres | 1087834006 > > 2006-09-28 18:04:24.489935+00 | fwdb01 | 2039254861 > > 2006-09-28 18:04:24.489935+00 | template1 | 2039253122 > > 2006-09-28 18:04:24.489935+00 | template0 | 1542808250 > > (4 rows) > > > > fwdb01=# select current_timestamp,datname,age(datfrozenxid) from > pg_database; > > now | datname | age > > ------------------------------+-----------+------------ > > 2006-09-28 18:10:45.64452+00 | postgres | 1088357075 > > 2006-09-28 18:10:45.64452+00 | fwdb01 | 2039777930 > > 2006-09-28 18:10:45.64452+00 | template1 | 2039776191 > > 2006-09-28 18:10:45.64452+00 | template0 | 1543331319 > > > > > > In approximately 6 minutes, the fwdb01 count has gone up by about 500K. I > am generating about 250K rows for every 6 . I am also running vacuumdb. > > > > Does vacuumdb generate a lot of transactions that affects this counter. > ------=_Part_76053_7622401.1159469892442 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hi sriram,
 
datfrozenxid column of pg_database row is updated at the completion of any database wide vacuum operation. The value store here is the cuttoff xid used by the vacuum operation all xid's older then this cutoffxid are replaced by theis xid so i think the behaviour being shown by your database is quite normal.
 
Regards
Talha Khan 
 
The datfrozenxid column of a database's pg_database row is updated at the completion of any database-wide VACUUM operation (i.e., VACUUM that does not name a specific table). The value stored in this field is the freeze cutoff XID that was used by that VACUUM command. All normal XIDs older than this cutoff XID are guaranteed to have been replaced by FrozenXID within that database

 
On 9/28/06, Sriram Dandapani <sdandapani@counterpane.com> wrote:

When I run this query

 

fwdb01=# select current_timestamp,datname,age(datfrozenxid) from pg_database;

              now              |  datname  |    age

-------------------------------+-----------+------------

 2006-09-28 18:04:24.489935+00 | postgres  | 1087834006

 2006-09-28 18:04:24.489935+00 | fwdb01    | 2039254861

 2006-09-28 18:04:24.489935+00 | template1 | 2039253122

 2006-09-28 18:04:24.489935+00 | template0 | 1542808250

(4 rows)

 

fwdb01=# select current_timestamp,datname,age(datfrozenxid) from pg_database;

             now              |  datname  |    age

------------------------------+-----------+------------

 2006-09-28 18:10:45.64452+00 | postgres  | 1088357075

 2006-09-28 18:10:45.64452+00 | fwdb01    | 2039777930

 2006-09-28 18:10:45.64452+00 | template1 | 2039776191

 2006-09-28 18:10:45.64452+00 | template0 | 1543331319

 

 

In approximately 6 minutes, the fwdb01 count has gone up by about 500K. I am generating about 250K rows for every 6 . I am also running vacuumdb.

 

Does vacuumdb generate a lot of transactions that affects this counter.


------=_Part_76053_7622401.1159469892442--