Re: Impact of loss of unique SERIAL?

On Wed, Apr 30, 2003 at 09:33:03AM -0400, Justin Long wrote:

> We have been running Postgres 7.2.x for our website, and are considering the
> upgrade to 7.3.x. However, in reading through the changelogs I notice that
> SERIAL values are no longer unique. Now, I'm no SQL guru, but I'm wondering

SERIAL _columns_ no longer automatically get a unique index on them.
You need to add one if you want to ensure uniqueness there: CREATE
UNIQUE INDEX. . .

> what happens if two INSERTs are done at the "same" time (we have multiple
> web hosts that access one large database server). Would they result in two
> identical values on the SERIAL? We haven't gotten a stupendous amount of

No.  The underlying _sequence_, which is what those inserts call, is
still transaction- (and connection-) aware, so you won't have this
problem.

I don't actually recall the argument for removing the automatic
unique index.  But it's easy enough at the time of creation, since
you could specify a UNIQUE constraint on the column anyway.

When you upgrade, you should get the unique index, since your dump
will contain it.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110