Re: portable DBAPI auto-increment

Daniele Varrazzo wrote:
> On Fri, Apr 8, 2011 at 5:23 PM, Mark Sienkiewicz <sienkiew(at)stsci(dot)edu> wrote:
>
>
>> That basic code could be the core of the UID generation. It would also need
>> to deal with possibly non-unique numbers after it wraps (in postgres, the
>> value after 2147483647 is 1), but I probably have at least 5 years to figure
>> that out.
>>
>
> This is plain wrong. Where did you get this idea?
>

I did an experiment:

test=# create table foo ( k serial, v varchar(10) );
NOTICE: CREATE TABLE will create implicit sequence "foo_k_seq" for
serial column "foo.k"
CREATE TABLE
test=# insert into foo ( k, v ) values ( 2147483647, 'max' );
INSERT 0 1
test=# insert into foo ( v ) values ( 'a' ) ;
INSERT 0 1
test=# select * from foo;
2147483647 | max
1 | a

test=#

So, for a "serial" column with 2147483647 in it, I got 1 for the next
value inserted. I read your example to find out what you were doing
different and then went back to the postgres manual. What I see is that
"serial" is not the same thing as auto-increment -- it is different
thing entirely that you can use instead of auto-increment in some
cases. That is important to know, but was not immediately obvious to me
from what I have read so far.

>> (For comparison, mysql uses an unsigned 64 bit value for auto increment and
>> chokes when it runs out. sqlite wraps, but it automatically finds a new key
>> value that is not used.)
>>
>
> Please, read the fine manual: PostgreSQL has a 64 bit bigserial data type too.
>

Yes, I do know about the manual and I have spent considerable time
reading it. As you may know about manuals for a system of this size, it
takes time to find everything you need to know and to understand all the
implications.

Mark S.