2

I have an application which relies totally on PostgreSQL OIDs for its operation. It's currently at PostgreSQL 9.6.1, but I'm looking to upgrade to 13. Is there a recommended/standard mechanism (using triggers?) to recreate the OID functionality in newer versions?

For background, the reason the application uses OIDs is because the app knows nothing about the database schema and is intended to be used on any random database schema, using reverse engineering to navigate. For this reason, a suggestion along the lines of "just add a primary key" won't work for me. Currently the app is running against 6 separate databases, each with its own schema.

Additional background

Practically every application understands its database schema, for example a payroll application understands the employee table, which perhaps has a primary key of emp_id.

My app is different.

My app automatically generates a CRUD application for any random PostgreSQL database that it is pointed at. As such, it makes no assumptions about primary keys, table names, column names, etc etc. The only assumption it makes is that the database it has been pointed at has a unique OID for each row in each table. As things currently stand, I use this application against five separate and distinct databases, and have done so since around 2007.

So I get that, in the absence of a system generated OID, I probably need to create a user column, called OID defined as a sequence. The purpose of the question is to ask if there is a canonical solution for apps that use the pre-v12 OID. Assume, that the application cannot be changed, so any solution must be entirely implemented within the database.

0

1 Answer 1

1

You can simply add a new column called oid that is automatically filled:

ALTER TABLE test ADD oid bigint GENERATED ALWAYS AS IDENTITY NOT NULL;

Using an identity column like this has the advantage that you get an error if you by mistake try to INSERT a value into the column manually.

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.