The age() of a column?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

The age() of a column?

Ron-2
What does it mean, and where is it documented?  The only place in the docs I
see reference to age() is
https://www.postgresql.org/docs/9.6/functions-datetime.html and this doesn't
seem to fit that use case.

Thanks

TAPb=# select COALESCE(ta.schemaname, 'pg_toast') || '.' || cl.relname,
        cl.relfrozenxid,
        age(cl.relfrozenxid)
from pg_class cl FULL JOIN pg_tables ta
     ON ta.tablename = cl.relname
where not cl.relfrozenxid = xid '0'
   --and age(cl.relfrozenxid) > 4000000
order by 1
limit 30;
            ?column?            | relfrozenxid |   age
-------------------------------+--------------+---------
cds.ach_return_batch           |     58569152 | 2111005
cds.ach_return_detail          |     58569152 | 2111005
cds.all_day_event              |     58569152 | 2111005
cds.all_day_event_trigger      |     58569152 | 2111005
[snip]
cds.bank_ftp                   |     58569152 | 2111005
cds.bank_health_care_job       |     58569152 | 2111005
cds.bank_inbound_file          |     58569152 | 2111005
cds.bank_input_format          |     58569152 | 2111005
(30 rows)


--
Angular momentum makes the world go 'round.

Reply | Threaded
Open this post in threaded view
|

Re: The age() of a column?

Tom Lane-2
Ron <[hidden email]> writes:
> What does it mean, and where is it documented?

age(xid) returns the difference between the given xid and the current xid.
It's not terribly well documented, but psql's \df gives some info:

regression=# \df+ age
   Schema   | Name | Result data type |                   Argument data types                    | Type | Volatility |  Parallel  |  Owner   | Security | Access privileges | Language |                                 Source code                                  |                             Description                
...
 pg_catalog | age  | integer          | xid                                                      | func | stable     | restricted | postgres | invoker  |                   | internal | xid_age                                                                      | age of a transaction ID, in transactions before current transaction

The only reference I could find in the SGML docs is in the maintenance
chapter.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: The age() of a column?

Adrian Klaver-4
In reply to this post by Ron-2
On 11/28/18 7:32 PM, Ron wrote:
> What does it mean, and where is it documented?  The only place in the

https://www.postgresql.org/docs/10/routine-vacuuming.html

"The age column measures the number of transactions from the cutoff XID
to the current transaction's XID."

> docs I see reference to age() is
> https://www.postgresql.org/docs/9.6/functions-datetime.html and this
> doesn't seem to fit that use case.
>
> Thanks
>
> TAPb=# select COALESCE(ta.schemaname, 'pg_toast') || '.' || cl.relname,
>         cl.relfrozenxid,
>         age(cl.relfrozenxid)
> from pg_class cl FULL JOIN pg_tables ta
>      ON ta.tablename = cl.relname
> where not cl.relfrozenxid = xid '0'
>    --and age(cl.relfrozenxid) > 4000000
> order by 1
> limit 30;
>             ?column?            | relfrozenxid |   age
> -------------------------------+--------------+---------
> cds.ach_return_batch           |     58569152 | 2111005
> cds.ach_return_detail          |     58569152 | 2111005
> cds.all_day_event              |     58569152 | 2111005
> cds.all_day_event_trigger      |     58569152 | 2111005
> [snip]
> cds.bank_ftp                   |     58569152 | 2111005
> cds.bank_health_care_job       |     58569152 | 2111005
> cds.bank_inbound_file          |     58569152 | 2111005
> cds.bank_input_format          |     58569152 | 2111005
> (30 rows)
>
>


--
Adrian Klaver
[hidden email]