Re: Using oids

On Wed, Sep 03, 2003 at 01:47:01PM +0200, Bo Lorentsen wrote:
> On Wed, 2003-09-03 at 13:19, Martijn van Oosterhout wrote:
> > The only thing you need to know is the name of the primary key field. This
> > many be a problem in a generic layer. If you like you can make a UNIQUE
> > INDEX on the oid column and retry inserts when they fail.
> Hmm, it all end up putting alot of information to a lower layer, and
> this is sad as PG already knows, but it may not tell me.

Well, in a sense it know and in a sense it doesn't. Sequences are not
considered special in terms of returning data to the client. It's just
another function from the parser's point of view.

> > In your code, do create an index on the OID column? If not, that's be a
> > performance hit,
> I'm not sure what you mean !

If you know the OID of a row, PostgreSQL doesn't have a special lookup table
to find it. That's also why they're not unique; the backend would have to
scan through every table to find out if the next one is available.

So, unless you specifically add an index to the table, looking up by OID
will always trigger a sequential scan.

That said, there is no reason why someone couldn't create a last_sequence()
function so you could say SELECT currval( last_sequence() ). Ofcourse, if
your table has no SERIAL field, you're stuffed either way.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato