Thread: tsearch2: rebuilding of fulltext index

On Sat, Feb 25, 2006 at 07:26:07PM +0100, me@alternize.com wrote:
> is there an easy way to enforce a full rebuild of a tsearch2
> index? as the index is not really a pgsql index but rather an
> additional field in a record, REINDEX doesn't seem to do the job...

What do you mean by "doesn't seem to do the job"?  What are you
wanting to happen and what actually is happening?  What's the purpose
of what you're calling a "full rebuild"?

Maybe what you're after is a full-table update that will cause the
tsearch2 trigger to fire for every row and regenerate the tsvector.
Something like this:

UPDATE foo SET id = id;

This assumes that you have a tsearch2 trigger on the table.  Do
you?  Or have I misunderstood what you're trying to do?

--
Michael Fuhr

> On Sat, Feb 25, 2006 at 07:26:07PM +0100, me@alternize.com wrote:
>> is there an easy way to enforce a full rebuild of a tsearch2
>> index? as the index is not really a pgsql index but rather an
>> additional field in a record, REINDEX doesn't seem to do the job...
>
> What do you mean by "doesn't seem to do the job"?  What are you
> wanting to happen and what actually is happening?  What's the purpose
> of what you're calling a "full rebuild"?

we have a discussion board whose posts should be indexed by tsearch2. now it
came to our attention, that not all posts seem to be properly indexed (why
so i do not know yet). as i don't know which posts are not index, i just
want to reindex all of them.

> Maybe what you're after is a full-table update that will cause the
> tsearch2 trigger to fire for every row and regenerate the tsvector.
> Something like this:
>
> UPDATE foo SET id = id;
>
> This assumes that you have a tsearch2 trigger on the table.  Do
> you?  Or have I misunderstood what you're trying to do?

that probably work, i was wondering tho if there was a "native" tsearch2
update function that could be used just like a REINDEX; without actually
having to use UPATE.

thanks,
thomas

On Sat, Feb 25, 2006 at 08:03:41PM +0100, me@alternize.com wrote:
> we have a discussion board whose posts should be indexed by tsearch2. now
> it came to our attention, that not all posts seem to be properly indexed
> (why so i do not know yet). as i don't know which posts are not index, i
> just want to reindex all of them.

How do the posts "seem not to be properly indexed"?  What did you
do, what did you expect to happen, and what did happen?  Let's see
if we can identify the problem before trying to solve it.

Are queries not returning messages that you'd expect them to?  If
so then could you post one such query and the row that it should
match but didn't (or did match but shouldn't)?  If that's not the
case then how did you discover the problem?  What unexpected behavior
are you seeing?

--
Michael Fuhr

> On Sat, Feb 25, 2006 at 08:03:41PM +0100, me@alternize.com wrote:
>> we have a discussion board whose posts should be indexed by tsearch2. now
>> it came to our attention, that not all posts seem to be properly indexed
>> (why so i do not know yet). as i don't know which posts are not index, i
>> just want to reindex all of them.
>
> How do the posts "seem not to be properly indexed"?  What did you
> do, what did you expect to happen, and what did happen?  Let's see
> if we can identify the problem before trying to solve it.


already solved - programming error. for tsearch2 we stripe the board message
from html codes for better search results. INSERT did not properly set this
text in auto-generated board topics (we have some content for which board
messages are system-generated).

> Are queries not returning messages that you'd expect them to?  If
> so then could you post one such query and the row that it should
> match but didn't (or did match but shouldn't)?  If that's not the
> case then how did you discover the problem?  What unexpected behavior
> are you seeing?


everything is fine now after fixing the INSERT and doing a dummy UPDATE on
all posts. thanks for your help!

- thomas