Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!hellgate.utah.edu!dog.ee.lbl.gov!pasteur!eden.Berkeley.EDU!mao
From: mao@eden.Berkeley.EDU (Mike Olson)
Newsgroups: comp.databases
Subject: Re: Wondering about using datetime as unique key...
Message-ID: <10461@pasteur.Berkeley.EDU>
Date: 25 Jan 91 18:21:00 GMT
References: <48429@apple.Apple.COM>
Sender: news@pasteur.Berkeley.EDU
Reply-To: mao@eden.Berkeley.EDU (Mike Olson)
Organization: University of California, Berkeley
Lines: 36

In <48429@apple.Apple.COM>, dshapiro@Apple.COM (David Shapiro) writes
> I haven't seen anyone talk about using the timestamp as a unique
> key to table.

we tried this in postgres for a while (actually, transaction id's were
a function of the system time).  we abandoned the approach after a while
as unworkable.

> The positive aspects I see are:
> Zero wait time for a new key for a table

no, you're guaranteed to have to do a system call, which guarantees
a context switch.

> Guaranteed monotonically increasing unique number (date)

you would hope so.  unfortunately, people *do* adjust their clocks.  recent
schemes for keeping the clocks on a network synchronized mean that every
machine's notion of the current time will be changed with pretty high
frequency.

> and assuming it is impossible to write to disk a row in less than
> a ms, no two rows will have the same key/timestamp. 

this is an invalid assumption.  if you're doing a multi-row insert (for
example, copying records from one table to another) you can do lots of
insertions to a single page fast, since that page will generally be in
the buffer cache.

this is another case where the database guys can't let the operating
system guys do their work for them, because the operating system can't
be trusted.
					mike olson
					postgres research group
					uc berkeley
					mao@postgres.berkeley.edu