Questions about SERIAL type - Mailing list pgsql-hackers

From reina@nsi.edu (Tony Reina)
Subject Questions about SERIAL type
Date
Msg-id f40d3195.0111281330.44460e0d@posting.google.com
Whole thread Raw
Responses Re: Questions about SERIAL type
Re: Questions about SERIAL type
List 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
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


pgsql-hackers by date:

Previous
From: Lamar Owen
Date:
Subject: Re: CVS branch management (was Re: A problem with new
Next
From: Tom Lane
Date:
Subject: Re: [GENERAL] Rules