Re: [INTERFACES] Jdbc and oid

On Tue, 1 Sep 1998, Tom Lane wrote:

> 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.

Although not a standard JDBC feature, 6.4 does include a method that will
return the oid from an insert. The code was in there, but nothing was
available to get at it in earlier versions. It's mainly intended for the
new serialization class, but your'e problem would also be solved by it.

The method is in Statement, and a quick example would be:

postgresql.Statement mystat = db.createStatement();
mystat.executeUpdate("insert .....");
System.out.println("insert returned: "+mystat.getResultStatusString());

Also, postgresql.ResultSet also has a method, called getStatusString()
which does the same thing (Statement handles it as you don't always get a
ResultSet back).

> > 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).

You may want to look at the new Serialization stuff (still buggy, but
that's what beta's for ;-) )

It basically allows you to store objects into postgresql tables, rather
than into large objects (so you can query on them).

> 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.

--
Peter T Mount peter(at)retep(dot)org(dot)uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf