On Wed, Aug 26, 2015 at 9:45 AM, Igor Neyman <iney...@perceptron.com> wrote:
> *From:* pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] *On Behalf Of *Melvin Davidson > *Sent:* Tuesday, August 25, 2015 8:18 PM > *To:* Adrian Klaver <adrian.kla...@aklaver.com> > *Cc:* Jerry Sievers <gsiever...@comcast.net>; John R Pierce < > pie...@hogranch.com>; pgsql-general@postgresql.org > *Subject:* Re: [GENERAL] PostgreSQL Developer Best Practices > > > > …. > > Before ANYONE continues to insist that a serial id column is good, > consider the case where the number of tuples will exceed a bigint. > > Don't say it cannot happen, because it can. > > …………………… > > *Melvin Davidson* > > > > Now, it’s easy to overcome this limitation. > > You just make concatenated PK (id1, id2) with both columns of BIGINT type. > > > Easy, yes, but at this point I'd probably resort to converting to a length-limited text field (so as ensure toasting never occurs). In general, I see the main advantage of artificial PK in NO NEED to change > multiple child tables, when NATURAL key changes in the parent table. And I > never saw a system where NATURAL key wouldn’t need to be changed eventually. > > So, my conclusion: use artificial PK (for db convenience) and unique > NATURAL key (for GUI representation). > > > I haven't really had a chance to implement this formally but I've had similar thoughts along these lines. One nice thing about this, in theory, is that you can have a different lifecycle and usage policy for those GUI identifiers and they can be made to be inherently changeable. A unique tag that you can remove from one entity and reuse on a different one should the need arise. David J.
