Questions about SERIAL type
От | reina@nsi.edu (Tony Reina) |
---|---|
Тема | Questions about SERIAL type |
Дата | |
Msg-id | f40d3195.0111281330.44460e0d@posting.google.com обсуждение исходный текст |
Ответы |
Re: Questions about SERIAL type
Re: Questions about SERIAL type |
Список | 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 по дате отправления: