consider adding hash operator and commutator

[wasdee]

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.

[pksunkara]

Hello,

We appreciate your enthusiasm to contribute to this open-source project and to help make it better. Unfortunately, there's not enough information in your bug report for me to understand what this issue is about. Can you please add clear-cut steps on how to reproduce this error?

To make effective usage of the maintainers' time, we will be closing this issue until further information as requested is provided.

Thanks

[jmcdo29]

I received a similar error when trying to migrate from a simpler function based ulid generator to this extension (very excited to use it by the way). Personally, I was getting an error of could not find commutator for operator 16605, which seemed to be happening on the = check of a join. Interestingly, it doesn't happen on a simple WHERE id = '', only on the join check. Hopefully the following script will be enough to help.

Very simple tables, creating a join between them causes an error of could not find hash function for hash operator 16605

Open a SQL script to a postgres database with the ulid extension installed (will add steps if this is necessary, but I feel you already have a postgres instance with this available)

Run the following SQL

CREATE TABLE foo (
    id ulid DEFAULT gen_ulid()
    ,data TEXT
);

CREATE TABLE foobar (
    id ulid DEFAULT gen_ulid()
    ,foo_id ulid
);

INSERT INTO foo
	(data)
VALUES 
	('hello')
	,('world');

INSERT INTO foobar
	(foo_id) 
VALUES
	((SELECT id FROM foo WHERE data = 'hello'))
	,((SELECT id FROM foo WHERE data = 'world'));

SELECT 
	foobar.id
	, foo.data 
FROM foobar
JOIN foo ON foobar.foo_id = foo.id;

Notice the error could not find hash function for hash operator 16605 or could not find commutator for operator 16605

[wasdee]

for my case it is many-to-many relationship table

Reproducing

CREATE EXTENSION ulid;

CREATE TABLE foo (
    id ulid DEFAULT gen_ulid() PRIMARY KEY
    ,data TEXT
);

CREATE TABLE bar (
    id ulid DEFAULT gen_ulid() PRIMARY KEY
    ,data TEXT
);

CREATE TABLE foo_bar_mapping (
    foo_id ulid,
    bar_id ulid,
    PRIMARY KEY (foo_id, bar_id),
    FOREIGN KEY (foo_id) REFERENCES foo(id),
    FOREIGN KEY (bar_id) REFERENCES bar(id)
);

INSERT INTO foo
    (data)
VALUES
    ('hello')
    ,('world');

INSERT INTO bar
    (data)
VALUES
    ('alpha')
    ,('beta');

INSERT INTO foo_bar_mapping
    (foo_id, bar_id)
VALUES
    ((SELECT id FROM foo WHERE data = 'hello'), (SELECT id FROM bar WHERE data = 'alpha')),
    ((SELECT id FROM foo WHERE data = 'world'), (SELECT id FROM bar WHERE data = 'beta'));

SELECT
    f.id as foo_id
    , b.id as bar_id
    , f.data as foo_data
    , b.data as bar_data
FROM foo_bar_mapping fbm
JOIN foo f ON fbm.foo_id = f.id
JOIN bar b ON fbm.bar_id = b.id;

It will show error

ERROR: could not find hash function for hash operator 41865

my postgres verision is 14

[jmcdo29]

I'm not super familiar with rust, but I added the above snippets to the man branches tests locally, and it looks like thanks to this commit (relevant merged PR), joins are working as expected, we'd just either need a new release, or to build the extension from the source ourselves

[pksunkara]

Yup, I am waiting on adding tests to the code before releasing it.

I also need to look into commutator. Os is it only an issue with postgis?

[jmcdo29]

I'll see if I can't create some sort of minimum reproduction for that too. I think it has to do with casting existing ulids that are from a text format to the ulid type form this package and then trying to create a foreign key off of that.

[wasdee]

Yup, I am waiting on adding tests to the code before releasing it.

I also need to look into commutator. Os is it only an issue with postgis?

Repoduce by

continue from my last example.

SELECT
    *
FROM foo_bar_mapping
Join foo on  foo_bar_mapping.foo_id = foo.id
WHERE foo_bar_mapping.bar_id IN ('01H60WY47SQG5GCJDRERST3PNE', '01H60WY47RMTSKNBVGAM976678');

Error

[XX000] ERROR: could not find commutator for operator 44764

[jmcdo29]

Looks like the same PR fixed that too. I can make a PR that adds my local tests if you'd like. I'm sure the formatting is off, but at the very least it could be a head start for you :)

[pksunkara]

Thanks, I will be able to clean it up and release it early next week since I am going to be a bit busy until then.

[osdiab]

