Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.8.8/8.7.5) with ESMTP id QAA07175 for ; Tue, 1 Sep 1998 16:24:46 -0400 (EDT) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id QAA00889; Tue, 1 Sep 1998 16:24:32 -0400 (EDT) To: Pierre-Olivier Gaillard cc: pgsql-interfaces@postgreSQL.org Subject: Re: [INTERFACES] Jdbc and oid In-reply-to: Your message of Tue, 01 Sep 1998 17:20:51 +0000 <35EC2CF3.3451941@hol.fr> Date: Tue, 01 Sep 1998 16:24:31 -0400 Message-ID: <886.904681471@sss.pgh.pa.us> From: Tom Lane Pierre-Olivier Gaillard 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