We're deployed on RDS using Postgres 11.4, and I'm hoping that there is some way to define a custom thesaurus dictionary. I think the answer is a hard no, based on this StackOverflow thread and this AWS thread.
Is there any way to define a thesaurus within the database, and not from an external file? The docs, even for PG 12, do not suggest anyting of the kind.
I'm asking what sounds like a silly question, so here's what makes it less inane. Following some instructions from Erwin Brandstetter,
I've defined custom dictionary that combines the
simple dictionary with the skip word definitions from the
CREATE TEXT SEARCH DICTIONARY data.simple_skip_dictionary ( TEMPLATE = pg_catalog.simple, STOPWORDS = english ); CREATE TEXT SEARCH CONFIGURATION data.simple_skip (COPY = simple); ALTER TEXT SEARCH CONFIGURATION data.simple_skip ALTER MAPPING FOR asciiword WITH data.simple_skip_dictionary; -- 1, 'Word, all ASCII' CREATE INDEX record_changes_log_detail_old_value_tsv_gin ON record_changes_log_detail USING GIN (to_tsvector('simple_skip', old_value)); SELECT * FROM record_changes_log_detail WHERE to_tsvector('simple_skip', old_value) @@ to_tsquery('simple', 'double-horn');
This all works fine on RDS, and the PG docs here also refer to exteral files. I suspect I'll find I'm out of luck because what I'm looking for is a custom thesaurus dictionary, not leveraging an existing file that may already be defined on RDS.
Does anyone know of a trick to create a custom thesaurus dictionary on RDS, or should I just put this one on the "reaasons not to use RDS" list?
Putting another PG in Front
Assuming (as Laurenz Albe likely confirms) that a custom thesaurus is not possible on a hosted PG without file system access, what about putting another PG in front to generate the text search vector? So, set up one or more PG instances that pre-process the data and then push the vector up to our RDS Postgres?
The question buried in there is "does the thesaurus come into play during the query, or only during the indexing?" The to_tsquery() call obviously wouldn't have access to the thesaurus, but I'm imagining that it might not matter. The query term wouldn't normalize identically, but I'm hoping that's part of the point of the digestion-with-thesaurus process."
I know this question is remedial, I'm just getting my head around FTS in Postgres. It's a pretty substantial topic area and I've only been able to digest the docs in bits and pieces.