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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
