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

Re: Re: Best practice for long-lived journal tables: bigintor recycling IDs?

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Mark Stosberg <mark(at)summersault(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Re: Best practice for long-lived journal tables: bigintor recycling IDs?
Date: 2008-07-08 22:00:55
Message-ID: 20080708220055.GG4095@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-sql
Mark Stosberg wrote:
> On Tue, 8 Jul 2008 17:20:13 -0400
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> 
> > 3. Deal with wraparound by ensuring that the applications behave sanely
> 
> Wrap-around?  
> 
> Exceeding the max size of "int" looks more like a brick wall than wrap-around to me:
> 
>  insert into t values (2147483648);
>  ERROR:  integer out of range

Hmm, you can alter the sequence so that it wraps around at the point it
reaches INT_MAX.  So inserting this number would never actually happen.

alvherre=# create table t (a serial);
NOTICE:  CREATE TABLE créera des séquences implicites « t_a_seq » pour la colonne serial « t.a »
CREATE TABLE
alvherre=# alter sequence t_a_seq maxvalue 2147483647;
ALTER SEQUENCE
alvherre=# alter sequence t_a_seq cycle;
ALTER SEQUENCE
alvherre=# select setval('t_a_seq', 2147483645);
   setval   
------------
 2147483645
(1 ligne)

alvherre=# insert into t default values;
INSERT 0 1
alvherre=# insert into t default values;
INSERT 0 1
alvherre=# insert into t default values;
INSERT 0 1
alvherre=# insert into t default values;
INSERT 0 1
alvherre=# insert into t default values;
INSERT 0 1
alvherre=# select * from t;
     a      
------------
 2147483646
 2147483647
          1
          2
          3
(5 lignes)


-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

In response to

Responses

pgsql-sql by date

Next:From: Nacef LABIDIDate: 2008-07-09 08:22:09
Subject: Profiling postgresql queries
Previous:From: Mark StosbergDate: 2008-07-08 21:35:24
Subject: Re: Best practice for long-lived journal tables: bigint or recycling IDs?

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