On Oct 19, 2006, at 10:30 , John D. Burger wrote:
cckramer wrote:
I have table for online chat system that keep messages sent
between users.
Question: is it okay to use timestamp as primary key, or there is
possibility of collision? (Meaning two processes may INSERT into
table
within same millisecond.) It is a web application.
tometzky wrote:
If your insert fail you can always try again after some random short
time.
But then the timestamp field does not accurately represent the
actual time of the event. If you really want a primary key, and
you really don't want to just use a sequence default, I would make
the key a composite:
PRIMARY KEY (user_id_from, user_id_to, message_time)
This should cut way down on the possibility of key collision.
Only if each message is contained in its own transaction since now()
is effectively a constant throughout a transaction. In this case, I
would choose a surrogate key since it is likely that the table will
be referenced.
-M
