Re: Cluster OID Limit - Mailing list pgsql-general

From SERHAD ERDEM
Subject Re: Cluster OID Limit
Date
Msg-id GV1P195MB178542931895433287EB1BEAA3A79@GV1P195MB1785.EURP195.PROD.OUTLOOK.COM
Whole thread Raw
In response to Re: Cluster OID Limit  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
Hi ,
its  about  xid.
u may use the following sqls  for check.


-----------Transaction ID Exhaustion Analysis  ------------------------------

SELECT datname
    , age(datfrozenxid)
    , current_setting('autovacuum_freeze_max_age')
FROM pg_database where datallowconn = true
ORDER BY 2 DESC;


 
WITH max_age AS (
    SELECT 2000000000 as max_old_xid
        , setting AS autovacuum_freeze_max_age
        FROM pg_catalog.pg_settings
        WHERE name = 'autovacuum_freeze_max_age' )
, per_database_stats AS (
    SELECT datname
        , m.max_old_xid::int
        , m.autovacuum_freeze_max_age::int
        , age(d.datfrozenxid) AS oldest_current_xid
    FROM pg_catalog.pg_database d
    JOIN max_age m ON (true)
    WHERE d.datallowconn )
SELECT max(oldest_current_xid) AS oldest_current_xid
    , max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound
    , max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovac
FROM per_database_stats;





SELECT c.oid::regclass
    , age(c.relfrozenxid)
    , pg_size_pretty(pg_total_relation_size(c.oid))
FROM pg_class c
JOIN pg_namespace n on c.relnamespace = n.oid
WHERE relkind IN ('r', 't', 'm')
AND n.nspname NOT IN ('pg_toast')
ORDER BY 2 DESC LIMIT 100;

From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Thursday, June 9, 2022 3:02 PM
To: Lucas <lucas75@gmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Cluster OID Limit
 
On 6/9/22 02:10, Lucas wrote:
> Hello,
>
> In the company I work for, some clusters reached the OID limit (2^32)
> and we had to reinstall the cluster.

Was this really about OIDs or XID wraparound?:

https://www.postgresql.org/docs/14/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

>
> I was wondering if there is any discussion on:
> * "compress" the OID space
> * "warp around" the OID space
> * segment a OID range for temporary tables with "wrap around"
>
> --
> Lucas


--
Adrian Klaver
adrian.klaver@aklaver.com


pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Cluster OID Limit
Next
From: Joshua Drake
Date:
Subject: Re: Cluster OID Limit