fboulnois / pg_uuidv7 Goto Github PK

View Code? Open in Web Editor NEW

273.0 273.0 23.0 38 KB

A tiny Postgres extension to create version 7 UUIDs

License: Mozilla Public License 2.0

Dockerfile 24.04% Makefile 6.16% C 58.74% Shell 11.06%

c libpq postgres postgresql postgresql-extension uuid uuid-generator uuidv7

pg_uuidv7's People

Contributors

Stargazers

Watchers

pg_uuidv7's Issues

[Docs] Add Docker COPY from Example

Hey there, I wonder if we could "merge" the pg_uuidv7 image into the base image.

Why would you do that?

I have other extension requirements aside from pg_uuidv7, and I am hoping that such a pattern becomes familiar enough so Extension Authors could provide a straightforward image to COPY from instead of relying on tools like pgxn or pgxman, or deal with "complex" (unfamiliar) installations steps and instead take advantage of Official Docker Image.

FROM postgres:latest

COPY --from=ghcr.io/fboulnois/pg_uuidv7:1.5.0 /[help here] /[help here]

I appreciate any help you can provide.

PL/pgSQL Version

Hey there. Thanks for creating this extension! Was curious if it was either performant or possible to create a version of this using PL/pgSQL so that it can be used with AWS RDS, Supabase, etc (cloud postgres) and pg_tle.

Generated values not always monotonic

Hi, thanks for creating this extension!

When I generate multiple UUIDv7 values with the same timestamp component, the resulting values are not always monotonic:

If I sort the generated values, they end up in a different order:

018a6bae-69c8-7071-a5a9-6d5305fe0f9c
018a6bae-69c8-7332-8334-225b8cf94177
018a6bae-69c8-75c7-a4a1-c472293bc553
018a6bae-69c8-770d-bbc4-1d878d654e61
018a6bae-69c8-7b81-9cdf-71f21523e24d
018a6bae-69c8-7bcf-96ee-dc1fbf232699
018a6bae-69c8-7c98-8ef0-6f23c3f68da6
018a6bae-69c8-7e18-91f1-9d93b1570dd6
018a6bae-69c8-7e56-a84d-7043f3879e45
018a6bae-69c8-7e6c-acf8-7490e59452d8

Could you please confirm if this is the expected behavior?

Thanks in advance!

Feature: Please add to github workflow pushing docker image to https://hub.docker.com/

Dockerfile is not working

Built the image using provided Dockerfile.

postgres=# CREATE EXTENSION pg_uuidv7;
ERROR:  extension "pg_uuidv7" is not available
DETAIL:  Could not open extension control file "/usr/share/postgresql/16/extension/pg_uuidv7.control": No such file or directory.
HINT:  The extension must first be installed on the system where PostgreSQL is running.
postgres=# SELECT uuid_generate_v7();
ERROR:  function uuid_generate_v7() does not exist
LINE 1: SELECT uuid_generate_v7();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Add ability to extract the timestamp from uuid value

Would be nice to have a simple function that could extract the timestamp part of the uuidv7 value.

For now, I've found this ugly hack:

SELECT to_timestamp(('x' || ltrim((decode(replace(uuid_generate_v7()::text, '-', ''), 'hex'))::text, '\x'))::bit(48)::bigint / 1000);

ERROR: extension "pg_uuidv7" is not available

Hi, I am quite new to docker so please bear with me.

I am trying to create temporary docker containers with testcontainers node to test using vitest my Postgres 15 database. I am using pg_uuidv7 on Neon.tech so trying to replicate the extension.

For example tables in my db are like (defined using drizzle)

CREATE TABLE IF NOT EXISTS "invitations" (
	"invitation_id" uuid PRIMARY KEY DEFAULT uuid_generate_v7() NOT NULL,
	"inviter_team_id" uuid NOT NULL,
	"inviter_user_id" uuid NOT NULL,
	"invitee_email" varchar(100),
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp,
	"deleted_at" timestamp,
	CONSTRAINT "invitations_invitation_token_unique" UNIQUE("invitation_token")
);

