digoal
2023-12-24
PostgreSQL , PolarDB , DuckDB , pg_idkit , uuid , 集合
pg_idkit是rust写的一个PostgreSQL UUID生成方法函数大集合插件.
https://github.com/VADOSWARE/pg_idkit
后期将集成到宇宙最强PostgreSQL学习镜像中.
《2023-PostgreSQL/DuckDB/MySQL/PolarDB-X Docker镜像学习环境 ARM64版, 已集成热门插件和工具》
《2023-PostgreSQL/DuckDB/MySQL/PolarDB-X Docker镜像学习环境 AMD64版, 已集成热门插件和工具》
克隆项目
docker exec -ti pg bash
cd /tmp
git clone --depth 1 https://github.com/VADOSWARE/pg_idkit.git
配置cargo源, 参考: https://mirrors.ustc.edu.cn/help/crates.io-index.html
# export CARGO_HOME=/root
# mkdir -vp ${CARGO_HOME:-$HOME/.cargo}
# vi ${CARGO_HOME:-$HOME/.cargo}/config
[source.crates-io]
replace-with = 'ustc'
[source.ustc]
registry = "sparse+https://mirrors.ustc.edu.cn/crates.io-index/"
安装pg_idkit插件
cd /tmp/pg_idkit
cargo install cargo-get cargo-pgrx just
# 如果对pgrx有版本要求请参考修改, 例如:
# grep pgrx Cargo.toml
# cargo install --locked --version 0.10.2 cargo-pgrx
cargo pgrx init # create PGRX_HOME 后, 立即ctrl^c 退出
cargo pgrx init --pg14=`which pg_config` # 不用管报警
PGRX_IGNORE_RUST_VERSIONS=y cargo pgrx install --pg-config `which pg_config`
Installing extension
Copying control file to /usr/share/postgresql/14/extension/pg_idkit.control
Copying shared library to /usr/lib/postgresql/14/lib/pg_idkit.so
Discovering SQL entities
Discovered 35 SQL entities: 0 schemas (0 unique), 35 functions, 0 types, 0 enums, 0 sqls, 0 ords, 0 hashes, 0 aggregates, 0 triggers
Writing SQL entities to /usr/share/postgresql/14/extension/pg_idkit--0.2.1.sql
Finished installing pg_idkit
使用pg_idkit:
postgres=# create extension pg_idkit ;
CREATE EXTENSION
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
----------+---------+------------+----------------------------------------------------------------------------------------------
pg_idkit | 0.2.1 | public | multi-tool for generating new/niche universally unique identifiers (ex. UUIDv6, ULID, KSUID)
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
postgres=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-------------------------------------+--------------------------+---------------------+------
public | idkit_cuid2_generate | text | | func
public | idkit_cuid2_generate_text | text | | func
public | idkit_cuid_extract_timestamptz | timestamp with time zone | val text | func
public | idkit_cuid_generate | text | | func
public | idkit_cuid_generate_text | text | | func
public | idkit_ksuid_extract_timestamptz | timestamp with time zone | val text | func
public | idkit_ksuid_generate | text | | func
public | idkit_ksuid_generate_text | text | | func
public | idkit_ksuidms_extract_timestamptz | timestamp with time zone | val text | func
public | idkit_ksuidms_generate | text | | func
public | idkit_ksuidms_generate_text | text | | func
public | idkit_nanoid_generate | text | | func
public | idkit_nanoid_generate_text | text | | func
public | idkit_pushid_generate | text | | func
public | idkit_pushid_generate_text | text | | func
public | idkit_timeflake_extract_timestamptz | timestamp with time zone | val text | func
public | idkit_timeflake_generate | text | | func
public | idkit_timeflake_generate_text | text | | func
public | idkit_timeflake_generate_uuid | uuid | | func
public | idkit_ulid_extract_timestamptz | timestamp with time zone | val text | func
public | idkit_ulid_from_uuid | text | uuid uuid | func
public | idkit_ulid_from_uuid_text | text | uuid text | func
public | idkit_ulid_generate | text | | func
public | idkit_ulid_generate_text | text | | func
public | idkit_uuidv6_extract_timestamptz | timestamp with time zone | val text | func
public | idkit_uuidv6_generate | text | | func
public | idkit_uuidv6_generate_text | text | | func
public | idkit_uuidv6_generate_uuid | uuid | | func
public | idkit_uuidv7_extract_timestamptz | timestamp with time zone | val text | func
public | idkit_uuidv7_generate | text | | func
public | idkit_uuidv7_generate_text | text | | func
public | idkit_uuidv7_generate_uuid | uuid | | func
public | idkit_xid_extract_timestamptz | timestamp with time zone | val text | func
public | idkit_xid_generate | text | | func
public | idkit_xid_generate_text | text | | func
(35 rows)
postgres=# select idkit_ksuidms_generate();
idkit_ksuidms_generate
-----------------------------
2ZyFFpsNplQfZLzuFpXvi0vSEhH
(1 row)
postgres=# select idkit_ksuidms_generate();
idkit_ksuidms_generate
-----------------------------
2ZyFG60s2KHkrAnwHgDG7UFIbmj
(1 row)
postgres=# CREATE EXTENSION pg_idkit;
CREATE EXTENSION
postgres=# SELECT idkit_uuidv7_generate();
idkit_uuidv7_generate
--------------------------------------
018c106f-9304-79bb-b5be-4483b92b036c
pg_idkit is a Postgres extension for generating many popular types of identifiers:
| Methodology | Function | Crate | Description |
|---|---|---|---|
| UUID v6 | idkit_uuidv6_generate() |
uuidv6 |
UUID v6 (RFC 4122) |
idkit_uuidv6_generate_uuid() |
|||
idkit_uuidv6_extract_timestamptz(TEXT) |
|||
| UUID v7 | idkit_uuidv7_generate() |
uuid7 |
UUID v7 (RFC 4122) |
idkit_uuidv7_generate_uuid() |
|||
idkit_uuidv7_extract_timestamptz(TEXT) |
|||
| nanoid | idkit_nanoid_generate() |
nanoid |
NanoID, developed by Andrey Sitnik |
| ksuid | idkit_ksuid_generate() |
svix-ksuid |
Created by Segment |
idkit_ksuid_extract_timestamptz(TEXT) |
|||
| ksuid | idkit_ksuidms_generate() |
svix-ksuid |
Same as ksuid but with millisecond precision |
idkit_ksuidms_extract_timestamptz(TEXT) |
|||
| ulid | idkit_ulid_generate() |
ulid |
Unique, lexicographically sortable identifiers |
idkit_ulid_extract_timestamptz(TEXT) |
|||
| Timeflake | idkit_timeflake_generate() |
timeflake-rs |
Twitter's Snowflake + Instagram's ID + Firebase's PushID |
idkit_timeflake_extract_timestamptz(TEXT) |
|||
| PushID | idkit_pushid_generate() |
pushid |
Google Firebase's PushID |
| xid | idkit_xid_generate() |
xid |
XID |
idkit_xid_extract_timestamptz(TEXT) |
|||
| cuid (deprecated) | idkit_cuid_generate() |
cuid |
CUID |
idkit_cuid_extract_timestamptz(TEXT) |
|||
| cuid2 | idkit_cuid2_generate() |
cuid2 |
CUID2 |
This Postgres extension is made possible thanks to [pgrx][pgrx].
You can try out pg_idkit incredibly quickly by using docker, and a previously released package of pg_idkit:
docker run \
--rm \
-e POSTGRES_PASSWORD=replace_this \
-p 5432 \
--name pg_idkit \
ghcr.io/vadosware/pg_idkit:0.2.1-pg16.1-alpine3.18-amd64 Warning
Currently only amd64 (x86_64) images are present/supported (See pg_idkit packages).
Work to support more platforms is described in issue #30
Once the postgres server is running, open another shell and connect to the dockerized Postgres instance running on port 5432:
➜ docker exec -it pg_idkit psql -U postgres
psql (16.1)
Type "help" for help.
postgres=# CREATE EXTENSION pg_idkit;
CREATE EXTENSION
postgres=# SELECT idkit_uuidv7_generate();
idkit_uuidv7_generate
--------------------------------------
018c106f-9304-79bb-b5be-4483b92b036c
(1 row) 📃 From Source
To build pg_idkit from source, clone this repository and run the following:
cargo install cargo-get cargo-pgrx just
just package After running these commands you should see the following directory structure in target/release/pg_idkit-pg16:
target/release/pg_idkit-pg16
├── home
│ └── <user>
│ └── .pgrx
│ └── 16.1
│ └── pgrx-install
│ ├── lib
│ │ └── postgresql
│ │ └── pg_idkit.so
│ └── share
│ └── postgresql
│ └── extension
│ ├── pg_idkit--0.2.1.sql
│ └── pg_idkit.control
└── usr
├── lib
│ └── postgresql
│ └── pg_idkit.so
└── share
└── postgresql
└── extension
└── pg_idkit.control
24 directories, 8 files
As the installation of the extension into a specific version of postgres uses your local installation of pgrx-managed Postgres by default (normally at $HOME/.pgrx), cargo pgrx package reproduces the directory structure in target/release. You can safely ignore the shorter usr/lib/user/share tree.
In the example above, the files you need for a Postgres extension are:
target/release/home/<user>/.pgrx/16.1/pgrx-install/lib/postgresql/pg_idkit.sotarget/release/home/<user>/.pgrx/16.1/pgrx-install/share/postgresql/extension/pg_idkit--0.2.1.sqltarget/release/home/<user>/.pgrx/16.1/pgrx-install/share/postgresql/extension/pg_idkit.control
Install these files in the relevant folders for your Postgres installation -- note that exactly where these files should go can can differ across linux distributions and containerized environments.
💽 From Binary
If running a custom version of locally/globally manually installed Postgres, you may download (and verify the checksum of) a shared library version from the releases, and add it as one of your shared_preload_libraries in postgresql.conf.
Assuming you have downloaded the pg_idkit-vX.X.X.so file to /etc/postgresql/extensions, you might change the file like this:
postgresql.conf
shared_preload_libraries = '/etc/postgresql/extensions/pg_idkit-vX.X.X.so'
Once your postgres instance is started up, you should be able to CREATE EXTENSION:
postgres=# CREATE EXTENSION pg_idkit;
CREATE EXTENSION
postgres=# SELECT idkit_uuidv7_generate();
idkit_uuidv7_generate
--------------------------------------
018c106f-9304-79bb-b5be-4483b92b036c
🐳 Dockerfile
To use pg_idkit easily from a containerized environment, you can use the pg_idkit image, built from postgres:
docker run \
--rm \
-e POSTGRES_PASSWORD=replace_this \
-p 5432 \
--name pg_idkit \
ghcr.io/vadosware/pg_idkit:0.2.1-pg16.1-alpine3.18-amd64 From another terminal, you can exec into the pg_idkit container and enable pg_idkit:
➜ docker exec -it pg_idkit psql -U postgres
psql (16.1)
Type "help" for help.
postgres=# CREATE EXTENSION pg_idkit;
CREATE EXTENSION
postgres=# SELECT idkit_uuidv7_generate();
idkit_uuidv7_generate
--------------------------------------
018c106f-9304-79bb-b5be-4483b92b036c
(1 row) [!WARNING]
Currently only amd64 (x86_64) images are present/supported (Seepg_idkitpackages).Work to support more platforms is described in issue #30
📦 Debian (RPM)
RPMs are produced upon every official release of pg_idkit.
Grab a released version of the RPM (or build one yourself by running just build-rpm after setting up local development).
For example, with an RPM named pg_idkit-0.2.1-pg16.x86_64.rpm, you should be able to run:
dnf install pg_idkit-0.2.1-pg16.x86_64.rpm
There are some other projects in the Postgres ecosystem that implement alternative UUID generation mechanisms.
Here are some you may or may not have heard of:
- spa5k/uids-postgres
scoville/pgsql-ulidpg-xidgeckoboard/pgulid- this gist by
fabiolimacefor generating UUIDv6
Interested in contributing on the project? Set up your local development environment w/ docs/local-development.md.
Contributions are welcome!
If you find a bug or an impovement that should be included in pg_idkit, create an issue.
If you'd like to contribute code, get started by:
- Reading the local development guide
- Creating an issue (if necessary) to explain the new feature/bugfix/etc
- Forking this repository
- Creating a feature/bugfix/etc branch (we expect [conventional commits][conventional-commits], i.e.
feat: new awesome feature) - Opening a Pull Request to this repository
