Support for PGroonga operators

[steve-chavez]

PGroonga offers performance and usability advantages over pg native textsearch and pg_trgm.

Tutorial: https://pgroonga.github.io/tutorial/

Reference : https://pgroonga.github.io/reference/

Proposal

Since pgroonga operators work on regular data types like text and jsonb, we can conditionally enable them on a column if it's indexed. This is to prevent expensive searches on unindexed columns.

CREATE INDEX index_name ON table USING pgroonga (column);

Then to do SELECT * FROM table WHERE column &@~ 'PostgreSQL';:

GET /table?column=groonga_query.PostgreSQL

(&@~ is groonga_query for now).

Then we can document this in an integrations(or extensions) page like we did for timescale.

[wolfgangwalther]

like we did for timescale.

Why is this only in the old v5.2 docs? Any specific reason for removing it from v6 onwards?

[wolfgangwalther]

&@~ is groonga_query for now

I was wondering, whether we could somehow add metadata to custom operators in the database, to make PostgREST be able to identify and map them automatically. Kind of similar to what we're doing with CREATE FUNCTION ... SET ... in #1582. Of course, SET does not work on operators. I'm pretty sure this was discussed before and turned down for good reason, but what about using COMMENT to add metadata to database objects?

We could use a yaml front matter block to add metadata, while keeping the current openapi comments support backwards compatible. This would also prevent issues with inlining, when using SET on functions as mentioned in #1582 (comment).

COMMENT ON OPERATOR "&@~" $$
---
pgrst:
  filter:
    name: groonga_query
    requires_index: pgroonga
---
This operator will provide fulltext magic!
$$;


-- similar to the original idea in #1582
COMMENT ON FUNCTION ret_image(name text) $$
---
pgrst:
  accept:
    - image/png
---
This function will respond to Accept: image/png requests!
$$;


-- related to improved routing
COMMENT ON TABLE really_long_and_inaccessible_name $$
---
pgrst:
  route: short_name
---
This table can now be queried at /short_name instead.
$$;


-- moving more of the config closer to database objects
COMMENT ON SCHEMA my_extensions $$
---
pgrst:
  search_path: true
---
This replaces db-extra-search-path in the config.
$$;

[steve-chavez]

I'm pretty sure this was discussed before and turned down for good reason, but what about using COMMENT to add metadata to database objects?

It was not thoroughly discussed IIRC. I thought it would not be a good idea because it "pollutes" the comments with postgrest-specific metadata(output of slash commands like \d+ would be weird).

Creating a DSL on top of comments, like shown on #1179 (comment), was also described as "icky" on a hacker news comment. I somewhat agree, as it feels "invasive" and prone to errors(also no syntax highlight).

Another problem is that if other tools also (ab)use the comments then we'd "clash" with their DSL.

We could use a yaml front matter block to add metadata

yaml certainly looks better. I guess we'd have to reach consensus with other tools though.

I still think the correct way to do this would be the way mentioned in #1710 (comment), whether is our in-db config or postgrest-contrib functions, that way our metadata would be namespaced.

---

Since pgroonga operators work on regular data types like text and jsonb, we can conditionally enable them on a column if it's indexed

Do you see a problem with this interface? I like the idea of being strict and requiring indexes for enabling certain filters.

I also think we'll need a more generic interface eventually, but for now we don't have too many operators for this to be a problem.

---

Why is this only in the old v5.2 docs? Any specific reason for removing it from v6 onwards?

It was an agreement between me and Timescale(former backer) sometime ago, where we'd show their logo + integration doc for the duration of their sponsorship on Patreon, which only lasted for a portion of v5.2 lifetime.

I think we can reincorporate the timescale doc though, it's good content and an extension section(with pgroonga and postgis later on) would look good.

[steve-chavez]

&@~ is groonga_query for now

One thought, now that we have many client libraries, we can expose the original operator and leave the urlencoding to them, so we don't need to make an alias. This was discussed before on #938 (comment):

However I don't think we should have aliases for all operators, having to remember e.g. that to use -|- I have to tell posgret adj is pointless trivia that will only trip me up; and more importantly: make it something else you have to know to use a postgrest API. If I'm reading misc http client, I'm not goint to be able to infer what adj means. The less you have to know the better.

Or, we could also offer both, the original operator and the url-safe alias.

[wolfgangwalther]

One thought, now that we have many client libraries, we can expose the original operator and leave the urlencoding to them, so we don't need to make an alias. This was discussed before on #938 (comment):

Hm. I don't buy the argument "it's hard to remember aliases" very much. I doubt people really know all those PG operators out of their head, because the regularly use them. For me, it's more a "I know there is something that does this... let's look up the operator in the docs.". Also the client-side code is probably read by a lot more people that don't know PostgreSQL well, and the aliased operators are much more meaningful to them.

If you really want to support the original operator (where I don't really see much value in), we absolutely have to keep supporting the url-safe aliases.

[wolfgangwalther]

It was not thoroughly discussed IIRC. I thought it would not be a good idea because it "pollutes" the comments with postgrest-specific metadata(output of slash commands like \d+ would be weird).

I guess that depends on what you're expecting to see in that case. Assuming we are reading comments of objects in the public (or other postgrest specific) schema only, it could also be helpful. The objects in the api schema should only be used for postgrest anyway, so having the postgrest specific information readily in one place would be much better than having them stored away in some big json config setting.

Of course we can try to see what kind of format we can put the metadata comments in to have them show in a nice way in \d+ etc.

Creating a DSL on top of comments, like shown on #1179 (comment) [...] Another problem is that if other tools also (ab)use the comments then we'd "clash" with their DSL.

I would assume all other tools to be somewhat resistant against stuff that doesn't match their DSL - and the same for us. As long as we are prefixing this on our side, we should be fine.

Do you see a problem with this interface? I like the idea of being strict and requiring indexes for enabling certain filters.

I think it's a great idea to be strict about requiring indexes. I like it so much, that I added requires_index: ... to my example above, too ;)

I think we can reincorporate the timescale doc though, it's good content and an extension section(with pgroonga and postgis later on) would look good.

Fully agree.

[wolfgangwalther]

I also think we'll need a more generic interface eventually, but for now we don't have too many operators for this to be a problem.

Looks like an extensible interface could already be useful, as new operators have now been asked for repeatedly:

• This issue: PGroonga
• Support for geometry operators #2035: Geometry operators
• Feature request: Expose ~ operator for Ltree #2064: Ltree operator

[steve-chavez]

@wolfgangwalther (Ab)using function names, how about this for exposing new operators

CREATE FUNCTION "pgrst.projects.name.groonga_query"(test.projects, test.projects.name%TYPE) RETURNS bool
AS $_$ 
  SELECT $1.name  &@~ $2 
$_$ LANGUAGE SQL IMMUTABLE;

This would allow calling

GET /projects?name=groonga_query.stuff

I've checked and the resulting queries are inlinable too

select * from test.projects x where "pgrst.projects.name.groonga_query"(x, 2);

Related to #2442 (comment)

---

Might be possible to make this more generic(for all columns) with some conventions on the function naming

[steve-chavez]

I was mixing the concern about restricting the operator columns on #2442, there's a more plausible idea there now.

Adding new operators could be simply:

CREATE FUNCTION "pgrst.groonga_query"(text, text) RETURNS bool
AS $_$ 
  SELECT $1 &@~ $2 
$_$ LANGUAGE SQL IMMUTABLE;

Meaning any function name with our prefix pgrst would be exposed as a filter operator.