Geek Answers Handbook

Why is this full-text PostgreSQL search interrupted on another (RDS) server?

On several development servers, this query returns the expected records:

SELECT name, name_tsv FROM vision WHERE name_tsv @@ plainto_tsquery('Washington Square Park');

name_tsv was originally populated on my dev server with

 UPDATE vision SET name_tsv=to_tsvector(name);

and is updated using a trigger.

I created the same database on an AWS PostgreSQL RDS instance. Both versions of dev and RDS postgres are at level 9.3.1. As far as I can tell, pg_catalog on each one has the same FTS configurations, dictionaries, parsers and templates (by default I didn't mess up at all). Of course, I cannot access the pg conf file on the RDS instance. In this RDS instance, the above query returns 0 records.

I performed this diagnostic request on both:

 SELECT name, name_tsv, to_tsvector(name), plainto_tsquery('Washington Square Park'), name_tsv @@ plainto_tsquery('Washington Square Park') AS matches_stored_name, to_tsvector(name) @@ plainto_tsquery('Washington Square Park') AS matches_fresh_tsvector FROM vision WHERE id_vision = 2977;

Result of an RDS instance:

 "1609: Washington Square Park";"'1609':1 'park':4 'squar':3 'washington':2";"'1609':1 'park':4 'square':3 'washington':2";"'washington' & 'square' & 'park'";f;t

The result of the dev instance:

 "1609: Washington Square Park";"'1609':1 'park':4 'squar':3 'washington':2";"'1609':1 'park':4 'squar':3 'washington':2";"'washington' & 'squar' & 'park'";t;t

From the above, it can be seen that in RDS, to_tsvector () and plainto_tsquery () both do not seem to produce the truncated squar token that they execute on the dev server (the same no-lexeme pattern occurs with other lines). However, I tried to run

 UPDATE vision SET name_tsv=to_tsvector(name);

on the RDS server, but tsv_name has not changed (anyway = "'1609': 1 'park': 4 'squar': 3 'washington': 2").

What can I do on the new RDS server so that the first query returns the expected records in the same way as on my dev server? I think I just need to make plainto_tsquery ("Washington Square Park") normalize tokens into tokens (for example, return "square" not "square"), but I cannot tell from this how to do this.

+6
source share
1 answer

This request:

 SELECT name, name_tsv FROM vision WHERE name_tsv @@ plainto_tsquery('Washington Square Park');

uses the default dictionary. I would say that the default dictionary is different between two machines. Cm:

 regress=> SELECT plainto_tsquery('Washington Square Park'); plainto_tsquery --------------------------------- 'washington' & 'squar' & 'park' (1 row) regress=> SELECT plainto_tsquery('english', 'Washington Square Park'); plainto_tsquery --------------------------------- 'washington' & 'squar' & 'park' (1 row) regress=> SELECT plainto_tsquery('simple', 'Washington Square Park'); plainto_tsquery ---------------------------------- 'washington' & 'square' & 'park' (1 row)

Compare the result of the work:

 SHOW default_text_search_config ;

on both machines. Betcha they are different.

+10
source

More articles:


All Articles