New operators and class for jsonb with gin indexing

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

New operators and class for jsonb with gin indexing

Luka Zivkovic
Hello all!

I am having problems applying gin indexing to my new operator class for type jsonb.
I created an operator that can query(jsonb column) keys and dates where dates are more than, less than, equal and not equal to the one provided inside another jsonb.
Because I want to make it use index search and not sequential search, I wanted to use gin indexing, but it says that i'm missing support functions. Did anybody try to do something like this? I searched everywhere online, and I didn't see an example of how they should look like. This is how i created an operator class

CREATE OPERATOR CLASS custom_jsonb_dates_operators FOR TYPE jsonb
USING gin  AS
OPERATOR 1 #>? (jsonb, jsonb),
OPERATOR 2 #<? (jsonb, jsonb),
OPERATOR 3 #=? (jsonb, jsonb),
OPERATOR 4 #!=? (jsonb, jsonb)
After this, when i created an index with this operator class, i got the following error:
missing support function 2 for attribute 1 of index "idx_name"
I tried writing my own support functions, but none worked(after creating an index, it just never returned any results.

Thanks,
Luka Zivkovic
Reply | Threaded
Open this post in threaded view
|

Re: New operators and class for jsonb with gin indexing

Tom Lane-2
Luka Zivkovic <[hidden email]> writes:
> I am having problems applying gin indexing to my new operator class for
> type jsonb.
> I created an operator that can query(jsonb column) keys and dates where
> dates are more than, less than, equal and not equal to the one provided
> inside another jsonb.
> Because I want to make it use index search and not sequential search, I
> wanted to use gin indexing, but it says that i'm missing support functions.
> Did anybody try to do something like this?

There's not a whole lot of documentation about this, but there is some:

https://www.postgresql.org/docs/13/gin-extensibility.html

After reading that, I'd suggest looking into the code for gin opclass
support in various contrib modules (hstore might be a good start).

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: New operators and class for jsonb with gin indexing

Luka Zivkovic
Thank you for your response!

Yeah, i was looking i to that, and it looks like i just didn't implement it correctly. I'm just wondering because I'm using it for jsonb type, can i just use already implemented support functions for jsonb? I'm just not too sure how those functions work

Thanks, 
Luka Živković

On Tue, Feb 23, 2021, 16:28 Tom Lane <[hidden email]> wrote:
Luka Zivkovic <[hidden email]> writes:
> I am having problems applying gin indexing to my new operator class for
> type jsonb.
> I created an operator that can query(jsonb column) keys and dates where
> dates are more than, less than, equal and not equal to the one provided
> inside another jsonb.
> Because I want to make it use index search and not sequential search, I
> wanted to use gin indexing, but it says that i'm missing support functions.
> Did anybody try to do something like this?

There's not a whole lot of documentation about this, but there is some:

https://www.postgresql.org/docs/13/gin-extensibility.html

After reading that, I'd suggest looking into the code for gin opclass
support in various contrib modules (hstore might be a good start).

                        regards, tom lane
Reply | Threaded
Open this post in threaded view
|

Re: New operators and class for jsonb with gin indexing

Tom Lane-2
Luka Zivkovic <[hidden email]> writes:
> Yeah, i was looking i to that, and it looks like i just didn't implement
> it correctly. I'm just wondering because I'm using it for jsonb type, can i
> just use already implemented support functions for jsonb? I'm just not too
> sure how those functions work

The support functions need to know about the operators' semantics, so
you have to write your own when inventing new operators.

                        regards, tom lane