Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Posts
    3

    Unanswered: Random number sequences

    Hi,

    I have a problem that is too hard for me, so I've decided to turn to you gurus.

    I need random five-digit sequence, with MINVALUE 10000 and MAXVALUE 99999, to pe put in a column with every INSERT. Since regular sequences can't achieve this, can it be done some other way in PostgreSQL? Of course, Something like NO CYCLE feture would be useful. Am I asking too much?

  2. #2
    Join Date
    Jun 2004
    Posts
    31

    Random Sequences

    We had a similar need where we wanted to store state information about a web session, but wanted a random key that the browser passed in -- making it much harder for someone to guess a valid value.

    The obvious choice would be to set the default value to some random function, but that does not ensure uniqueness. You can't just check to see that it's not in the table either, since it could be inserted by another transaction after you between when you checked for it and when you inserted it. Further, another uncommitted transaction may be trying to insert the same value at the same time, and your transaction would not see it. This opens up a host of race conditions.

    In the end we wrote a function that replaces the INSERT command. This function tries to insert the values with different random keys until it succeeds and returns the key.

    If you want, I will post the code.

  3. #3
    Join Date
    Jan 2005
    Posts
    3
    Quote Originally Posted by ms139us
    ...
    In the end we wrote a function that replaces the INSERT command. This function tries to insert the values with different random keys until it succeeds and returns the key.

    If you want, I will post the code.
    Yes, of course! Thank you very much!

  4. #4
    Join Date
    Jun 2004
    Posts
    31
    Here it is. Of course, replace the function, field and table names to suit your liking.

    Good Luck,
    Marty

    Code:
    CREATE OR REPLACE FUNCTION
    fn_damnow_get_sel(damnow_stat.user_id%TYPE, damnow_stat.vars%TYPE)
    RETURNS damnow_stat.sel%TYPE AS '
    DECLARE
            -- selector we will return
            r_sel   damnow_stat.sel%TYPE;
    
            -- user passed in vars
            v_uid ALIAS for $1;
            v_var ALIAS for $2;
    BEGIN
            -- do not recycle selectors -- it helps
            -- avoid browser caching problems
            -- get next sel value
            -- keep looking until we have no collisions
            LOOP
                    -- use the random function and then mash it up
                    -- via MD5.
                    -- note the bizarre use of insert / where
                    -- this gives us atomicity so that there
                    -- are no race conditions.  This will either
                    -- insert a record or fail to, without raising
                    -- a Pg exception.  Later on, we check the FOUND
                    -- variable to determine if an insert actually happend
                    SELECT INTO r_sel MD5(RANDOM());
    
                    -- insert it, but ONLY if not already there
                    INSERT INTO damnow_stat (sel, user_id, vars)
                    SELECT r_sel, v_uid, v_var
                    WHERE NOT EXISTS (
                            SELECT sel
                            FROM damnow_stat
                            WHERE sel = r_sel
                            );
    
                    -- exit if this actually worked
                    EXIT WHEN FOUND;
            END LOOP;
    
            RETURN r_sel;
    END;
    ' LANGUAGE plpgsql SECURITY DEFINER;

  5. #5
    Join Date
    Jan 2005
    Posts
    3
    Quote Originally Posted by ms139us
    Here it is. Of course, replace the function, field and table names to suit your liking.
    Thanks once more!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •