Greetings,
I have seen many a warning against using the Postgres internal OIDs to
refer to DB objects, but I've got a situation that may warrant it. In a
nutshell, I've got a table called 'Notes', which contains (you guessed
it) notes that users can create to attach to various records in the DB.
These records may well be in different tables of different sorts of
entities (e.g., an inventory item, a calendar event, a facility, etc.).
One note may be attached to many records and each record may have
multiple notes.
The notes are attached to the records via a separate associative table
that contains the 'note_id', the 'record_id' (both generated by a
sequence), and the 'table_name' in which the record resides. It's
managable now, but my gut tells me that the association to the table
should be handled by something besides just 'table_name' because if that
were to be changed it would break things or potentially cause a lot of
maintenance issues. Is the OID a good bet for something to use as a
unique and reliable table identifier?
If so, is there an elegant way to dereference the OID instead of using
the alias (i.e. table name) to run a query against that table?
I want to do this:
> SELECT * FROM inventory_item;
But, the following does not work (where 16675 is the OID of tabled
inventory_item):
> SELECT * FROM 16675;
The one (very scary) pitfall I can see with using the OID is that if the
DB were rebuilt, there's probably no guarantee or expectation that a
table would have the same OID as before. That's certainly a deal
breaker.
Maybe the best solution is to continue using the table name, but to
create that as a foreign key to the official table name in the
information_schema? That way it could cascade if the name was changed,
but I'm not sure what kind of ugliness might result if you tried to drop
the table and it still had a referencing record. Any opinions on that
or any other ways to approach this challenge?
Thanks in advance,
Joel
