pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ken Corey <ken.corey@atomic-interactive.com>
To: Josh Berkus <josh@agliodbs.com>
To: Bernardo de Barros Franco <electric_csf@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Re: Using Random Sequence as Key
Date: Thu, 5 Jul 2001 15:44:16 +0000
Message-ID: <0107051544160J.03822@kenlinux.bithub.org> (raw)
In-Reply-To: <web-40649@davinci.ethosmedia.com>
References: <web-40649@davinci.ethosmedia.com>

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



view thread (12+ messages)

Message-ID: <0107051544160J.03822@kenlinux.bithub.org>
Permalink:  ../0107051544160J.03822@kenlinux.bithub.org/
Also on:    postgresql.org/message-id/0107051544160J.03822@kenlinux.bithub.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: ken.corey@atomic-interactive.com, josh@agliodbs.com, electric_csf@hotmail.com
  Subject: Re: Re: Using Random Sequence as Key
  In-Reply-To: <0107051544160J.03822@kenlinux.bithub.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox