Re: [PERFORM] 8.2 Autovacuum BUG ?

pkalva@livedatagroup.com (Pallav Kalva) writes:
> Tom Lane wrote:

>> Pallav Kalva  writes:
>>
>>>     We turned on autovacuums on 8.2 and we have a database which is
>>> read only , it is basically a USPS database used only for address
>>> lookups (only SELECTS, no updates/deletes/inserts).
>>>
>>
>>
>>>     This database has about 10gig data and yesterday autovacuum
>>> started on this database and all of a sudden I see lot of archive
>>> logs generated during this time, I guess it might have generated
>>> close to 3-4gig data during this period.
>>>
>>
>> Probably represents freezing of old tuples, which is a WAL-logged
>> operation as of 8.2.  Is it likely that the data is 200M transactions
>> old?
>>

It does so very easily, by changing the XID from whatever it was to 2 (which indicates that a tuple has been "frozen.")

I don't imagine you were wondering how it is done - more likely you were wondering why.

"Why" is to prevent transaction ID wraparound failures.

> Does it mean that once it reaches 200M transactions it will do the
> same thing all over again ?

It won't freeze those same tuples again, as they're obviously already frozen, but a vacuum next week may be expected to freeze tuples that are roughly a week newer.

> If I am doing just SELECTS on these tables ? how can there be any
> transactions ? or SELECTS considered transactions too ?

Every query submitted comes in the context of a transaction. If there wasn't a BEGIN submitted somewhere, then yes, every SELECT could potentially invoke a transaction, irrespective of whether it writes data or not.

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related postgresql.org Links pgsql-admin pgsql-advocacy pgsql-announce pgsql-bugs pgsql-cygwin pgsql-docs pgsql-general pgsql-interfaces pgsql-jdbc pgsql-jobs pgsql-novice pgsql-odbc pgsql-performance pgsql-php pgsql-ports pgsql-sql Request more information about Pantek

If you submit a million SELECT statements, yes, that could, indeed, indicate a million transactions.

-- 
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/nonrdbms.html
How much deeper would the ocean be if sponges didn't live there? 

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               
http://www.postgresql.org/docs/faq