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?
Thanks,
--
------------------------------------------------------------------------
*Doug Gorley* | doug.gorley@gmail.com <mailto:doug.gorley@gmail.com>