Обсуждение: serious issue with age(relfrozenxid)::int.
Hi all
Most of my tables in postgresql database 8.3 is showing up this. Pls suggest me what should be done with this and if this is something really seriouscaesius=# select relname, age(relfrozenxid)::int from pg_class order by 2 desc ;
relname | age
-----------------------------------------------------------------+------------
idx_app_param_app_name | 2147483647
triggered_update_columns | 2147483647
triggers | 2147483647
usage_privileges | 2147483647
view_column_usage | 2147483647
view_routine_usage | 2147483647
view_table_usage | 2147483647
views | 2147483647
data_type_privileges | 2147483647
element_types | 2147483647
schemata | 2147483647
sequences | 2147483647
agoda_site_pkey | 2147483647
pg_toast_351460902_index | 2147483647
pg_toast_1380431671_index | 2147483647
airfare_site_market_4_pkey | 2147483647
airfare_site_market_4_uk1 | 2147483647
rollup_upload_id_seq | 2147483647
pg_toast_1174975817_index | 2147483647
rollup_upload_pkey | 2147483647
pg_toast_1128485971_index | 2147483647
report_subscriptions_id_seq | 2147483647
pk_report_subscriptions | 2147483647
report_subscriptions_unique | 2147483647
pg_toast_1496288301_index | 2147483647
idx_work_unit_1_status_5 | 2147483647
pg_toast_905799698_index | 2147483647
organization_audit_pkey | 2147483647
idx_organization_audit_row_id | 2147483647
idx_organization_audit_user_id | 2147483647
pg_toast_351460980_index | 2147483647
pg_toast_351460986_index | 2147483647
pg_toast_1607259454_index | 2147483647
idx_turing_requests_date_inserted | 2147483647
account_organization_id | 2147483647
idx_tmp_new_output_counts_work_unit_id | 2147483647
ferry_lineitem_pkey | 2147483647
pg_toast_905583507_index | 2147483647
script_override_audit_pkey | 2147483647
idx_script_override_audit_row_id | 2147483647
pg_toast_618155951_index | 2147483647
idx_script_override_audit_user_id | 2147483647
pg_toast_351461018_index | 2147483647
pg_toast_618182134_index | 2147483647
finance_lineitem_pkey | 2147483647
pg_toast_618181998_index | 2147483647
On 7/20/2014 12:29 AM, Prabhjot Sheena wrote: > Most of my tables in postgresql database 8.3 is showing up > this. Pls suggest me what should be done with this and if this is > something really serious why are you casting age() to an int ? -- john r pierce 37N 122W somewhere on the middle of the left coast
On 07/20/2014 01:05 AM, John R Pierce wrote: > On 7/20/2014 12:29 AM, Prabhjot Sheena wrote: >> Most of my tables in postgresql database 8.3 is showing up >> this. Pls suggest me what should be done with this and if this is >> something really serious > > why are you casting age() to an int ? My question would be why are you using age() on relfrozenxid? From here: http://www.postgresql.org/docs/8.3/static/catalog-pg-class.html relfrozenxid xid All transaction IDs before this one have been replaced with a permanent ("frozen") transaction ID in this table. This is used to track whether the table needs to be vacuumed in order to prevent transaction ID wraparound or to allow pg_clog to be shrunk. Zero (InvalidTransactionId) if the relation is not a table From here: http://www.postgresql.org/docs/8.3/static/functions-datetime.html age(timestamp) interval Subtract from current_date So it is not surprising that it is not working. > > > > -- Adrian Klaver adrian.klaver@aklaver.com
Prabhjot Sheena <prabhjot.sheena@rivalwatch.com> writes:
> Most of my tables in postgresql database 8.3 is showing up this.
> Pls suggest me what should be done with this and if this is something
> really serious
There's no reason to think that this means anything at all. You did not
bother to filter by relkind, so most likely those MAXINT results
correspond to entries that have relfrozenxid = 0 because the system
doesn't track relfrozenxid for them (eg, indexes).
regards, tom lane