Skip site navigation (1) Skip section navigation (2)

Re: portable DBAPI auto-increment

From: Mark Sienkiewicz <sienkiew(at)stsci(dot)edu>
To: Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
Cc: psycopg(at)postgresql(dot)org
Subject: Re: portable DBAPI auto-increment
Date: 2011-04-08 17:43:28
Message-ID: 4D9F4940.8030206@stsci.edu (view raw or flat)
Thread:
Lists: psycopg
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.


In response to

Responses

psycopg by date

Next:From: Adrian KlaverDate: 2011-04-08 18:09:29
Subject: Re: portable DBAPI auto-increment
Previous:From: Daniele VarrazzoDate: 2011-04-08 17:40:42
Subject: Re: portable DBAPI auto-increment

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group