1

I have a full-text search column on one of my tables in PostgreSQL 9.3 implemented based on this tutorial (without the South part).
There is a postgres function where I

SELECT concat_ws(' ', a."my_column1") INTO c1;
SELECT concat_ws(' ', a."my_column2") INTO c2;
RETURN setweight(to_tsvector('pg_catalog.simple', c1), 'C') ||
       setweight(to_tsvector('pg_catalog.english', c2), 'D'); 

This is being written into a column called fts_document in the same table. I have INSERT and UPDATE triggers on this table and a trigger function as in the tutorial.

Everything has worked fine at some point.

After a dump from another db with identical structure and a restore to an empty db I got my table with a gap of the field id from 7306 to 21431. The main problem is that now the full-text-search retrieves documents only from this missing id range and the my_column1 and my_column2 values are very valid ones. I have deleted the index on fts_document column and rebuilt it, but no result.

Where does postgres retrieve these weird ("not existing") rows from? How can I fix the search to retrieve also the existing rows and postgres to show the missing docs?

0

Found my stupid mistake... instead of running the query as my_table.objects.raw(sql_query) I was creating a connection to another db with

conn = psycopg2.connect("dbname='wrong_db' user='my_user' password='my_password'")
cur = conn.cursor() 
cur.execute(sql_query)

so lesson learnt... use the Django built-in capabilities to eliminate the possibility of a connection to a wrong db.

The coincidence that I was getting ids from my gap range was caused by the fact that at some point I had tried to import (unsuccessfully) the entries from wrong_db to my db and this created the gap in ids.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.