Re: 8.2 Autovacuum BUG ?

pkalva(at)livedatagroup(dot)com (Pallav Kalva) writes:
> Tom Lane wrote:
>> Pallav Kalva <pkalva(at)livedatagroup(dot)com> 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?
>>
> If nothing changed on these tables how can it freeze old tuples ?

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.

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?