Re: [INTERFACES] Jdbc and oid

Pierre-Olivier Gaillard <pierre(dot)gaillard(at)hol(dot)fr> writes:
> I need to use the oid of postgresql rows as primary key. The trouble is
> that INSERT does not return the oid of created objects in JDBC (the psql
> frontend does display the oid after INSERT, so the info should be
> available).

I agree this is a serious shortcoming in the API for JDBC.

> In case you need some background : I need this to write classes that
> automatically store themselves to the database. Relationships are to be
> stored as well. This is why I need oids : they are a convenient way to
> reference objects (and probably fast, too).

A couple of points here ---

1. OIDs normally are not preserved across a database dump and reload.
(You can force them to be, but it's inefficient and hastens the day when
you run out of OIDs...) So it's a bad idea to use OIDs as *long term*
identifiers --- in particular, I'd strongly recommend against storing an
OID in one table row as a reference to another table row.

It's perfectly OK to use OIDs as temporary pointers to particular rows
inside an application, if you can shut down and restart the application
when reloading the database. I do this all the time.

2. OIDs aren't any faster than any other method of finding a table row.
If you expect SELECT or UPDATE ... "WHERE oid = something" to be fast
for a large table, you'd better create an index on OID for that table.
Otherwise the system's gonna resort to sequential scan.

If you need permanent identifiers for table rows, it's a much better
idea to assign them from a sequence object. This does mean that you
need an ID column in the table along with the actual data columns.

regards, tom lane