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
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 |