Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f6ALVea22308 for ; Tue, 10 Jul 2001 17:31:40 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from bithub.org ([217.149.96.22]) by postgresql.org (8.11.3/8.11.1) with SMTP id f65FcQa49305 for ; Thu, 5 Jul 2001 11:38:26 -0400 (EDT) (envelope-from ken.corey@atomic-interactive.com) Received: (qmail 19679 invoked from network); 5 Jul 2001 15:39:05 -0000 Received: from unknown (HELO kenlinux.bithub.org) (192.168.202.201) by 192.168.202.10 with SMTP; 5 Jul 2001 15:39:05 -0000 From: Ken Corey Organization: Atomic Interactive, Ltd. To: "Josh Berkus" , "Bernardo de Barros Franco" , pgsql-sql@postgresql.org Subject: Re: Re: Using Random Sequence as Key Date: Thu, 5 Jul 2001 15:44:16 +0000 X-Mailer: KMail [version 1.1.99] Content-Type: text/plain; charset="ISO-8859-1" References: In-Reply-To: MIME-Version: 1.0 Message-Id: <0107051544160J.03822@kenlinux.bithub.org> Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id f65FcRa49319 X-Archive-Number: 200107/123 X-Sequence-Number: 3911 First of all, let me apologise for stepping so far back into the archives, but I wanted to get this in the archives in case anyone else wanted to use this. There's a slight addendum here...as written, it's fairly likely that people will get duplicate ID's using this system. A small change gets rid of the problem: multiply the serialportion by the number of digits you'll have in timeportion. So, if timeportion is three digits, you'd multiply serialportion by 1000 and *then* add the timeportion. Below, I use the mod operator '%' to make sure it's 3 digits or less. Caveat: this will only work as long as you have less than 40 million users. ;^) -Ken create FUNCTION new_request_id() RETURNS INT4 as ' DECLARE timeportion INT4; serialportion INT4; BEGIN timeportion := cast (date_part(''milliseconds'',timeofday()::timestamp) as integer); serialportion := nextval(''request_idfake_seq'')* 1000; RETURN (timeportion % 1000) + serialportion; END;' LANGUAGE 'plpgsql' ; On Tuesday 17 April 2001 3:08 pm, Josh Berkus wrote: > Bernardo, > > > I needed the random field because if I use serial and the user gets a > > 34203 > > he's sure that 34202 exists, and that (probably, there where 34202 > > inserts > > before him (or at least an offset + some)). Using a random just makes > > the > > user totally blind. > > As I said I could use a serial for indexing the table but I NEED the > > random > > field and I need to to be unique since all the queries will be using > > it as a > > search parameter. > > If inserting this way is slow it's not such a big deal since it's a > > small db > > and inserts are seldom made. > > Thanks in advance for any help. > > Here's another suggestion for you then: > > 1. Add a sequence "Sales_sq" > > 1. write a custom function for new id numbers: > CREATE FUNCTION new_sales_id() RETURNS INT4 AS ' > DECLARE > timeportion VARCHAR; > serialportion INT4; > BEGIN > timeportion := to_char(current_timestamp, ''ms''); > -- (or whatever the abbreviation for 2-digit milliseconds is) > serialportion := 100*(nextval(''sales_seq'')); > RETURN CAST(to_number(timeportion) AS INT4) + serialportion; > END; > > 3. Then set the id column to default to this new function. > > This would give you (after you correct my mistakes) a number, the first > X digits of are Serial, and the last 2 digits based on the server's > internal clock. Thus, the numbers would *not* be sequential, and would > appear fairly random, but would be unique *without* and expensive check > for that value anywhere in the table for each insert. > > -Josh Berkus > > > > > > > ______AGLIO DATABASE SOLUTIONS___________________________ > Josh Berkus > Complete information technology josh@agliodbs.com > and data management solutions (415) 565-7293 > for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Ken Corey, CTO Atomic Interactive, Ltd. ken.corey@atomic-interactive.com