pg.ddx.io pgsql-interfaces@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Jdbc and oid 3+ messages / 3 participants [nested] [flat]
* Jdbc and oid @ 1998-09-01 17:20 Pierre-Olivier Gaillard <pierre.gaillard@hol.fr> 1998-09-01 20:24 ` Re: [INTERFACES] Jdbc and oid Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 3+ messages in thread From: Pierre-Olivier Gaillard @ 1998-09-01 17:20 UTC (permalink / raw) To: pgsql-interfaces 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [INTERFACES] Jdbc and oid 1998-09-01 17:20 Jdbc and oid Pierre-Olivier Gaillard <pierre.gaillard@hol.fr> @ 1998-09-01 20:24 ` Tom Lane <tgl@sss.pgh.pa.us> 1998-09-02 21:44 ` Re: [INTERFACES] Jdbc and oid Peter T Mount <peter@retep.org.uk> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 1998-09-01 20:24 UTC (permalink / raw) To: Pierre-Olivier Gaillard <pierre.gaillard@hol.fr>; +Cc: pgsql-interfaces 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [INTERFACES] Jdbc and oid 1998-09-01 17:20 Jdbc and oid Pierre-Olivier Gaillard <pierre.gaillard@hol.fr> 1998-09-01 20:24 ` Re: [INTERFACES] Jdbc and oid Tom Lane <tgl@sss.pgh.pa.us> @ 1998-09-02 21:44 ` Peter T Mount <peter@retep.org.uk> 0 siblings, 0 replies; 3+ messages in thread From: Peter T Mount @ 1998-09-02 21:44 UTC (permalink / raw) To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pierre-Olivier Gaillard <pierre.gaillard@hol.fr>; pgsql-interfaces 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1998-09-02 21:44 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 1998-09-01 17:20 Jdbc and oid Pierre-Olivier Gaillard <pierre.gaillard@hol.fr> 1998-09-01 20:24 ` Tom Lane <tgl@sss.pgh.pa.us> 1998-09-02 21:44 ` Peter T Mount <peter@retep.org.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox