Re: Re: serial properties - Mailing list pgsql-general

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
>