Usability or Data Bug in SERIAL column declarations - Mailing list pgsql-bugs

This was tested in 7.4.


If you have a table where you declare a column to be
SERIAL, the data type of that column will be set to
type integer (int4).

If you have a table where you declare a column to be
SERIAL8, the data type of that column will be set to
type bigint (int8).

In both cases sequences are created as int8 values with
a maximum of 9223372036854775807.

BUG: The assignment of the table containing the int4 column
will overflow at 2147483648 (max integer size).

A possible solution is to set the maximum of the sequence
to max integer size when it is created for a SERIAL column
(and to max bigint when created for a SERIAL8).

Tell me if I've misunderstood something.

--elein


elein=# create table iceseq (myseq SERIAL, acol text );
NOTICE:  CREATE TABLE will create implicit sequence "iceseq_myseq_seq" for "serial" column "iceseq.myseq"
CREATE TABLE
elein=# create table iceseq2 (myseq SERIAL8, acol text );
NOTICE:  CREATE TABLE will create implicit sequence "iceseq2_myseq_seq" for "serial" column "iceseq2.myseq"
CREATE TABLE
elein=# \d iceseq_myseq_seq
Sequence "public.iceseq_myseq_seq"
    Column     |  Type
---------------+---------
 sequence_name | name
 last_value    | bigint
 increment_by  | bigint
 max_value     | bigint
 min_value     | bigint
 cache_value   | bigint
 log_cnt       | bigint
 is_cycled     | boolean
 is_called     | boolean

elein=# select * from iceseq_myseq_seq;
  sequence_name   | last_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled |
is_called 

------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 iceseq_myseq_seq |          1 |            1 | 9223372036854775807 |         1 |           1 |       1 | f         | f
(1 row)

elein=# select * from iceseq2_myseq_seq;
   sequence_name   | last_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled |
is_called 

-------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 iceseq2_myseq_seq |          1 |            1 | 9223372036854775807 |         1 |           1 |       1 | f         |
f
(1 row)

elein=# \d iceseq
                            Table "public.iceseq"
 Column |  Type   |                         Modifiers
--------+---------+-----------------------------------------------------------
 myseq  | integer | not null default nextval('public.iceseq_myseq_seq'::text)
 acol   | text    |

elein=# \d iceseq2
                            Table "public.iceseq2"
 Column |  Type  |                         Modifiers
--------+--------+------------------------------------------------------------
 myseq  | bigint | not null default nextval('public.iceseq2_myseq_seq'::text)
 acol   | text   |