Re: How to create "auto-increment" field WITHOUT a sequence object? - Mailing list pgsql-general

From Greg Smith
Subject Re: How to create "auto-increment" field WITHOUT a sequence object?
Date
Msg-id 4E0CF89E.6040506@2ndQuadrant.com
Whole thread Raw
In response to Re: How to create "auto-increment" field WITHOUT a sequence object?  (Dmitry Koterov <dmitry@koterov.ru>)
List pgsql-general
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@2ndQuadrant.com   Baltimore, MD
Comprehensive and Customized PostgreSQL Training Classes:
http://www.2ndquadrant.us/postgresql-training/


pgsql-general by date:

Previous
From: Dmitry Koterov
Date:
Subject: Re: How to create "auto-increment" field WITHOUT a sequence object?
Next
From: Casey Havenor
Date:
Subject: Re: Inheritence issue scheme advice?