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

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
>