Обсуждение: Cluster OID Limit

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

Cluster OID Limit

От
Lucas
Дата:
Hello,

In the company I work for, some clusters reached the OID limit (2^32) and we had to reinstall the cluster. 

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

Re: Cluster OID Limit

От
Tom Lane
Дата:
Lucas <lucas75@gmail.com> writes:
> In the company I work for, some clusters reached the OID limit (2^32) and
> we had to reinstall the cluster.

Uh ... why did you think you needed to do that?  The OID counter
will wrap around and things should carry on fine.  There are defenses
to prevent creation of duplicate OID values within any one catalog
or TOAST table, and it doesn't particularly matter if there are
duplicates across tables.

            regards, tom lane

Re: Cluster OID Limit

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

Re: Cluster OID Limit

От
SERHAD ERDEM
Дата:
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


Re: Cluster OID Limit

От
Joshua Drake
Дата:
Lucas,

If you run out of OIDs you are doing something wrong. We haven't supported user space OIDs in a lot of releases. Which release are you using?

JD

On Thu, Jun 9, 2022 at 2:11 AM Lucas <lucas75@gmail.com> wrote:
Hello,

In the company I work for, some clusters reached the OID limit (2^32) and we had to reinstall the cluster. 

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