From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | vtap01(at)gmail(dot)com |
Subject: | BUG #16693: Weird behaviour of function uuid_generate_v5() when used in a trigger |
Date: | 2020-10-30 21:48:34 |
Message-ID: | 16693-5e7a7034d8eace85@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 16693
Logged by: Vincent Tap
Email address: vtap01(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2020-10-30 22:51:25 | Re: BUG #16693: Weird behaviour of function uuid_generate_v5() when used in a trigger |
Previous Message | Tom Lane | 2020-10-30 21:07:04 | Re: segfault with incremental sort |