Oooooh, this is a very interesting approach! I didn't realize any UUIDs could be created in a predictable way. Thank you, this might be what I need.
digimer
On 2018-09-25 1:47 a.m., James Keener wrote:
Also, modified time doesn't need to be the current time, if it starts as "null" and is set on the first update, and all subsequent updates, the pre-update modified time could be used to help key the history pk.
Jim
v3 UUIDs are basically MD5 hashes (v5 is sha1?). So for the same input you'll always get the same hash.
I had assumed the modified time would be the same; if that's not, then I'm not sure and my gut tells me this becomes A Really Hard Problem™.
Jim
On 2018-09-25 1:33 a.m., James Keener wrote:
> Do you need a single field for the pk or can you just make it the
> (original_table_pk, modified_time)? Alternatively, you could generate
> a uuid v3 from the (original_table_pk, modified_time) using something
> like uuid_generate_v3(uuid_nil(), original_table_pk || ":" ||
> modified_time)?
I need to preset the modified_time, I can't use now() or else the value
would differ between databases. Also, unless I am missing something,
uuid_generate_v3() would generate a different UUID per trigger of the
procedure, so I'd end up with different history_uuids on each database
that I ran the query against.
If I am missing something (and entirely possible I am), please hit me
with a clue stick. :)
digimer