Questions about SERIAL type

From: reina(at)nsi(dot)edu (Tony Reina)
To: pgsql-hackers(at)postgresql(dot)org
Subject: Questions about SERIAL type
Date: 2001-11-28 21:30:46
Message-ID: f40d3195.0111281330.44460e0d@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I was thinking of re-designing my database schema to use a SERIAL
value as an indentification across tables (i.e. as a foreign key).
I've been playing with some example tables and have found the
following behavior from SERIAL:

(1) I think SERIAL is defined as an int4. However, the upper bound
seems to be 2^31 - 1 (217483647) not 2^32 - 1. I suppose this is
because a generic int4 should have one bit for the sign
(negative/positive). However, shouldn't SERIAL always be a positive
number? Would it be correct to make it some kind of unsigned int4
instead?

(2) The SERIAL number increases even if the transaction was aborted
(e.g. if a repeated tuple were trying to be inserted into a unique
table, the transaction fails, but the SERIAL gets incremented).
I was hoping that VACUUM VERBOSE ANALYZE would somehow reclaim the
lost SERIAL indicies. So, for example, if I had the table:

db02=# select * from center_out order by id;
subject | arm | target | rep | id
---------+-----+--------+-----+------------
F | L | 1 | 1 | 1
F | L | 1 | 2 | 3
F | L | 10 | 2 | 4
F | L | 100 | 2 | 100001
F | L | 100 | 3 | 10000002
F | L | 500 | 3 | 2110000001
F | L | 501 | 3 | 2147483646
F | L | 502 | 3 | 2147483647
(8 rows)

then a VACUUM VERBOSE ANALYZE would do the following:

db02=# select * from center_out order by id;
subject | arm | target | rep | id
---------+-----+--------+-----+------------
F | L | 1 | 1 | 1
F | L | 1 | 2 | 2
F | L | 10 | 2 | 3
F | L | 100 | 2 | 4
F | L | 100 | 3 | 5
F | L | 500 | 3 | 6
F | L | 501 | 3 | 7
F | L | 502 | 3 | 8
(8 rows)

I figure that I should never reach 2^31 - 1 transaction per table even
with many aborted ones; however, I think these would be nice changes.

Comments?

-Tony

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2001-11-28 21:49:09 Re: Rules
Previous Message Lamar Owen 2001-11-28 21:22:18 Re: CVS branch management (was Re: A problem with new