Doug Gorley <doug.gorley@xxxxxxxxx> 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? The only reason I can think to add that much complexity is to ensure gap-free sequences, which Postgres' internal sequences do _not_ guarantee. And yes, it's pretty much guaranteed to be slower than built in sequences, with blocking when multiple threads want a sequence all at the same time. I'm rather concerned by the third column, as I'm not sure what his implementation approach is, and I'm concerned that he's using a home-brewed locking mechanism instead of using table locks. -- Bill Moran http://www.potentialtech.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
