Re: pg_system_identifier()

On Fri, Aug 23, 2013 at 3:10 AM, Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> wrote:
>
>> After someone in IRC asked if there was an equivalent to MySQL's
>> server_id, it was noted that we do have a system identifier but it's not
>> very accessible.
>>
>> The attached patch implements a pg_system_identifier() function that
>> exposes it.
>
>
> Would it make sense for such identifiers be standard UUID
> (http://en.wikipedia.org/wiki/UUID)?
>
> Should there be a UUID per cluster? and/or per database, possibly deduce
> from the cluster one? Should it be configurable, say from "postgresql.conf"?
>
> get_pg_uuid()
> get_pg_uuid('template0')
>
> Note that there is a set of uuid functions provided as a module that may
> help.

There is sense to this, sure.

I'd think that constructing a Type 5 (SHA-1) UUID based on some local
information would make a lot of sense.

In effect, based on constructing SHA-1 on a string looking like:

"Database system identifier: 5651554613500795646
Maximum data alignment: 8
Database block size: 8192
WAL block size: 8192
Maximum length of identifiers: 64
Date/time type storage: 64-bit integers
Version: PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by
gcc (Debian 4.6.1-4) 4.6.1, 64-bit"

==> SHA-1 of b1b012cc85149d2fe4bf0fc18c38dcf1218e95a5

(Note that I didn't put anything into that which is mutable such as
port numbers, MAC addresses, or IP
addresses - seems to introduce risk of false-negatives to me...)
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"