Re: Index speeds up one row table (why)?

On Mon, 2 Jun 2003, Dave E Martin XXIII wrote:

> Bruno Wolff III wrote:
>
> >Maybe you should reconsider how badly you want the app to be totally database
> >agnostic? Using a sequence might be less of a contortion than using vacuum
> >a few times a minute. You are likely to have similar performance issues
> >with other databases, so this section of code may not turn out to be very
> >portable in any case.
> >
> >
> Maybe I can further abstract out the generate unique-id portion, Since
> unique-id generation does seem to be a pretty common database extension
> (for some reason...), and then provide a generic schema definition, and
> a postgresql specific one (along with whatever others I can drum up).
> The generic one will rely on the software to come up with the unique id
> in the fashion I'm currently doing.
>
> Speaking of which, is there a better way than what i'm currently doing
> (when the database doesn't have any such support)? I've heard of one
> method based on something like "select max(id)+1 from table" but this
> seems error prone, at the very least, you'd have to have a unique index,
> and be prepared to redo on failure, which could get messy if its a big
> transaction, and frequent if there is a lot of concurrent inserting
> going on.
>
For a generic solution you could have an extra table that fed you
ids and update it every time you took a value. (Maybe a trigger could be
used?) Due to table locking during transactions no two concurrent
requested would get the same answer. Implementation could be
interesting but relatively simple.

BEGIN;
SELECT id from unqid where name='seq_name';
UPDATE unqid set id=id+1 where name='seq_name';
COMMIT;

Peter Childs