Handling OID Changes in Regression Tests for C Extensions

Hello Narayana,

On 6/14/25 11:43, Lakshmi Narayana Velayudam wrote:

Dear pg-hackers,

I am facing a challenge related to OID changes in regression tests while
working on a PostgreSQL C extension. Whenever I add or remove a function
to the SQL file, it alters OIDs and leads to cascading modifications in
multiple test files. This creates a significant overhead in maintaining
the regression tests.

I would like to understand if there are best practices or tools used in
PostgreSQL's own regression test suite to handle or mitigate the impact
of OID changes. How does PostgreSQL manage OIDs in its tests to minimize
disruption? Are there strategies, configurations, or patterns that can
be recommended for extensions?

Any guidance or reference to documentation on this topic would be
greatly appreciated.

The OIDs for user-defined objects (e.g. those from extensions) are not
stable, and this will not change. The only way is to prevent the test
output, e.g. by not including OIDs in the results, and eliminating all
other types of non-determinism - eg. by enforcing ordering, etc.

regards

--
Tomas Vondra

Tomas Vondra <tomas@vondra.me> writes:

On 6/14/25 11:43, Lakshmi Narayana Velayudam wrote:

I am facing a challenge related to OID changes in regression tests while
working on a PostgreSQL C extension. Whenever I add or remove a function
to the SQL file, it alters OIDs and leads to cascading modifications in
multiple test files. This creates a significant overhead in maintaining
the regression tests.

The OIDs for user-defined objects (e.g. those from extensions) are not
stable, and this will not change. The only way is to prevent the test
output, e.g. by not including OIDs in the results, and eliminating all
other types of non-determinism - eg. by enforcing ordering, etc.

You could also make your tests print database object OIDs in
symbolic form, e.g. cast function OIDs to regprocedure, use
pg_describe_object(), etc. Then it doesn't matter if the
underlying numbers change.

regards, tom lane

On Sat, Jun 14, 2025 at 11:20:31AM -0400, Tom Lane wrote:

Tomas Vondra <tomas@vondra.me> writes:

The OIDs for user-defined objects (e.g. those from extensions) are not
stable, and this will not change. The only way is to prevent the test
output, e.g. by not including OIDs in the results, and eliminating all
other types of non-determinism - eg. by enforcing ordering, etc.

You could also make your tests print database object OIDs in
symbolic form, e.g. cast function OIDs to regprocedure, use
pg_describe_object(), etc. Then it doesn't matter if the
underlying numbers change.

Definitely never expose an OID for something that users should be able
to trigger by themselves in the output. First, it is usually not
helpful because only the OID does not tell one which object is wrong.
When it comes to check the state of the OIDs in the catalogs, or when
it comes to manipulate the generation of some commands, another trick
that we are relying on quite a bit in the regression tests is to save
an OID in a \gset command, then reuse it in a query for equality
comparison. For utility commands generated, it is also possible
(well, kind of), to generate command names and pass them to a PL/pgsql
function triggered by an EXECUTE. This would not work for commands
like VACUUM or REINDEX commands on toast relations, because these
cannot run inside a transaction context, still that can be really
useful.

A last thing I've been doing in this area in the last few years which
is a interesting trick is the isolation test
reindex-concurrently-toast.spec, that checks a case with TOAST
relations and concurrent REINDEX CONCURRENTLY: I've been abusing of
allow_system_table_mods to rename the TOAST relations to a fixed name,
then issue REINDEX commands on fixed names. In this case, the test
case is able to become deterministic even if the OIDs vary in time.
--
Michael