------=_Part_16895_12770529.1210524385250
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Hi all,
I wonder whether any of you can help me out with this problem. We were
performed a routine "lazy" VACUUM in order to reassign frozen XIDs and
prevent data-loss.
After the VACUUM completed successfully, the command "SELECT datname,
age(datfrozenxid) FROM pg_database" still showed an excess of
1,800,000,000
transactions from the cutoff XID of some table meaning that either the
VACUUM somehow failed or else missed out a table.
Just to make sure, we re-ran the VACUUM but the result in the end was the
same. We then decided to find out which table was causing this problem by
running the following query:
select relname from pg_class where relfrozenxid = (select datfrozenxid
from
pg_database where datname = 'CDR')
Since datfrozenxid in pg_database stores the oldest XID, using this query
we
were able to home on the database object which was allegedly being missed
by
the VACUUM. The query returned a TOAST object: pg_toast_35027430. This
was
puzzling; as far as I know pg_toasts objects can't be vacuumed directly b
ut only when vacuuming their parent. This means that somehow this
pg_toast
object was orphaned, fact confirmed by the following query:
select relname from pg_class a where relname like 'pg_toast_3%' and
relkind
= 't' and not exists (
select 1 from pg_class b where a.oid = b.reltoastrelid and
relkind
= 'r')
To get vacuum the TOAST object we created a tem****ary table foo (col1
char(1)) and assigned its reltoastrelid (up till now set to 0) to
pg_toast_35027430's OID and then vacuumed foo. The plan worked and
immediately age(datfrozenxid) in pg_database reflected a much younger XID.
We then decided to get rid of pg_toast_35027430 by dropping foo. Foo
disappeared but pg_toast_35027430 persisted. I'd like to get rid of it
because in a few months' time we will bump into the same problem again.
Does anyone have any idea how this can be removed manually without causing
any unwarranted damage to the system catalogue?
Also, can one un-vacuumed database object cause dataloss in other
unrelated
tabled which have otherwise been VACUUMed and their respective FrozenXIDs
reassigned?
Thank you in advance,
James.
------=_Part_16895_12770529.1210524385250
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
<div>Hi all,</div>
<div> </div>
<div>I wonder whether any of you can help me out with this problem.
We were performed a routine "lazy" VACUUM in order to reassign
frozen XIDs and prevent data-loss. </div>
<div> </div>
<div>After the VACUUM completed successfully, the command "SELECT
datname, age(datfrozenxid) FROM pg_database" still showed an excess
of 1,800,000,000 transactions from the cutoff XID of some table meaning
that either the VACUUM somehow failed or else missed out a table.</div>
<div> </div>
<div>Just to make sure, we re-ran the VACUUM but the result in the end was
the same. We then decided to find out which table was causing this
problem by running the following query:</div>
<div> </div>
<div>select relname from pg_class where relfrozenxid = (select
datfrozenxid from pg_database where datname = 'CDR')</div>
<div> </div>
<div>Since datfrozenxid in pg_database stores the oldest XID, using this
query we were able to home on the database object which was
allegedly being missed by the VACUUM. The query returned a TOAST
object: pg_toast_35027430. This was puzzling; as far as I know
pg_toasts objects can't be vacuumed directly b</div>
<div>ut only when vacuuming their parent. This means that somehow
this pg_toast object was orphaned, fact confirmed by the following
query:</div>
<div> </div>
<div>select relname from pg_class a where relname like
'pg_toast_3%' and relkind = 't' and not exists (</div>
<div> select 1
from pg_class b where a.oid = b.reltoastrelid and relkind =
'r')</div>
<div> </div>
<div>To get vacuum the TOAST object we created a tem****ary table foo (col1
char(1)) and assigned its reltoastrelid (up till now set to 0) to
pg_toast_35027430's OID and then vacuumed foo. The plan
worked and immediately age(datfrozenxid) in pg_database reflected a much
younger XID.</div>
<div> </div>
<div>We then decided to get rid of pg_toast_35027430 by dropping
foo. Foo disappeared but pg_toast_35027430 persisted. I'd
like to get rid of it because in a few months' time we will bump
into the same problem again. Does anyone have any idea how this can
be removed manually without causing any unwarranted damage to the system
catalogue?</div>
<div> </div>
<div>Also, can one un-vacuumed database object cause dataloss in other
unrelated tabled which have otherwise been VACUUMed and their respective
FrozenXIDs reassigned?</div>
<div> </div>
<div>Thank you in advance,</div>
<div> </div>
<div>James.</div>
------=_Part_16895_12770529.1210524385250--
|
