pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16693: Weird behaviour of function uuid_generate_v5() when used in a trigger
2+ messages / 2 participants
[nested] [flat]

* BUG #16693: Weird behaviour of function uuid_generate_v5() when used in a trigger
@ 2020-10-30 21:48  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 2+ messages in thread

From: PG Bug reporting form @ 2020-10-30 21:48 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: vtap01@gmail.com

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: BUG #16693: Weird behaviour of function uuid_generate_v5() when used in a trigger
@ 2020-10-30 22:51  Andres Freund <andres@anarazel.de>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 0 replies; 2+ messages in thread

From: Andres Freund @ 2020-10-30 22:51 UTC (permalink / raw)
  To: vtap01@gmail.com; pgsql-bugs@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2020-10-30 22:51 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-10-30 21:48 BUG #16693: Weird behaviour of function uuid_generate_v5() when used in a trigger PG Bug reporting form <noreply@postgresql.org>
2020-10-30 22:51 ` Andres Freund <andres@anarazel.de>

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