Обсуждение: sequences vs oids as primary keys

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).

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.

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).

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).

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

thanks!
--craig


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

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

craigp wrote:

>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.
>
>
>
We do this in a trigger.  We assign the NEXTVAL to a variable, write
that to the row, then raise its value as a notice.  Then we just
retrieve the notice.

On 7/25/06, Kenneth Downs <ken@secdat.com> wrote:
> craigp wrote:
>
> >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.
> >
> >
> >
> We do this in a trigger.  We assign the NEXTVAL to a variable, write
> that to the row, then raise its value as a notice.  Then we just
> retrieve the notice.

another way to to this is make a dynamic plpgsql function that takes
an insert statement and sequence name as parameters and returns the
currval on the way out.  to the op I would suggest that you can inline
currval into insert statements following the original insert e.g.

insert into master default values;
insert into detail(currval('master_id_seq'), foo, bar);

merlin