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 |


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

elein <elein (AT) varlena (DOT) com> writes:
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

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:
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

elein <elein (AT) varlena (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)