get PostgreSQL’s next oid

背景

今天在群里聊到的话题, 怎么取PostgreSQL的oid.

首先解释一下什么是oid :

Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables.   
  
OIDs are not added to user-created tables, unless WITH OIDS is specified when the table is created,   
or the default_with_oids configuration variable is enabled. Type oid represents an object identifier.  
  
The oid type is currently implemented as an unsigned four-byte integer.   
  
Therefore, it is not large enough to provide database-wide uniqueness in large databases,   
or even in large individual tables. So, using a user-created table's OID column as a primary key is discouraged.   
  
OIDs are best used only for references to system tables.  
  

所以说oid是一个集群范围内的概念, 而不是某个数据库或者某个表上的, oid的产生也是非常严格的. 类似序列.

可以参考 src/backend/access/transam/varsup.c 的GetNewObjectId函数.

/*  
 * GetNewObjectId -- allocate a new OID  
 *  
 * OIDs are generated by a cluster-wide counter.  Since they are only 32 bits  
 * wide, counter wraparound will occur eventually, and therefore it is unwise  
 * to assume they are unique unless precautions are taken to make them so.  
 * Hence, this routine should generally not be used directly.  The only  
 * direct callers should be GetNewOid() and GetNewRelFileNode() in  
 * catalog/catalog.c.  
 */  
Oid  
GetNewObjectId(void)  
{  
        Oid                     result;  
  
        /* safety check, we should never get this far in a HS slave */  
        if (RecoveryInProgress())  
                elog(ERROR, "cannot assign OIDs during recovery");  
  
        LWLockAcquire(OidGenLock, LW_EXCLUSIVE);  
  
        /*  
         * Check for wraparound of the OID counter.  We *must* not return 0  
         * (InvalidOid); and as long as we have to check that, it seems a good  
         * idea to skip over everything below FirstNormalObjectId too. (This  
         * basically just avoids lots of collisions with bootstrap-assigned OIDs  
         * right after a wrap occurs, so as to avoid a possibly large number of  
         * iterations in GetNewOid.)  Note we are relying on unsigned comparison.  
         *  
         * During initdb, we start the OID generator at FirstBootstrapObjectId, so  
         * we only enforce wrapping to that point when in bootstrap or standalone  
         * mode.  The first time through this routine after normal postmaster  
         * start, the counter will be forced up to FirstNormalObjectId. This  
         * mechanism leaves the OIDs between FirstBootstrapObjectId and  
         * FirstNormalObjectId available for automatic assignment during initdb,  
         * while ensuring they will never conflict with user-assigned OIDs.  
         */  
        if (ShmemVariableCache->nextOid < ((Oid) FirstNormalObjectId))  
        {  
                if (IsPostmasterEnvironment)  
                {  
                        /* wraparound in normal environment */  
                        ShmemVariableCache->nextOid = FirstNormalObjectId;  
                        ShmemVariableCache->oidCount = 0;  
                }  
                else  
                {  
                        /* we may be bootstrapping, so don't enforce the full range */  
                        if (ShmemVariableCache->nextOid < ((Oid) FirstBootstrapObjectId))  
                        {  
                                /* wraparound in standalone environment? */  
                                ShmemVariableCache->nextOid = FirstBootstrapObjectId;  
                                ShmemVariableCache->oidCount = 0;  
                        }  
                }  
        }  
  
        /* If we run out of logged for use oids then we must log more */  
        if (ShmemVariableCache->oidCount == 0)  
        {  
                XLogPutNextOid(ShmemVariableCache->nextOid + VAR_OID_PREFETCH);  
                ShmemVariableCache->oidCount = VAR_OID_PREFETCH;  
        }  
  
        result = ShmemVariableCache->nextOid;  
  
        (ShmemVariableCache->nextOid)++;  
        (ShmemVariableCache->oidCount)--;  
  
        LWLockRelease(OidGenLock);  
  
        return result;  
}  

oid的信息存储在共享内存中, 数据结构如下 :

/*  
 * VariableCache is a data structure in shared memory that is used to track  
 * OID and XID assignment state.  For largely historical reasons, there is  
 * just one struct with different fields that are protected by different  
 * LWLocks.  
 *  
 * Note: xidWrapLimit and oldestXidDB are not "active" values, but are  
 * used just to generate useful messages when xidWarnLimit or xidStopLimit  
 * are exceeded.  
 */  