I am also experiencing issues where joins don't work with mysterious errors I don't fully understand,

ERROR:  could not find hash function for hash operator 16536

and

ERROR:  could not find commutator for operator 16536

Is this going to be released soon? as it is blocking us from proceeding, might need to rip out ulid instead. Thanks!

[pksunkara]

Yup, as mentioned in #16 (comment), just waiting for a release in the underlying dependency.

[osdiab]

@pksunkara looks like the underlying dependency has been released! Looking forward to this being bumped too. https://github.com/pgcentralfoundation/pgrx/releases/tag/v0.10.0-beta.4

[workingjubilee]

All problems should be resolved as-of #18 (which currently doesn't include regression tests, isn't officially accepted yet, doesn't have prebuilt releases, et cetera). The patched Dockerfile in my branch at https://github.com/workingjubilee/pgx_ulid/tree/use-pgrx-0.10.0-beta.4 should be good enough to build this extension if one cannot wait for the extension developer's official release. Or, if you have an available recent Rust compiler, using cargo install cargo-pgrx --version 0.10.0-beta.4 --locked and then cargo pgrx package or whatever else you need. As a reminder, the software is provided AS-IS, etc., though do let us know if you run into more issues.

Regarding the errors:

ERROR: could not find hash function for hash operator 16536

ERROR: could not find commutator for operator 16536

These errors are essentially saying "this set of operators, which Postgres has assigned the object ID #16536, cannot support this query". I say "set" because it may be referencing an operator, an operator class(!), or an operator family(!!), and I can't automatically tell which is which because those errors have like 4 different locations in the Postgres source.

[jmcdo29]

Can confirm my migrations work as intended migrating existing data from a text ulid to this extension! Thank you all for the work on this!

[osdiab]

I tried updating the version of pgx_ulid to the latest release and it still seems to be showing the same issues 😢

• Video

My Postgres Dockerfile is like so:

ARG PG_MAJOR

FROM postgres:${PG_MAJOR}

ARG PG_MAJOR
ARG PGX_ULID_VERSION

WORKDIR /app

RUN apt-get update && apt-get install -y wget

# Download the pre-built binary from GitHub releases
RUN if [ "$(dpkg --print-architecture)" = "amd64" ]; then \
  wget https://github.com/pksunkara/pgx_ulid/releases/download/v${PGX_ULID_VERSION}/pgx_ulid-v${PGX_ULID_VERSION}-pg${PG_MAJOR}-amd64-linux-gnu.deb; \
  else \
  wget https://github.com/pksunkara/pgx_ulid/releases/download/v${PGX_ULID_VERSION}/pgx_ulid-v${PGX_ULID_VERSION}-pg${PG_MAJOR}-arm64-linux-gnu.deb; \
  fi

# Install the binary using dpkg
RUN dpkg -i pgx_ulid-v${PGX_ULID_VERSION}-pg${PG_MAJOR}-*-linux-gnu.deb

# Cleanup the .deb file
RUN rm pgx_ulid-v${PGX_ULID_VERSION}-pg${PG_MAJOR}-*-linux-gnu.deb

# Reset the Docker image's command back to the original entrypoint script
CMD ["postgres"]

with PG_MAJOR: 15 and PGX_ULID_VERISON: 0.1.2 in the docker-compose.yml file.

Any help would be appreciated!

[workingjubilee]

@osdiab Are you doing this in a fresh database which has never had a prior version of pgx_ulid installed, or are you doing this in your existing database?

My instincts say there should be a different OID this time for the operator. There is a possibility that the old operator definition is still being used, despite the new extension being installed, and that the extension version number is a red herring. What DDL commands did you run after replacing the binary?

[osdiab]

It’s the existing database (locally); I just replaced the version of pgx_ulid installed in the Docker image but kept the same volume. I didn’t run any SQL commands.

[workingjubilee]

...so this is a bit of a shot in the dark but try running

ALTER EXTENSION ulid UPDATE TO '0.1.2';

[osdiab]

Message ID: ***@***.***>I ended up just not using ulid anymore hence the delay but I tried your suggestion on the volume I kept - got this error: ERROR: extension "ulid" has no update path from version "0.1.1" to version "0.1.2" Hope this helps!

[pksunkara]

Hey, would you be able to drop the extension completely and recreate it?

[workingjubilee]

@osdiab I am not surprised it failed, but unfortunately, that confirms what I thought.

Your Postgres was never actually on 0.1.2.

It is not enough to reinstall the binary. You must also run the SQL command to actually update the database, because extensions are not just binaries, they are also sequences of SQL commands that inform Postgres about how to use the binary code. Postico was providing you its idea of what it saw, not showing you what Postgres saw.