Warning when joining pg_search index with pg_analytics Foreign table

[pdpark]

What happens?

Joining a BM25 Tantivy full text index and a foreign table created with pg_analytics produces warning with: Binder Error: Catalog "my_schema" does not exist!

To Reproduce

0. Create a schema called my_schema and create all following tables and indexes in that schema.
1. Create a foreign table called pricing
2. Create a regular postgresql table called product
3. Create a BM25 index on the product table called product_idx

Run a query like this:

select *
from my_schema.product_idx.search(
    'description:"red"'
    ,limit_rows => 10
) as x
join my_schema.pricing as p
on p.id = x.id

Result:

WARNING:  This query was not fully pushed down to DuckDB because DuckDB returned an error. Query times may be impacted. If you would like to see this query pushed down, please submit a request to https://github.com/paradedb/paradedb/issues with the following context:
Binder Error: Catalog "my_schema" does not exist!

OS:

Amazon Linux 2023 / PostgreSQL 16.4 (Debian 16.4-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

ParadeDB Version:

pg_search: 0.9.1 pg_analytics: 0.1.0

Are you using ParadeDB Docker, Helm, or the extension(s) standalone?

ParadeDB Docker Image

Full Name:

Patrick Park

Affiliation:

Payzer

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include the code required to reproduce the issue?

• Yes, I have

Did you include all relevant configurations (e.g., CPU architecture, PostgreSQL version, Linux distribution) to reproduce the issue?

• Yes, I have

[philippemnoel]

Thanks for reporting, we'll take a look ASAP

[rebasedming]

I took a look, this happens because my_schema.product_idx.search is a UDF, so the only Relation present in this query is the foreign table, so we try to push the query down to DuckDB.

If we were to refactor our search API to have users use the search operator directly instead of the UDF then the relation that product_idx is created over would appear in the query plan and we would know to not push it down to DuckDB. But that's a long term project so this will likely take some time.

[philippemnoel]

Fixed