Re: Questions about SERIAL type
От | Ned Wolpert |
---|---|
Тема | Re: Questions about SERIAL type |
Дата | |
Msg-id | XFMail.20011128160412.ned.wolpert@knowledgenet.com обсуждение исходный текст |
Ответ на | Questions about SERIAL type (reina@nsi.edu (Tony Reina)) |
Список | pgsql-hackers |
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I'm not 100% sure that you actually want this. The main reason I say this is that in most cases I use sequence numbers is to do forign-key relationships. If you change sequence numbers on rows in a table, unless all tables that use that sequence number are also modified, then the relationship between tables that rely on the sequence number is lost. If for any reason the sequence number is used externally, (not usually a good idea, but sometimes it is) then that relationship is also lost. And for argument sake, lets assume that we know each location a sequence number is referenced, so you can make the changes everywhere. (And that these numbers aren't used for other things like order-numbers that need to appear in a string format and printed/referenced later) That means that the database needs to be off-line during this access. So the modifications to Vacuum to make it less intrusive to users while its occuring is now lost. I don't think this is a good idea... (Also, does 7.2 have an 8 byte sequence number (serial8) anyways? So isn't this problem moot?) On 28-Nov-2001 Tony Reina wrote: > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly Virtually, Ned Wolpert <ned.wolpert@knowledgenet.com> D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE8BW1siysnOdCML0URAjFqAJ9RJk25zXl/mjhJmjC5tsf4bkj7EQCeNpph PcrtIXqceZLqdkDOyfAcq84= =MqDe -----END PGP SIGNATURE-----
В списке pgsql-hackers по дате отправления: