5

According to postgresql uuid-ossp documentation uuid_generate_v1() is based on Mac address + timestamp:

https://www.postgresql.org/docs/9.4/static/uuid-ossp.html

On a distributed database scenario where we have hundreds of databases generating records with UUID keys and syncing back to a central database.

Suppose we detect a machine has a wrong date/time in the future and we change it back to the correct date/time. May it generate a conflicted UUID key on this particular machine?

One scenario is the summer time / daylight savings.

Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
Thiago Sayão
  • 487
  • 8
  • 16

2 Answers2

2

There is no reason, that I know of to use uuid_generate_v1() over uuid_generate_v4(). That said, Philᵀᴹ is largely correct when he says

"One scenario is the summer time / daylight savings." -- no, it is not. Everything should be stored as UTC. Time always goes forwards. – Philᵀᴹ

Even if your display time is set to DST, (or something like CSTCDT), the underlying storage mechanism should be UTC.

That said, though everything should be stored as UTC, it's not always that way. There are exceptions, like with XP, or Windows 7, you may see some kind of abnormality, especially if you permit the other OS to modify the RTC.

And, there is always the problem of leap seconds if you really want to nitpick, when some kernels or daemons will roll back the second and it's basically up to them to see if they support this. Linux-proper and OSX does, Android does not afaik humorous NDT on Leap seconds

For reference, the spec details the process of generating a UUID v1 time-based UUID, 4.2 Algorithms for Creating a Time-Based UUID.

Community
  • 1
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
0

The doc for pg 17 says:

Historically this module depended on the OSSP UUID library, which accounts for the module's name. While the OSSP UUID library can still be found at http://www.ossp.org/pkg/lib/uuid/, it is not well maintained, and is becoming increasingly difficult to port to newer platforms. uuid-ossp can now be built without the OSSP library on some platforms. On FreeBSD and some other BSD-derived platforms, suitable UUID creation functions are included in the core libc library. On Linux, macOS, and some other platforms, suitable functions are provided in the libuuid library, which originally came from the e2fsprogs project (though on modern Linux it is considered part of util-linux-ng). When invoking configure, specify --with-uuid=bsd to use the BSD functions, or --with-uuid=e2fs to use e2fsprogs' libuuid, or --with-uuid=ossp to use the OSSP UUID library. More than one of these libraries might be available on a particular machine, so configure does not automatically choose one.

So, I would say, the answer might depend on the OS. On GNU/Linux, it might depend on choices made by the package maintainer.

The doc says the utility behind for Linux is libuuid library. I noticed elsewhere (for an R function, uuid:UUIDgenerate) that some software using libuuid too misbehave by generating duplicated time-based UUID (version 1).

I am sorry that I do not bring a definitive answer; just that at least one FLOS software using libuuid too is working incorrectly (even without changing computer time). Unless making numerous tests and inspecting the source code of several components, I think that double-checking uniqueness is required for critical operations. I suggest you have a look at how I implemented this for R, and get ideas to write your own tests for PostgreSQL. For instance, you could RAISE NOTICE when a backward-clock change due to Daylight Saving approches; for this, instead of my R warnings.DST section, you could detect a coming clock change with the code seen here:

SELECT date_part('timezone', date_trunc('day', now())) <> date_part('timezone',
    date_trunc('day', now()) + '1 day'::interval);

To be safe, if your data model allows this, also store the UUIDs in a field that ensure uniqueness (if needed, in a separate log table).

mayeulk
  • 111
  • 3