I forked the repo and built the image

❯ docker build --no-cache  . --tag pg_uuidv7
[+] Building 32.8s (11/11) FINISHED                                                              docker:desktop-linux
 => [internal] load .dockerignore                                                                                0.0s
 => => transferring context: 2B                                                                                  0.0s
 => [internal] load build definition from Dockerfile                                                             0.0s
 => => transferring dockerfile: 400B                                                                             0.0s
 => [internal] load metadata for docker.io/library/postgres:15-bullseye                                          0.0s
 => CACHED [1/6] FROM docker.io/library/postgres:15-bullseye                                                     0.0s
 => [internal] load build context                                                                                0.0s
 => => transferring context: 2.47kB                                                                              0.0s
 => [2/6] RUN apt-get update && apt-get -y upgrade   && apt-get install -y build-essential libpq-dev postgresq  30.0s
 => [3/6] WORKDIR /srv                                                                                           0.0s
 => [4/6] COPY . /srv                                                                                            0.0s
 => [5/6] RUN make                                                                                               0.4s
 => [6/6] RUN tar -czvf pg_uuidv7.tar.gz pg_uuidv7--1.2.sql pg_uuidv7.control pg_uuidv7.so   && sha256sum pg_uu  0.3s
 => exporting to image                                                                                           2.0s
 => => exporting layers                                                                                          2.0s
 => => writing image sha256:fcdac1e74592953d86d900a443125920b3f72ec3ad6ae28f329628283013651b                     0.0s
 => => naming to docker.io/library/pg_uuidv7                                                                     0.0s

Started the container

