Cluster OID Limit

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

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

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

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: