Re: How to create "auto-increment" field WITHOUT a sequence object?

On 06/30/2011 03:01 PM, Dmitry Koterov wrote:
>
> It may not be "fully" gapless. The main cause is to keep uniq_id as
> low as it could be to not to exhaust 100000 values too fast.
> I think solutions with addition tables look too complicated for this
> case, is there a possiblilty to not to use an additional table?

You may think it's possible to build a gapless design that is less
complicated by writing some application or server code to enforce it.
You've already tried this and learned that it's much harder than it
seems. Doing this correctly without causing timeout and deadlock issues
is a hard problem.

Meanwhile, generating a gapless translation table that only includes
things that have been committed is easy, and you're not likely to run
into really strange and unexpected bugs in that implementation later.

Given those are the two situations you're comparing here, I would say
using the extra table is less complicated in every way. Sure, you're
adding another table, but the process happening against it is really
easy. The alternative doesn't have the extra table, but that doesn't
make it less complicated. Complexity needs to consider how difficult a
program is going to be to debug and maintain. And in those areas,
making a single table gapless is quite complicated.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
Comprehensive and Customized PostgreSQL Training Classes:
http://www.2ndquadrant.us/postgresql-training/