BUG #17483: postgres_fdw used with text to_tsvector and custom search configuration
От | PG Bug reporting form |
---|---|
Тема | BUG #17483: postgres_fdw used with text to_tsvector and custom search configuration |
Дата | |
Msg-id | 17483-795757fa99607659@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #17483: postgres_fdw used with text to_tsvector and custom search configuration
|
Список | 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).
В списке pgsql-bugs по дате отправления:
Следующее
От: Magnus HaganderДата:
Сообщение: Re: Regarding postgresql installation on Google Cloud [Error]