0

Software typically reserves id release ranges for identifiers of standard objects (PostgreSQL calls these 'built-in' objects) for objects created during an install or patch/upgrade. I'm having difficulty finding the OID release ranges for PostgreSQL objects. Can someone point me to the documentation for this? Also, are OIDs being phased out of PostgreSQL? How will this impact my question(s)?

As an example, pg_roles table seems to use OIDs of four digits or less for objects created during installation or for patches, but the documentation is limited. This was the closest thing I could find.

https://www.postgresql.org/docs/12/release-12.html

Patches that manually assign OIDs for new built-in objects (such as new functions) should now randomly choose OIDs in the range 8000—9999. At the end of a development cycle, the OIDs used by committed patches will be renumbered down to lower numbers, currently somewhere in the 4xxx range, using the new renumber_oids.pl script. This approach should greatly reduce the odds of OID collisions between different in-process patches.

While there is no specific policy reserving any OIDs for external use, it is recommended that forks and other projects needing private manually-assigned OIDs use numbers in the high 7xxx range. This will avoid conflicts with recently-merged patches, and it should be a long time before the core project reaches that range.

And given the quote from above, perhaps a better way to ask the question is "What is the OID seed value or OID algorithm used for new object creation of each type (ie: pg_roles)?"

While there is no specific policy reserving any OIDs for external use

2
  • Do you plan to create your own catalog objects?
    – user1822
    Sep 9, 2022 at 21:32
  • Hoping to... ... Sep 9, 2022 at 21:34

1 Answer 1

2

The range of OIDs for system objects is 1 to 16383.

In PostgreSQL include files, server/access/transam.h has this presentation of how OIDs are assigned, which might help:

/* ----------
 *      Object ID (OID) zero is InvalidOid.
 *
 *      OIDs 1-9999 are reserved for manual assignment (see .dat files in
 *      src/include/catalog/).  Of these, 8000-9999 are reserved for
 *      development purposes (such as in-progress patches and forks);
 *      they should not appear in released versions.
 *
 *      OIDs 10000-11999 are reserved for assignment by genbki.pl, for use
 *      when the .dat files in src/include/catalog/ do not specify an OID
 *      for a catalog entry that requires one.  Note that genbki.pl assigns
 *      these OIDs independently in each catalog, so they're not guaranteed
 *      to be globally unique.  Furthermore, the bootstrap backend and
 *      initdb's post-bootstrap processing can also assign OIDs in this range.
 *      The normal OID-generation logic takes care of any OID conflicts that
 *      might arise from that.
 *
 *      OIDs 12000-16383 are reserved for unpinned objects created by initdb's
 *      post-bootstrap processing.  initdb forces the OID generator up to
 *      12000 as soon as it's made the pinned objects it's responsible for.
 *
 *      OIDs beginning at 16384 are assigned from the OID generator
 *      during normal multiuser operation.  (We force the generator up to
 *      16384 as soon as we are in normal operation.)
 *
 * The choices of 8000, 10000 and 12000 are completely arbitrary, and can be
 * moved if we run low on OIDs in any category.  Changing the macros below,
 * and updating relevant documentation (see bki.sgml and RELEASE_CHANGES),
 * should be sufficient to do this.  Moving the 16384 boundary between
 * initdb-assigned OIDs and user-defined objects would be substantially
 * more painful, however, since some user-defined OIDs will appear in
 * on-disk data; such a change would probably break pg_upgrade.
 *
 * NOTE: if the OID generator wraps around, we skip over OIDs 0-16383
 * and resume with 16384.  This minimizes the odds of OID conflict, by not
 * reassigning OIDs that might have been assigned during initdb.  Critically,
 * it also ensures that no user-created object will be considered pinned.
 * ----------
 */
#define FirstGenbkiObjectId     10000
#define FirstUnpinnedObjectId   12000
#define FirstNormalObjectId     16384

If you needed your own OIDs in the system range for some reason, I think that would fall under the "fork" category, so in the 8000-9999 range.

Q: Also, are OIDs being phased out of PostgreSQL

No, but the OID columns have been demoted to "normal" columns in Postgres 12, as opposed to system columns. There have been a gradual process in the evolution of Postgres to normalize the OIDs as normal per-table numeric primary keys, instead of the "globally unique primary key" that they were in the original design. See for instance the blog post OIDs demoted to normal columns: a glance at the past about this evolution. This is not to phase out OIDs but to ensure that they don't get in the way of progress.

Q: As an example, pg_roles table seems to use OIDs of four digits or less for objects created during installation or for patches

pg_roles itself is not a table, it's a view . One of its columns is named oid, and its value comes from pg_authid.oid. It's the primary key of pg_authid. There's really nothing in that that has much to do with installation, except that the system role created (generally postgres) has an oid value below 16384, and the roles created post-installation with CREATE USER or CREATE ROLE will have oid values above 16384.

1
  • This is exactly what I needed! Thank you so much! Sep 10, 2022 at 0:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.