Re: Any justification for sequence table vs. native sequences?

On 08/18/2009 01:14 PM, Doug Gorley wrote:
> I just stumbled across this table in a database
> developed by a collegue:
>
>
> field_name  | next_value  | lock
> ------------+-------------+--------
> id_alert    | 500010      | FREE
> id_page     | 500087      | FREE
> id_group    | 500021      | FREE
>
>
> These "id_" fields correspond to the primary keys
> on their respective tables.  Instead of making
> them of type serial, they are of bigints with a
> NOT NULL constraint, and the sequence numbers are
> being managed by the application (not the database.)
>
> I googled around a bit trying to find an argument
> either in favour of or against this approach, but
> didn't find much.  I can't see the advantage to
> this approach over using native PostgreSQL sequences,
> and it seems that there are plenty of disadvantages
> (extra database queries to find the next sequence
> number for one, and a locking mechanism that doesn't
> play well with multiuser updates for two.)
>
> Can anyone comment on this?  Has anyone ever had to
> apply a pattern like this when native sequences
> weren't sufficient?  If so, what was the justification?

One justification I can see is if there would otherwise
be an unmanageably large number of individual sequences.

I have an app in which there is a table containing
"things" that have a type code.  There can be an arbitrary
number of type codes and in practice may be many dozens.
Each "thing" also has a user-visible id number which
users normally assign sequentially within each type.
The app currently creates a sequence for each type and
uses them to provide a default values for the id numbers.
I am considering changing this to something like you
describe.  In my case there is a low insert rate so
contention (which I read is the biggest problem with
this approach) should not be an issue.