pksunkara / pgx_ulid Goto Github PK
View Code? Open in Web Editor NEWPostgres extension for ulid
License: MIT License
Postgres extension for ulid
License: MIT License
There is an novel error when trying insert row.
The database is setup with https://github.com/pksunkara/pgx_ulid.
And it is using a function gen_monotonic_ulid()
as a default.
I followed the readme guide and added the shared_preload_libraries = 'ulid'
.
With or without this, the error still persisted.
there is also postgis installed.
FROM postgis/postgis:${PG_MAJOR}-3.3
...
COPY postgres.conf /etc/postgresql/postgresql.conf
CMD ["postgres", "-c", "config_file=/etc/postgresql/postgresql.conf"]
do you have any advice for my case?
# PostGIS, but add ULID extension
ARG PG_MAJOR=15
FROM postgis/postgis:${PG_MAJOR}-3.3
RUN apt update
ENV build_deps ca-certificates \
git \
build-essential \
libpq-dev \
postgresql-server-dev-${PG_MAJOR} \
curl \
libreadline6-dev \
zlib1g-dev
RUN apt-get install -y --no-install-recommends $build_deps pkg-config cmake
WORKDIR /home/postgres
ENV HOME=/home/postgres
ENV PATH=/home/postgres/.cargo/bin:$PATH
RUN chown postgres:postgres /home/postgres
USER postgres
# install rust
RUN \
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh -s -- -y --no-modify-path --profile minimal --default-toolchain 1.68.0 && \
rustup --version && \
rustc --version && \
cargo --version
# pgrx
RUN cargo install cargo-pgrx --version 0.7.4 --locked
RUN cargo pgrx init --pg${PG_MAJOR} $(which pg_config)
USER root
COPY . .
RUN cargo pgrx install
RUN chown -R postgres:postgres /home/postgres
RUN chown -R postgres:postgres /usr/share/postgresql/${PG_MAJOR}/extension
RUN chown -R postgres:postgres /usr/lib/postgresql/${PG_MAJOR}/lib
USER postgres
ENV POSTGRES_HOST_AUTH_METHOD=trust
ENV USER=postgres
I am not sure if this is the part that author should taking care or not.
Here is a note for future dudes who stucks like me.
ERROR: could not load library "/usr/lib/postgresql/15/lib/ulid.so": /lib/x86_64-linux-gnu/libc.so.6: version `GLIBC_2.33' not found (required by /usr/lib/postgresql/15/lib/ulid.so)
SQL state: 58P01
The reason is that the GLIBC
on image has 2.3.1 version and the requirement is 2.3.3.
Thus, the only way to use it is by compile only.
# PostGIS, but add ULID extension
ARG PG_MAJOR=15
FROM postgis/postgis:${PG_MAJOR}-3.3
RUN apt update
RUN apt-get install -y --no-install-recommends curl
RUN apt upgrade -y
RUN curl -OJL https://github.com/pksunkara/pgx_ulid/releases/download/v0.1.0/pgx_ulid-v0.1.0-pg15-amd64-linux-gnu.deb && \
apt install ./pgx_ulid-v0.1.0-pg15-amd64-linux-gnu.deb
WORKDIR /home/postgres
ENV HOME=/home/postgres
ENV PATH=/home/postgres/.cargo/bin:$PATH
RUN chown postgres:postgres /home/postgres
USER postgres
I just installed pgx_ulid into a Postgres 15 database, and while inserting and viewing in a database tool (DataSpell) works fine, when I try to query from Rust using SQLx I get a "no binary output function available for type ulid" error. I thought it might be due to how I was implementing SQLx's Encode
and Decode
traits, but I reworked those to be based on strings and I'm still getting the error. I'll paste some example code shortly.
There are valid reasons to disagree, but since this crate is more an artifact that is built and distributed than a library crate consumed as such, it would make sense to commit the Cargo.lock for reproducible builds. As a point of comparison, timescaledb-toolkit is also a pgrx project, they commit their lock file. This question is coming from noticing how others do it, and because it's slightly inconvenient for nix packaging — definitely not a blocker though.
What do you think?
We have installed the extension in a schema called utils
:
CREATE SCHEMA utils;
CREATE EXTENSION ULID SCHEMA utils;
The function utils.gen_monotonic_ulid()
works fine.
If I run the following query in postgresql 16.1:
SELECT CAST('01HTHV582X9B5YBFP7GRBWXRHX' AS utils.ulid) = CAST('01HTHV582X9B5YBFP7GRBWXRHX' AS utils.ulid)
Same result if we do:
SELECT utils.gen_monotonic_ulid() = utils.gen_monotonic_ulid();
I am getting the following error:
[2024-05-22 16:28:51] [42725] ERROR: operator is not unique: utils.ulid = utils.ulid
[2024-05-22 16:28:51] Hint: Could not choose a best candidate operator. You might need to add explicit type casts.
I have checked and I have the = operator defined in the DB for ulid types:
SELECT
n.nspname,
o.oprname,
o.oprleft,
o.oprright,
o.oprresult,
p.proname
FROM
pg_operator o
JOIN
pg_namespace n ON o.oprnamespace = n.oid
JOIN
pg_proc p ON o.oprcode = p.oid
WHERE
n.nspname = 'utils' AND oprname = '=';
Returns:
nspname | oprname | oprleft | oprright | oprresult | proname |
---|---|---|---|---|---|
utils | = | 1403350 | 1403350 | 16 | ulid_eq |
utils | = | 1403766 | 1403766 | 16 | geometry_eq |
utils | = | 1404472 | 1404472 | 16 | geography_eq |
We are having that same error I reported when joining tables by ULID ids.
Thank you!
This is mostly for help wanted. I have the following docker-compose file:
version: "3.9"
services:
postgres:
image: postgres:16.1-alpine
restart: always
ports:
- "5432:5432"
environment:
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres
- DEBUG=false
- POSTGRES_DB=postgres
volumes:
- ./init:/docker-entrypoint-initdb.d
healthcheck:
test: [ "CMD-SHELL", "pg_isready -U postgres" ]
interval: 5s
timeout: 5s
retries: 5
Would there be a way to install the extension as part of the docker compose up
command?
Any idea about how to upgrade without doing DROP EXTENSION ulid;
cannot drop extension ulid because other objects depend on it
DETAIL: column id of table tokens depends on type ulid
column id of table metadata depends on type ulid
HINT: Use DROP ... CASCADE to drop the dependent objects too.
I tried using buildjet.com to do this, but the cargo pgx package
command is erroring out.
error: could not compile `pgx-pg-sys`
Caused by:
process didn't exit successfully: `rustc --crate-name pgx_pg_sys --edition=2021 /home/runner/.cargo/registry/src/github.com-1ecc6299db9ec823/pgx-pg-sys-0.7.3/src/lib.rs --error-format=json --json=diagnostic-rendered-ansi,artifacts,future-incompat --crate-type lib --emit=dep-info,metadata,link -C opt-level=3 -C linker-plugin-lto -C codegen-units=1 --cfg 'feature="cshim"' --cfg 'feature="default"' --cfg 'feature="pg14"' -C metadata=99fc4c4a02631fc7 -C extra-filename=-99fc4c4a02631fc7 --out-dir /home/runner/actions-runner/_work/pgx_ulid/pgx_ulid/target/release/deps -L dependency=/home/runner/actions-runner/_work/pgx_ulid/pgx_ulid/target/release/deps --extern libc=/home/runner/actions-runner/_work/pgx_ulid/pgx_ulid/target/release/deps/liblibc-fee5e7f38549019b.rmeta --extern memoffset=/home/runner/actions-runner/_work/pgx_ulid/pgx_ulid/target/release/deps/libmemoffset-2553d6b519627a07.rmeta --extern pgx_macros=/home/runner/actions-runner/_work/pgx_ulid/pgx_ulid/target/release/deps/libpgx_macros-89dad6d80b6a0e62.so --extern pgx_sql_entity_graph=/home/runner/actions-runner/_work/pgx_ulid/pgx_ulid/target/release/deps/libpgx_sql_entity_graph-9ba6ec15df70d28e.rmeta --extern serde=/home/runner/actions-runner/_work/pgx_ulid/pgx_ulid/target/release/deps/libserde-c1f57a673154f56e.rmeta --extern sptr=/home/runner/actions-runner/_work/pgx_ulid/pgx_ulid/target/release/deps/libsptr-5b5c933b229e05ce.rmeta --cap-lints allow -Clink-arg=-Wl,-undefined,dynamic_lookup -L /home/runner/actions-runner/_work/pgx_ulid/pgx_ulid/target/release/build/pgx-pg-sys-9c5c9a50b0329d68/out/cshim -l static=pgx-cshim-14` (signal: 9, SIGKILL: kill)
This is also probably the same issue with testing on buildjet
Hi, I am currently installing the extension from https://github.com/pksunkara/pgx_ulid/releases (thanks 👍 ) but you could please add a checksum file, in my case when automating the deployment (ansible) I have roles with something like:
ulid_version: "0.1.4"
ulid_checksum: "sha256:xxx..."
...
But the checksum apart helping to verify the integrity helps prevent typo/human error when for example wanting to install amd64
but by mistake the arm64
is selected.
Hi, how feasible would it be to support a two-byte prefix to create a "PULID" - a prefixed ULID? The first 2 bytes encode the type of the entity such as table "users" having an ID of UR01J2309A8G8NJ776A7XTPFRBG9
.
drop table if exists users ;
CREATE TABLE users (
id ulid NOT NULL DEFAULT gen_monotonic_ulid() PRIMARY KEY,
name text NOT NULL
);
insert into users("name")values('bill');
ERRORS:
org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [XX000]: ERROR: Can't give out exclusive, lock is in an empty state
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:614)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$2(SQLQueryJob.java:505)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:527)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:976)
at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:4133)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5148)
at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:114)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: org.postgresql.util.PSQLException: ERROR: Can't give out exclusive, lock is in an empty state
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:333)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:319)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:295)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:290)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
... 11 more
I'm interfacing with the JVM / JDBC, and when I call ResultSet.getBytes(col)
, I am getting an exception because the column is not 16 bytes.
I confirmed this is the case by executing the below command in postgres:
select gen_ulid(), pg_column_size(gen_ulid()), gen_random_uuid(), pg_column_size(gen_random_uuid())
pg_column_size(gen_ulid()) |
pg_column_size(gen_random_uuid()) |
---|---|
20 | 16 |
Any idea how to resolve this?
Hi, I noticed there were functions to cast to/from uuid. Would you accept a PR to do so from bytea?
I am reading the code, of course, I am new to Rust.
and I can't find the random generation calls.
How/What do you use for random generation?
in cryptography, faster is not better - need to ensure you use a crypto ready random generator.
Thank you
Hello!
I would like to request pg17 support for pgx_ulid.
When I build against PG17 using the latest released version (v0.1.5), I get the following error:
error: unexpected argument '--pg17' found
tip: a similar argument exists: '--pg16'
Usage: cargo pgrx init <--pg11 <PG11>|--pg12 <PG12>|--pg13 <PG13>|--pg14 <PG14>|--pg15 <PG15>|--pg16 <PG16>|--base-port <BASE_PORT>|--base-testing-port <BASE_TESTING_PORT>|--configure-flag <CONFIGURE_FLAG>|--valgrind>
For more information, try '--help'.
Taking a quick look, I can see that at a minimum, PG17 needs to be added to the top level Cargo.toml file:
I am unsure if more changes would be required.
Thanks!
I am using PostgreSQL 14 on (WSL2 : Ubuntu) and have installed your extension using deb files which @pksunkara have provided here.
Here are my table schema.
CREATE TABLE IF NOT EXISTS "project_schema"."project"(
"id" ulid NOT NULL DEFAULT gen_ulid() PRIMARY KEY,
"name" VARCHAR(255) NOT NULL
);
CREATE TABLE IF NOT EXISTS "demo_schema"."demo" (
"id" ulid NOT NULL DEFAULT gen_ulid() PRIMARY KEY,
"name" varchar(255) DEFAULT NULL
);
CREATE TABLE IF NOT EXISTS "demo_schema"."demo_project" (
"demo_id" ulid NOT NULL REFERENCES "demo_schema"."demo"(id),
"project_id" ulid NOT NULL REFERENCES "project_schema"."project"(id)
);
When I want to run a join function to get all the demo for a particular project, I am using the following query
SELECT * FROM demo_schema.demo JOIN demo_schema.demo_project ON demo.id= demo_project.demo_id WHERE demo_project.project_id = '01H9FS1KBXDKAH9GNEGNMNDYW4';
I encounter the following error :
ERROR: could not find commutator for operator 17239
SQL state: XX000
Can you please help me find a solution for the above.
running 13 tests
Building extension with features pg_test pg15
Running command "/opt/homebrew/Cellar/rust/1.74.0/bin/cargo" "build" "--features" "pg_test pg15" "--no-default-features" "--message-format=json-render-diagnostics"
Installing extension
Copying control file to /Users/orefalo/GitRepositories/pgrx/15.5/pgrx-install/share/postgresql/extension/ulid.control
Copying shared library to /Users/orefalo/GitRepositories/pgrx/15.5/pgrx-install/lib/postgresql/ulid.so
Finished installing ulid
The files belonging to this database system will be owned by user "orefalo".
This user must also own the server process.
The database cluster will be initialized with this locale configuration:
provider: libc
LC_COLLATE: C
LC_CTYPE: UTF-8
LC_MESSAGES: C
LC_MONETARY: C
LC_NUMERIC: C
LC_TIME: C
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "UTF-8"
The default text search configuration will be set to "simple".
Data page checksums are disabled.
creating directory /Users/orefalo/GitRepositories/pgx_ulid/target/pgrx-test-data-15 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Dubai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
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.
Success. You can now start the database server using:
/Users/orefalo/GitRepositories/pgrx/15.5/pgrx-install/bin/pg_ctl -D /Users/orefalo/GitRepositories/pgx_ulid/target/pgrx-test-data-15 -l logfile start
test tests::pg_test_timestamp_to_ulid ... ok
test tests::pg_test_ulid_to_string ... ok
test tests::pg_test_ulid_to_uuid ... ok
test tests::pg_test_null_to_ulid ... ok
test tests::pg_test_string_to_ulid_lowercase ... ok
test tests::pg_test_string_to_ulid ... ok
test tests::pg_test_generate ... ok
test tests::pg_test_commutator ... ok
test tests::pg_test_hash ... ok
test tests::pg_test_uuid_to_ulid ... ok
test tests::pg_test_string_to_ulid_invalid_length - should panic ... ok
test tests::pg_test_string_to_ulid_invalid_char - should panic ... ok
test tests::pg_test_ulid_to_timestamp ... FAILED
failures:
---- tests::pg_test_ulid_to_timestamp stdout ----
thread 'tests::pg_test_ulid_to_timestamp' panicked at /Users/orefalo/.cargo/registry/src/index.crates.io-6f17d22bba15001f/pgrx-tests-0.11.1/src/framework.rs:172:9:
Postgres Messages:
[2023-11-28 11:19:17.139 +04] [66984] [65659475.105a8]: LOG: starting PostgreSQL 15.5 on aarch64-apple-darwin23.1.0, compiled by Apple clang version 15.0.0 (clang-1500.0.40.1), 64-bit
[2023-11-28 11:19:17.140 +04] [66984] [65659475.105a8]: LOG: listening on IPv6 address "::1", port 32215
[2023-11-28 11:19:17.140 +04] [66984] [65659475.105a8]: LOG: listening on IPv4 address "127.0.0.1", port 32215
[2023-11-28 11:19:17.140 +04] [66984] [65659475.105a8]: LOG: listening on Unix socket "/Users/orefalo/GitRepositories/pgrx/.s.PGSQL.32215"
[2023-11-28 11:19:17.144 +04] [66984] [65659475.105a8]: LOG: database system is ready to accept connections
Test Function Messages:
[2023-11-28 11:19:18.652 +04] [67071] [65659476.105ff]: LOG: statement: BEGIN
[2023-11-28 11:19:18.652 +04] [67071] [65659476.105ff]: LOG: statement: SELECT "tests"."test_ulid_to_timestamp"();
[2023-11-28 11:19:18.653 +04] [67071] [65659476.105ff]: ERROR: assertion `left == right` failed
[2023-11-28 11:19:18.653 +04] [67071] [65659476.105ff]: STATEMENT: SELECT "tests"."test_ulid_to_timestamp"();
[2023-11-28 11:19:18.653 +04] [67071] [65659476.105ff]: LOG: statement: ROLLBACK
Client Error:
assertion `left == right` failed
left: Some("2023-03-10 12:00:49.111")
right: Some("2023-03-10 16:00:49.111")
postgres location: lib.rs
rust location: <unknown>
I'm experiencing some difficulties related to queries, potentially tied to missing operations on the ULID type.
Here are the error messages I've been encountering:
SELECT public.geometries.id, public.geometries.properties, ST_AsEWKB(public.geometries.geometry) AS geometry,
geometry_groups_1.id AS id_1, geometry_groups_1.path, geometry_groups_1.name,
geometry_groups_1.description, geometry_groups_1.geometry_type,
geometry_groups_1.mapbox_style, geometry_groups_1.layer_index
FROM public.geometries
LEFT OUTER JOIN (public.geometry_group_geometry_association AS geometry_group_geometry_association_1
JOIN public.geometry_groups AS geometry_groups_1
ON geometry_groups_1.id = geometry_group_geometry_association_1.geometry_group_id)
ON public.geometries.id = geometry_group_geometry_association_1.geometry_id
WHERE public.geometries.geometry && ST_GeomFromEWKT($1)
SELECT geometries_1.id AS geometries_1_id, public.geometry_groups.id AS public_geometry_groups_id,
public.geometry_groups.path AS public_geometry_groups_path, public.geometry_groups.name AS public_geometry_groups_name,
public.geometry_groups.description AS public_geometry_groups_description,
public.geometry_groups.geometry_type AS public_geometry_groups_geometry_type,
public.geometry_groups.mapbox_style AS public_geometry_groups_mapbox_style,
public.geometry_groups.layer_index AS public_geometry_groups_layer_index
FROM public.geometries AS geometries_1
JOIN public.geometry_group_geometry_association AS geometry_group_geometry_association_1
ON geometries_1.id = geometry_group_geometry_association_1.geometry_id
JOIN public.geometry_groups ON public.geometry_groups.id = geometry_group_geometry_association_1.geometry_group_id
WHERE geometries_1.id IN ($1, $2, $3, $4, $5, $6, $7)
If anyone has suggestions or insights into these errors, I'd greatly appreciate your help.
Nix is gaining more and more in popularity, with tools like devenv and process-compose making it a viable alternative to docker-compose to run postgres locally in your dev setup. We use pgx_ulid and this is the only thing preventing us from switching right now. I gave a short try at a nixpkgs PR already and things seem to be mostly working, but I ran in a bindgen error that I don't have time to debug today. There are other extensions using pgx already in there, so the groundwork is already done.
Please consider this issue a place to discuss and coordinate, and not a demand for you to spend time on it. I'll try to find time and come back to it myself.
Big thanks for working on pgx_ulid!
please proivde the RPM package to support rhel,centos ,rocky linux and so on,for example el7,el8,el9.
I tried to put the files on /opt/homebrew/share/postgresql@14/extension/ulid.control
but no success.
I'd like to use / to test this pgx_ulid
extension on PostgreSQL 15 on Windows.
Could you please share the binary extension? Or at least instructions how to build it (I'm not an expert in Rust, sorry).
Any help is appreciated. Thanks!
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.