Add suport for TEXT[] types when creating bm25 index

[academiaresf]

Problem Statement

The array_to_string(ARRAY, text, [ text) is not immutable and can't be used as source for indexes.

It's very common to use TEXT[] and not TEXT for large text content because each separation \n represents an element of the array.

Proposed Solution

CREATE INDEX docs_bm25_idx ON docs
USING bm25(array_to_string(content, ' ')) -- working!
WITH (text_config='spanish');

-- OR

CREATE INDEX docs_bm25_idx ON docs
USING bm25(content) -- row as text[]
WITH (text_config='spanish');

Alternatives Considered

Paradedb actually supports the use case.

Additional Context

pg_textsearch v0.5.0
postgresql v18.1

[tjgreen42]

Hopefully we get this for free with #52 which @SteveLauC is working on.

[SteveLauC]

As you @academiaresf mentioned, array_to_string() is immutable so Postgres won't allow you to build index for it, this restriction applies to both pg_textsearch and pg_search (Paradedb). So this will not be fixed by adding expression index support.

pg_textsearch (my branch):

steve=# CREATE TABLE test_array (content text[]);
CREATE TABLE
steve=# INSERT INTO test_array VALUES (ARRAY['postgres', 'search']);
INSERT 0 1
steve=# SELECT * FROM test_array;
      content
-------------------
 {postgres,search}
(1 row)

steve=# CREATE INDEX ON test_array USING bm25 (array_to_string(content, ' ')) WITH (text_config='english');
ERROR:  functions in index expression must be marked IMMUTABLE

pg_search:

steve=# create extension pg_search;
CREATE EXTENSION

steve=# select * from pg_am where amname = 'bm25';
  oid   | amname |       amhandler       | amtype
--------+--------+-----------------------+--------
 467983 | bm25   | paradedb.bm25_handler | i
(1 row)

steve=# CREATE INDEX ON test_array USING bm25 (array_to_string(content, ' '));
ERROR:  functions in index expression must be marked IMMUTABLE

But pg_search does allow you to build index on columns of type text[]:

steve=# CREATE INDEX ON test_array USING bm25 (content) WITH (key_field='ctid');
CREATE INDEX

---

Possible workaround

Once we implemented #52, you can create an immutable wrapper function, then build index using it:

steve=# CREATE OR REPLACE FUNCTION bm25_array_to_string(text[], text)
RETURNS text
AS $$
    SELECT array_to_string($1, $2);
$$ LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT;
CREATE FUNCTION

steve=# CREATE INDEX ON test_array USING bm25 (bm25_array_to_string(content, ' ')) WITH (text_config='english');
NOTICE:  BM25 index build started for relation test_array_bm25_array_to_string_idx
NOTICE:  Using text search configuration: english
NOTICE:  Using index options: k1=1.20, b=0.75
NOTICE:  BM25 index build completed: 1 documents, avg_length=2.00, text_config='english' (k1=1.20, b=0.75)
CREATE INDEX

[academiaresf]

@SteveLauC you're right.

[tjgreen42]

Got it, I didn't appreciate the detail about mutability. However I don't think it's going to end well to tell Postgres that a mutable function is actually immutable. I think the plan should instead be to support TEXT[] natively, which seems easy enough. (Just need to map to_tsvector to each line, accumulate the results into a single bag of words, and then it's business as usual.)

[SteveLauC]

However I don't think it's going to end well to tell Postgres that a mutable function is actually immutable. I

I agree

I think the plan should instead be to support TEXT[] natively, which seems easy enough.

Yeah, let's do that