Re: Questions about SERIAL type

-----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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

Virtually,
Ned Wolpert <ned(dot)wolpert(at)knowledgenet(dot)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-----