typedef struct VariableCacheData  
{  
        /*  
         * These fields are protected by OidGenLock.  
         */  
        Oid                     nextOid;                /* next OID to assign */  
        uint32          oidCount;               /* OIDs available before must do XLOG work */  
  
        /*  
         * These fields are protected by XidGenLock.  
         */  
        TransactionId nextXid;          /* next XID to assign */  
  
        TransactionId oldestXid;        /* cluster-wide minimum datfrozenxid */  
        TransactionId xidVacLimit;      /* start forcing autovacuums here */  
        TransactionId xidWarnLimit; /* start complaining here */  
        TransactionId xidStopLimit; /* refuse to advance nextXid beyond here */  
        TransactionId xidWrapLimit; /* where the world ends */  
        Oid                     oldestXidDB;    /* database with minimum datfrozenxid */  
  
        /*  
         * These fields are protected by ProcArrayLock.  
         */  
        TransactionId latestCompletedXid;       /* newest XID that has committed or  
                                                                                 * aborted */  
} VariableCacheData;  

在PostgreSQL中没有提供直接查找nextoid的函数或视图, 通过pg_controldata可以看到最后一次checkpoint时的nextoid. 但是看不到系统当前的nextoid.

如下 :

pg9.2.0@db-172-16-3-150-> pg_controldata | grep OID  
Latest checkpoint's NextOID:          65890  

注意这里的oid有prefetch的概念, 不是每次都去fetch的, 注意到 :

src/backend/access/transam/varsup.c:#define VAR_OID_PREFETCH            8192  
src/backend/access/transam/varsup.c:            XLogPutNextOid(ShmemVariableCache->nextOid + VAR_OID_PREFETCH);  
src/backend/access/transam/varsup.c:            ShmemVariableCache->oidCount = VAR_OID_PREFETCH;  

前面从pg_controldata就能看出来, 实际获取的值比pg_controldata的小. 因为已经prefetch了还没消耗完.

那么要怎么查询呢 ?

需要借组C函数, 如下 :

pg9.2.0@db-172-16-3-150-> cat test.c  
#include <stdio.h>  
#include "postgres.h"  
#include "access/transam.h"  
#include "fmgr.h"  
  
PG_MODULE_MAGIC;  
  
PG_FUNCTION_INFO_V1(getoid);  
  
Datum getoid() {  
  return GetNewObjectId();  
}  

假设我的源码放在 /home/pg9.2.0/postgresql-9.2.0, 这里要用到这里的头文件.

pg9.2.0@db-172-16-3-150-> gcc -O3 -Wall -Wextra -Werror -I /home/pg9.2.0/postgresql-9.2.0/src/include -g -fPIC -c ./test.c -o test.o  
pg9.2.0@db-172-16-3-150-> gcc -O3 -Wall -Wextra -Werror -I /home/pg9.2.0/postgresql-9.2.0/src/include -g -shared test.o -o libtest.so  

拷贝到$PGHOME/lib下面

pg9.2.0@db-172-16-3-150-> cp libtest.so /home/pg9.2.0/pgsql9.2.0/lib/  

接下来在数据库中创建一个函数来获取nextoid :

pg9.2.0@db-172-16-3-150-> psql digoal postgres  
psql (9.2.0)  
Type "help" for help.  
  
digoal=# create or replace function getoid() returns int as '$libdir/libtest.so', 'getoid' language C STRICT;  
CREATE FUNCTION  
digoal=# select getoid();  
 getoid   
--------  
  57720  
(1 row)  

看一下这个nextoid是不是对的, 创建一个表, 查看它的oid.

digoal=# create table oid_test(id int);  
CREATE TABLE  
digoal=# select oid from pg_class where relname='oid_test';  
  oid    
-------  
 57721  
(1 row)  

看起来这个nextoid获取后就消耗掉了, 类似sequence的nextval()函数.

参考

1. http://www.postgresql.org/docs/9.2/static/xfunc-c.html

2. src/backend/catalog/catalog.c

3. src/backend/access/transam/varsup.c

4. src/include/access/transam.h

5. pg_controldata

6. http://www.postgresql.org/docs/9.2/static/catalog-pg-database.html

7. http://www.postgresql.org/docs/9.2/static/datatype-oid.html

digoal’s 大量PostgreSQL文章入口