❯ docker run -p 49153:5432 -e POSTGRES_USER=testuser -e POSTGRES_PASSWORD=testpassword -e POSTGRES_DB=mydatabase pg_uuidv7

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
waiting for server to start....2023-09-19 06:17:20.308 UTC [49] LOG:  starting PostgreSQL 15.4 (Debian 15.4-2.pgdg110+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2023-09-19 06:17:20.309 UTC [49] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-09-19 06:17:20.312 UTC [52] LOG:  database system was shut down at 2023-09-19 06:17:20 UTC
2023-09-19 06:17:20.316 UTC [49] LOG:  database system is ready to accept connections
 done
server started
CREATE DATABASE


/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

waiting for server to shut down...2023-09-19 06:17:20.470 UTC [49] LOG:  received fast shutdown request
.2023-09-19 06:17:20.471 UTC [49] LOG:  aborting any active transactions
2023-09-19 06:17:20.472 UTC [49] LOG:  background worker "logical replication launcher" (PID 55) exited with exit code 1
2023-09-19 06:17:20.473 UTC [50] LOG:  shutting down
2023-09-19 06:17:20.473 UTC [50] LOG:  checkpoint starting: shutdown immediate
2023-09-19 06:17:20.504 UTC [50] LOG:  checkpoint complete: wrote 918 buffers (5.6%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.013 s, sync=0.017 s, total=0.032 s; sync files=301, longest=0.005 s, average=0.001 s; distance=4217 kB, estimate=4217 kB
2023-09-19 06:17:20.510 UTC [49] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2023-09-19 06:17:20.589 UTC [1] LOG:  starting PostgreSQL 15.4 (Debian 15.4-2.pgdg110+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2023-09-19 06:17:20.589 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-09-19 06:17:20.590 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2023-09-19 06:17:20.592 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-09-19 06:17:20.594 UTC [65] LOG:  database system was shut down at 2023-09-19 06:17:20 UTC
2023-09-19 06:17:20.597 UTC [1] LOG:  database system is ready to accept connections

Then I connected to the database using beekeeper and ran the SQL statement CREATE EXTENSION pg_uuidv7

2023-09-19 06:18:57.539 UTC [83] ERROR:  extension "pg_uuidv7" is not available
2023-09-19 06:18:57.539 UTC [83] DETAIL:  Could not open extension control file "/usr/share/postgresql/15/extension/pg_uuidv7.control": No such file or directory.
2023-09-19 06:18:57.539 UTC [83] HINT:  The extension must first be installed on the system where PostgreSQL is running.
2023-09-19 06:18:57.539 UTC [83] STATEMENT:  CREATE EXTENSION pg_uuidv7

I am not seeing the pg_uuidv7 files in the container. Not sure if i am missing something or did something wrong. Thanks!

Trouble Creating Extension on M1 Mac

Hi there,
I'm trying to install this extension on my Apple Silicon Mac, but I get this error on the CREATE EXTENSION step:

ERROR:  could not load library "/opt/homebrew/lib/postgresql@14/pg_uuidv7.so": dlopen(/opt/homebrew/lib/postgresql@14/pg_uuidv7.so, 0x000A): tried: '/opt/homebrew/lib/postgresql@14/pg_uuidv7.so' (not a mach-o file), '/System/Volumes/Preboot/Cryptexes/OS/opt/homebrew/lib/postgresql@14/pg_uuidv7.so' (no such file), '/opt/homebrew/lib/postgresql@14/pg_uuidv7.so' (not a mach-o file), '/opt/homebrew/Cellar/postgresql@14/14.10_1/lib/postgresql@14/pg_uuidv7.so' (not a mach-o file), '/System/Volumes/Preboot/Cryptexes/OS/opt/homebrew/Cellar/postgresql@14/14.10_1/lib/postgresql@14/pg_uuidv7.so' (no such file), '/opt/homebrew/Cellar/postgresql@14/14.10_1/lib/postgresql@14/pg_uuidv7.so' (not a mach-o file)

I'd appreciate it if I got some help on the install.
Thanks.

Support Postgres 16

The most targeted, cleanest UUIDv7 extension around

Hi Florian,

I couldn't find any way to contact you besides opening an issue. (I will close immediately after.) I just wanted to write you to let you know that, after surveying all the available sequential UUID implementations for PostgreSQL, for my use case, your pg_uuidb7 came out as the clear winner:

https://blog.bigsmoke.us/2023/06/04/postgresql-sequential-uuids

"Boundary" UUID function to create UUID from timestamp

I wondered if you would consider adding a function to create "boundary" UUIDs from a given timestamp, to aid in date range query filters. By "boundary" I mean a v7 UUID with the first 48 bits set to a timestamp, but all other non-UUID spec bits set to 0.

For example, a function uuid_v7_boundary(timestamtz) could be used like:

-- "id" column is a v7 UUID
SELECT *
FROM t
WHERE id >= uuid_v7_boundary('2023-09-01Z'::timestamptz)
AND id < uuid_v7_boundary('2023-09-02Z'::timestamptz)

I have been using a SQL function to do this, but I thought it could be a nice feature of this extension.

Add to pgxn?

Hi!

This postgres extension is really useful! Would it be possible to add it to pgxn.org for easy installation?

ARM support

Are you able to also release ARM platform binaries?

Support GreenPlum

It would be greate to support GreenPlum (version 6.26)

Examples: https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/ref_guide-modules-intro.html

Support microsecond resolution

Thank you for writing this handy extension! I wondered if you would consider adding a function to create a v7 UUID variant with microsecond precision, by making use of the high 10 bits of bytes 6 & 7 for a 0-999 microseconds value instead of random bits. The reason for this is because I found the millisecond-level precision in a v7 UUID inadequate for an application I worked on, and using microseconds was a good compromise between time precision and randomness.

The UUID layout looks like this:

 0                   1                   2                   3
 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                           unix_ts_ms                          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|          unix_ts_ms           |  ver  |       micros      |rnd|
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|var|                        rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                            rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

I thought this could be an additional function like uuid_generate_v7_micro() so it did not impact the existing uuid_generate_v7() function. I have been using a SQL function for this, but a C function would be much faster!

fboulnois / pg_uuidv7 Goto Github PK

pg_uuidv7's People

Contributors

Stargazers

Watchers

Forkers

pg_uuidv7's Issues

Recommend Projects

Recommend Topics

Recommend Org