Re: sequences vs oids as primary keys

craigp wrote:
> i had some questions/thoughts on using sequences vs oids as pks... it's a
> common requirement to obtain the last value of a sequence after an insert. most
> other databases support such a feature, but since oid's are deprecated, and
> only oid's are returned, this isn't supported by postgres (and performance
> suffers from having to make an extra sql call).

Really? What percentage drop in your transaction rate are you seeing
from calling nextval()? Does the load vary appreciably with the number
of concurrent clients?

> 1) does it make sense (and would it be possible) to make a rule which would,
> say, somehow write into the oid field of a tuple to be returned by lastoid? i'm
> assuming here that the database would not have oid's enabled.

I'm not sure I understand what you mean here.

> 2) if not, can the C code be modified to support this (maybe ExecInsert())?
> basically, if oid's are disabled for a given relation, and a sequence is the
> primary key, then instead of returning InvalidOid return the current sequence
> value (only for inserting a single row, possibly only if the db has been
> configured to support that, etc etc).

Hmm - you're probably better off seeing if any work has been done on
INSERT ... RETURNING (I think that's the syntax). I seem to remember
someone mentioning it on the hackers list.

> 3) if not that, would it make sense to enable applications to reserve a pool of
> sequence numbers? say, some kind of sequence.reserve(int count) function which
> takes the number of id's to reserve and returns the start of the sequence,
> where all the sequence #'s in [start, start + count -1] are guaranteed to be
> contiguous (or just overload nextval).

If you manually create a sequence, you can set an INCREMENT for
nextval() which sounds like what you want.

> 4) maybe there's a better way of handling this?

Why are you fetching an auto-generated id value? What meaning does it
have for you?

--
Richard Huxton
Archonet Ltd