Any justification for sequence table vs. native sequences?

From: Doug Gorley <doug(dot)gorley(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Any justification for sequence table vs. native sequences?
Date: 2009-08-18 19:14:37
Message-ID: 4A8AFD9D.4090104@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(dot)gorley(at)gmail(dot)com <mailto:doug(dot)gorley(at)gmail(dot)com>

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stuart McGraw 2009-08-18 20:11:42 Re: Any justification for sequence table vs. native sequences?
Previous Message Michael Clark 2009-08-18 18:42:22 Fwd: PQgetlength vs. octet_length()