Thread: BUG #17483: postgres_fdw used with text to_tsvector and custom search configuration

The following bug has been logged on the website:

Bug reference:      17483
Logged by:          Emmanuel Quincerot
Email address:      equincerot@yahoo.fr
PostgreSQL version: 12.10
Operating system:   Linux
Description:

Hello everyone,

I have an issue with the evaluation of to_tsvector and custom search
configuration through foreign data wrapper (from PG to PG).
The issues occurs on PG 12.10 but also with the latest docker image of PG
14.2.
Here is a snippet that reproduces the issue, with two workarounds:

create database local;
create database remote;

\c remote
    CREATE TABLE public.foo (i int, comment text);
    CREATE EXTENSION IF NOT EXISTS unaccent WITH SCHEMA public;
    CREATE TEXT SEARCH CONFIGURATION public.custom_search (PARSER =
pg_catalog.default);
    ALTER TEXT SEARCH CONFIGURATION custom_search ADD MAPPING FOR word WITH
public.unaccent, simple;
    ALTER TEXT SEARCH CONFIGURATION custom_search ADD MAPPING FOR asciiword
with simple;
    INSERT INTO foo SELECT generate_series(1,1000000), 'some comment';
\c local
CREATE EXTENSION IF NOT EXISTS unaccent WITH SCHEMA public;
CREATE EXTENSION postgres_fdw;
CREATE SERVER IF NOT EXISTS fdw_remote
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'localhost', port '5432', dbname 'remote');
CREATE USER MAPPING IF NOT EXISTS FOR CURRENT_USER SERVER fdw_remote OPTIONS
( user 'postgres');
IMPORT FOREIGN SCHEMA public FROM SERVER fdw_remote INTO public OPTIONS
(import_default 'true');

SELECT i, to_tsvector('custom_search'::regconfig, comment)
FROM foo
where length(to_tsvector('custom_search'::regconfig, comment)) > 0;
-- ERROR:  text search configuration "custom_search" does not exist
-- LIGNE 1 : SELECT i, to_tsvector('custom_search'::regconfig, comment)
--                                 ^

CREATE TEXT SEARCH CONFIGURATION public.custom_search (PARSER =
pg_catalog.default);
ALTER TEXT SEARCH CONFIGURATION custom_search  ADD MAPPING FOR word WITH
public.unaccent, simple;
ALTER TEXT SEARCH CONFIGURATION custom_search ADD MAPPING FOR asciiword with
simple;

-- Now this works:
SELECT i, to_tsvector('custom_search'::regconfig, comment)
FROM foo;

-- But still:
SELECT i, to_tsvector('custom_search'::regconfig, comment)
FROM foo
where length(to_tsvector('custom_search'::regconfig, comment)) > 0;
-- ERROR:  text search configuration "custom_search" does not exist
-- CONTEXTE : remote SQL command: SELECT i, comment FROM public.foo WHERE
((to_tsvector('custom_search'::regconfig, comment) IS NOT NULL))


-- A first workaround
CREATE FOREIGN TABLE IF NOT EXISTS public.fdw_pg_ts_config
    (
        oid oid,
        cfgname name
        )
    SERVER fdw_remote
    OPTIONS (schema_name 'pg_catalog', table_name 'pg_ts_config');

-- This would work but is a bit complex
SELECT i, to_tsvector('custom_search'::regconfig, comment)
FROM foo
where length(to_tsvector((select oid from fdw_pg_ts_config where cfgname =
'custom_search' LIMIT 1), comment)) > 0;

-- An other workaround (thanks to Daniel Maumary)
\c remote
ALTER TABLE foo ADD COLUMN textsearchable_index_col tsvector  GENERATED
ALWAYS AS (to_tsvector('custom_search', comment) ) STORED;

\c local
drop foreign table foo;
IMPORT FOREIGN SCHEMA public FROM SERVER fdw_remote INTO public OPTIONS
(import_default 'true');
SELECT i, textsearchable_index_col
FROM foo
where length(textsearchable_index_col) > 0;

I would expect to be able to call tsvector on custom text configuration
without using the workaround described above, so that the application can
dynamically use the search config without altering the model, and without
having two distinct ways of writing to_tsvector (one for the select, one for
the where).

PG Bug reporting form <noreply@postgresql.org> writes:
> I have an issue with the evaluation of to_tsvector and custom search
> configuration through foreign data wrapper (from PG to PG).

There is no chance whatsoever of a to_tsvector call working in a
foreign query unless the referenced text search config exists
(with identical definitions) on both ends.  It has to exist locally
or the parser will not be able to make sense of the command.  It
has to exist remotely if you'd like the operation to be pushed down
to the remote.  (Given the large potential for user error there,
I'm a bit worried that we should never have marked this function
safe-to-ship in the first place.)

The other thing you're running into is that if the text search
config isn't defined in the pg_catalog schema, it's not visible
in the restricted search path that the remote session will use.
The query shipped to the far end should handle that by writing
"'public.custom_search'::regconfig", but it evidently doesn't ---
that feels like a bug, indeed.

Or perhaps we ought to reduce the error hazards a bit by not
shipping query conditions that involve non-built-in configurations?
But likely that would draw performance complaints.

            regards, tom lane

I wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
>> I have an issue with the evaluation of to_tsvector and custom search
>> configuration through foreign data wrapper (from PG to PG).

> The other thing you're running into is that if the text search
> config isn't defined in the pg_catalog schema, it's not visible
> in the restricted search path that the remote session will use.
> The query shipped to the far end should handle that by writing
> "'public.custom_search'::regconfig", but it evidently doesn't ---
> that feels like a bug, indeed.

FYI, I've now pushed a fix for that problem; it'll be in the
August minor releases.

Also, beginning in v16, it'll be necessary to ensure that any
custom TS configuration you want to use this way belongs to
an extension that the foreign server considers "shippable"
(see postgres_fdw documentation).  We won't enforce that in
existing branches, though.

            regards, tom lane