0004462: QA-UUID: Generated numbers are not unique on Windows/PostgreSQL

(0009988)
alostale (manager)
2008-11-05 10:07

This problem is caused by the postgreSQL contrib library for windows that generates duplicated UUIDs when it is called twice in short periods of time. The issue 0005006 is a workarround for this problem, but could cause a performance decrement for process doing big amounts of insertions in database.

(0010186)
pjuvara (reporter)
2008-11-12 07:15

Downgrading severity since we have a workaround in place which enables basic functionality of the product and the alpha release of the product.

(0010253)
plujan (manager)
2008-11-13 17:14

This is not an actual Openbravo ERP issue but a PostgreSQL bug. It was solved since 8.3.5.

(0010502)
rafaroda (developer)
2008-11-21 13:04

Testing function:

CREATE OR REPLACE FUNCTION test_uuidgen(countmax INTEGER) RETURNS character varying AS
$BODY$
DECLARE
  countmin INTEGER := 1;
  distinctuuid bigint;
  v_message VARCHAR(2000) := '';

BEGIN
    drop table if exists uuid_gen;
    CREATE TABLE uuid_gen
    (
      uuid_gen_col uuid NOT NULL

    );
    LOOP
        INSERT INTO uuid_gen (uuid_gen_col) VALUES (uuid_generate_v1());
        countmin := countmin + 1;
        EXIT WHEN countmin > countmax;
    END LOOP;
    SELECT COUNT(DISTINCT uuid_gen_col) INTO distinctuuid FROM uuid_gen;
    IF distinctuuid <> countmax THEN
        v_message := 'Repeated UUID: FAILS';
    ELSE
        v_message := 'NO repeated UUID: OK';
    END IF;
    RETURN v_message;
END;
$BODY$ LANGUAGE 'plpgsql';

(0010503)
rafaroda (developer)
2008-11-21 13:06

Add the previous function to your PostgreSQL database and execute:

select test_uuidgen(100000);

If no UUID are repeated you get as result 'NO repeated UUID: OK'

Issue History
Date Modified	Username	Field	Change
2008-07-21 10:47	plujan	New Issue
2008-07-21 10:47	plujan	Assigned To	=> cromero
2008-07-21 10:47	plujan	sf_bug_id	0 => 2023360
2008-07-21 10:48	plujan	Assigned To	cromero => alostale
2008-07-21 10:48	plujan	Summary	QA-UUID: Generated numbers are not unique => QA-UUID: Generated numbers are not unique on Windows/PostgreSQL
2008-07-21 16:11	cromero	Status	new => scheduled
2008-07-21 16:11	cromero	Target Version	=> 2.50
2008-10-10 11:31	cromero	Relationship added	has duplicate 0005006
2008-10-10 11:31	cromero	Relationship deleted	has duplicate 0005006
2008-10-10 11:32	cromero	Relationship added	has duplicate 0005319
2008-10-22 09:54	pjuvara	Priority	normal => immediate
2008-11-05 10:04	alostale	Relationship added	related to 0005006
2008-11-05 10:07	alostale	Note Added: 0009988
2008-11-05 10:08	alostale	Priority	immediate => high
2008-11-12 07:15	pjuvara	Note Added: 0010186
2008-11-12 07:15	pjuvara	Priority	high => urgent
2008-11-12 07:15	pjuvara	Severity	critical => major
2008-11-13 17:14	plujan	Regression testing	=> No
2008-11-13 17:14	plujan	Status	scheduled => closed
2008-11-13 17:14	plujan	Note Added: 0010253
2008-11-13 17:14	plujan	Resolution	open => no change required
2008-11-21 13:04	rafaroda	Note Added: 0010502
2008-11-21 13:06	rafaroda	Note Added: 0010503

Notes
(0009988) alostale (manager) 2008-11-05 10:07	This problem is caused by the postgreSQL contrib library for windows that generates duplicated UUIDs when it is called twice in short periods of time. The issue 0005006 is a workarround for this problem, but could cause a performance decrement for process doing big amounts of insertions in database.

(0010186) pjuvara (reporter) 2008-11-12 07:15	Downgrading severity since we have a workaround in place which enables basic functionality of the product and the alpha release of the product.

(0010253) plujan (manager) 2008-11-13 17:14	This is not an actual Openbravo ERP issue but a PostgreSQL bug. It was solved since 8.3.5.

(0010502) rafaroda (developer) 2008-11-21 13:04	Testing function: CREATE OR REPLACE FUNCTION test_uuidgen(countmax INTEGER) RETURNS character varying AS $BODY$ DECLARE countmin INTEGER := 1; distinctuuid bigint; v_message VARCHAR(2000) := ''; BEGIN drop table if exists uuid_gen; CREATE TABLE uuid_gen ( uuid_gen_col uuid NOT NULL ); LOOP INSERT INTO uuid_gen (uuid_gen_col) VALUES (uuid_generate_v1()); countmin := countmin + 1; EXIT WHEN countmin > countmax; END LOOP; SELECT COUNT(DISTINCT uuid_gen_col) INTO distinctuuid FROM uuid_gen; IF distinctuuid <> countmax THEN v_message := 'Repeated UUID: FAILS'; ELSE v_message := 'NO repeated UUID: OK'; END IF; RETURN v_message; END; $BODY$ LANGUAGE 'plpgsql';

(0010503) rafaroda (developer) 2008-11-21 13:06	Add the previous function to your PostgreSQL database and execute: select test_uuidgen(100000); If no UUID are repeated you get as result 'NO repeated UUID: OK'