Thread: timestamp as primary key?
Hello, I have table for online chat system that keep messages sent between users. CREATE TABLE chat_message ( message_time timestamp without time zone NOT NULL DEFAULT now(), message_body text, user_id_from bigint, user_id_to bigint, CONSTRAINT chat_message_pkey PRIMARY KEY (message_time) ) WITHOUT OIDS; I don't want to add int primary key because I don't ever need to find messages by unique id. 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. Thanks.
On Thu, 19 Oct 2006, Joe Kramer wrote: > 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. If your insert fail you can always try again after some random short time. Just do that several times and if it fails, say, 5 times show an error to a user (sugest him to try a lottery - he'll surely win ;-) ). Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh
Joe Kramer wrote: > Hello, > > I have table for online chat system that keep messages sent between users. > > CREATE TABLE chat_message > ( > message_time timestamp without time zone NOT NULL DEFAULT now(), Hmm - timestamp without time zone. So you don't actually care when the message was sent? Or you know all your users will be in one timezone and don't care about calculating elapsed times, perhaps. You might want to re-read the date+time part of the manuals again - the difference between with/without time zone is subtle but important. > message_body text, > user_id_from bigint, An 8-byte integer for user_id? And you're worried about 4 bytes for a pkey. Well, if you've going to have billions of registered users then I can see why you'll want to save that four bytes per row. > user_id_to bigint, I'm assuming you've left out fkey references to a user table. > CONSTRAINT chat_message_pkey PRIMARY KEY (message_time) > ) > WITHOUT OIDS; Without OIDs is a good idea - especially if you're going to have billions of users sending messages. > I don't want to add int primary key because I don't ever need to find > messages by unique id. So why the meaningless constraint? You're not supposed to have a primary key to keep the RDBMS police from kicking down your door and dragging you away, you're supposed to have it one so you can distinguish individual rows. If you genuinely don't want to be able to locate an individual message then don't have a primary key at all. Don't lie to yourself by choosing columns that aren't unique. If you think you don't need a primary key at all, think about how you would recover from the following: 1. Insert a batch of messages from archive. 2. Accidentally insert the same batch again. 3. Delete archive. 4. Notice duplicates. Best of luck. > 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. You tell us. Is it possible that a database server could allocate the same now() time for two rows given current hardware (or future hardware for that matter). What happens if you do multiple inserts per transaction? What about with multiple processors? What if the system clock gets reset? What if you end up having two servers and need to merge their message stores? Does this start to sound like a lot of uncertain, uncontrollable things to be sure about? So long as you can *guarantee* that it's impossible to duplicate the time you're fine. I'd take a step back and check you're clear on all the use-cases for this message store. I'm guessing you'll decide you *do* need to be able to distinguish between different messages, that there is no pre-existing primary key and that you'll want an auto-incremented integer primary-key. -- Richard Huxton Archonet Ltd
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. - John D. Burger MITRE
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
On Thu, Oct 19, 2006 at 10:36:29AM -0400, AgentM wrote: > 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. See timeofday(). -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)