Thread: Usability or Data Bug in SERIAL column declarations

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   |

elein <elein@varlena.com> writes:
> 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).

I don't think this is really a problem.

You are going to have errors when you run out of int4-sized identifiers
in either case.  If we reduce the sequence's maximum, that just means
one more thing you'll have to fix in order to recover.

            regards, tom lane

But shouldn't it wrap when it hits the max?
Assuming wrapping is an OK behavior, not setting
a proper max will eliminate that option for overflow.

--elein


On Wed, Oct 27, 2004 at 08:30:37PM -0400, Tom Lane wrote:
> elein <elein@varlena.com> writes:
> > 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).
>
> I don't think this is really a problem.
>
> You are going to have errors when you run out of int4-sized identifiers
> in either case.  If we reduce the sequence's maximum, that just means
> one more thing you'll have to fix in order to recover.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

elein <elein@varlena.com> writes:
> But shouldn't it wrap when it hits the max?

No, absolutely not.  The default sequence behavior is NO CYCLE, meaning
you'll start getting errors from nextval() when you reach the max.
I don't see a lot of difference between getting that error and getting
an int8-to-int4 conversion error.

If someone actually wants wrapping in a serial column, they can adjust
the sequence CYCLE parameter with ALTER SEQUENCE, and fix the maxval
to be what they want at the same time.

Memo to whoever feels like hacking on pg_dump: I believe that any such
manual adjustment to a serial sequence's parameters would be lost over a
dump-n-restore, since pg_dump doesn't emit any CREATE SEQUENCE for it.
Maybe we need an ALTER SEQUENCE in there instead.

            regards, tom lane