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
reclaimthe
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 | 1F | L | 1 | 2 |
3F | L | 10 | 2 | 4F | L | 100 | 2 | 100001F | L | 100 | 3 |
10000002F | L | 500 | 3 | 2110000001F | L | 501 | 3 | 2147483646F | 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 | 1F | L | 1 | 2 |
2F | L | 10 | 2 | 3F | L | 100 | 2 | 4F | L | 100 | 3 |
5F | L | 500 | 3 | 6F | L | 501 | 3 | 7F | 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