serial properties

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:

Show quoted text

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

Show quoted text

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:

Show quoted text

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.