Thread: serial properties
Hi, I would like to know which are the properties of the SERIAL type. Is a column defined SERIAL a primary key? Saludos... :-) System Administration: It's a dirty job, but someone told I had to do it. ----------------------------------------------------------------- Martín Marqués email: martin@math.unl.edu.ar Santa Fe - Argentina http://math.unl.edu.ar/~martin/ Administrador de sistemas en math.unl.edu.ar -----------------------------------------------------------------
From: "Martin A. Marques" <martin@math.unl.edu.ar> > Hi, I would like to know which are the properties of the SERIAL type. > Is a column defined SERIAL a primary key? > > Saludos... :-) Basically serial is NOT NULL with DEFAULT of nextval(some-sequence) and a primary key index defined on it. In fact if you do a \d on the table concerned, that's what it'll say. It's really just shorthand to save typing - Richard Huxton
On Thu, Mar 01, 2001 at 04:49:25PM -0300, Martin A. Marques wrote: > Hi, I would like to know which are the properties of the SERIAL type. > Is a column defined SERIAL a primary key? > > Saludos... :-) create table foo ( id serial primary key, data text not null check(char_length(data) > 0) ); Note: SERIAL isn't really a "type". The data type of "id" is an integer (oid I think??), and some hooks to use a SEQUENCE for the default value of "id" are created (as is the SEQUENCE). If you drop the table, you also need to drop the sequence that "SERIAL" creates. IMHO, automatically incremented number fields used for primary keys are both a blessing and a curse. It is almost always better to use some other data that *means something* for a primary key. If there's no possible candidate key, *then* maybe an autonumber key is appropriate. -- Eric G. Miller <egm2@jps.net>
> IMHO, automatically incremented number fields used for primary keys are > both a blessing and a curse. It is almost always better to use some > other data that *means something* for a primary key. If there's no > possible candidate key, *then* maybe an autonumber key is appropriate. Just wanted to say, I disagree strongly here (also MHO). I see quite a few benefits and very few drawbacks to using an auto-incrementing field for a primary key. In fact, the only drawback I can think of would be that it takes up a little more space per record to add a field used solely to uniquely identify that record. I can think of several drawbacks to a non-auto-incrementing primary key though: 1. Less efficient joins. Comparing integers is about as easy as it gets... text, char, and varchar require string comparisons, while floating point numbers are not good as keys because of rounding errors. 2. Discourages value changes. A value that "means something" might need to be modified in some manner. Sure you can define foreign keys with CASCADEs, but if you are using an auto-increment, you don't need to! 3. No value is guaranteed to be unique (well, when doing an INSERT or UPDATE... it only gets into the database if it *is* unique) unless all queries go through a critical section. To the best of my knowledge, the only way to do this inside the database is to use nextval either implicitly or explicitly. The only time I don't use auto-incrementing fields is when I have a many-to-many join table with two foreign keys that are both auto-incrementing fields, in which case the primary key is a combination of those two fields. Other than a bit of extra space, I don't see any reason not to. Greg
I agree that they are very handy. They become a major pain in the butt when you start doing replication between servers. For instance if you fail over to a standby server and you forget to update it's sequence first, merging data later becomes a nightmare. I'd like to have int8 sequences and basically give each server it's own block of numbers to work with. Alex. On Fri, 2 Mar 2001, Gregory Wood wrote: > > IMHO, automatically incremented number fields used for primary keys are > > both a blessing and a curse. It is almost always better to use some > > other data that *means something* for a primary key. If there's no > > possible candidate key, *then* maybe an autonumber key is appropriate. > > Just wanted to say, I disagree strongly here (also MHO). I see quite a few > benefits and very few drawbacks to using an auto-incrementing field for a > primary key. In fact, the only drawback I can think of would be that it > takes up a little more space per record to add a field used solely to > uniquely identify that record. I can think of several drawbacks to a > non-auto-incrementing primary key though: > > 1. Less efficient joins. Comparing integers is about as easy as it gets... > text, char, and varchar require string comparisons, while floating point > numbers are not good as keys because of rounding errors. > 2. Discourages value changes. A value that "means something" might need to > be modified in some manner. Sure you can define foreign keys with CASCADEs, > but if you are using an auto-increment, you don't need to! > 3. No value is guaranteed to be unique (well, when doing an INSERT or > UPDATE... it only gets into the database if it *is* unique) unless all > queries go through a critical section. To the best of my knowledge, the only > way to do this inside the database is to use nextval either implicitly or > explicitly. > > The only time I don't use auto-incrementing fields is when I have a > many-to-many join table with two foreign keys that are both > auto-incrementing fields, in which case the primary key is a combination of > those two fields. Other than a bit of extra space, I don't see any reason > not to. > > Greg > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
Currently there's a method that an individual backend can cache > 1 number from a sequence. Would it be practical to have a master control the sequences and let the replicated backends (different networks potentially) cache a 'slew' of numbers for use? Standard cache of 1, and inter-server cache of several hundred. Rules apply as normal from there -- of course this breaks down when the master goes down... -- Rod Taylor There are always four sides to every story: your side, their side, the truth, and what really happened. ----- Original Message ----- From: "adb" <adb@Beast.COM> To: "Gregory Wood" <gregw@com-stock.com> Cc: "PostgreSQL-General" <pgsql-general@postgresql.org> Sent: Friday, March 02, 2001 2:11 PM Subject: Re: [GENERAL] Re: serial properties > I agree that they are very handy. They become a major pain in > the butt when you start doing replication between servers. > For instance if you fail over to a standby server and you > forget to update it's sequence first, merging data later > becomes a nightmare. I'd like to have int8 sequences and > basically give each server it's own block of numbers to work > with. > > Alex. > > On Fri, 2 Mar 2001, Gregory Wood wrote: > > > > IMHO, automatically incremented number fields used for primary keys are > > > both a blessing and a curse. It is almost always better to use some > > > other data that *means something* for a primary key. If there's no > > > possible candidate key, *then* maybe an autonumber key is appropriate. > > > > Just wanted to say, I disagree strongly here (also MHO). I see quite a few > > benefits and very few drawbacks to using an auto-incrementing field for a > > primary key. In fact, the only drawback I can think of would be that it > > takes up a little more space per record to add a field used solely to > > uniquely identify that record. I can think of several drawbacks to a > > non-auto-incrementing primary key though: > > > > 1. Less efficient joins. Comparing integers is about as easy as it gets... > > text, char, and varchar require string comparisons, while floating point > > numbers are not good as keys because of rounding errors. > > 2. Discourages value changes. A value that "means something" might need to > > be modified in some manner. Sure you can define foreign keys with CASCADEs, > > but if you are using an auto-increment, you don't need to! > > 3. No value is guaranteed to be unique (well, when doing an INSERT or > > UPDATE... it only gets into the database if it *is* unique) unless all > > queries go through a critical section. To the best of my knowledge, the only > > way to do this inside the database is to use nextval either implicitly or > > explicitly. > > > > The only time I don't use auto-incrementing fields is when I have a > > many-to-many join table with two foreign keys that are both > > auto-incrementing fields, in which case the primary key is a combination of > > those two fields. Other than a bit of extra space, I don't see any reason > > not to. > > > > Greg > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
The caching of sequence numbers is one of the reasons you can end up wrapping around. A while back I was working with sybase and our dba had set the precache for some identity columns (same as postgres serial) pretty high and we ran into a sybase bug that caused the server to reboot a large number of times in a few weeks and next thing we knew we had huge gaps in the sequence and were getting pretty close to dealing with wrap around. If the sequence could be int8, I'd just allocate a couple billion for each server and not worry about it. It would be handy to have some master control of handing out blocks of sequence numbers. That's esentially what we're building at my current job but it would be great to not have to worry about it. I'm guessing that the 7.2 release if it indeed supports more than just basic one way replication will have to do something similar. Alex. On Fri, 2 Mar 2001, Rod Taylor wrote: > Currently there's a method that an individual backend can cache > 1 > number from a sequence. Would it be practical to have a master > control the sequences and let the replicated backends (different > networks potentially) cache a 'slew' of numbers for use? Standard > cache of 1, and inter-server cache of several hundred. Rules apply as > normal from there -- of course this breaks down when the master goes > down... > > -- > Rod Taylor > > There are always four sides to every story: your side, their side, the > truth, and what really happened. > ----- Original Message ----- > From: "adb" <adb@Beast.COM> > To: "Gregory Wood" <gregw@com-stock.com> > Cc: "PostgreSQL-General" <pgsql-general@postgresql.org> > Sent: Friday, March 02, 2001 2:11 PM > Subject: Re: [GENERAL] Re: serial properties > > > > I agree that they are very handy. They become a major pain in > > the butt when you start doing replication between servers. > > For instance if you fail over to a standby server and you > > forget to update it's sequence first, merging data later > > becomes a nightmare. I'd like to have int8 sequences and > > basically give each server it's own block of numbers to work > > with. > > > > Alex. > > > > On Fri, 2 Mar 2001, Gregory Wood wrote: > > > > > > IMHO, automatically incremented number fields used for primary > keys are > > > > both a blessing and a curse. It is almost always better to use > some > > > > other data that *means something* for a primary key. If there's > no > > > > possible candidate key, *then* maybe an autonumber key is > appropriate. > > > > > > Just wanted to say, I disagree strongly here (also MHO). I see > quite a few > > > benefits and very few drawbacks to using an auto-incrementing > field for a > > > primary key. In fact, the only drawback I can think of would be > that it > > > takes up a little more space per record to add a field used solely > to > > > uniquely identify that record. I can think of several drawbacks to > a > > > non-auto-incrementing primary key though: > > > > > > 1. Less efficient joins. Comparing integers is about as easy as it > gets... > > > text, char, and varchar require string comparisons, while floating > point > > > numbers are not good as keys because of rounding errors. > > > 2. Discourages value changes. A value that "means something" might > need to > > > be modified in some manner. Sure you can define foreign keys with > CASCADEs, > > > but if you are using an auto-increment, you don't need to! > > > 3. No value is guaranteed to be unique (well, when doing an INSERT > or > > > UPDATE... it only gets into the database if it *is* unique) unless > all > > > queries go through a critical section. To the best of my > knowledge, the only > > > way to do this inside the database is to use nextval either > implicitly or > > > explicitly. > > > > > > The only time I don't use auto-incrementing fields is when I have > a > > > many-to-many join table with two foreign keys that are both > > > auto-incrementing fields, in which case the primary key is a > combination of > > > those two fields. Other than a bit of extra space, I don't see any > reason > > > not to. > > > > > > Greg > > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > > > TIP 2: you can get off all lists at once with the unregister > command > > > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) > > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > > > > > ---------------------------(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 >
At 11:11 AM 3/2/01 -0800, adb wrote: >I agree that they are very handy. They become a major pain in >the butt when you start doing replication between servers. >For instance if you fail over to a standby server and you >forget to update it's sequence first, merging data later >becomes a nightmare. I'd like to have int8 sequences and >basically give each server it's own block of numbers to work >with. Yah. I'd like int8 sequences too. Server number X starts with X, increment by 256. Then again maybe blocks of numbers may be better. What happens if a sequence hits the end? Cheerio, Link.