Обсуждение: Jdbc and oid

Hello,

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). So I can create objects but cannot know their oid, so I
cannot find them in the database (other columns may not be assumed to
have unique values).

Does somebody know if this will be solved in 6.4 ? Or is it good enough
to use the Large object creation (which seems to return an oid) ?

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). By the way maybe some FREE
implementation of such a framework is available, do you know any ?


    Thanks a lot.

    P.O. Gaillard

Pierre-Olivier Gaillard <pierre.gaillard@hol.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

On Tue, 1 Sep 1998, Tom Lane wrote:

> Pierre-Olivier Gaillard <pierre.gaillard@hol.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@retep.org.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