Thread: BUG #16693: Weird behaviour of function uuid_generate_v5() when used in a trigger
BUG #16693: Weird behaviour of function uuid_generate_v5() when used in a trigger
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 16693 Logged by: Vincent Tap Email address: vtap01@gmail.com PostgreSQL version: 12.4 Operating system: Windows 10 (64 bits) Description: Hi, I want to generate stable UUIDs only depending on the contents of a field. Function uuid_generate_v5 from extension uuid-ossp looked perfect. I checked that the function generates an identical UUID even on 2 different databases : SELECT uuid_generate_v5 (uuid_nil (), LOWER(TRIM('ALIMENTATION'))); always generates ff27240f-7f6c-5127-a8b1-e0289d14d895 But when I include it in a trigger, results vary each time. My trigger contains the following -- Génération nouvel identifiant IF (TG_OP = 'INSERT') THEN IF (NEW.analytique_uuid IS NULL) THEN NEW.analytique_uuid = uuid_generate_v5(uuid_nil(), LOWER(TRIM(groupe))); END IF; END IF; -- Génération horodatages IF (TG_OP IN ('INSERT', 'UPDATE')) THEN NEW.ts = now(); -- Message dans le fichier de log RAISE NOTICE '* * * Trigger % appelé % %. analytique_uuid = [%] * * *', TG_NAME, TG_WHEN, TG_OP, NEW.analytique_uuid; RETURN NEW; END IF; Using the log, the notice is available, so I'm sure that the trigger is fired. But inserting twice the same value generates 2 diifferent UUIDs : Example : SELECT groupe, analytique_uuid, uuid_generate_v5 (uuid_nil (), LOWER(TRIM(groupe))), ts FROM analytique WHERE groupe = 'ALIMENTATION'; ALIMENTATION f0974b62-5422-c44d-0766-92a7669a6678 ff27240f-7f6c-5127-a8b1-e0289d14d895 2020-10-30 22:08:40 +0100 ALIMENTATION 40e7a8e7-865e-407f-b1b1-e9a7331e51b3 ff27240f-7f6c-5127-a8b1-e0289d14d895 2020-10-30 22:08:46 +0100 Did I miss something or is it a bug ? Thanks and regards. Vincent Tap France
Re: BUG #16693: Weird behaviour of function uuid_generate_v5() when used in a trigger
From
Andres Freund
Date:
Hi, On 2020-10-30 21:48:34 +0000, PG Bug reporting form wrote: > I want to generate stable UUIDs only depending on the contents of a field. > Function uuid_generate_v5 from extension uuid-ossp looked perfect. I checked > that the function generates an identical UUID even on 2 different databases > : > > Did I miss something or is it a bug ? Hard to tell - this isn't a self contained example, so we can't easily try to reproduce and narrow down the issue. Greetings, Andres Freund