pgflake icon indicating copy to clipboard operation
pgflake copied to clipboard

IDs generated by the library are not sortable

Open gautamborad opened this issue 2 years ago • 0 comments

Wanted to bring up an issue I've encountered while trying the library, specifically regarding the sorting of generated IDs. I am not sure, but maybe this part of the logic needs to be revisited: https://github.com/mausimag/pgflake/blob/main/pgflake.c#L119

When there is a rollover of the sequence, we are subtracting the curr_time from now(ms) and once we are out of the loop, we take do the shift operation on that curr_time (without subtracting the pgflake_start_epoch)

CREATE EXTENSION IF NOT EXISTS pgflake;
CREATE TABLE simple_test (x bigint, y bigint NOT NULL DEFAULT pgflake_generate());
INSERT INTO simple_test (x) SELECT generate_series(1, 100000);
-- Check if any values are non-monotonic 
SELECT lead(y) over (order by x) - y from simple_test order by 1 asc;

This gives the output:

?column?      |
--------------+
-4180420853760|
-4179741376512|
-4126859591680|
-4109616807938|
             1|
             1|
             1|
             1|
             1|
             1|
             1|
             1|
             1|
             1|
             1|
             1|
             1|

Also, i was not able to make use of the pgflake.start_epoch. Even after setting it using SET pgflake.start_epoch TO '1686560555905';, the default epoch was getting use.

gautamborad avatar Jun 12 '23 16:06 gautamborad