Обсуждение: serious issue with age(relfrozenxid)::int.

Поиск
Список
Период
Сортировка

serious issue with age(relfrozenxid)::int.

От
Prabhjot Sheena
Дата:
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 serious

caesius=# 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

Re: serious issue with age(relfrozenxid)::int.

От
John R Pierce
Дата:
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



Re: serious issue with age(relfrozenxid)::int.

От
Adrian Klaver
Дата:
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


Re: serious issue with age(relfrozenxid)::int.

От
Tom Lane
Дата:
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