pksunkara / pgx_ulid Goto Github PK

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?

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!

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 experiencing some difficulties related to queries, potentially tied to missing operations on the ULID type.

Here are the error messages I've been encountering:

1. The error message, "Could not find commutator for operator 20638" seems to arise from my attempt to join 'geometries' and 'geometry_groups' tables. Here's the SQL statement that's causing the issue:

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)

More info on this error here

2. I also received an error message stating, "Could not find hash function for hash operator 20638". This one occurred when I was trying to select some fields after joining 'geometries' and 'geometry_groups' tables. Here's the SQL that's giving me this trouble:

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)

More info on this error here

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!

Hi there,

I noticed that the MIT/X11 license is mentioned in the README, but there are no corresponding files in the repo. We're interested in compiling this extension and publishing to a registry (pgt.dev), which requires the license to be bundled with the source material. May I ask whether making this file is an option?

Thank you.

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())

Any idea how to resolve this?

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.

please proivde the RPM package to support rhel,centos ,rocky linux and so on,for example el7,el8,el9.

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

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.

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?

Hi, I noticed there were functions to cast to/from uuid. Would you accept a PR to do so from bytea?

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.

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

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:

We are having that same error I reported when joining tables by ULID ids.

Thank you!

from https://dba.stackexchange.com/questions/329047/postgres-ulid-xx000-error-cant-give-out-exclusive-lock-is-in-an-empty-sta/329048#329048

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.

Setup Details

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?

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