BUG #17483: postgres_fdw used with text to_tsvector and custom search configuration - Mailing list pgsql-bugs

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).