Re: select/update performance?

You are buying yourself trouble. The holes in the IDs are the least
problem, and a generated sequence should not have any business meaning
anyway. BTW, what happens when you delete a record ? That would surely
leave you a hole in the IDs...
Just a quick thought: if you are going to handle manually the ID
creation, and lock rows/tables to do that, you will effectively
serialize all transactions which create ids, because the locks are held
until the transactions finish. Not to mention increased deadlock
probability.
Sequences were designed to overcome all these problems, so why not just
use them ?

Cheers,
Csaba.

On Wed, 2003-11-05 at 10:49, Bjørn T Johansen wrote:
> Yes, but the table in question have 3 PK and only one that needs this
> "sequence" so I just thought instead of getting holes in the IDs I just
> manually handle this counter somehow.. Not a big deal but... :)
>
>
> BTJ
>
> On Wed, 2003-11-05 at 10:42, Rob Fielding wrote:
> > Bjørn T Johansen wrote:
> > > I need to maintain a manually counter for an id-field, but I can do this
> > > two ways. Either make a counter table (which means one select and one
> > > update) or just selecting the largest id from existing table and
> > > increment by one (just one select + one table lock). Which one is
> > > fastest?
> >
> > Is would be better to create a SEQUENCE and simply call nextval on it.
> > Then you are assured that you'll get a unique sequence when working in a
> > concurrent environment.
> >
> > It would also be guaranteed faster than interrogating tables.
> >
> > Hope this helps,
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)